MacQueen, Don | 16 Nov 20:11 2012

ROracle 1.1-5 and date fields?

I updated R and packages today, and am seeing some changes in ROracle
behavior that I want to ask about. I'll describe the problems first;
details follow.

1) dbListTables now lists only my personal tables
previously it listed global tables
(don't see any way to show an example of this)

2) simple queries to tables with date fields fail.
For example,

> dbGetQuery(con,"select * from wdrstats")
Error in .oci.GetQuery(conn, statement, ...) :
  Error in try({ : ORA-01805: possible error in date/time operation

The table in this example has, among others, two date fields.
That is, "describe wdrstats;" returns (among others)
EFFDATE             DATE
ENTERED   NOT NULL  DATE
By the way, I'm very happy to see that ROracle is being actively
maintained. Thank you!

Thanks
-Don

-------------------------------
This was a substantial upgrade; previously I had
  ROracle 0.5-12 with R 2.14.1
now I have
  ROracle 1.1-5 and R 2.15.2
(Continue reading)

Benjamin Tyner | 11 Nov 05:10 2012
Picon

RJDBC: dbGetQuery works but fetch does not.

Hello,

When using fetch() with RJDBC, I get the error:

   Error in function (classes, fdef, mtable)  :
     unable to find an inherited method for function "fetch", for 
signature "JDBCResult", "missing"

However if I use dbGetQuery instead, it works splendidly. my driverClass 
is com.sybase.jdbc2.jdbc.SybDriver and I'm using:

   R version 2.14.1 (2011-12-22)
   Platform: x86_64-unknown-linux-gnu (64-bit)
   RJDBC_0.2-0
   rJava_0.9-3
   DBI_0.2-5

Any ideas?

Thanks
Ben
Attachment (smime.p7s): application/x-pkcs7-signature, 5105 bytes
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@...
https://stat.ethz.ch/mailman/listinfo/r-sig-db
ajit aher | 30 Oct 15:01 2012
Picon

Java Exception error while reading large data in R from DB using RJDBC


Dear List, 

Java Exception error while reading large data in R from DB using RJDBC. 
I am trying to read large data from DB table(Vectorwise), using RJDBC connection. 
I have tested the connection with small size data and was able to fetch DB tables using same connection(conn
as in my code).   

Please suggest where  am i going wrong or alternate option to solve such issues  while reading large DB
table. 

drv <- JDBC(paste(db_driver,  sep = ""), 
           paste(db_jar_file,  sep = ""), 
           identifier.quote="`") 

conn <<- dbConnect(drv, paste(db_server,  sep = ""), 
                                        paste(db_server_lgn,  sep = ""), 
                                        paste(db_server_pwd,  sep = "")) 
s <- sprintf("select * from  cypress_modeldev_account_info") 
 temp <- dbGetQuery(conn, s) 
Error in .jcheck() : 
  Java Exception <no description because toString() 
failed>.jcall(rp, "I", "fetch", stride)<S4 object of class 
"jobjRef">

Thanks,
Ajit

	[[alternative HTML version deleted]]

(Continue reading)

NISHIYAMA Tomoaki | 9 Oct 11:21 2012
Picon

Insufficient PROTECT in RMySQL code

Hi,

We recently identified errors in RPostgreSQL that PROTECT is not
issued where needed.  The behaviour reported at
http://code.google.com/p/rpostgresql/issues/detail?id=42
is similar in RMySQL.

The fix is seeking every C code returning a pointer to s_object or 
its aliases (i.e., Mgr_Handle, Con_Handle, Res_Handle, Db_Handle),
and carefully ensure the structure returned is properly constructed in
PROTECTed block.

We do not have test for every places, but the following example
suggests that RMySQL probably has at least one similar error.

> library(RMySQL)
Loading required package: DBI
> con <- dbConnect('MySQL')
> rs <-dbSendQuery(con,'SELECT 1;')
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> gctorture(TRUE)
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> dbColumnInfo(rs)
(Continue reading)

David | 7 Oct 23:16 2012

DB updates with ROracle - ORA-01000

I have an R script that makes an ROracle connection to a DB and pulls
data.. analyzes and then updates approx 7,000 records.

I have two functions defined in my script that look like this:

       # Create the updateTC function
        updateTC <- function(dfrm) {
                with(dfrm, {
                updateDB(paste("update SIMETRA.RTA_MARKET_STATS set
CLOSEST_LOC_TRAVEL_COEFFICIENT = '",format(GEOGRAPHIC_COEFFICIENT,
scientific=FALSE, digits=6),"' where RTA_MARKET_STATS_ID = '",MSID,"'", sep
= ""))
                })
        }

        # Create the updateDB function
        updateDB <- function(stmt) {
                for (update in stmt) {
                        res <- dbSendQuery(conn, update)
                        res <- dbCommit(conn)
                        summary(drv)
                }
        }

and I call it like so (cr is a dataframe with my results):

        # Update the DB with the new geographic coefficients
        updateTC(cr)
        res <- dbCommit(conn)

(Continue reading)

David | 4 Oct 22:44 2012

Intermittent connectivity issues for RJDBC / Oracle

I will forward more details like versions of xyz when i can, but any
thoughts on what I can focus on to debug the problem described below?

Thank you, David
---------- Forwarded message ----------
From: "dh_ss" <david@...>
Date: Oct 4, 2012 1:29 AM
Subject: [R] Intermittent connectivity issues for JDBC / Oracle
To: <r-help@...>

I have been experiencing issues with an R script hanging when connecting to
an Oracle database.  To help debug, I found a function here in the nabble
forum and made a small test script:

  #!/usr/bin/Rscript --no-restore --no-save --no-init-file
  library("RJDBC")
  drv<-JDBC("oracle.jdbc.OracleDriver","/home/oracle/lib/ojdbc6.jar", "'")

  dbCheck = function(url, user='', pwd='') {
    j = .jcall("java/sql/DriverManager", "Ljava/sql/Connection;",
  "getConnection", url, user, pwd, check=FALSE)
    x = .jgetEx()
    .jcheck()
    x
  }
  dbCheck("jdbc:oracle:thin: <at> 192.168.3.10:1521:psrndpt1","myuser","mypass")

When I run the file - sometimes it works, returning NULL, and sometimes it
hangs for between 1 and 2.5 minutes before throwing an error.  The database
is new and stable, on the same network - with no connection issues from any
(Continue reading)

James David Smith | 28 Sep 16:14 2012
Picon

R and PostgreSQL - Writing data?

Dear all,

Sorry if this isn't quite the right place, but it's the first time
I've posted here. My issue is to do with writing to a PostgreSQL
database from within R. My situation is best explained by some R code
to start:

#Connect to the database
con <- dbConnect(PostgreSQL(), user="postgres", password="password",
dbname="database")

#Get some data out of the database.
ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table")

This returns say 10000 rows of data, but there is only data in about
half of those rows. What I want to do is interpolate the missing data
so I do this:

#Generate some data
ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE)

This works well and I now have 10000 rows of data with no empty cells.
I now want to write this back into my PostgresSQL database. Into the
same row that I took the data from in the first place. But I don't
know how. I can write to a new table with something like the below,
but what I'd really like to do is put the data back into the table I
got it from.

# Try to write the data back
dbWriteTable(con, "new_data", ucam_no2$interpolated_data)
(Continue reading)

Paul Gilbert | 18 Sep 17:40 2012
Picon

RMySQL and RPostgreSQL interaction

I am doing some comparison for which I load both RMySQL and RPostgreSQL 
from my own packages TSMySQL and TSPostgreSQL and I am getting the 
following warning building a vignette. I think with package namespaces / 
S4 definitions it is a bit difficult to generate a self contained 
example, at least I have not yet managed to do that, but I would 
appreciate any suggestions about what needs to be done.

Loading required package: TSPostgreSQL
Loading required package: RPostgreSQL
Warning in .simpleDuplicateClass(def, prev) :
   A specification for class "dbObjectId" in package 'RPostgreSQL' seems 
equivalent to one from package 'RMySQL' and is not turning on duplicate 
class definitions for this class

(I'm using  R version 2.15.1 on Ubuntu.)

Thanks,
Paul

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@...
https://stat.ethz.ch/mailman/listinfo/r-sig-db

Luis Ridao | 7 Sep 15:26 2012
Picon

RODBC connection linux (ubuntu)

HI,

i think this problem has come up pretty often on this and other lists
and still have not managed to solved it.

i need to connect to a an Oracle database through R.

I have installed the oracle install client and the package RODBC in R
but still get the same error:

[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data
source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=MAGNUS;UID=my_uid;PWD=my_pwd") :
  ODBC connection failed

/etc/odbc.ini looks like:

odbc.ini
---------------------------------------
[ORACLE_SAMPLE]
driver = Easysoft ODBC-Oracle WP
description = Easysoft Oracle ODBC WP driver
server = oserver
port = 1521
sid = 192.168.20.129/MAGNUS
user = luisr
password =my_pwd
logging = No
logfile =
enable_user_catalog = yes
(Continue reading)

Kate Dresh | 25 Aug 01:10 2012
Picon

data frame remove column

*Hi all,*

I'm trying to filter a file through the columns. This file below is a
example of my data frame. My true data frame has seven hundred thousand
columns and 500 hundred lines. I need to identify and to remove all columns
that all elements equal a number 1. In this my case, columns were deleted
are number 1,5 and 10.

file <-read.table(text="

1  0  2  2  1  1  5  1  1  1

1  0  2  2  1  1  5  1  1  1

1  0  2  2  1  2  5  2  2  1

1  0  2  2  1 1  5  1  1  1

1  0 2  2   1  0  5  0  2  1

1  0  2 2   1  1  5  1  0  1

",sep="",header=FALSE)

the result after the filter will be

0  2  2  1  5  1  1

 0  2  2  1  5  1  1

(Continue reading)

julien cuisinier | 25 Jun 13:46 2012
Picon

FW: Connect R to MySQL DB installed via XAMPP


Hi List,

On windows XP & R2.15. Session info below. 

I installed XAMPP on my machine - I can't install MYSQL on the C:\Program file dir because no admin rights, so
I installed XAMPP onto a personal directory...which I guess makes things slightly harder, but I am
hopeful I am not thhe first one struggling on this!

Any feedback  redirection / hints hugely appreciated

(1) I tried to install RMySQL which I used successsfully on Linux in the past but it seems to be a trouble on
Windows, no binary available on CRAN (anymore?) & when trying the "type = 'source' " option I get the
following: 

> install.packages("RMySQL",type="source")
trying URL 'http://cran.ma.imperial.ac.uk/src/contrib/RMySQL_0.9-3.tar.gz'
Content type 'application/x-gzip' length 165363 bytes (161 Kb)
opened URL
downloaded 161 Kb

'\\UKLonHome.HDS.Int\Home$\julien.cuisinier'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.
* installing *source* package 'RMySQL' ...
** package 'RMySQL' successfully unpacked and MD5 sums checked
ERROR: configuration failed for package 'RMySQL'
* removing 'P:/PersoServer/Programs/R/R-2.15.0/library/RMySQL'

The downloaded source packages are in
(Continue reading)


Gmane