From m@cqueen1 @end|ng |rom ||n|@gov Sat Oct 2 01:57:32 2010 From: m@cqueen1 @end|ng |rom ||n|@gov (MacQueen, Don) Date: Fri, 1 Oct 2010 16:57:32 -0700 Subject: [R-sig-DB] Problem installing Roracle in RHEL5 Message-ID: I?m having trouble installing Roracle_0.5-9 in R version 2.11.1 on a RHEL5 machine. Here is the error message (full transcript at the end of this email): proc CODE=ANSI_C MODE=ORACLE INCLUDE=/usr/lib64/R/include \ PARSE=NONE LINES=false PREFETCH=1 RS-Oracle.pc proc: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory However, the file libclntsh.so.11.1 is present in $ORACLE_HOME/lib, and is not empty. [157]% ls -l $ORACLE_HOME/lib/libclntsh.* lrwxrwxrwx 1 root root 17 Aug 19 14:09 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so -> libclntsh.so.11.1* lrwxrwxrwx 1 root root 17 Aug 19 14:10 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.10.1 -> libclntsh.so.11.1* -rwxr-xr-x 1 oracle oinstall 48724689 Sep 15 2009 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1* I had previously succeeded in installing Roracle on this machine; in fact, it?s still there, but: > require(ROracle) Loading required package: ROracle Error: package 'ROracle' was built before R 2.10.0: please re-install it And I recall running $ORACLE_HOME/bin/genclntsh, which is why the required lib file has non-zero size. Given the error message, perhaps the ROracle installer is not looking in the right place, but if so I haven?t succeeded in ensuring it does. I have set LD_LIBRARY_PATH to include $ORACLE_HOME/lib. Suggestions would be much appreciated. I feel like I?m forgetting something that I?ve done before to install ROracle, but can?t think what it was. -Don Further informaton: > sessionInfo() R version 2.11.1 (2010-05-31) x86_64-redhat-linux-gnu locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 [7] LC_PAPER=en_US.UTF-8 LC_NAME=C [9] LC_ADDRESS=C LC_TELEPHONE=C [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base > install.packages('ROracle') Warning in install.packages("ROracle") : argument 'lib' is missing: using '/usr/lib64/R/library' trying URL 'http://cran.cnr.Berkeley.edu/src/contrib/ROracle_0.5-9.tar.gz' Content type 'application/x-gzip' length 155808 bytes (152 Kb) opened URL ================================================== downloaded 152 Kb Loading required package: rmacq * installing *source* package ? ... checking for gcc... gcc checking for C compiler default output... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ANSI C... none needed checking how to run the C preprocessor... gcc -E configure: creating ./config.status config.status: creating src/Makevars config.status: creating src/Makefile ** libs ** arch - R CMD COMPILE RS-DBI.c make[1]: Entering directory `/tmp/RtmpHDWrkX/R.INSTALL327b23c6/ROracle/src' gcc -m64 -std=gnu99 -I/usr/include/R -DRS_ORA_SQLGLS_WORKAROUND -I/usr/local/include -fpic -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -c RS-DBI.c -o RS-DBI.o RS-DBI.c: In function ?: RS-DBI.c:1175: warning: assignment discards qualifiers from pointer target type RS-DBI.c:1188: warning: implicit declaration of function ? RS-DBI.c: In function ?: RS-DBI.c:1226: warning: assignment discards qualifiers from pointer target type make[1]: Leaving directory `/tmp/RtmpHDWrkX/R.INSTALL327b23c6/ROracle/src' proc CODE=ANSI_C MODE=ORACLE INCLUDE=/usr/lib64/R/include \ PARSE=NONE LINES=false PREFETCH=1 RS-Oracle.pc proc: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory make: *** [RS-Oracle.c] Error 127 ERROR: compilation failed for package ? -- Don MacQueen Environmental Protection Department Lawrence Livermore National Laboratory 925 423-1062 [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Sat Oct 2 15:18:08 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Sat, 02 Oct 2010 08:18:08 -0500 Subject: [R-sig-DB] Problem installing Roracle in RHEL5 In-Reply-To: References: Message-ID: On Oct 1, 2010, at 6:57 PM, MacQueen, Don wrote: > I?m having trouble installing Roracle_0.5-9 in R version 2.11.1 on a RHEL5 machine. > > Here is the error message (full transcript at the end of this email): > > proc CODE=ANSI_C MODE=ORACLE INCLUDE=/usr/lib64/R/include \ > PARSE=NONE LINES=false PREFETCH=1 RS-Oracle.pc > proc: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory > > However, the file libclntsh.so.11.1 is present in $ORACLE_HOME/lib, and is not empty. > > [157]% ls -l $ORACLE_HOME/lib/libclntsh.* > lrwxrwxrwx 1 root root 17 Aug 19 14:09 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so -> libclntsh.so.11.1* > lrwxrwxrwx 1 root root 17 Aug 19 14:10 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.10.1 -> libclntsh.so.11.1* > -rwxr-xr-x 1 oracle oinstall 48724689 Sep 15 2009 /u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1* > > I had previously succeeded in installing Roracle on this machine; in fact, it?s still there, but: >> require(ROracle) > Loading required package: ROracle > Error: package 'ROracle' was built before R 2.10.0: please re-install it > > And I recall running $ORACLE_HOME/bin/genclntsh, which is why the required lib file has non-zero size. > > Given the error message, perhaps the ROracle installer is not looking in the right place, but if so I haven?t succeeded in ensuring it does. I have set LD_LIBRARY_PATH to include $ORACLE_HOME/lib. > > Suggestions would be much appreciated. I feel like I?m forgetting something that I?ve done before to install ROracle, but can?t think what it was. > > -Don Don, I have never used ROracle so may not be able to offer detailed assistance. From what I can tell, the package has not been updated since late 2007, so presumably the package itself is fairly stable and any installation issues are external to the package itself. That being said, there are Warnings present on CRAN for the package, largely related to the help files, which is not a surprise given the increased depth of checking now present. The change in static to dynamic help files is the primary reason for the error you are getting above related to the package being built prior to R 2.10.0. If you have not yet, you may wish to review: http://cran.r-project.org/web/packages/ROracle/INSTALL to see if there are any hints there. One other thing comes to mind, which is the setting of LD_LIBRARY_PATH. Be sure to set that in /etc/ld.so.conf and then run (as root) ldconfig to update the configuration. If you just set LD_LIBRARY_PATH in the shell environment (eg. in the bash profile), it may not get picked up by R. I found that to be an issue using RODBC with Oracle when I was on Fedora. Otherwise, you may wish to contact David James directly to solicit assistance. HTH, Marc Schwartz From @v@m|th @end|ng |rom gm@||@com Tue Oct 5 01:09:13 2010 From: @v@m|th @end|ng |rom gm@||@com (Albert Vernon Smith) Date: Mon, 4 Oct 2010 23:09:13 +0000 Subject: [R-sig-DB] Null values from DBI connection Message-ID: <2D21F3E3-71CF-4AA6-B3A0-1C01FC20D3E6@gmail.com> I am connecting to an Oracle database with RJDBC, and creating a data frame with dbGetQuery. When I get values return, values which are NULL in the database are being returned as 0 from my query. How can I have them returned as ? I am querying as follows. -- require(RJDBC) drv <- JDBC("oracle.jdbc.driver.OracleDriver","/usr/local/ojdbc/ojdbc14.jar","'") conn <- dbConnect(drv, "dbc:oracle:thin:@ADDRESS","user","pass") results <- dbGetQuery("select rownum, col1 from table where col1 is null") -- > results ROWNUM COL1 1 1 0 2 2 0 3 3 0 4 4 0 5 5 0 I'd rather see: > results ROWNUM COL1 1 1 NA 2 2 NA 3 3 NA 4 4 NA 5 5 NA -- -albert From th|@@|@@mvw @end|ng |rom gm@||@com Tue Oct 5 00:15:15 2010 From: th|@@|@@mvw @end|ng |rom gm@||@com (Mike Williamson) Date: Mon, 4 Oct 2010 15:15:15 -0700 Subject: [R-sig-DB] [R] trouble with RODBC -- chopping off part of column names In-Reply-To: <26B2CA6B-1335-41F4-B04E-60AB789691C9@me.com> References: <26B2CA6B-1335-41F4-B04E-60AB789691C9@me.com> Message-ID: Marc, et. al, Below are all of the pertinent version info. However, I think versions, etc., are somewhat irrelevant. Instead, somewhere there must be an environment variable or something that 'R' is talking to that is forcing the column name to fit within a set column width, and I need to either blow away that variable or make it much larger. Again, to recap: if I make SQL queries outside of 'R', I am able to grab column names properly. If I make SQL queries *within 'R' and through a Windows server*, I grab all column names properly. However, if I make SQL queries *within 'R' and through the linux/unix server described below*, the column names are cut off at a fixed length of 30 characters. Any advice as to where to look for this environment variable or whatever setting would help greatly! Thanks! Mike *OS information: *Linux 2.6.18-8.1.15.el5 #1 SMP Mon Oct 22 08:32:28 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux *DB info: *Microsoft SQL Server Management Studio 10.0.2531.0 Microsoft Data Access Components (MDAC) 6.1.7600.16385 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 8.0.7600.16385 Microsoft .NET Framework 2.0.50727.4952 Operating System 6.1.7600 *'R' info: *> R.Version() $platform [1] "x86_64-redhat-linux-gnu" $arch [1] "x86_64" $os [1] "linux-gnu" $system [1] "x86_64, linux-gnu" $status [1] "" $major [1] "2" $minor [1] "10.0" $year [1] "2009" $month [1] "10" $day [1] "26" $`svn rev` [1] "50208" $language [1] "R" $version.string [1] "R version 2.10.0 (2009-10-26)" "Telescopes and bathyscaphes and sonar probes of Scottish lakes, Tacoma Narrows bridge collapse explained with abstract phase-space maps, Some x-ray slides, a music score, Minard's Napoleanic war: The most exciting frontier is charting what's already here." -- xkcd -- Help protect Wikipedia. Donate now: http://wikimediafoundation.org/wiki/Support_Wikipedia/en On Sat, Oct 2, 2010 at 6:31 AM, Marc Schwartz wrote: > On Oct 1, 2010, at 6:26 PM, Mike Williamson wrote: > > > Hello all, > > > > I have a strange / interesting problem that might be 'R' settings > > themselves, or it might be something with the OS. > > > > I am using the RODBC library. I have a script that goes out and, > before > > making a query for a big data set, will first query for the column names > of > > the data set. The column names could sometimes be quite long (e.g., > "Time > > Background Estimation (seconds)" ). If I make this query for the column > > names from my Windows laptop or from a Windows server, using > odbcConnect() & > > sqlQuery(), I get the column names properly. However, if I run this via > > unix, it will chop off part of the column name. (E.g., with "Time > > Background Estimation (seconds)", it becomes "Time Background Estimation > > (se", which is 30 characters long.) > > > > Does anyone have a clue what might be causing this (settings in 'R', > > something within unix, etc.)? I am not even sure how to debug, and I > can't > > really get around this because I cannot simply query all of the columns, > the > > data set would become too large. > > > > Thanks! > > Mike > > > Mike, > > You indicated 'unix' above. Is that Solaris or are you being generic in a > reference to a Linux platform? > > We need the details of your OS, the version of R you are running and > whether it is 32 or 64 bit, as well as the database that you are connecting > to (eg. Oracle). > > You can use: > > vignette("RODBC") > > from the R command line to bring up a PDF carefully written by Prof. > Ripley, that contains additional details the use of RODBC and some OS/DB > specific documentation for the package. > > For further details on how we can better help you, see the R Posting Guide: > > http://www.R-project.org/posting-guide.html > > Lastly, for future reference, there is an R-SIG-DB list: > > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > Marc Schwartz > > [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Tue Oct 5 15:25:14 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Tue, 05 Oct 2010 08:25:14 -0500 Subject: [R-sig-DB] [R] trouble with RODBC -- chopping off part of column names In-Reply-To: References: <26B2CA6B-1335-41F4-B04E-60AB789691C9@me.com> Message-ID: <6CC4C1EA-D9B5-4150-AD32-16DE17842DC3@me.com> On Oct 4, 2010, at 5:15 PM, Mike Williamson wrote: > Marc, et. al, > > Below are all of the pertinent version info. However, I think versions, > etc., are somewhat irrelevant. Instead, somewhere there must be an > environment variable or something that 'R' is talking to that is forcing the > column name to fit within a set column width, and I need to either blow away > that variable or make it much larger. > Again, to recap: if I make SQL queries outside of 'R', I am able to > grab column names properly. If I make SQL queries *within 'R' and through a > Windows server*, I grab all column names properly. However, if I make SQL > queries *within 'R' and through the linux/unix server described below*, the > column names are cut off at a fixed length of 30 characters. > Any advice as to where to look for this environment variable or whatever > setting would help greatly! > > Thanks! > Mike [Moving to R-SIG-DB alone] Mike, I don't have hands on experience with MS SQL Server, so my comments here are based upon my experience with Oracle and RODBC on both Fedora Linux and OSX. With Oracle, the setting of 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery() are required to preclude the corruption of data returned from a query. See ?odbcConnect and ?sqlQuery for more information. This is also covered in the RODBC vignette. There are also some references in the archives to setting 'believeNRows = FALSE' in odbcConnect() and sqlQuery(). This value defaults to TRUE. I do not need this with Oracle, but you may with SQL Server. So you may wish to set all four arguments in your code and then re-run your connection and queries from a clean R session, to see if they have any impact on the returned results. If that helps, then backout each of the settings to see if you can isolate which one seems to be of value. You may also wish to use sqlColumns() to query the table in question to see what it returns for the column names, if different than the results from the above queries. I am not aware of any environment (shell) settings that affect this. At least with Oracle, such settings are solely to define the $PATH for the relevant libraries and config files. HTH, Marc From p@u|@|ergn@n| @end|ng |rom y@hoo@com@@r Tue Oct 5 17:12:44 2010 From: p@u|@|ergn@n| @end|ng |rom y@hoo@com@@r (Paula Fergnani Salvia) Date: Tue, 5 Oct 2010 08:12:44 -0700 (PDT) Subject: [R-sig-DB] Question about assigning values in a matrix, conditional on column first row; how to do the loop. Message-ID: <636877.34610.qm@web110611.mail.gq1.yahoo.com> Hello, I?m new at programming and I will greatly appreciate if you can help me with this. I have a very large matrix (hundreds of rows and columns), with the first raw filled with different numbers (between 0 and 1). The rest of the matrix is filled with values 0, 1, 2. What I need is to replace the values in the matrix (except the first row will has to remain intact). More specifically: in each column I have to replace value 2 for the value of the first row. Also, I want to replace the values 0 and 1 by -999. I was able to write the loop for a vector but I do not know how to expand the loop to the matrix. Please see these examples For a vector I did this and it works > V = c(0.1,1,0,2) > V [1] 0.1 1.0 0.0 2.0 > for (i in 2:length(V)){if(V[i]==2)V[i]=V[1] + else (V[i]=-999)} > V [1] 0.1 -999.0 -999.0 0.1 For a matrix I could not perform the task. Here it is an example of a small matrix and the expected result, but the original has more rows and columns. > matr <- matrix(c(0.1,1,0,2,0.5,1,2,2), nrow=4, ncol=2) > matr [,1] [,2] [1,] 0.1 0.5 [2,] 1.0 1.0 [3,] 0.0 2.0 [4,] 2.0 2.0 > matr.expectedresult= matrix(c(0.1,-999,-999,0.1,0.5,-999,0.5,0.5), nrow=4, ncol=2) > matr.expectedresult [,1] [,2] [1,] 0.1 0.5 [2,] -999.0 -999.0 [3,] -999.0 0.5 [4,] 0.1 0.5 Thank you very much! Paula From j|ngy|@|| @end|ng |rom cred|t-@u|@@e@com Sat Oct 9 00:19:47 2010 From: j|ngy|@|| @end|ng |rom cred|t-@u|@@e@com (Li, Jing Yi) Date: Fri, 8 Oct 2010 18:19:47 -0400 Subject: [R-sig-DB] append rows to Sybase datatable using RJDBC function dbWriteTable In-Reply-To: <55FF8B948E3E4D49BD177B4EF1668A3E03A8495C@EPRI17P32001B.csfb.cs-group.com> References: <55FF8B948E3E4D49BD177B4EF1668A3E03A8495C@EPRI17P32001B.csfb.cs-group.com> Message-ID: <55FF8B948E3E4D49BD177B4EF1668A3E03A8495D@EPRI17P32001B.csfb.cs-group.com> > Hi, > > Does anyone have experience using RJDBC library to write to Sybase > datatable? The jdbc driver I am using is jConnect 6.0. I can > successfully write into a new datatable, but have problem appending > rows to existing one. It either complains "Table *** already exists" > or simply overwrite the existing one. Here is the example. > > I am trying to append "data" to the existing datatable "tmp" > > > data > SecurityID Ticker > 1 12345 AAPL > > > dbWriteTable(conn, "tmp", data, append = T, overwrite = F) > Error in .local(conn, name, value, ...) : > Table `tmp' already exists > > > dbWriteTable(conn, "tmp", data, append = T) > [1] TRUE > > The first dbWriteTable failed as it's complaining "tmp" already > exists, it looks strange to me as append is set to TRUE. The second > dbWriteTable succeeded but it actually overwrite exisiting rows with > "data" which is not expected as append is set to TRUE and overwrite is > default to FALSE. > > Can anyone shed me some lights here? Thanks a lot for your help! > > Jingyi > > > > =============================================================================== Please access the attached hyperlink for an important el...{{dropped:8}} From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sat Oct 9 06:00:13 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Fri, 08 Oct 2010 21:00:13 -0700 Subject: [R-sig-DB] adding to a MySQL database from within R? Message-ID: <4CAFE8CD.3050205@structuremonitoring.com> Hello, All: How can I add rows to a MySQL database? I can do it manually within MySQL server 5.1.50-community. I can connect to it using the RMySQL package. When I tried "dbReadTable {DBI}", I got a warning that "table headers exists: > MySQL. <- MySQL() > MySQLcon <- dbConnect(MySQL., user=User, password=pw, dbname=myDB) > dbWriteTable(MySQLcon, 'headers', a.data.frame) Warning message: In mysqlWriteTable(conn, name, value, ...) : table headers exists in database: aborting mysqlWriteTable I know that table "headers" exists: I want to add to it, not create the entire table from scratch at once. Thanks, Spencer sessionInfo() R version 2.11.1 (2010-05-31) i386-pc-mingw32 locale: [1] LC_COLLATE=English_United States.1252 [2] LC_CTYPE=English_United States.1252 [3] LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C [5] LC_TIME=English_United States.1252 attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] SIM_1.1-5 RCurl_1.4-3 bitops_1.0-4.1 [4] R2HTML_2.1 oce_0.1-80 TSMySQL_2010.5-1 [7] RMySQL_0.7-5 DBI_0.2-5 TSdbi_2010.5-1 [10] tframePlus_2009.10-2 tframe_2009.10-1 loaded via a namespace (and not attached): [1] tools_2.11.1 > -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From |hok @end|ng |rom hotm@||@com Sat Oct 9 06:23:12 2010 From: |hok @end|ng |rom hotm@||@com (Jack Tanner) Date: Sat, 9 Oct 2010 00:23:12 -0400 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <4CAFE8CD.3050205@structuremonitoring.com> References: <4CAFE8CD.3050205@structuremonitoring.com> Message-ID: I think you're looking for an INSERT query, no? On 10/9/2010 12:00 AM, Spencer Graves wrote: > Hello, All: > > > How can I add rows to a MySQL database? > > > I can do it manually within MySQL server 5.1.50-community. I can connect > to it using the RMySQL package. When I tried "dbReadTable {DBI}", I got > a warning that "table headers exists: > > > > MySQL. <- MySQL() > > MySQLcon <- dbConnect(MySQL., user=User, password=pw, dbname=myDB) > > dbWriteTable(MySQLcon, 'headers', a.data.frame) > Warning message: > In mysqlWriteTable(conn, name, value, ...) : > table headers exists in database: aborting mysqlWriteTable > > > I know that table "headers" exists: I want to add to it, not create the > entire table from scratch at once. > > > Thanks, > Spencer > sessionInfo() > R version 2.11.1 (2010-05-31) > i386-pc-mingw32 > > locale: > [1] LC_COLLATE=English_United States.1252 > [2] LC_CTYPE=English_United States.1252 > [3] LC_MONETARY=English_United States.1252 > [4] LC_NUMERIC=C > [5] LC_TIME=English_United States.1252 > > attached base packages: > [1] stats graphics grDevices utils datasets methods base > > other attached packages: > [1] SIM_1.1-5 RCurl_1.4-3 bitops_1.0-4.1 > [4] R2HTML_2.1 oce_0.1-80 TSMySQL_2010.5-1 > [7] RMySQL_0.7-5 DBI_0.2-5 TSdbi_2010.5-1 > [10] tframePlus_2009.10-2 tframe_2009.10-1 > > loaded via a namespace (and not attached): > [1] tools_2.11.1 > > > From @d@v|@2 @end|ng |rom m@||@n|h@gov Sat Oct 9 14:47:23 2010 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Sat, 9 Oct 2010 08:47:23 -0400 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <4CAFE8CD.3050205@structuremonitoring.com> References: <4CAFE8CD.3050205@structuremonitoring.com> Message-ID: On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves < spencer.graves at structuremonitoring.com> wrote: > Hello, All: > > > How can I add rows to a MySQL database? > > > I can do it manually within MySQL server 5.1.50-community. I can > connect to it using the RMySQL package. When I tried "dbReadTable > {DBI}", I got a warning that "table headers exists: > > > > MySQL. <- MySQL() > > MySQLcon <- dbConnect(MySQL., user=User, password=pw, dbname=myDB) > > dbWriteTable(MySQLcon, 'headers', a.data.frame) > Warning message: > In mysqlWriteTable(conn, name, value, ...) : > table headers exists in database: aborting mysqlWriteTable > > > I know that table "headers" exists: I want to add to it, not > create the entire table from scratch at once. > > See the help for dbWriteTable(). In particular, check out the append parameter. Sean [[alternative HTML version deleted]] From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sat Oct 9 15:44:16 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Sat, 09 Oct 2010 06:44:16 -0700 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: References: <4CAFE8CD.3050205@structuremonitoring.com> Message-ID: <4CB071B0.7080202@structuremonitoring.com> Hi, Sean and Jack Tanner: It's hard to RTFM when I don't know which FMTR. Thanks to you both for helping me overcome this problem. 1. Before I submitted this question, I read the help page for "dbWriteTable". Unfortunately, I missed the "append" argument. In this case, I RTFM without seeing what was there! 2. Jack's suggestion led me to try "RSiteSearch('MySQL INSERT')". This led me to a vignette written by Brian Ripley for the RODBC package. I tried other searches without getting an answer. Also, I had previously seen a post where Ripley recommended the RODBC package. However, my previous searched did not help me figure out how to get started with it. Best Wishes, Spencer On 10/9/2010 5:47 AM, Sean Davis wrote: > On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< > spencer.graves at structuremonitoring.com> wrote: > >> Hello, All: >> >> >> How can I add rows to a MySQL database? >> >> >> I can do it manually within MySQL server 5.1.50-community. I can >> connect to it using the RMySQL package. When I tried "dbReadTable >> {DBI}", I got a warning that "table headers exists: >> >> >> > MySQL.<- MySQL() >> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) >> > dbWriteTable(MySQLcon, 'headers', a.data.frame) >> Warning message: >> In mysqlWriteTable(conn, name, value, ...) : >> table headers exists in database: aborting mysqlWriteTable >> >> >> I know that table "headers" exists: I want to add to it, not >> create the entire table from scratch at once. >> >> > See the help for dbWriteTable(). In particular, check out the append > parameter. > > Sean > -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From spocht m@iii@g oii gmx@@t Mon Oct 11 16:19:36 2010 From: spocht m@iii@g oii gmx@@t (spocht m@iii@g oii gmx@@t) Date: Mon, 11 Oct 2010 16:19:36 +0200 Subject: [R-sig-DB] issues compiling RMysql source Message-ID: <20101011141936.49700@gmx.net> Hello, previously I was using Mysql 5.0 and the pre-compiled RMysql library. After updating MySQL to 5.1 I had to compile RMysql from source since I could not find any compiled RMysql libraries. I followed the intallation procedures given on: but was not successful. The R output is just: * installing *source* package 'RMySQL' ... ERROR: configuration failed for package 'RMySQL' I double checked already the value for the MYSQL_HOME environment variable since during installation a editor app is opened giving me the error message: "The syntax for the file name, the directory name or the hard drive is wrong" (translated by myselfe) I thought that the error message might refere to the installation path of Mysql given in the MYSQL_HOME environment variable. But a Sys.getenv('MYSQL_HOME') shows me: "C:/PROGRA~1/MySQL/MYSQLS~1.1/" So I'm lost especially I could not even find a log file to see what the problem might be. Thanks for your help. -- Matthias Wirtz - Karlsruhe, DE Sicherer, schneller und einfacher. Die aktuellen Internet-Browser - jetzt kostenlos herunterladen! http://portal.gmx.net/de/go/atbrowser From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Tue Oct 12 05:00:01 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Mon, 11 Oct 2010 20:00:01 -0700 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: References: <4CAFE8CD.3050205@structuremonitoring.com> Message-ID: <4CB3CF31.7010805@structuremonitoring.com> Hello: How can I connect to a MySQL database with RODBC under Vista? I tried the following, inspired by Ripley's RODBC vignette: > MySQLcon <- odbcConnect('fillmoredb', uid='sbg', pwd='********') Warning messages: 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") : ODBC connection failed > MySQL. <- odbcConnect('MySQL') Warning messages: 1: In odbcDriverConnect("DSN=MySQL") : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed Thanks, Spencer Graves > sessionInfo() R version 2.11.1 (2010-05-31) i386-pc-mingw32 locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C LC_TIME=English_United States.1252 attached base packages: [1] splines stats graphics grDevices utils datasets methods base other attached packages: [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92 loaded via a namespace (and not attached): [1] tools_2.11.1 #### Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.1.50-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ######################## Hi, Sean and Jack Tanner: It's hard to RTFM when I don't know which FMTR. Thanks to you both for helping me overcome this problem. 1. Before I submitted this question, I read the help page for "dbWriteTable". Unfortunately, I missed the "append" argument. In this case, I RTFM without seeing what was there! 2. Jack's suggestion led me to try "RSiteSearch('MySQL INSERT')". This led me to a vignette written by Brian Ripley for the RODBC package. I tried other searches without getting an answer. Also, I had previously seen a post where Ripley recommended the RODBC package. However, my previous searched did not help me figure out how to get started with it. Best Wishes, Spencer On 10/9/2010 5:47 AM, Sean Davis wrote: > On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< > spencer.graves at structuremonitoring.com> wrote: > >> Hello, All: >> >> >> How can I add rows to a MySQL database? >> >> >> I can do it manually within MySQL server 5.1.50-community. I can >> connect to it using the RMySQL package. When I tried "dbReadTable >> {DBI}", I got a warning that "table headers exists: >> >> >> > MySQL.<- MySQL() >> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) >> > dbWriteTable(MySQLcon, 'headers', a.data.frame) >> Warning message: >> In mysqlWriteTable(conn, name, value, ...) : >> table headers exists in database: aborting mysqlWriteTable >> >> >> I know that table "headers" exists: I want to add to it, not >> create the entire table from scratch at once. >> >> > See the help for dbWriteTable(). In particular, check out the append > parameter. > > Sean > -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From edd @end|ng |rom deb|@n@org Tue Oct 12 05:14:45 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Mon, 11 Oct 2010 22:14:45 -0500 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <4CB3CF31.7010805@structuremonitoring.com> References: <4CAFE8CD.3050205@structuremonitoring.com> <4CB3CF31.7010805@structuremonitoring.com> Message-ID: <19635.53925.557551.307196@max.nulle.part> On 11 October 2010 at 20:00, Spencer Graves wrote: | | How can I connect to a MySQL database with RODBC under Vista? I | tried the following, inspired by Ripley's RODBC vignette: | | | > MySQLcon <- odbcConnect('fillmoredb', uid='sbg', pwd='********') | Warning messages: | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) : | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver | Manager] Data source name not found and no default driver specified ^^^^^^^^^^^^^^^^^^^^^^^^^^ You need to define a DSN named 'fillmoredb'. Windows being Windows, there are countless tutorials on the net about how to do this using the admin tools. The nice thing is that process is just about the same for every 'source' providing ODBC connectivity, so once you grok this you can re-use the same trick for non-MySQL-stored data. The not-so-nice thing, in my book, is that you still need to install the odbc drivers and beed to set up the dsn on each machine trying to use it. Dirk | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") : | ODBC connection failed | > MySQL. <- odbcConnect('MySQL') | Warning messages: | 1: In odbcDriverConnect("DSN=MySQL") : | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver | Manager] Data source name not found and no default driver specified | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed | | | Thanks, | Spencer Graves | | | > sessionInfo() | R version 2.11.1 (2010-05-31) | i386-pc-mingw32 | | locale: | [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United | States.1252 LC_MONETARY=English_United States.1252 | [4] LC_NUMERIC=C LC_TIME=English_United | States.1252 | | attached base packages: | [1] splines stats graphics grDevices utils datasets methods | base | | other attached packages: | [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92 | | loaded via a namespace (and not attached): | [1] tools_2.11.1 | | #### | Enter password: ******** | Welcome to the MySQL monitor. Commands end with ; or \g. | Your MySQL connection id is 7 | Server version: 5.1.50-community MySQL Community Server (GPL) | | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. | This software comes with ABSOLUTELY NO WARRANTY. This is free software, | and you are welcome to modify and redistribute it under the GPL v2 license | | Type 'help;' or '\h' for help. Type '\c' to clear the current input | statement. | | mysql> | | ######################## | Hi, Sean and Jack Tanner: | | | | It's hard to RTFM when I don't know which FMTR. Thanks to you | both for helping me overcome this problem. | | | 1. Before I submitted this question, I read the help page | for "dbWriteTable". Unfortunately, I missed the "append" argument. In | this case, I RTFM without seeing what was there! | | | 2. Jack's suggestion led me to try "RSiteSearch('MySQL | INSERT')". This led me to a vignette written by Brian Ripley for the | RODBC package. I tried other searches without getting an answer. Also, | I had previously seen a post where Ripley recommended the RODBC package. | However, my previous searched did not help me figure out how to get | started with it. | | | Best Wishes, | Spencer | | | On 10/9/2010 5:47 AM, Sean Davis wrote: | > On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< | > spencer.graves at structuremonitoring.com> wrote: | > | >> Hello, All: | >> | >> | >> How can I add rows to a MySQL database? | >> | >> | >> I can do it manually within MySQL server 5.1.50-community. I can | >> connect to it using the RMySQL package. When I tried "dbReadTable | >> {DBI}", I got a warning that "table headers exists: | >> | >> | >> > MySQL.<- MySQL() | >> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) | >> > dbWriteTable(MySQLcon, 'headers', a.data.frame) | >> Warning message: | >> In mysqlWriteTable(conn, name, value, ...) : | >> table headers exists in database: aborting mysqlWriteTable | >> | >> | >> I know that table "headers" exists: I want to add to it, not | >> create the entire table from scratch at once. | >> | >> | > See the help for dbWriteTable(). In particular, check out the append | > parameter. | > | > Sean | > | | | -- | Spencer Graves, PE, PhD | President and Chief Operating Officer | Structure Inspection and Monitoring, Inc. | 751 Emerson Ct. | San Jos?, CA 95126 | ph: 408-655-4567 | | _______________________________________________ | R-sig-DB mailing list -- R Special Interest Group | R-sig-DB at stat.math.ethz.ch | https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Tue Oct 12 05:34:50 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Mon, 11 Oct 2010 20:34:50 -0700 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <19635.53925.557551.307196@max.nulle.part> References: <4CAFE8CD.3050205@structuremonitoring.com> <4CB3CF31.7010805@structuremonitoring.com> <19635.53925.557551.307196@max.nulle.part> Message-ID: <4CB3D75A.2070309@structuremonitoring.com> Hi, Dirk, et al.: Thanks for the quick reply. What are the strengths and weaknesses of using RODBC vs. RMySQL? I don't have either doing what I want yet, but I think I'm closer with RMySQL than RODBC. On the other hand, I had the impression that RODBC was better developed than RMySQL and therefore RODBC might be faster and ultimately easier. Thanks again. Spencer Graves On 10/11/2010 8:14 PM, Dirk Eddelbuettel wrote: > On 11 October 2010 at 20:00, Spencer Graves wrote: > | > | How can I connect to a MySQL database with RODBC under Vista? I > | tried the following, inspired by Ripley's RODBC vignette: > | > | > |> MySQLcon<- odbcConnect('fillmoredb', uid='sbg', pwd='********') > | Warning messages: > | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) : > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver > | Manager] Data source name not found and no default driver specified > ^^^^^^^^^^^^^^^^^^^^^^^^^^ > > You need to define a DSN named 'fillmoredb'. Windows being Windows, there > are countless tutorials on the net about how to do this using the admin tools. > > The nice thing is that process is just about the same for every 'source' > providing ODBC connectivity, so once you grok this you can re-use the same > trick for non-MySQL-stored data. > > The not-so-nice thing, in my book, is that you still need to install the odbc > drivers and beed to set up the dsn on each machine trying to use it. > > Dirk > > | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") : > | ODBC connection failed > |> MySQL.<- odbcConnect('MySQL') > | Warning messages: > | 1: In odbcDriverConnect("DSN=MySQL") : > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver > | Manager] Data source name not found and no default driver specified > | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed > | > | > | Thanks, > | Spencer Graves > | > | > |> sessionInfo() > | R version 2.11.1 (2010-05-31) > | i386-pc-mingw32 > | > | locale: > | [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United > | States.1252 LC_MONETARY=English_United States.1252 > | [4] LC_NUMERIC=C LC_TIME=English_United > | States.1252 > | > | attached base packages: > | [1] splines stats graphics grDevices utils datasets methods > | base > | > | other attached packages: > | [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92 > | > | loaded via a namespace (and not attached): > | [1] tools_2.11.1 > | > | #### > | Enter password: ******** > | Welcome to the MySQL monitor. Commands end with ; or \g. > | Your MySQL connection id is 7 > | Server version: 5.1.50-community MySQL Community Server (GPL) > | > | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. > | This software comes with ABSOLUTELY NO WARRANTY. This is free software, > | and you are welcome to modify and redistribute it under the GPL v2 license > | > | Type 'help;' or '\h' for help. Type '\c' to clear the current input > | statement. > | > | mysql> > | > | ######################## > | Hi, Sean and Jack Tanner: > | > | > | > | It's hard to RTFM when I don't know which FMTR. Thanks to you > | both for helping me overcome this problem. > | > | > | 1. Before I submitted this question, I read the help page > | for "dbWriteTable". Unfortunately, I missed the "append" argument. In > | this case, I RTFM without seeing what was there! > | > | > | 2. Jack's suggestion led me to try "RSiteSearch('MySQL > | INSERT')". This led me to a vignette written by Brian Ripley for the > | RODBC package. I tried other searches without getting an answer. Also, > | I had previously seen a post where Ripley recommended the RODBC package. > | However, my previous searched did not help me figure out how to get > | started with it. > | > | > | Best Wishes, > | Spencer > | > | > | On 10/9/2010 5:47 AM, Sean Davis wrote: > |> On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< > |> spencer.graves at structuremonitoring.com> wrote: > |> > |>> Hello, All: > |>> > |>> > |>> How can I add rows to a MySQL database? > |>> > |>> > |>> I can do it manually within MySQL server 5.1.50-community. I can > |>> connect to it using the RMySQL package. When I tried "dbReadTable > |>> {DBI}", I got a warning that "table headers exists: > |>> > |>> > |>> > MySQL.<- MySQL() > |>> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) > |>> > dbWriteTable(MySQLcon, 'headers', a.data.frame) > |>> Warning message: > |>> In mysqlWriteTable(conn, name, value, ...) : > |>> table headers exists in database: aborting mysqlWriteTable > |>> > |>> > |>> I know that table "headers" exists: I want to add to it, not > |>> create the entire table from scratch at once. > |>> > |>> > |> See the help for dbWriteTable(). In particular, check out the append > |> parameter. > |> > |> Sean > |> > | > | > | -- > | Spencer Graves, PE, PhD > | President and Chief Operating Officer > | Structure Inspection and Monitoring, Inc. > | 751 Emerson Ct. > | San Jos?, CA 95126 > | ph: 408-655-4567 > | > | _______________________________________________ > | R-sig-DB mailing list -- R Special Interest Group > | R-sig-DB at stat.math.ethz.ch > | https://stat.ethz.ch/mailman/listinfo/r-sig-db > From edd @end|ng |rom deb|@n@org Tue Oct 12 13:24:18 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Tue, 12 Oct 2010 06:24:18 -0500 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <4CB3D75A.2070309@structuremonitoring.com> References: <4CAFE8CD.3050205@structuremonitoring.com> <4CB3CF31.7010805@structuremonitoring.com> <19635.53925.557551.307196@max.nulle.part> <4CB3D75A.2070309@structuremonitoring.com> Message-ID: <19636.17762.446930.940557@max.nulle.part> On 11 October 2010 at 20:34, Spencer Graves wrote: | Hi, Dirk, et al.: | | | Thanks for the quick reply. | | | What are the strengths and weaknesses of using RODBC vs. RMySQL? That is also a question which have been debated to death before on the Intertubes or this list. Do some searches. In short, and as I see it, - ODBC is more generic so what you do here may work similarly with other software providing ODBC interfaces. - Specific binary interface can be faster, sometimes by a lot. - For R in particular, RMySQL supports the DBI interface so here the situation is the inverse of the general solution: you may use what you did with RMySQL again with RSQLite or RPostgreSQL or ... | I don't have either doing what I want yet, but I think I'm closer | with RMySQL than RODBC. On the other hand, I had the impression that | RODBC was better developed than RMySQL and therefore RODBC might be | faster and ultimately easier. Unsure. They are both exceedingly mature packages that have been around well over half a decade. From what I understand RMySQL has its headaches with different MySQL versions as well as Windows builds -- but I never had an issue. Dirk | | Thanks again. | Spencer Graves | | | On 10/11/2010 8:14 PM, Dirk Eddelbuettel wrote: | > On 11 October 2010 at 20:00, Spencer Graves wrote: | > | | > | How can I connect to a MySQL database with RODBC under Vista? I | > | tried the following, inspired by Ripley's RODBC vignette: | > | | > | | > |> MySQLcon<- odbcConnect('fillmoredb', uid='sbg', pwd='********') | > | Warning messages: | > | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) : | > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver | > | Manager] Data source name not found and no default driver specified | > ^^^^^^^^^^^^^^^^^^^^^^^^^^ | > | > You need to define a DSN named 'fillmoredb'. Windows being Windows, there | > are countless tutorials on the net about how to do this using the admin tools. | > | > The nice thing is that process is just about the same for every 'source' | > providing ODBC connectivity, so once you grok this you can re-use the same | > trick for non-MySQL-stored data. | > | > The not-so-nice thing, in my book, is that you still need to install the odbc | > drivers and beed to set up the dsn on each machine trying to use it. | > | > Dirk | > | > | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") : | > | ODBC connection failed | > |> MySQL.<- odbcConnect('MySQL') | > | Warning messages: | > | 1: In odbcDriverConnect("DSN=MySQL") : | > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver | > | Manager] Data source name not found and no default driver specified | > | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed | > | | > | | > | Thanks, | > | Spencer Graves | > | | > | | > |> sessionInfo() | > | R version 2.11.1 (2010-05-31) | > | i386-pc-mingw32 | > | | > | locale: | > | [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United | > | States.1252 LC_MONETARY=English_United States.1252 | > | [4] LC_NUMERIC=C LC_TIME=English_United | > | States.1252 | > | | > | attached base packages: | > | [1] splines stats graphics grDevices utils datasets methods | > | base | > | | > | other attached packages: | > | [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92 | > | | > | loaded via a namespace (and not attached): | > | [1] tools_2.11.1 | > | | > | #### | > | Enter password: ******** | > | Welcome to the MySQL monitor. Commands end with ; or \g. | > | Your MySQL connection id is 7 | > | Server version: 5.1.50-community MySQL Community Server (GPL) | > | | > | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. | > | This software comes with ABSOLUTELY NO WARRANTY. This is free software, | > | and you are welcome to modify and redistribute it under the GPL v2 license | > | | > | Type 'help;' or '\h' for help. Type '\c' to clear the current input | > | statement. | > | | > | mysql> | > | | > | ######################## | > | Hi, Sean and Jack Tanner: | > | | > | | > | | > | It's hard to RTFM when I don't know which FMTR. Thanks to you | > | both for helping me overcome this problem. | > | | > | | > | 1. Before I submitted this question, I read the help page | > | for "dbWriteTable". Unfortunately, I missed the "append" argument. In | > | this case, I RTFM without seeing what was there! | > | | > | | > | 2. Jack's suggestion led me to try "RSiteSearch('MySQL | > | INSERT')". This led me to a vignette written by Brian Ripley for the | > | RODBC package. I tried other searches without getting an answer. Also, | > | I had previously seen a post where Ripley recommended the RODBC package. | > | However, my previous searched did not help me figure out how to get | > | started with it. | > | | > | | > | Best Wishes, | > | Spencer | > | | > | | > | On 10/9/2010 5:47 AM, Sean Davis wrote: | > |> On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< | > |> spencer.graves at structuremonitoring.com> wrote: | > |> | > |>> Hello, All: | > |>> | > |>> | > |>> How can I add rows to a MySQL database? | > |>> | > |>> | > |>> I can do it manually within MySQL server 5.1.50-community. I can | > |>> connect to it using the RMySQL package. When I tried "dbReadTable | > |>> {DBI}", I got a warning that "table headers exists: | > |>> | > |>> | > |>> > MySQL.<- MySQL() | > |>> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) | > |>> > dbWriteTable(MySQLcon, 'headers', a.data.frame) | > |>> Warning message: | > |>> In mysqlWriteTable(conn, name, value, ...) : | > |>> table headers exists in database: aborting mysqlWriteTable | > |>> | > |>> | > |>> I know that table "headers" exists: I want to add to it, not | > |>> create the entire table from scratch at once. | > |>> | > |>> | > |> See the help for dbWriteTable(). In particular, check out the append | > |> parameter. | > |> | > |> Sean | > |> | > | | > | | > | -- | > | Spencer Graves, PE, PhD | > | President and Chief Operating Officer | > | Structure Inspection and Monitoring, Inc. | > | 751 Emerson Ct. | > | San Jos?, CA 95126 | > | ph: 408-655-4567 | > | | > | _______________________________________________ | > | R-sig-DB mailing list -- R Special Interest Group | > | R-sig-DB at stat.math.ethz.ch | > | https://stat.ethz.ch/mailman/listinfo/r-sig-db | > -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Tue Oct 12 16:32:42 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Tue, 12 Oct 2010 07:32:42 -0700 Subject: [R-sig-DB] adding to a MySQL database from within R? In-Reply-To: <19636.17762.446930.940557@max.nulle.part> References: <4CAFE8CD.3050205@structuremonitoring.com> <4CB3CF31.7010805@structuremonitoring.com> <19635.53925.557551.307196@max.nulle.part> <4CB3D75A.2070309@structuremonitoring.com> <19636.17762.446930.940557@max.nulle.part> Message-ID: <4CB4718A.9060602@structuremonitoring.com> Hi, Dirk: Thanks for the reply. That gives me search terms for how to look for more information and encourages me to try first with the MySQL / DBI solution that I think I almost have working. Best Wishes, Spencer On 10/12/2010 4:24 AM, Dirk Eddelbuettel wrote: > On 11 October 2010 at 20:34, Spencer Graves wrote: > | Hi, Dirk, et al.: > | > | > | Thanks for the quick reply. > | > | > | What are the strengths and weaknesses of using RODBC vs. RMySQL? > > That is also a question which have been debated to death before on the > Intertubes or this list. Do some searches. > > In short, and as I see it, > > - ODBC is more generic so what you do here may work similarly with other > software providing ODBC interfaces. > > - Specific binary interface can be faster, sometimes by a lot. > > - For R in particular, RMySQL supports the DBI interface so here the > situation is the inverse of the general solution: you may use what you > did with RMySQL again with RSQLite or RPostgreSQL or ... > > | I don't have either doing what I want yet, but I think I'm closer > | with RMySQL than RODBC. On the other hand, I had the impression that > | RODBC was better developed than RMySQL and therefore RODBC might be > | faster and ultimately easier. > > Unsure. They are both exceedingly mature packages that have been around well > over half a decade. From what I understand RMySQL has its headaches with > different MySQL versions as well as Windows builds -- but I never had an > issue. > > Dirk > > | > | Thanks again. > | Spencer Graves > | > | > | On 10/11/2010 8:14 PM, Dirk Eddelbuettel wrote: > |> On 11 October 2010 at 20:00, Spencer Graves wrote: > |> | > |> | How can I connect to a MySQL database with RODBC under Vista? I > |> | tried the following, inspired by Ripley's RODBC vignette: > |> | > |> | > |> |> MySQLcon<- odbcConnect('fillmoredb', uid='sbg', pwd='********') > |> | Warning messages: > |> | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) : > |> | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver > |> | Manager] Data source name not found and no default driver specified > |> ^^^^^^^^^^^^^^^^^^^^^^^^^^ > |> > |> You need to define a DSN named 'fillmoredb'. Windows being Windows, there > |> are countless tutorials on the net about how to do this using the admin tools. > |> > |> The nice thing is that process is just about the same for every 'source' > |> providing ODBC connectivity, so once you grok this you can re-use the same > |> trick for non-MySQL-stored data. > |> > |> The not-so-nice thing, in my book, is that you still need to install the odbc > |> drivers and beed to set up the dsn on each machine trying to use it. > |> > |> Dirk > |> > |> | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") : > |> | ODBC connection failed > |> |> MySQL.<- odbcConnect('MySQL') > |> | Warning messages: > |> | 1: In odbcDriverConnect("DSN=MySQL") : > |> | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver > |> | Manager] Data source name not found and no default driver specified > |> | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed > |> | > |> | > |> | Thanks, > |> | Spencer Graves > |> | > |> | > |> |> sessionInfo() > |> | R version 2.11.1 (2010-05-31) > |> | i386-pc-mingw32 > |> | > |> | locale: > |> | [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United > |> | States.1252 LC_MONETARY=English_United States.1252 > |> | [4] LC_NUMERIC=C LC_TIME=English_United > |> | States.1252 > |> | > |> | attached base packages: > |> | [1] splines stats graphics grDevices utils datasets methods > |> | base > |> | > |> | other attached packages: > |> | [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92 > |> | > |> | loaded via a namespace (and not attached): > |> | [1] tools_2.11.1 > |> | > |> | #### > |> | Enter password: ******** > |> | Welcome to the MySQL monitor. Commands end with ; or \g. > |> | Your MySQL connection id is 7 > |> | Server version: 5.1.50-community MySQL Community Server (GPL) > |> | > |> | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. > |> | This software comes with ABSOLUTELY NO WARRANTY. This is free software, > |> | and you are welcome to modify and redistribute it under the GPL v2 license > |> | > |> | Type 'help;' or '\h' for help. Type '\c' to clear the current input > |> | statement. > |> | > |> | mysql> > |> | > |> | ######################## > |> | Hi, Sean and Jack Tanner: > |> | > |> | > |> | > |> | It's hard to RTFM when I don't know which FMTR. Thanks to you > |> | both for helping me overcome this problem. > |> | > |> | > |> | 1. Before I submitted this question, I read the help page > |> | for "dbWriteTable". Unfortunately, I missed the "append" argument. In > |> | this case, I RTFM without seeing what was there! > |> | > |> | > |> | 2. Jack's suggestion led me to try "RSiteSearch('MySQL > |> | INSERT')". This led me to a vignette written by Brian Ripley for the > |> | RODBC package. I tried other searches without getting an answer. Also, > |> | I had previously seen a post where Ripley recommended the RODBC package. > |> | However, my previous searched did not help me figure out how to get > |> | started with it. > |> | > |> | > |> | Best Wishes, > |> | Spencer > |> | > |> | > |> | On 10/9/2010 5:47 AM, Sean Davis wrote: > |> |> On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves< > |> |> spencer.graves at structuremonitoring.com> wrote: > |> |> > |> |>> Hello, All: > |> |>> > |> |>> > |> |>> How can I add rows to a MySQL database? > |> |>> > |> |>> > |> |>> I can do it manually within MySQL server 5.1.50-community. I can > |> |>> connect to it using the RMySQL package. When I tried "dbReadTable > |> |>> {DBI}", I got a warning that "table headers exists: > |> |>> > |> |>> > |> |>> > MySQL.<- MySQL() > |> |>> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB) > |> |>> > dbWriteTable(MySQLcon, 'headers', a.data.frame) > |> |>> Warning message: > |> |>> In mysqlWriteTable(conn, name, value, ...) : > |> |>> table headers exists in database: aborting mysqlWriteTable > |> |>> > |> |>> > |> |>> I know that table "headers" exists: I want to add to it, not > |> |>> create the entire table from scratch at once. > |> |>> > |> |>> > |> |> See the help for dbWriteTable(). In particular, check out the append > |> |> parameter. > |> |> > |> |> Sean > |> |> > |> | > |> | > |> | -- > |> | Spencer Graves, PE, PhD > |> | President and Chief Operating Officer > |> | Structure Inspection and Monitoring, Inc. > |> | 751 Emerson Ct. > |> | San Jos?, CA 95126 > |> | ph: 408-655-4567 > |> | > |> | _______________________________________________ > |> | R-sig-DB mailing list -- R Special Interest Group > |> | R-sig-DB at stat.math.ethz.ch > |> | https://stat.ethz.ch/mailman/listinfo/r-sig-db > |> > -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From n||z@b@rro@ @end|ng |rom gm@||@com Sat Oct 16 04:39:40 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Fri, 15 Oct 2010 23:39:40 -0300 Subject: [R-sig-DB] [R] Rmysql - dbWritetable In-Reply-To: References: Message-ID: Dear Rusers, I am trying to feed my database with data from a file. But since my file (2010101000.txt) there`s no headers I am facing problem because the result data.frame is not separated my columns. How could I set variables names for each columns in *dbWriteTable*? I have tried the command below but I don`t know how to fill the *field.type*option. dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = "\t",row.names=c("V01", "V02", "V03", "V04", "V05", "V06"," V07","V08", "V09", "V10", "V11", "V12", "V13", "V14"),field.type=????) ===Below what I have been used. I need to know how to set the column names to xx (my dataframe). > drv=dbDriver("MySQL") > con <- dbConnect(drv,dbname='buoy',user="xx",password="xxx") > dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = "\t", head = F) [1] TRUE > > *xx*<- dbGetQuery(con, "SELECT * FROM b20101010 ") > xx * V1 *1 00859 2010 10 10 00 50 -41.51 6.83 -9999 1016 -9999 -9999.0 -9999.0 -9999.0 2 00859 2010 10 09 22 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 -9999.0 3 00707 2010 10 09 23 00 -34.04 -37.63 -9999 1025 -9999 -9999.0 -9999.0 -9999.0 4 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 -9999.0 5 00859 2010 10 09 23 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 -9999.0 6 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 -9999.0 7 00617 2010 10 09 23 20 -18.43 -23.09 -9999 1023 -9999 -9999.0 -9999.0 -9999.0 8 00707 2010 10 10 00 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 -9999.0 9 00707 2010 10 10 01 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 -9999.0 *> length(xx) [1] 1 * -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sat Oct 16 05:16:47 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Fri, 15 Oct 2010 20:16:47 -0700 Subject: [R-sig-DB] [R] Rmysql - dbWritetable In-Reply-To: References: Message-ID: <4CB9191F.2060100@structuremonitoring.com> Hi, Nilza: Did you receive my reply to your earlier post to R-Help, which looks to me identical to this (https://stat.ethz.ch/pipermail/r-help/2010-October/256012.html)? I'd like to know if you tried what I suggested there. If you did, what were the results? If not, why not? PLEASE do read the posting guide "www.R-project.org/posting-guide.html" and provide commented, minimal, self-contained, reproducible code. Your example is not completely self contained. It might help if you include "sessionInfo()". In my previous reply, there are help pages documenting "dbWriteTable" in 10 different packages. I've not used "dbWriteTable" very much, but it might help to know which of these 10 packages you are using. I've found that following the posting guide often helps me find a solution to my problem. If I still can't solve the problem, this often helps me pose the question in a way more likely to generate an answer that actually helps me solve the problem. I'm sorry if this is not any more helpful than my previous reply. Spencer Graves On 10/15/2010 7:39 PM, Nilza BARROS wrote: > Dear Rusers, > > I am trying to feed my database with data from a file. > > But since my file (2010101000.txt) there`s no headers I am facing problem > because the > result data.frame is not separated my columns. > > How could I set variables names for each columns in *dbWriteTable*? I have > tried the command below but I don`t know how to fill the *field.type*option. > > > dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = > "\t",row.names=c("V01", "V02", "V03", "V04", "V05", "V06"," V07","V08", > "V09", "V10", "V11", "V12", "V13", "V14"),field.type=????) > > ===Below what I have been used. I need to know how to set the column names > to xx (my dataframe). > >> drv=dbDriver("MySQL") >> con<- dbConnect(drv,dbname='buoy',user="xx",password="xxx") >> dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = > "\t", head = F) > [1] TRUE >> *xx*<- dbGetQuery(con, "SELECT * FROM b20101010 ") >> xx > * V1 > *1 00859 2010 10 10 00 50 -41.51 6.83 -9999 1016 -9999 -9999.0 > -9999.0 -9999.0 > 2 00859 2010 10 09 22 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 > -9999.0 > 3 00707 2010 10 09 23 00 -34.04 -37.63 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > 4 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 > -9999.0 > 5 00859 2010 10 09 23 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 > -9999.0 > 6 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 > -9999.0 > 7 00617 2010 10 09 23 20 -18.43 -23.09 -9999 1023 -9999 -9999.0 -9999.0 > -9999.0 > 8 00707 2010 10 10 00 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > 9 00707 2010 10 10 01 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > *> length(xx) > [1] 1 > * > > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db [[alternative HTML version deleted]] From n||z@b@rro@ @end|ng |rom gm@||@com Sun Oct 17 01:31:49 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Sat, 16 Oct 2010 20:31:49 -0300 Subject: [R-sig-DB] [R] Rmysql - dbWritetable In-Reply-To: <4CB9191F.2060100@structuremonitoring.com> References: <4CB9191F.2060100@structuremonitoring.com> Message-ID: Dear Spencer, Thank you for your help. I read your email and that`s the reason why I sent an email to r-sig-db as you suggested. But I have just realized that you suggested the command below too. I`ve tried it and that`s fine. I was waiting to solve my problem to reply the email to r-help group. That`s what I am doing now! Thank you!! x2010 <- read.table('2010101000.txt') dbWriteTable(con, "b20101010", x2010, overwrite = T) xx<- dbGetQuery(con, "SELECT * FROM b20101010 ") *> dim(xx) [1] 338 17 * row_names V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 1 1 82824 2010 10 10 0 0 -63.91 -8.76 102 -9999 1002 1012 50 10.3 2 2 5125 2010 10 10 0 0 -46.90 -19.60 1000 -9999 903 1015 117 1.8 3 3 9223 2010 10 10 0 0 -54.60 -16.40 284 -9999 980 1012 224 2.3 4 4 8 2010 10 10 0 0 -48.10 -15.90 1000 -9999 890 999 290 2.6 5 5 5133 2010 10 10 0 0 -43.80 -20.60 1000 -9999 902 1016 120 3.8 6 6 5147 2010 10 10 0 0 -41.50 -17.90 475 -9999 965 1020 65 4.8 V15 V16 1 297.1 295.4 2 293.4 282.1 3 300.9 280.8 4 295.8 288.9 5 288.1 283.6 6 294.4 289.2 On Sat, Oct 16, 2010 at 12:16 AM, Spencer Graves < spencer.graves at structuremonitoring.com> wrote: > Hi, Nilza: > > > Did you receive my reply to your earlier post to R-Help, which looks > to me identical to this ( > https://stat.ethz.ch/pipermail/r-help/2010-October/256012.html)? I'd like > to know if you tried what I suggested there. If you did, what were the > results? If not, why not? > > > PLEASE do read the posting guide " > www.R-project.org/posting-guide.html" > and provide commented, minimal, self-contained, reproducible code. Your > example is not completely self contained. It might help if you include > "sessionInfo()". In my previous reply, there are help pages documenting > "dbWriteTable" in 10 different packages. I've not used "dbWriteTable" very > much, but it might help to know which of these 10 packages you are using. > > > > I've found that following the posting guide often helps me find a > solution to my problem. If I still can't solve the problem, this often > helps me pose the question in a way more likely to generate an answer that > actually helps me solve the problem. > > > I'm sorry if this is not any more helpful than my previous reply. > Spencer Graves > > > On 10/15/2010 7:39 PM, Nilza BARROS wrote: > > Dear Rusers, > > I am trying to feed my database with data from a file. > > But since my file (2010101000.txt) there`s no headers I am facing problem > because the > result data.frame is not separated my columns. > > How could I set variables names for each columns in *dbWriteTable*? I have > tried the command below but I don`t know how to fill the *field.type*option. > > > dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = > "\t",row.names=c("V01", "V02", "V03", "V04", "V05", "V06"," V07","V08", > "V09", "V10", "V11", "V12", "V13", "V14"),field.type=????) > > ===Below what I have been used. I need to know how to set the column names > to xx (my dataframe). > > > drv=dbDriver("MySQL") > con <- dbConnect(drv,dbname='buoy',user="xx",password="xxx") > dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite = T, sep = > > "\t", head = F) > [1] TRUE > > *xx*<- dbGetQuery(con, "SELECT * FROM b20101010 ") > xx > > * V1 > *1 00859 2010 10 10 00 50 -41.51 6.83 -9999 1016 -9999 -9999.0 > -9999.0 -9999.0 > 2 00859 2010 10 09 22 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 > -9999.0 > 3 00707 2010 10 09 23 00 -34.04 -37.63 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > 4 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 > -9999.0 > 5 00859 2010 10 09 23 50 -41.51 6.81 -9999 1015 -9999 -9999.0 -9999.0 > -9999.0 > 6 00005 2010 10 09 23 00 -34.69 -18.89 -9999 1017 207 7.2 297.1 > -9999.0 > 7 00617 2010 10 09 23 20 -18.43 -23.09 -9999 1023 -9999 -9999.0 -9999.0 > -9999.0 > 8 00707 2010 10 10 00 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > 9 00707 2010 10 10 01 00 -34.03 -37.62 -9999 1025 -9999 -9999.0 -9999.0 > -9999.0 > *> length(xx) > [1] 1 > * > > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest GroupR-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > > -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From momb@ch @end|ng |rom hotm@||@com Mon Oct 18 03:35:27 2010 From: momb@ch @end|ng |rom hotm@||@com (Maciej Ombach) Date: Sun, 17 Oct 2010 21:35:27 -0400 Subject: [R-sig-DB] RODBC: how to view multiple objects returned by a stored procedure? Message-ID: I am trying to use a stored procedure (MS SQL Server 2005) that requires an xml input (R 2.11.0, RODBC 1.3-2). This stored procedure returns a header/metadata and a data table (which I verified in DBArtisan). My R code looks like this: library(RODBC) query <- " exec sp_GetIndexData @ApplicationName='XXXXX', @UserName='XXXXX', @XMLInput=N'
' " query1 <- gsub("\n", "", query) dbSource <- odbcConnect("XXX", uid = "xxx", pwd = "xxx") out <- sqlQuery(dbSource, query1) odbcClose(dbSource) This and other slightly modified calls return an empty character string (out). I am able to run simple select queries against this database, so the connection is working. The stored proc also works in Excel VBA, where it returns two recordsets: the first one is empty (header?), the second one has the data I need (in VBA, rs.NextRecordset allows to loop through recordsets). At this point, my best guess is that something similar is happening in R. I am getting back an empty header and need to figure out how to view the actual data table. Any suggestions would be much appreciated. Maciek [[alternative HTML version deleted]] From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Mon Oct 18 08:20:30 2010 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Mon, 18 Oct 2010 07:20:30 +0100 (BST) Subject: [R-sig-DB] RODBC: how to view multiple objects returned by a stored procedure? In-Reply-To: References: Message-ID: Sorry, this is a question about ODBC, not R. On Sun, 17 Oct 2010, Maciej Ombach wrote: > > > I am trying to use a stored procedure (MS SQL Server 2005) that requires an xml input (R 2.11.0, RODBC 1.3-2). This stored procedure returns a header/metadata and a data table (which I verified in DBArtisan). > > My R code looks like this: > > library(RODBC) > query <- " > exec sp_GetIndexData @ApplicationName='XXXXX', @UserName='XXXXX', > @XMLInput=N' >
> > > > ' > " > query1 <- gsub("\n", "", query) > dbSource <- odbcConnect("XXX", uid = "xxx", pwd = "xxx") > out <- sqlQuery(dbSource, query1) > odbcClose(dbSource) > > This and other slightly modified calls return an empty character string (out). I am able to run simple select queries against this database, so the connection is working. The stored proc also works in Excel VBA, where it returns two recordsets: the first one is empty (header?), the second one has the data I need (in VBA, rs.NextRecordset allows to loop through recordsets). > > At this point, my best guess is that something similar is happening in R. I am getting back an empty header and need to figure out how to view the actual data table. Any suggestions would be much appreciated. > > Maciek > > > > [[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > -- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 From ohr|2007 @end|ng |rom gm@||@com Sun Oct 24 20:10:06 2010 From: ohr|2007 @end|ng |rom gm@||@com (Ajay Ohri) Date: Sun, 24 Oct 2010 23:40:06 +0530 Subject: [R-sig-DB] [RPostgreSQL] Unable to find Message-ID: Hi I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. Is the package still available and working? Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and which one is recommended for PostgresSQL databases. ( I couldnt find rdbi as well) I am using PostgreSql 9.0 Regards Ajay I get the error message- Websites- http://decisionstats.com http://dudeofdata.com Linkedin- www.linkedin.com/in/ajayohri [[alternative HTML version deleted]] From ne||t @end|ng |rom ne||t||||n@com Sun Oct 24 20:20:57 2010 From: ne||t @end|ng |rom ne||t||||n@com (Neil Tiffin) Date: Sun, 24 Oct 2010 13:20:57 -0500 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: Message-ID: http://cran.r-project.org/web/packages/RPostgreSQL/index.html It is source only and a new version was released 17 Oct 2010. You will need to compile from the source. Neil On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: > Hi > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. > > Is the package still available and working? > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi as > well) > > I am using PostgreSql 9.0 > > > > Regards > > Ajay > > > > > I get the error message- > > Websites- > http://decisionstats.com > http://dudeofdata.com > > > Linkedin- www.linkedin.com/in/ajayohri > > [[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db From ohr|2007 @end|ng |rom gm@||@com Sun Oct 24 20:40:50 2010 From: ohr|2007 @end|ng |rom gm@||@com (Ajay Ohri) Date: Mon, 25 Oct 2010 00:10:50 +0530 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: Message-ID: how do I do that in windows please. ajay Websites- http://decisionstats.com http://dudeofdata.com Linkedin- www.linkedin.com/in/ajayohri On Sun, Oct 24, 2010 at 11:50 PM, Neil Tiffin wrote: > http://cran.r-project.org/web/packages/RPostgreSQL/index.html > > It is source only and a new version was released 17 Oct 2010. You will > need to compile from the source. > > Neil > > On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: > > > Hi > > > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. > > > > Is the package still available and working? > > > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and > > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi > as > > well) > > > > I am using PostgreSql 9.0 > > > > > > > > Regards > > > > Ajay > > > > > > > > > > I get the error message- > > > > Websites- > > http://decisionstats.com > > http://dudeofdata.com > > > > > > Linkedin- www.linkedin.com/in/ajayohri > > > > [[alternative HTML version deleted]] > > > > _______________________________________________ > > R-sig-DB mailing list -- R Special Interest Group > > R-sig-DB at stat.math.ethz.ch > > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > [[alternative HTML version deleted]] From edd @end|ng |rom deb|@n@org Sun Oct 24 20:44:10 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Sun, 24 Oct 2010 13:44:10 -0500 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: Message-ID: <19652.32378.188855.570702@max.nulle.part> On 24 October 2010 at 13:20, Neil Tiffin wrote: | It is source only and a new version was released 17 Oct 2010. You will need to compile from the source. Correct. The windows build systems for CRAN do not currently have a Postgresql _library_ against which the RPostgreSQL package can be built. Volunteer help in getting current 32 and 64 bit Postgresql libraries onto the CRAN build systems would be appreciated. Contact us off-list if you can help. Dirk | Neil | | On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: | | > Hi | > | > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. | > | > Is the package still available and working? | > | > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and | > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi as | > well) | > | > I am using PostgreSql 9.0 | > | > | > | > Regards | > | > Ajay | > | > | > | > | > I get the error message- | > | > Websites- | > http://decisionstats.com | > http://dudeofdata.com | > | > | > Linkedin- www.linkedin.com/in/ajayohri | > | > [[alternative HTML version deleted]] | > | > _______________________________________________ | > R-sig-DB mailing list -- R Special Interest Group | > R-sig-DB at stat.math.ethz.ch | > https://stat.ethz.ch/mailman/listinfo/r-sig-db | | _______________________________________________ | R-sig-DB mailing list -- R Special Interest Group | R-sig-DB at stat.math.ethz.ch | https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From edd @end|ng |rom deb|@n@org Sun Oct 24 20:52:01 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Sun, 24 Oct 2010 13:52:01 -0500 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: Message-ID: <19652.32849.209509.925251@max.nulle.part> On 25 October 2010 at 00:10, Ajay Ohri wrote: | how do I do that in windows please. If everything else fails, you could consider reading the R manuals 'Installation and Administration' and 'Writing R Extensions' that came with your R installation. Dirk | ajay | | Websites- | http://decisionstats.com | http://dudeofdata.com | | | Linkedin- www.linkedin.com/in/ajayohri | | | | | On Sun, Oct 24, 2010 at 11:50 PM, Neil Tiffin wrote: | | > http://cran.r-project.org/web/packages/RPostgreSQL/index.html | > | > It is source only and a new version was released 17 Oct 2010. You will | > need to compile from the source. | > | > Neil | > | > On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: | > | > > Hi | > > | > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. | > > | > > Is the package still available and working? | > > | > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and | > > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi | > as | > > well) | > > | > > I am using PostgreSql 9.0 | > > | > > | > > | > > Regards | > > | > > Ajay | > > | > > | > > | > > | > > I get the error message- | > > | > > Websites- | > > http://decisionstats.com | > > http://dudeofdata.com | > > | > > | > > Linkedin- www.linkedin.com/in/ajayohri | > > | > > [[alternative HTML version deleted]] | > > | > > _______________________________________________ | > > R-sig-DB mailing list -- R Special Interest Group | > > R-sig-DB at stat.math.ethz.ch | > > https://stat.ethz.ch/mailman/listinfo/r-sig-db | > | > | | [[alternative HTML version deleted]] | | _______________________________________________ | R-sig-DB mailing list -- R Special Interest Group | R-sig-DB at stat.math.ethz.ch | https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From ohr|2007 @end|ng |rom gm@||@com Sun Oct 24 21:14:24 2010 From: ohr|2007 @end|ng |rom gm@||@com (Ajay Ohri) Date: Mon, 25 Oct 2010 00:44:24 +0530 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: <19652.32849.209509.925251@max.nulle.part> References: <19652.32849.209509.925251@max.nulle.part> Message-ID: If you have only a source package that is known to work with current R and just want a binary (32- or 64-bit) Windows build of it, you could make use of the building service offered athttp://win-builder.r-project.org/. will that work? Websites- http://decisionstats.com http://dudeofdata.com Linkedin- www.linkedin.com/in/ajayohri On Mon, Oct 25, 2010 at 12:22 AM, Dirk Eddelbuettel wrote: > > On 25 October 2010 at 00:10, Ajay Ohri wrote: > | how do I do that in windows please. > > If everything else fails, you could consider reading the R manuals > 'Installation and Administration' and 'Writing R Extensions' that came with > your R installation. > > Dirk > > | ajay > | > | Websites- > | http://decisionstats.com > | http://dudeofdata.com > | > | > | Linkedin- www.linkedin.com/in/ajayohri > | > | > | > | > | On Sun, Oct 24, 2010 at 11:50 PM, Neil Tiffin > wrote: > | > | > http://cran.r-project.org/web/packages/RPostgreSQL/index.html > | > > | > It is source only and a new version was released 17 Oct 2010. You will > | > need to compile from the source. > | > > | > Neil > | > > | > On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: > | > > | > > Hi > | > > > | > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via > CRAN. > | > > > | > > Is the package still available and working? > | > > > | > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- > and > | > > which one is recommended for PostgresSQL databases. ( I couldnt find > rdbi > | > as > | > > well) > | > > > | > > I am using PostgreSql 9.0 > | > > > | > > > | > > > | > > Regards > | > > > | > > Ajay > | > > > | > > > | > > > | > > > | > > I get the error message- > | > > > | > > Websites- > | > > http://decisionstats.com > | > > http://dudeofdata.com > | > > > | > > > | > > Linkedin- www.linkedin.com/in/ajayohri > | > > > | > > [[alternative HTML version deleted]] > | > > > | > > _______________________________________________ > | > > R-sig-DB mailing list -- R Special Interest Group > | > > R-sig-DB at stat.math.ethz.ch > | > > https://stat.ethz.ch/mailman/listinfo/r-sig-db > | > > | > > | > | [[alternative HTML version deleted]] > | > | _______________________________________________ > | R-sig-DB mailing list -- R Special Interest Group > | R-sig-DB at stat.math.ethz.ch > | https://stat.ethz.ch/mailman/listinfo/r-sig-db > > -- > Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com > [[alternative HTML version deleted]] From edd @end|ng |rom deb|@n@org Mon Oct 25 08:07:11 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Mon, 25 Oct 2010 01:07:11 -0500 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: <19652.32849.209509.925251@max.nulle.part> Message-ID: <19653.7823.667748.375073@max.nulle.part> On 25 October 2010 at 00:44, Ajay Ohri wrote: | If you have only a source package that is known to work with current R and | just want a binary (32- or 64-bit) Windows build of it, you could make use | of the building service offered athttp://win-builder.r-project.org/. | | will that work? No. I wrote earlier: Correct. The windows build systems for CRAN do not currently have a Postgresql _library_ against which the RPostgreSQL package can be built. And win-builder needs a Postgresql library. Dirk | | | Websites- | http://decisionstats.com | http://dudeofdata.com | | | Linkedin- www.linkedin.com/in/ajayohri | | | | | On Mon, Oct 25, 2010 at 12:22 AM, Dirk Eddelbuettel wrote: | | > | > On 25 October 2010 at 00:10, Ajay Ohri wrote: | > | how do I do that in windows please. | > | > If everything else fails, you could consider reading the R manuals | > 'Installation and Administration' and 'Writing R Extensions' that came with | > your R installation. | > | > Dirk | > | > | ajay | > | | > | Websites- | > | http://decisionstats.com | > | http://dudeofdata.com | > | | > | | > | Linkedin- www.linkedin.com/in/ajayohri | > | | > | | > | | > | | > | On Sun, Oct 24, 2010 at 11:50 PM, Neil Tiffin | > wrote: | > | | > | > http://cran.r-project.org/web/packages/RPostgreSQL/index.html | > | > | > | > It is source only and a new version was released 17 Oct 2010. You will | > | > need to compile from the source. | > | > | > | > Neil | > | > | > | > On Oct 24, 2010, at 1:10 PM, Ajay Ohri wrote: | > | > | > | > > Hi | > | > > | > | > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via | > CRAN. | > | > > | > | > > Is the package still available and working? | > | > > | > | > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- | > and | > | > > which one is recommended for PostgresSQL databases. ( I couldnt find | > rdbi | > | > as | > | > > well) | > | > > | > | > > I am using PostgreSql 9.0 | > | > > | > | > > | > | > > | > | > > Regards | > | > > | > | > > Ajay | > | > > | > | > > | > | > > | > | > > | > | > > I get the error message- | > | > > | > | > > Websites- | > | > > http://decisionstats.com | > | > > http://dudeofdata.com | > | > > | > | > > | > | > > Linkedin- www.linkedin.com/in/ajayohri | > | > > | > | > > [[alternative HTML version deleted]] | > | > > | > | > > _______________________________________________ | > | > > R-sig-DB mailing list -- R Special Interest Group | > | > > R-sig-DB at stat.math.ethz.ch | > | > > https://stat.ethz.ch/mailman/listinfo/r-sig-db | > | > | > | > | > | | > | [[alternative HTML version deleted]] | > | | > | _______________________________________________ | > | R-sig-DB mailing list -- R Special Interest Group | > | R-sig-DB at stat.math.ethz.ch | > | https://stat.ethz.ch/mailman/listinfo/r-sig-db | > | > -- | > Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com | > -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From ggrothend|eck @end|ng |rom gm@||@com Mon Oct 25 16:56:59 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Mon, 25 Oct 2010 10:56:59 -0400 Subject: [R-sig-DB] [RPostgreSQL] Unable to find In-Reply-To: References: Message-ID: On Sun, Oct 24, 2010 at 2:10 PM, Ajay Ohri wrote: > Hi > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. > > Is the package still ?available and working? > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi as > well) > > I am using PostgreSql 9.0 > RpgSQL was developed so that PostgreSQL could be used as an optional backend to sqldf of the sqldf package. It was developed because the existing driver packages do not currently have all the features sqldf needed. RpgSQL uses JDBC and so requires java. It can also be used without sqldf and supports a DBI interface so its usage should be reasonably straight forward. There is some discussion in thel link referenced here: http://code.google.com/p/sqldf/#12._How_does_one_use_sqldf_with_PostgreSQL? and there are examples of using it directly, i.e. without sqldf, in the examples section of the ?pgSQL help page: library(pgSQL) ?pgSQL -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From ohr|2007 @end|ng |rom gm@||@com Tue Oct 26 13:11:49 2010 From: ohr|2007 @end|ng |rom gm@||@com (Ajay Ohri) Date: Tue, 26 Oct 2010 16:41:49 +0530 Subject: [R-sig-DB] R-sig-DB Digest, Vol 72, Issue 13 In-Reply-To: References: Message-ID: Thank you Gabor RpgSQL did the job nicely Regards Ajay Websites- http://decisionstats.com http://dudeofdata.com Linkedin- www.linkedin.com/in/ajayohri On Tue, Oct 26, 2010 at 3:30 PM, wrote: > Send R-sig-DB mailing list submissions to > r-sig-db at stat.math.ethz.ch > > To subscribe or unsubscribe via the World Wide Web, visit > https://stat.ethz.ch/mailman/listinfo/r-sig-db > or, via email, send a message with subject or body 'help' to > r-sig-db-request at stat.math.ethz.ch > > You can reach the person managing the list at > r-sig-db-owner at stat.math.ethz.ch > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of R-sig-DB digest..." > > > Today's Topics: > > 1. Re: [RPostgreSQL] Unable to find (Gabor Grothendieck) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 25 Oct 2010 10:56:59 -0400 > From: Gabor Grothendieck > To: Ajay Ohri > Cc: r-sig-db at stat.math.ethz.ch > Subject: Re: [R-sig-DB] [RPostgreSQL] Unable to find > Message-ID: > > Content-Type: text/plain; charset=ISO-8859-1 > > On Sun, Oct 24, 2010 at 2:10 PM, Ajay Ohri wrote: > > Hi > > > > I am unable to find R PostgreSQL ( I am on WIndows, R 2.12.0 ) via CRAN. > > > > Is the package still ?available and working? > > > > Also what are the differences between RODBC ,RPostgreSQL and RpgSQL- and > > which one is recommended for PostgresSQL databases. ( I couldnt find rdbi > as > > well) > > > > I am using PostgreSql 9.0 > > > > > RpgSQL was developed so that PostgreSQL could be used as an optional > backend to sqldf of the sqldf package. It was developed because the > existing driver packages do not currently have all the features sqldf > needed. RpgSQL uses JDBC and so requires java. It can also be used > without sqldf and supports a DBI interface so its usage should be > reasonably straight forward. > > There is some discussion in thel link referenced here: > http://code.google.com/p/sqldf/#12._How_does_one_use_sqldf_with_PostgreSQL > ? > > and there are examples of using it directly, i.e. without sqldf, in > the examples section of the ?pgSQL help page: > library(pgSQL) > ?pgSQL > > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.com > > > > ------------------------------ > > _______________________________________________ > R-sig-DB mailing list > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > > End of R-sig-DB Digest, Vol 72, Issue 13 > **************************************** > [[alternative HTML version deleted]] From n||z@b@rro@ @end|ng |rom gm@||@com Tue Oct 26 21:23:51 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Tue, 26 Oct 2010 19:23:51 +0000 Subject: [R-sig-DB] [R] Argument to database Message-ID: Dear Rusers, I am using Rscript and I'd like to use arguments as an input. I need it because I will use these arguments to consult Mysql Databse using (SELECT) . I need different select to each Model Type and date. ./GrafDens.R [ModelType trim date ] The script I am using it is working but I'd like to know if there is something more efficient. Below here what I am using: ================== #! /usr/bin/Rscript --vanilla args <- commandArgs(TRUE) if (length(args)!=4 ) { print("********************************************") print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year (AAAA)") print("********************************************") stop("Incorrect arguments length") } npt.freq <- args[1] npt.trim <-args[2] npt.ano <- args[3] MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") ===How I use the strings above to SELECT my data: drv=dbDriver("MySQL") con=dbConnect(drv,dbname='xxx',user='xx',password='xx') dados <- dbGetQuery(con,paste("SELECT OBS_StationNo as station_no, OBS_date as obsdate, FCT_fdate as fdate, FCT_mtype as fct_mtype, FCT_mrun as fct_mrun, FROM VWFct_Obs WHERE FCT_mtype=",npt.mtype, " AND OBS_StationNo <> 'NULL' AND (FCT_fdate LIKE ", paste("'",MES1.00,"'",sep=""), " OR FCT_fdate LIKE ", paste("'",MES1.12,"'",sep=""), " OR FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), " OR FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), " OR FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), "OR FCT_fdate LIKE " ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) # ==================== -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From @d@v|@2 @end|ng |rom m@||@n|h@gov Tue Oct 26 22:11:03 2010 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Tue, 26 Oct 2010 16:11:03 -0400 Subject: [R-sig-DB] [R] Argument to database In-Reply-To: References: Message-ID: On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS wrote: > Dear Rusers, > > I am using Rscript and I'd like to use arguments as an input. > I need it because I will use these arguments to consult Mysql Databse > using > (SELECT) . > I need different select to each Model Type and date. > > ./GrafDens.R [ModelType trim date ] > > The script I am using it is working but I'd like to know if there is > something more efficient. > > > Below here what I am using: > ================== > #! /usr/bin/Rscript --vanilla > args <- commandArgs(TRUE) > > if (length(args)!=4 ) > { > print("********************************************") > print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year > (AAAA)") > print("********************************************") > stop("Incorrect arguments length") > } > npt.freq <- args[1] > npt.trim <-args[2] > npt.ano <- args[3] > > > MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > > > ===How I use the strings above to SELECT my data: > > You could consider using sprintf() to generate your SQL string, as well. > drv=dbDriver("MySQL") > con=dbConnect(drv,dbname='xxx',user='xx',password='xx') > dados <- dbGetQuery(con,paste("SELECT > OBS_StationNo as station_no, > OBS_date as obsdate, > FCT_fdate as fdate, > FCT_mtype as fct_mtype, > FCT_mrun as fct_mrun, > FROM VWFct_Obs > WHERE > FCT_mtype=",npt.mtype, > " AND OBS_StationNo <> 'NULL' > AND (FCT_fdate LIKE ", paste("'",MES1.00,"'",sep=""), > " OR FCT_fdate LIKE ", paste("'",MES1.12,"'",sep=""), > " OR FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), > " OR FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), > " OR FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), > "OR FCT_fdate LIKE " ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) > # > ==================== > -- > Abra??o, > Nilza Barros > > [[alternative HTML version deleted]] > > [[alternative HTML version deleted]] From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Wed Oct 27 03:44:30 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Tue, 26 Oct 2010 18:44:30 -0700 Subject: [R-sig-DB] Installing RMySQL under CentOS 5.5 version of Linux? Message-ID: <4CC783FE.5080004@structuremonitoring.com> Hello: I'm not very unixed, and I'm trying to install RMySQL under CentOS 5.5 version of Linux. I'm having trouble parsing the installation instructions, e.g., at "http://cran.fhcrc.org/web/packages/RMySQL/INSTALL". Any suggestions? Thanks, Spencer -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp Wed Oct 27 04:13:46 2010 From: tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp (Tomoaki NISHIYAMA) Date: Wed, 27 Oct 2010 11:13:46 +0900 Subject: [R-sig-DB] Installing RMySQL under CentOS 5.5 version of Linux? In-Reply-To: <4CC783FE.5080004@structuremonitoring.com> References: <4CC783FE.5080004@structuremonitoring.com> Message-ID: <152CB81C-1198-451E-BDAF-347A86DBCAA1@kenroku.kanazawa-u.ac.jp> Hi, Did you install mysql-devel package? On CentOS # yum install mysql-devel as root. That should install the library and headers required for MySQL client development. Provided R is well installed as user application just $ R CMD INSTALL RMySQL_0.7-5.tar.gz after downloading works. If it is common application, perhaps you need to do it as root again. -- Tomoaki NISHIYAMA Advanced Science Research Center, Kanazawa University, 13-1 Takara-machi, Kanazawa, 920-0934, Japan On 2010/10/27, at 10:44, Spencer Graves wrote: > Hello: > > > I'm not very unixed, and I'm trying to install RMySQL under > CentOS 5.5 version of Linux. I'm having trouble parsing the > installation instructions, e.g., at "http://cran.fhcrc.org/web/ > packages/RMySQL/INSTALL". Any suggestions? > > > Thanks, > Spencer > > -- > Spencer Graves, PE, PhD > President and Chief Operating Officer > Structure Inspection and Monitoring, Inc. > 751 Emerson Ct. > San Jos?, CA 95126 > ph: 408-655-4567 > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Wed Oct 27 04:22:43 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Tue, 26 Oct 2010 19:22:43 -0700 Subject: [R-sig-DB] Installing RMySQL under CentOS 5.5 version of Linux? In-Reply-To: <152CB81C-1198-451E-BDAF-347A86DBCAA1@kenroku.kanazawa-u.ac.jp> References: <4CC783FE.5080004@structuremonitoring.com> <152CB81C-1198-451E-BDAF-347A86DBCAA1@kenroku.kanazawa-u.ac.jp> Message-ID: <4CC78CF3.4030900@structuremonitoring.com> Hi, Tomjoaki: It worked. Thanks very much. Best Wishes, Spencer On 10/26/2010 7:13 PM, Tomoaki NISHIYAMA wrote: > Hi, > > Did you install mysql-devel package? > On CentOS > # yum install mysql-devel > as root. That should install the library and headers required for > MySQL client development. > > Provided R is well installed as user application > just > $ R CMD INSTALL RMySQL_0.7-5.tar.gz > after downloading works. > If it is common application, perhaps you need to > do it as root again. > -- Tomoaki NISHIYAMA > > Advanced Science Research Center, > Kanazawa University, > 13-1 Takara-machi, > Kanazawa, 920-0934, Japan > > > On 2010/10/27, at 10:44, Spencer Graves wrote: > >> Hello: >> >> >> I'm not very unixed, and I'm trying to install RMySQL under >> CentOS 5.5 version of Linux. I'm having trouble parsing the >> installation instructions, e.g., at >> "http://cran.fhcrc.org/web/packages/RMySQL/INSTALL". Any suggestions? >> >> >> Thanks, >> Spencer >> >> -- >> Spencer Graves, PE, PhD >> President and Chief Operating Officer >> Structure Inspection and Monitoring, Inc. >> 751 Emerson Ct. >> San Jos?, CA 95126 >> ph: 408-655-4567 >> >> _______________________________________________ >> R-sig-DB mailing list -- R Special Interest Group >> R-sig-DB at stat.math.ethz.ch >> https://stat.ethz.ch/mailman/listinfo/r-sig-db >> From n||z@b@rro@ @end|ng |rom gm@||@com Thu Oct 28 15:06:56 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Thu, 28 Oct 2010 13:06:56 +0000 Subject: [R-sig-DB] [R] Argument to database In-Reply-To: References: Message-ID: Dear R users, Thanks Sean for your idea. But since I am getting used to using R in my all tasks I 'd like to know if the Rusers can show me a better way to use arguments in Rscript or if the way I had applied (below here and in my previous email) the arguments is correct. What I used it the same way I am used to writing in my shell scripts. I really hope someone can help me. Thanks in advance *Below here the way I check the number of arguments. I am used to doing that in my shell scripts.* ================== #! /usr/bin/Rscript --vanilla args <- commandArgs(TRUE) if (length(args)!=4 ) { print("********************************************") print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year (AAAA)") print("********************************************") stop("Incorrect arguments length") } npt.freq <- args[1] npt.trim <-args[2] npt.ano <- args[3] Nilza Barros On Tue, Oct 26, 2010 at 8:11 PM, Sean Davis wrote: > > > On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS wrote: > >> Dear Rusers, >> >> I am using Rscript and I'd like to use arguments as an input. >> I need it because I will use these arguments to consult Mysql Databse >> using >> (SELECT) . >> I need different select to each Model Type and date. >> >> ./GrafDens.R [ModelType trim date ] >> >> The script I am using it is working but I'd like to know if there is >> something more efficient. >> >> >> Below here what I am using: >> ================== >> #! /usr/bin/Rscript --vanilla >> args <- commandArgs(TRUE) >> >> if (length(args)!=4 ) >> { >> print("********************************************") >> print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year >> (AAAA)") >> print("********************************************") >> stop("Incorrect arguments length") >> } >> npt.freq <- args[1] >> npt.trim <-args[2] >> npt.ano <- args[3] >> >> >> MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >> MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >> MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >> >> >> ===How I use the strings above to SELECT my data: >> >> > You could consider using sprintf() to generate your SQL string, as well. > > >> drv=dbDriver("MySQL") >> con=dbConnect(drv,dbname='xxx',user='xx',password='xx') >> dados <- dbGetQuery(con,paste("SELECT >> OBS_StationNo as station_no, >> OBS_date as obsdate, >> FCT_fdate as fdate, >> FCT_mtype as fct_mtype, >> FCT_mrun as fct_mrun, >> FROM VWFct_Obs >> WHERE >> FCT_mtype=",npt.mtype, >> " AND OBS_StationNo <> 'NULL' >> AND (FCT_fdate LIKE ", paste("'",MES1.00,"'",sep=""), >> " OR FCT_fdate LIKE ", paste("'",MES1.12,"'",sep=""), >> " OR FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), >> " OR FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), >> " OR FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), >> "OR FCT_fdate LIKE " ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) >> # >> ==================== >> -- >> Abra?o, >> Nilza Barros >> >> [[alternative HTML version deleted]] >> >> > -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From @d@v|@2 @end|ng |rom m@||@n|h@gov Thu Oct 28 15:13:21 2010 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Thu, 28 Oct 2010 09:13:21 -0400 Subject: [R-sig-DB] [R] Argument to database In-Reply-To: References: Message-ID: On Thu, Oct 28, 2010 at 9:06 AM, Nilza BARROS wrote: > Dear R users, > > Thanks Sean for your idea. But since I am getting used to using R in my > all tasks I 'd like to know if the Rusers can show me a better way to use > arguments in Rscript or if the way I had applied (below here and in my > previous email) the arguments is correct. > What I used it the same way I am used to writing in my shell scripts. > > Ah. This would better have been written to R-help, as it has nothing to do with databases. However, it is a pretty simple answer. Look at the optparse and getopt packages on CRAN. Sean > I really hope someone can help me. > Thanks in advance > > *Below here the way I check the number of arguments. I am used to doing > that in my shell scripts.* > > ================== > #! /usr/bin/Rscript --vanilla > args <- commandArgs(TRUE) > > if (length(args)!=4 ) > { > print("********************************************") > print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year > (AAAA)") > print("********************************************") > stop("Incorrect arguments length") > } > npt.freq <- args[1] > npt.trim <-args[2] > npt.ano <- args[3] > > > > Nilza Barros > > On Tue, Oct 26, 2010 at 8:11 PM, Sean Davis wrote: > >> >> >> On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS wrote: >> >>> Dear Rusers, >>> >>> I am using Rscript and I'd like to use arguments as an input. >>> I need it because I will use these arguments to consult Mysql Databse >>> using >>> (SELECT) . >>> I need different select to each Model Type and date. >>> >>> ./GrafDens.R [ModelType trim date ] >>> >>> The script I am using it is working but I'd like to know if there is >>> something more efficient. >>> >>> >>> Below here what I am using: >>> ================== >>> #! /usr/bin/Rscript --vanilla >>> args <- commandArgs(TRUE) >>> >>> if (length(args)!=4 ) >>> { >>> print("********************************************") >>> print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year >>> (AAAA)") >>> print("********************************************") >>> stop("Incorrect arguments length") >>> } >>> npt.freq <- args[1] >>> npt.trim <-args[2] >>> npt.ano <- args[3] >>> >>> >>> MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>> MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>> MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>> >>> >>> ===How I use the strings above to SELECT my data: >>> >>> >> You could consider using sprintf() to generate your SQL string, as well. >> >> >>> drv=dbDriver("MySQL") >>> con=dbConnect(drv,dbname='xxx',user='xx',password='xx') >>> dados <- dbGetQuery(con,paste("SELECT >>> OBS_StationNo as station_no, >>> OBS_date as obsdate, >>> FCT_fdate as fdate, >>> FCT_mtype as fct_mtype, >>> FCT_mrun as fct_mrun, >>> FROM VWFct_Obs >>> WHERE >>> FCT_mtype=",npt.mtype, >>> " AND OBS_StationNo <> 'NULL' >>> AND (FCT_fdate LIKE ", paste("'",MES1.00,"'",sep=""), >>> " OR FCT_fdate LIKE ", paste("'",MES1.12,"'",sep=""), >>> " OR FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), >>> " OR FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), >>> " OR FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), >>> "OR FCT_fdate LIKE " ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) >>> # >>> ==================== >>> -- >>> Abra??o, >>> Nilza Barros >>> >>> [[alternative HTML version deleted]] >>> >>> >> > > > -- > Abra??o, > Nilza Barros > [[alternative HTML version deleted]] From n||z@b@rro@ @end|ng |rom gm@||@com Thu Oct 28 15:27:50 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Thu, 28 Oct 2010 13:27:50 +0000 Subject: [R-sig-DB] [R] Argument to database In-Reply-To: References: Message-ID: OK, Thank you! You're rigth. It is better to sent my question to r-help. But I will check optparse and getopt. Bye and Best Regards, Nilza On Thu, Oct 28, 2010 at 1:13 PM, Sean Davis wrote: > > > On Thu, Oct 28, 2010 at 9:06 AM, Nilza BARROS wrote: > >> Dear R users, >> >> Thanks Sean for your idea. But since I am getting used to using R in my >> all tasks I 'd like to know if the Rusers can show me a better way to use >> arguments in Rscript or if the way I had applied (below here and in my >> previous email) the arguments is correct. >> What I used it the same way I am used to writing in my shell scripts. >> >> > Ah. This would better have been written to R-help, as it has nothing to do > with databases. However, it is a pretty simple answer. Look at the > optparse and getopt packages on CRAN. > > Sean > > >> I really hope someone can help me. >> Thanks in advance >> >> *Below here the way I check the number of arguments. I am used to doing >> that in my shell scripts.* >> >> ================== >> #! /usr/bin/Rscript --vanilla >> args <- commandArgs(TRUE) >> >> if (length(args)!=4 ) >> { >> print("********************************************") >> print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - Year >> (AAAA)") >> print("********************************************") >> stop("Incorrect arguments length") >> } >> npt.freq <- args[1] >> npt.trim <-args[2] >> npt.ano <- args[3] >> >> >> >> Nilza Barros >> >> On Tue, Oct 26, 2010 at 8:11 PM, Sean Davis wrote: >> >>> >>> >>> On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS wrote: >>> >>>> Dear Rusers, >>>> >>>> I am using Rscript and I'd like to use arguments as an input. >>>> I need it because I will use these arguments to consult Mysql Databse >>>> using >>>> (SELECT) . >>>> I need different select to each Model Type and date. >>>> >>>> ./GrafDens.R [ModelType trim date ] >>>> >>>> The script I am using it is working but I'd like to know if there is >>>> something more efficient. >>>> >>>> >>>> Below here what I am using: >>>> ================== >>>> #! /usr/bin/Rscript --vanilla >>>> args <- commandArgs(TRUE) >>>> >>>> if (length(args)!=4 ) >>>> { >>>> print("********************************************") >>>> print("Enter the arguments Model (20,21,...) - Trim(01,02,03,04) - >>>> Year >>>> (AAAA)") >>>> print("********************************************") >>>> stop("Incorrect arguments length") >>>> } >>>> npt.freq <- args[1] >>>> npt.trim <-args[2] >>>> npt.ano <- args[3] >>>> >>>> >>>> MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>>> MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>>> MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") >>>> >>>> >>>> ===How I use the strings above to SELECT my data: >>>> >>>> >>> You could consider using sprintf() to generate your SQL string, as well. >>> >>> >>>> drv=dbDriver("MySQL") >>>> con=dbConnect(drv,dbname='xxx',user='xx',password='xx') >>>> dados <- dbGetQuery(con,paste("SELECT >>>> OBS_StationNo as station_no, >>>> OBS_date as obsdate, >>>> FCT_fdate as fdate, >>>> FCT_mtype as fct_mtype, >>>> FCT_mrun as fct_mrun, >>>> FROM VWFct_Obs >>>> WHERE >>>> FCT_mtype=",npt.mtype, >>>> " AND OBS_StationNo <> 'NULL' >>>> AND (FCT_fdate LIKE ", paste("'",MES1.00,"'",sep=""), >>>> " OR FCT_fdate LIKE ", paste("'",MES1.12,"'",sep=""), >>>> " OR FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), >>>> " OR FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), >>>> " OR FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), >>>> "OR FCT_fdate LIKE " ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) >>>> # >>>> ==================== >>>> -- >>>> Abra?o, >>>> Nilza Barros >>>> >>>> [[alternative HTML version deleted]] >>>> >>>> >>> >> >> >> -- >> Abra?o, >> Nilza Barros >> > > -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From ggrothend|eck @end|ng |rom gm@||@com Thu Oct 28 15:41:57 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Thu, 28 Oct 2010 09:41:57 -0400 Subject: [R-sig-DB] [R] Argument to database In-Reply-To: References: Message-ID: On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS wrote: > Dear Rusers, > > I am using Rscript and I'd like to use arguments as an input. > I need it because I will use these arguments to consult Mysql ?Databse using > (SELECT) . > I need different select to each Model Type and date. > > ./GrafDens.R [ModelType trim date ] > > The script I am using it is working but I'd like to know if there is > something more efficient. > > > Below here what ?I am using: > ================== > #! /usr/bin/Rscript --vanilla > args <- commandArgs(TRUE) > > if (length(args)!=4 ) > { > print("********************************************") > print("Enter the arguments ? Model (20,21,...) - Trim(01,02,03,04) - Year > (AAAA)") > print("********************************************") > stop("Incorrect arguments length") > } > npt.freq <- args[1] > npt.trim <-args[2] > npt.ano <- args[3] > > > MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="") > > > ===How I use the strings above to SELECT my data: > > drv=dbDriver("MySQL") > con=dbConnect(drv,dbname='xxx',user='xx',password='xx') > dados <- dbGetQuery(con,paste("SELECT > OBS_StationNo as station_no, > OBS_date as obsdate, > FCT_fdate as fdate, > FCT_mtype as fct_mtype, > FCT_mrun as ?fct_mrun, > FROM VWFct_Obs > WHERE > FCT_mtype=",npt.mtype, > " AND OBS_StationNo <> 'NULL' > AND (FCT_fdate LIKE ?", ?paste("'",MES1.00,"'",sep=""), > " OR FCT_fdate LIKE ?", paste("'",MES1.12,"'",sep=""), > " OR ?FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""), > " OR ?FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""), > " OR ?FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""), > "OR ?FCT_fdate LIKE ?" ,paste("'",MES3.12,"'",sep="")," )" ,sep="")) > # You may wish to use sprintf to construct your strings s <- sprintf("select * from mytable where mycolumn = '%s' ", myvalue) out <- dbGetQuery(con, s) or else the perl-like string interpolation facilities of the gsubfn package. Just preface any function call with fn$ and you get string interpolation in the arguments (subject to certain rules): library(gsubfn) out <- fn$dbGetQuery(con, "select * from mytable where mycolumn = '$myvalue' ") -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From gux|@obo1982 @end|ng |rom gm@||@com Sun Oct 31 10:39:09 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Sun, 31 Oct 2010 17:39:09 +0800 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit Message-ID: Hi, Can you help with this > driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip") > con <- dbConnect(driver, user="postgres", password="postgres", dbname="demo") > con An object of class "pgSQLConnection" Slot "jc": [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection at 1ce2dd 4}" Slot "identifier.quote": [1] "\"" > data <- dbGetQuery(con, "select * from optest.colana") Error as.Date.default(.jcall(res at jr, "S", "getString", i)) : do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class "Date" > the DDL is : CREATE TABLE optest.colana ( a integer, b character varying(12), c date, d numeric ) [[alternative HTML version deleted]] From edd @end|ng |rom deb|@n@org Sun Oct 31 14:26:48 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Sun, 31 Oct 2010 08:26:48 -0500 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: Message-ID: <19661.28312.520318.108726@max.nulle.part> On 31 October 2010 at 17:39, Xiaobo Gu wrote: | Hi, | Can you help with this | | > driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip") | > con <- dbConnect(driver, user="postgres", password="postgres", | dbname="demo") | > con | An object of class "pgSQLConnection" | Slot "jc": | [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection at 1ce2dd 4}" | | Slot "identifier.quote": | [1] "\"" | | > data <- dbGetQuery(con, "select * from optest.colana") | Error as.Date.default(.jcall(res at jr, "S", "getString", i)) : | do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class | "Date" | > | the DDL is : | CREATE TABLE optest.colana | ( | a integer, | b character varying(12), | c date, | d numeric | ) Try casting the (SQL) date to (SQL) character, you can probably load the character into R and parse again as an (R) Date. RPostgreSQL could do that in one step, but we'd still need a volunteer to help build a libpq.a library for Windows/MinGW before a binary package can be provided. Dirk -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From ggrothend|eck @end|ng |rom gm@||@com Sun Oct 31 18:01:21 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Sun, 31 Oct 2010 13:01:21 -0400 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: Message-ID: On Sun, Oct 31, 2010 at 5:39 AM, Xiaobo Gu wrote: > Hi, > Can you help with this > >> driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip") >> con <- dbConnect(driver, user="postgres", password="postgres", > dbname="demo") >> con > An object of class "pgSQLConnection" > Slot "jc": > [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection at 1ce2dd 4}" > > Slot "identifier.quote": > [1] "\"" > >> data <- dbGetQuery(con, "select * from optest.colana") > Error as.Date.default(.jcall(res at jr, "S", "getString", i)) : > do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class > "Date" >> > the DDL is : > CREATE TABLE optest.colana > ( > a integer, > b character varying(12), > c date, > d numeric > ) > Could you write up a reproducible example? I created a reproducible example below but it works for me. Here we create a table with a Date field and then insert some data and then read it back. Note that the new table that results from the dbGetQuery at the end has a Date field as required. > options(RpgSQL.password = "postgres", RpgSQL.dbname = "mydb") > library(RpgSQL) > > con <- dbConnect(pgSQL()) > > ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)' > dbSendUpdate(con, ct) > > DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) > dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)") > > res <- dbGetUpdate(con, "select * from DF") Error: could not find function "dbGetUpdate" > str(res) 'data.frame': 1 obs. of 4 variables: $ a: num 1 $ b: Factor w/ 1 level "Hello": 1 $ c:Class 'Date' num 10957 $ d: num 32 > > dbSendUpdate(con, "drop table DF") > dbDisconnect(con) [1] TRUE > R.version.string [1] "R version 2.11.1 Patched (2010-05-31 r52167)" > win.version() [1] "Windows Vista (build 6002) Service Pack 2" -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From ggrothend|eck @end|ng |rom gm@||@com Sun Oct 31 18:03:09 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Sun, 31 Oct 2010 13:03:09 -0400 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: <19661.28312.520318.108726@max.nulle.part> References: <19661.28312.520318.108726@max.nulle.part> Message-ID: On Sun, Oct 31, 2010 at 9:26 AM, Dirk Eddelbuettel wrote: > > On 31 October 2010 at 17:39, Xiaobo Gu wrote: > | Hi, > | Can you help with this > | > | > driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip") > | > con <- dbConnect(driver, user="postgres", password="postgres", > | dbname="demo") > | > con > | An object of class "pgSQLConnection" > | Slot "jc": > | [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection at 1ce2dd 4}" > | > | Slot "identifier.quote": > | [1] "\"" > | > | > data <- dbGetQuery(con, "select * from optest.colana") > | Error as.Date.default(.jcall(res at jr, "S", "getString", i)) : > | do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class > | "Date" > | > > | the DDL is : > | CREATE TABLE optest.colana > | ( > | a integer, > | b character varying(12), > | c date, > | d numeric > | ) > > Try casting the (SQL) date to (SQL) character, you can probably load the > character into R and parse again as an (R) Date. > RpgSQL directly supports Date type. It should not be necessary to convert it to a character. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From ggrothend|eck @end|ng |rom gm@||@com Sun Oct 31 18:08:24 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Sun, 31 Oct 2010 13:08:24 -0400 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: Message-ID: On Sun, Oct 31, 2010 at 1:01 PM, Gabor Grothendieck wrote: > On Sun, Oct 31, 2010 at 5:39 AM, Xiaobo Gu wrote: >> Hi, >> Can you help with this >> >>> driver<-pgSQL(classPath="D:\\rtemp\\postgresql-9.0-801.jdbc4.zip") >>> con <- dbConnect(driver, user="postgres", password="postgres", >> dbname="demo") >>> con >> An object of class "pgSQLConnection" >> Slot "jc": >> [1] "Java-Object{org.postgresql.jdbc4.Jdbc4Connection at 1ce2dd 4}" >> >> Slot "identifier.quote": >> [1] "\"" >> >>> data <- dbGetQuery(con, "select * from optest.colana") >> Error as.Date.default(.jcall(res at jr, "S", "getString", i)) : >> do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class >> "Date" >>> >> the DDL is : >> CREATE TABLE optest.colana >> ( >> a integer, >> b character varying(12), >> c date, >> d numeric >> ) >> > > Could you write up a reproducible example? ?I created a reproducible > example below but it works for me. ?Here we create a table with a Date > field and then insert some data and then read it back. ?Note that the > new table that results from the dbGetQuery at the end has a Date field > as required. > Sorry I pasted the wrong code in. Here is the correct reproducible code. (The prior one was correct except dbGetQuery was mistyped as dbGetUpdate.) > options(RpgSQL.password = "postgres", RpgSQL.dbname = "mydb") > library(RpgSQL) > > con <- dbConnect(pgSQL()) > > ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)' > dbSendUpdate(con, ct) > > DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) > dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)") > > res <- dbGetQuery(con, "select * from DF") > str(res) 'data.frame': 1 obs. of 4 variables: $ a: num 1 $ b: Factor w/ 1 level "Hello": 1 $ c:Class 'Date' num 10957 $ d: num 32 > > dbSendUpdate(con, "drop table DF") > dbDisconnect(con) [1] TRUE -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From edd @end|ng |rom deb|@n@org Sun Oct 31 18:10:16 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Sun, 31 Oct 2010 12:10:16 -0500 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: <19661.28312.520318.108726@max.nulle.part> Message-ID: <19661.41720.845742.291601@max.nulle.part> On 31 October 2010 at 13:03, Gabor Grothendieck wrote: | On Sun, Oct 31, 2010 at 9:26 AM, Dirk Eddelbuettel wrote: | > Try casting the (SQL) date to (SQL) character, you can probably load the | > character into R and parse again as an (R) Date. | > | | RpgSQL directly supports Date type. It should not be necessary to | convert it to a character. Thanks for the clarification. I didn't mean to imply that RpgSQL didn't have this feature and was just trying to help the OP with an alternate approach. Dirk -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From gux|@obo1982 @end|ng |rom gm@||@com Mon Nov 1 03:17:07 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Mon, 1 Nov 2010 10:17:07 +0800 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: <19661.41720.845742.291601@max.nulle.part> References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> Message-ID: Hi, What's the versions for all the related components, R, DBI, rJava,RJDBC, RpgSQL, PostgreSQL JDBC? Regards, Xiaobo Gu On Mon, Nov 1, 2010 at 1:10 AM, Dirk Eddelbuettel wrote: > > On 31 October 2010 at 13:03, Gabor Grothendieck wrote: > | On Sun, Oct 31, 2010 at 9:26 AM, Dirk Eddelbuettel > wrote: > | > Try casting the (SQL) date to (SQL) character, you can probably load > the > | > character into R and parse again as an (R) Date. > | > > | > | RpgSQL directly supports Date type. It should not be necessary to > | convert it to a character. > > Thanks for the clarification. I didn't mean to imply that RpgSQL didn't > have > this feature and was just trying to help the OP with an alternate approach. > > Dirk > > -- > Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com > [[alternative HTML version deleted]] From ggrothend|eck @end|ng |rom gm@||@com Mon Nov 1 03:33:59 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Sun, 31 Oct 2010 22:33:59 -0400 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> Message-ID: On Sun, Oct 31, 2010 at 10:17 PM, Xiaobo Gu wrote: > Hi, > What's the versions for all the related components, R, DBI, rJava,RJDBC, > RpgSQL, PostgreSQL JDBC? > I am using the most recent versions of all packages and R 2.11.1 Patched on Windows Vista: > packageDescription("RJDBC")$Version [1] "0.1-5" > packageDescription("DBI")$Version [1] "0.2-5" > packageDescription("rJava")$Version [1] "0.8-7" > packageDescription("RpgSQL")$Version [1] "0.1-3.2" > R.version.string [1] "R version 2.11.1 Patched (2010-05-31 r52167)" > win.version() [1] "Windows Vista (build 6002) Service Pack 2" The Windows binary of rJava seems currently not to be available on CRAN: http://cran.r-project.org/web/packages/rJava/index.html but you can build it yourself or get it from R-Forge. Did you try running the reproducible example in my email? Did it work for you? -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp Mon Nov 1 06:42:56 2010 From: tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp (Tomoaki NISHIYAMA) Date: Mon, 1 Nov 2010 14:42:56 +0900 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> Message-ID: <19B29F5A-BEC4-4EBB-BCE2-9251386D6EC8@kenroku.kanazawa-u.ac.jp> Hi, While the code presented by Gabor Grothendieck works fine. > options(RpgSQL.password = "", RpgSQL.dbname = "testdb", RpgSLQ.user="testuser") > con <- dbConnect(pgSQL()) > ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)' > dbSendUpdate(con, ct) > DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) > dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date ('2000-01-01', 'YYYY-MM-DD'), 32)") > res <- dbGetQuery(con, "select * from DF") > res a b c d 1 1 Hello 2000-01-01 32 I found that when the database contain NULL value for the date field: (This is prepared with psql) testdb=# insert into df values ('2', 'bye', NULL,'48'); INSERT 0 1 testdb=# select * from df; a | b | c | d ---+-------+------------+---- 1 | Hello | 2000-01-01 | 32 2 | bye | | 48 (2 rows) Then, the original error (by Xiaobo Gu) could be reproduced. > res <- dbGetQuery(con, "select * from DF") Error in as.Date.default(.jcall(res at jr, "S", "getString", i)) : do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class "Date" So, I guess there is some record with the c field having NULL value. -- Tomoaki NISHIYAMA Advanced Science Research Center, Kanazawa University, 13-1 Takara-machi, Kanazawa, 920-0934, Japan From gux|@obo1982 @end|ng |rom gm@||@com Mon Nov 1 08:46:28 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Mon, 1 Nov 2010 15:46:28 +0800 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> Message-ID: It works too, but the original problem still occurs. con <- dbConnect(pgSQL(classPath="D:\\rtemp\\postgresql-8.4-702.jdbc4.zip")) > ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)' > dbSendUpdate(con, ct) > > DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) > dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)") > res <- dbGetQuery(con, "select * from DF") > res a b c d 1 1 Hello 2000-01-01 32 > str(res) 'data.frame': 1 obs. of 4 variables: $ a: num 1 $ b: Factor w/ 1 level "Hello": 1 $ c:Class 'Date' num 10957 $ d: num 32 > On Mon, Nov 1, 2010 at 10:33 AM, Gabor Grothendieck wrote: > On Sun, Oct 31, 2010 at 10:17 PM, Xiaobo Gu > wrote: > > Hi, > > What's the versions for all the related components, R, DBI, rJava,RJDBC, > > RpgSQL, PostgreSQL JDBC? > > > > I am using the most recent versions of all packages and R 2.11.1 > Patched on Windows Vista: > > > packageDescription("RJDBC")$Version > [1] "0.1-5" > > packageDescription("DBI")$Version > [1] "0.2-5" > > packageDescription("rJava")$Version > [1] "0.8-7" > > packageDescription("RpgSQL")$Version > [1] "0.1-3.2" > > R.version.string > [1] "R version 2.11.1 Patched (2010-05-31 r52167)" > > win.version() > [1] "Windows Vista (build 6002) Service Pack 2" > > The Windows binary of rJava seems currently not to be available on CRAN: > http://cran.r-project.org/web/packages/rJava/index.html > but you can build it yourself or get it from R-Forge. > > Did you try running the reproducible example in my email? Did it work for > you? > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.com > On Mon, Nov 1, 2010 at 10:33 AM, Gabor Grothendieck wrote: > On Sun, Oct 31, 2010 at 10:17 PM, Xiaobo Gu > wrote: > > Hi, > > What's the versions for all the related components, R, DBI, rJava,RJDBC, > > RpgSQL, PostgreSQL JDBC? > > > > I am using the most recent versions of all packages and R 2.11.1 > Patched on Windows Vista: > > > packageDescription("RJDBC")$Version > [1] "0.1-5" > > packageDescription("DBI")$Version > [1] "0.2-5" > > packageDescription("rJava")$Version > [1] "0.8-7" > > packageDescription("RpgSQL")$Version > [1] "0.1-3.2" > > R.version.string > [1] "R version 2.11.1 Patched (2010-05-31 r52167)" > > win.version() > [1] "Windows Vista (build 6002) Service Pack 2" > > The Windows binary of rJava seems currently not to be available on CRAN: > http://cran.r-project.org/web/packages/rJava/index.html > but you can build it yourself or get it from R-Forge. > > Did you try running the reproducible example in my email? Did it work for > you? > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.com > [[alternative HTML version deleted]] From ggrothend|eck @end|ng |rom gm@||@com Mon Nov 1 13:11:24 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Mon, 1 Nov 2010 08:11:24 -0400 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: <19B29F5A-BEC4-4EBB-BCE2-9251386D6EC8@kenroku.kanazawa-u.ac.jp> References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> <19B29F5A-BEC4-4EBB-BCE2-9251386D6EC8@kenroku.kanazawa-u.ac.jp> Message-ID: On Mon, Nov 1, 2010 at 1:42 AM, Tomoaki NISHIYAMA wrote: > Hi, > > While the code presented by Gabor Grothendieck works fine. > >> options(RpgSQL.password = "", RpgSQL.dbname = "testdb", >> RpgSLQ.user="testuser") >> con <- dbConnect(pgSQL()) >> ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE >> PRECISION)' >> dbSendUpdate(con, ct) >> DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) >> dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', >> 'YYYY-MM-DD'), 32)") >> res <- dbGetQuery(con, "select * from DF") >> res > ?a ? ? b ? ? ? ? ?c ?d > 1 1 Hello 2000-01-01 32 > > > I found that when the database contain NULL value for the date field: > (This is prepared with psql) > > testdb=# insert into df values ('2', 'bye', NULL,'48'); > INSERT 0 1 > testdb=# select * from df; > ?a | ? b ? | ? ? c ? ? ?| d > ---+-------+------------+---- > ?1 | Hello | 2000-01-01 | 32 > ?2 | bye ? | ? ? ? ? ? ?| 48 > (2 rows) > > Then, the original error (by Xiaobo Gu) could be reproduced. > >> res <- dbGetQuery(con, "select * from DF") > Error in as.Date.default(.jcall(res at jr, "S", "getString", i)) : > ?do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class > "Date" > > So, I guess there is some record with the c field having NULL value. Thanks. I was able to reproduce the problem using your observation: > library(RpgSQL) > con <- dbConnect(pgSQL()) > ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE PRECISION)' > dbSendUpdate(con, ct) > dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', 'YYYY-MM-DD'), 32)") > dbSendUpdate(con, "insert into DF values(2, 'World', Null, 33)") > res <- dbGetQuery(con, "select * from DF") Error in as.Date.default(.jcall(res at jr, "S", "getString", i)) : do not know how to convert '.jcall(res at jr, "S", "getString", i)' to class "Date" I will look into this. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From gux|@obo1982 @end|ng |rom gm@||@com Mon Nov 1 13:17:07 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Mon, 1 Nov 2010 20:17:07 +0800 Subject: [R-sig-DB] R-sig-DB Digest, Vol 73, Issue 2 In-Reply-To: References: Message-ID: Hi Tomoaki NISHIYAMA You are right, we have NULL in the c column, and dbGetQuery works fine with none NULL columns . Xiaobo [[alternative HTML version deleted]] From gux|@obo1982 @end|ng |rom gm@||@com Mon Nov 1 14:07:27 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Mon, 1 Nov 2010 21:07:27 +0800 Subject: [R-sig-DB] Does RpgSQL implement dbHasCompleted function? Message-ID: <003d01cb79c5$bf49e3c0$3dddab40$@com> Hi, I write the following the code to retrieve very large result set and do some other processing by chunk, but the error message shows there is not an implementation for pgSQLResult for dbHasCompleted function. Can you package mantainers implement it for me please? unable to find an inherited method for function "dbHasCompleted", for signature "pgSQLResult" while(!dbHasCompleted(res)){ data1 <- fetch(res, next.rows) #other processing with data1 } Xiaobo.Gu [[alternative HTML version deleted]] From gux|@obo1982 @end|ng |rom gm@||@com Fri Nov 5 13:28:09 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Fri, 5 Nov 2010 20:28:09 +0800 Subject: [R-sig-DB] dbClearResult function error in package RpgSQL Message-ID: Hi, When I call the dbClearResult(res) in package RpgSQL, the following error message occurs: Error is(object, Cl) : no function "jcall" Note: the above error message is translated from Simplified Chinese. [[alternative HTML version deleted]] From ggrothend|eck @end|ng |rom gm@||@com Fri Nov 5 14:57:48 2010 From: ggrothend|eck @end|ng |rom gm@||@com (Gabor Grothendieck) Date: Fri, 5 Nov 2010 09:57:48 -0400 Subject: [R-sig-DB] dbClearResult function error in package RpgSQL In-Reply-To: References: Message-ID: Hi, RpgSQL is a thin layer over RJDBC and so only supports its functions. dbClearResults and hasCompleted seem not to be currently supported by RJDBC (I grep'd through its source code and do not see them mentioned) but perhaps it will in the future. If RJDBC does support them in the future then RpgSQL should automatically inherit them. On Fri, Nov 5, 2010 at 8:28 AM, Xiaobo Gu wrote: > Hi, > > When I call the ?dbClearResult(res) in package RpgSQL, the following ?error > message occurs: > > Error is(object, Cl) : no function "jcall" > > Note: the above error message is translated from Simplified Chinese. > > ? ? ? ?[[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com From b@ye@|@n|og|c @end|ng |rom @cm@org Fri Nov 5 20:54:16 2010 From: b@ye@|@n|og|c @end|ng |rom @cm@org (Jan Theodore Galkowski) Date: Fri, 05 Nov 2010 19:54:16 +0000 Subject: [R-sig-DB] connecting to remote database using RMySQL, from WinXP Pro In-Reply-To: <1288985551.1684.1403823987@webmail.messagingengine.com> References: <1288983872.29111.1403819539@webmail.messagingengine.com><1288985551.1684.1403823987@webmail.messagingengine.com> Message-ID: <1288986856.6010.1403827081@webmail.messagingengine.com> (Sorry, cross-posted from R-Help to more appropriate venue.) On Fri, 05 Nov 2010 19:32 +0000, "Jan Theodore Galkowski" wrote: Yes, what happens when I do that is: R version 2.11.1 (2010-05-31) Copyright (C) 2010 The R Foundation for Statistical Computing ISBN 3-900051-07-0 [snip] > Sys.getenv("MYSQL_USER") MYSQL_USER "mf1" > # As an example. Other env vars are defined. > library(RMySQL) Loading required package: DBI Error : .onLoad failed in loadNamespace() for 'RMySQL', details: call: utils::readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) error: Registry key 'SOFTWARE\MySQL AB' not found Error: package/namespace load failed for 'RMySQL' > I don't even get to do anything more. - Jan On Fri, 05 Nov 2010 17:14 -0200, "Henrique Dallazuanna" wrote: library(RMySQL) conn <- dbConnect(MySQL(), user = 'user', password = 'password', host = '[1]your_host.com') Look at [2]http://biostat.mc.vanderbilt.edu/RMySQL for more information On Fri, Nov 5, 2010 at 5:04 PM, Jan Theodore Galkowski <[3]bayesianlogic at acm.org> wrote: Apologies if this is the wrong place to ask. I'm not aware of a mail list devoted to database interfaces. Please direct me if so. I am trying to use TSMySQL. It loads RMySQL, and apparent the CRAN version has an .onLoad script which seeks out the local MySQL server. If that fails, the package fails to load. Thing is, I'm trying to access a MySQL database on a remote machine for which I have authorized access. Anyone know how to do this, or suggest where I can find out? Thanks. I have successfully accessed remote databases using RODBC. Is that how I need to go? - Jan [snip] References 1. http://your_host.com/ 2. http://biostat.mc.vanderbilt.edu/RMySQL 3. mailto:bayesianlogic at acm.org -- Jan Theodore Galkowski (o?) 607.239.1834 [mobile] 607.239.1834 [home] 617.444.4995 [work] bayesianlogic at acm.org http://www.linkedin.com/in/deepdevelopment "Eppur si muove." --Galilei [[alternative HTML version deleted]] From tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp Sat Nov 6 01:54:34 2010 From: tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp (Tomoaki NISHIYAMA) Date: Sat, 6 Nov 2010 09:54:34 +0900 Subject: [R-sig-DB] connecting to remote database using RMySQL, from WinXP Pro In-Reply-To: <1288986856.6010.1403827081@webmail.messagingengine.com> References: <1288983872.29111.1403819539@webmail.messagingengine.com><1288985551.1684.1403823987@webmail.messagingengine.com> <1288986856.6010.1403827081@webmail.messagingengine.com> Message-ID: <36705200-2C8C-48B0-8159-897B6872182C@kenroku.kanazawa-u.ac.jp> Hi, Did you exactly follow http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL ? especially MYSQL_HOMEE environmental variable and MySQL client library install path? If you followed the instruction you would have shown Sys.getenv('MYSQL_HOME') instead of Sys.getenv("MYSQL_USER") See, the difference of HOME and USER. The message says the R couldn't find MySQL library and the location will be directed by MYSQL_HOME. -- Tomoaki NISHIYAMA Advanced Science Research Center, Kanazawa University, 13-1 Takara-machi, Kanazawa, 920-0934, Japan On 2010/11/06, at 4:54, Jan Theodore Galkowski wrote: > (Sorry, cross-posted from R-Help to more appropriate venue.) > > On Fri, 05 Nov 2010 19:32 +0000, "Jan Theodore Galkowski" > wrote: > > Yes, what happens when I do that is: > > R version 2.11.1 (2010-05-31) > Copyright (C) 2010 The R Foundation for Statistical Computing > ISBN 3-900051-07-0 > [snip] >> Sys.getenv("MYSQL_USER") > MYSQL_USER > "mf1" >> # As an example. Other env vars are defined. >> library(RMySQL) > Loading required package: DBI > Error : .onLoad failed in loadNamespace() for 'RMySQL', details: > call: utils::readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", > maxdepth = 2) > error: Registry key 'SOFTWARE\MySQL AB' not found > Error: package/namespace load failed for 'RMySQL' >> > > I don't even get to do anything more. > > - Jan > > > On Fri, 05 Nov 2010 17:14 -0200, "Henrique Dallazuanna" > wrote: > > library(RMySQL) > conn <- dbConnect(MySQL(), user = 'user', password = > 'password', host = '[1]your_host.com') > Look at [2]http://biostat.mc.vanderbilt.edu/RMySQL for more > information > > On Fri, Nov 5, 2010 at 5:04 PM, Jan Theodore Galkowski > <[3]bayesianlogic at acm.org> wrote: > > Apologies if this is the wrong place to ask. I'm not aware of > a > mail list devoted to database interfaces. Please direct me if > so. > I am trying to use TSMySQL. It loads RMySQL, and apparent the > CRAN version has an .onLoad script which seeks out the local > MySQL server. If that fails, the package fails to load. > Thing is, I'm trying to access a MySQL database on a remote > machine for which I have authorized access. Anyone know how to > do > this, or suggest where I can find out? > Thanks. I have successfully accessed remote databases using > RODBC. Is that how I need to go? > - Jan > > > > [snip] > > References > > 1. http://your_host.com/ > 2. http://biostat.mc.vanderbilt.edu/RMySQL > 3. mailto:bayesianlogic at acm.org > -- > Jan Theodore Galkowski (o?) > > 607.239.1834 [mobile] > 607.239.1834 [home] > 617.444.4995 [work] > > bayesianlogic at acm.org > http://www.linkedin.com/in/deepdevelopment > > > "Eppur si muove." --Galilei > > > > [[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db From gux|@obo1982 @end|ng |rom gm@||@com Sat Nov 6 03:42:34 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Sat, 6 Nov 2010 10:42:34 +0800 Subject: [R-sig-DB] dbClearResult function error in package RpgSQL In-Reply-To: References: Message-ID: <000c01cb7d5c$47d70c10$d7852430$@com> I see, then I'll avoid using them now, what about the NULL problem with dbGetQuery, is there any good news? Xiaobo.Gu >>-----Original Message----- >>From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com] >>Sent: Friday, November 05, 2010 9:58 PM >>To: Xiaobo Gu >>Cc: r-sig-db at stat.math.ethz.ch >>Subject: Re: [R-sig-DB] dbClearResult function error in package RpgSQL >> >>Hi, RpgSQL is a thin layer over RJDBC and so only supports its >>functions. dbClearResults and hasCompleted seem not to be currently >>supported by RJDBC (I grep'd through its source code and do not see >>them mentioned) but perhaps it will in the future. If RJDBC does >>support them in the future then RpgSQL should automatically inherit >>them. >> >>On Fri, Nov 5, 2010 at 8:28 AM, Xiaobo Gu wrote: >>> Hi, >>> >>> When I call the dbClearResult(res) in package RpgSQL, the following error >>> message occurs: >>> >>> Error is(object, Cl) : no function "jcall" >>> >>> Note: the above error message is translated from Simplified Chinese. >>> >>> [[alternative HTML version deleted]] >>> >>> _______________________________________________ >>> R-sig-DB mailing list -- R Special Interest Group >>> R-sig-DB at stat.math.ethz.ch >>> https://stat.ethz.ch/mailman/listinfo/r-sig-db >>> >> >> >> >>-- >>Statistics & Software Consulting >>GKX Group, GKX Associates Inc. >>tel: 1-877-GKX-GROUP >>email: ggrothendieck at gmail.com From gux|@obo1982 @end|ng |rom gm@||@com Sat Nov 6 04:11:50 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Sat, 6 Nov 2010 11:11:50 +0800 Subject: [R-sig-DB] Data type error with RpgSQL on Windows XP SP3 32bit In-Reply-To: References: <19661.28312.520318.108726@max.nulle.part> <19661.41720.845742.291601@max.nulle.part> Message-ID: Hi, The fetch function in RpgSQL can't handle date column with NULL too, but it can handle integer, character, timestamp, time, and numeric columns with NULLs. Xiaobo On Mon, Nov 1, 2010 at 3:46 PM, Xiaobo Gu wrote: > It works too, but the original problem still occurs. > > con <- dbConnect(pgSQL(classPath="D:\\rtemp\\postgresql-8.4-702.jdbc4.zip")) >> ct <- 'CREATE TABLE DF ("a" INTEGER, "b" VARCHAR(255), "c" DATE,"d" DOUBLE >> PRECISION)' >>? dbSendUpdate(con, ct) >> >> DF <- data.frame(a = 1L, b = "Hello", c = Sys.Date(), d = 32) >> dbSendUpdate(con, "insert into DF values(1, 'Hello', to_date('2000-01-01', >> 'YYYY-MM-DD'), 32)") >> res <- dbGetQuery(con, "select * from DF") >> res > ? a???? b????????? c? d > 1 1 Hello 2000-01-01 32 >> str(res) > 'data.frame':?1 obs. of? 4 variables: > ?$ a: num 1 > ?$ b: Factor w/ 1 level "Hello": 1 > ?$ c:Class 'Date'? num 10957 > ?$ d: num 32 >> > > On Mon, Nov 1, 2010 at 10:33 AM, Gabor Grothendieck > wrote: >> >> On Sun, Oct 31, 2010 at 10:17 PM, Xiaobo Gu >> wrote: >> > Hi, >> > What's the versions for all the related components, R, DBI, rJava,RJDBC, >> > RpgSQL, PostgreSQL JDBC? >> > >> >> I am using the most recent versions of all packages and R 2.11.1 >> Patched on Windows Vista: >> >> > packageDescription("RJDBC")$Version >> [1] "0.1-5" >> > packageDescription("DBI")$Version >> [1] "0.2-5" >> > packageDescription("rJava")$Version >> [1] "0.8-7" >> > packageDescription("RpgSQL")$Version >> [1] "0.1-3.2" >> > R.version.string >> [1] "R version 2.11.1 Patched (2010-05-31 r52167)" >> > win.version() >> [1] "Windows Vista (build 6002) Service Pack 2" >> >> The Windows binary of rJava seems currently not to be available on CRAN: >> http://cran.r-project.org/web/packages/rJava/index.html >> but you can build it yourself or get it from R-Forge. >> >> Did you try running the reproducible example in my email? ?Did it work for >> you? >> >> -- >> Statistics & Software Consulting >> GKX Group, GKX Associates Inc. >> tel: 1-877-GKX-GROUP >> email: ggrothendieck at gmail.com > > > > On Mon, Nov 1, 2010 at 10:33 AM, Gabor Grothendieck > wrote: >> >> On Sun, Oct 31, 2010 at 10:17 PM, Xiaobo Gu >> wrote: >> > Hi, >> > What's the versions for all the related components, R, DBI, rJava,RJDBC, >> > RpgSQL, PostgreSQL JDBC? >> > >> >> I am using the most recent versions of all packages and R 2.11.1 >> Patched on Windows Vista: >> >> > packageDescription("RJDBC")$Version >> [1] "0.1-5" >> > packageDescription("DBI")$Version >> [1] "0.2-5" >> > packageDescription("rJava")$Version >> [1] "0.8-7" >> > packageDescription("RpgSQL")$Version >> [1] "0.1-3.2" >> > R.version.string >> [1] "R version 2.11.1 Patched (2010-05-31 r52167)" >> > win.version() >> [1] "Windows Vista (build 6002) Service Pack 2" >> >> The Windows binary of rJava seems currently not to be available on CRAN: >> http://cran.r-project.org/web/packages/rJava/index.html >> but you can build it yourself or get it from R-Forge. >> >> Did you try running the reproducible example in my email? ?Did it work for >> you? >> >> -- >> Statistics & Software Consulting >> GKX Group, GKX Associates Inc. >> tel: 1-877-GKX-GROUP >> email: ggrothendieck at gmail.com > > From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sun Nov 7 05:27:53 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Sat, 06 Nov 2010 21:27:53 -0700 Subject: [R-sig-DB] Installing RMySQL under CentOS 5.5 version of Linux? In-Reply-To: <152CB81C-1198-451E-BDAF-347A86DBCAA1@kenroku.kanazawa-u.ac.jp> References: <4CC783FE.5080004@structuremonitoring.com> <152CB81C-1198-451E-BDAF-347A86DBCAA1@kenroku.kanazawa-u.ac.jp> Message-ID: <4CD62AC9.5090205@structuremonitoring.com> Thanks very much to Tomoaki, Dirk, Jeremy, Sean, Jack, and all who responded to my earlier questions. I fixed the CentOS problems by replacing it with Fedora. Now I have a different problem: I'm trying to load just over 35 MB per day, 1 GB per month, into a MySQL database, and the time to load a file of 5 one-second observation is roughly 0.1*N^(1/6) seconds, where N = the cumulative number of files loaded to that point. Since 2^(1/6) = 1.12, each time the size of the database doubles, the time to load an observation (or a file) increases by 12 percent. The load time started at 90 ms per file. Now it's closer to 1 second per file. The size of the file will double roughly 6 more times before I'm done with this project, by which time the load time will be closer to 2 seconds per file -- if something else doesn't break before then. I don't think the problem is with R, because I've restarted R several times, and the total memory in use has not gone much over 400 MB = 12 percent of the 3.2 GB available on this computer; it's running nothing else at the moment. Is it normal for the load time to increase with the size of the table like this? Should I specify an index for each table? I did not. I understood from the documentation that it would take the first variable as the index under certain circumstances. Is this peculiar to MySQL? Might I have the same problem with postgreSQL? What do people usually do to work around problems like this? Thanks for your help. Best Wishes, Spencer On 10/26/2010 7:13 PM, Tomoaki NISHIYAMA wrote: > Hi, > > Did you install mysql-devel package? > On CentOS > # yum install mysql-devel > as root. That should install the library and headers required for > MySQL client development. > > Provided R is well installed as user application > just > $ R CMD INSTALL RMySQL_0.7-5.tar.gz > after downloading works. > If it is common application, perhaps you need to > do it as root again. > -- Tomoaki NISHIYAMA > > Advanced Science Research Center, > Kanazawa University, > 13-1 Takara-machi, > Kanazawa, 920-0934, Japan > > > On 2010/10/27, at 10:44, Spencer Graves wrote: > >> Hello: >> >> >> I'm not very unixed, and I'm trying to install RMySQL under >> CentOS 5.5 version of Linux. I'm having trouble parsing the >> installation instructions, e.g., at >> "http://cran.fhcrc.org/web/packages/RMySQL/INSTALL". Any suggestions? >> >> >> Thanks, >> Spencer >> >> _______________________________________________ >> R-sig-DB mailing list -- R Special Interest Group >> R-sig-DB at stat.math.ethz.ch >> https://stat.ethz.ch/mailman/listinfo/r-sig-db >> > > From tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp Sat Nov 13 06:31:18 2010 From: tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp (Tomoaki NISHIYAMA) Date: Sat, 13 Nov 2010 14:31:18 +0900 Subject: [R-sig-DB] Proposal of dbQuoteIdentifier for DBI specification Message-ID: <25881C42-50DB-4DF9-8400-78F292B0D5FA@kenroku.kanazawa-u.ac.jp> Hello all, We changed that the SQL identifiers be quoted instead of mangling in RPostgreSQL. This allows proper distinction of capital and small letters and make it possible to use the name as is what is defined in R world. This was applied uniformly to tablename argument for dbWriteTable(), dbExistsTable(), dbReadTable(), and dbRemoveTable() functions. However, this makes difficulty in schema usage, as now '.' (period) is allowed as a part of tablename and cannot be regarded as a separator. To allow construction of schema.table structure like "s.che.ma"."ta.ble", passing a vector c('s.che.ma', 'ta.ble') instead of string will be perhaps helpful. Each element of the vector will be quoted and thereafter connected with '.'. I am aware that RSQLite had similar discussion for mangling / quoting about one year ago https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000753.html but haven't found what the decision and what the future direction is. In addition while identifier quoting for Postgresql is simple, that looks different in MySQL that it is quote with back quote'`' rather than double quote and double quote works only under ANSI_QUOTES option. So, if quoting rule is different among backends, perhaps it is worth to define dbQuoteIdentifier(con, name) as a common way to quote an identifier. Perl DBI has quote_identifier and have two forms of synopsis $sql = $dbh->quote_identifier( $name ); $sql = $dbh->quote_identifier( $catalog, $schema, $table, \%attr ); http://search.cpan.org/~timb/DBI/DBI.pm#quote_identifier The example given there is interesting that $id = $dbh->quote_identifier( 'link', 'schema', 'table' ); should produce "schema"."table"@"link" for oracle. So, my proposal is that dbQuoteIdentifier() is defined similary that with a single string argument that should be quoted. For a vector argument two multiple strings, the two strings is interpreted as schema and tablename, and each element are quoted and concatenated according to the backend specification. For a vector argument with three elements, the elements are interpreted as dbname, schema, and tablename. Each element are quoted and concatenated using the rule appropriate for the back-end database management system. I think quote everything is the right way to go, and quoting should be made easier. Best wishes. -- Tomoaki NISHIYAMA Advanced Science Research Center, Kanazawa University, 13-1 Takara-machi, Kanazawa, 920-0934, Japan From gux|@obo1982 @end|ng |rom gm@||@com Sun Nov 14 05:28:33 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Sun, 14 Nov 2010 12:28:33 +0800 Subject: [R-sig-DB] character to factor transform in package RpgSQL Message-ID: Hi, I have looked at the source code of package RpgSQL, it seems the fetch function does not transform character column into factors, but when I use the class function to test the result data frame of function fetch, it all tells factor, can you help identify which part of the function doing the transformation, in our usage we want to keep some character column just as characters, bellowing is the source code of function fetch in package RpgSQL. Regards, Xiaobo Gu setMethod("fetch", signature(res="pgSQLResult", n="numeric"), def=function(res, n, ...) { cols <- .jcall(res at md, "I", "getColumnCount") if (cols < 1) return(NULL) l <- list() for (i in 1:cols) { ct <- .jcall(res at md, "I", "getColumnType", i) l[[i]] <- if (ct == -5 | ct ==-6 | (ct >= 2 & ct <= 8)) { numeric() } else if (ct == 91) { structure(numeric(), class = "Date") } else if (ct == 93) { structure(numeric(), class = class(Sys.time())) } else character() names(l)[i] <- .jcall(res at md, "S", "getColumnName", i) } j <- 0 while (.jcall(res at jr, "Z", "next")) { j <- j + 1 for (i in 1:cols) { l[[i]][j] <- if (is.numeric(l[[i]])) { l[[i]][j] <- .jcall(res at jr, "D", "getDouble", i) } else if (inherits(l[[i]], "Date")) { tentativeDate <- .jcall(res at jr, "S", "getString", i) if (length(tentativeDate) == 0 || tentativeDate == "0001-01-01 BC") tentativeDate <- NA l[[i]][j] <- as.Date(tentativeDate) } else { a <- .jcall(res at jr, "S", "getString", i) l[[i]][j] <- if (is.null(a)) NA else a } } if (n > 0 && j >= n) break } if (j) as.data.frame(l, row.names=1:j) else as.data.frame(l) }) From @eth @end|ng |rom u@erpr|m@ry@net Sun Nov 14 05:52:17 2010 From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon) Date: Sat, 13 Nov 2010 20:52:17 -0800 Subject: [R-sig-DB] character to factor transform in package RpgSQL In-Reply-To: References: Message-ID: Hi, On Sat, Nov 13, 2010 at 8:28 PM, Xiaobo Gu wrote: > Hi, > I have looked at the source code of package RpgSQL, it seems the fetch > function does not transform character column into factors, but when I > use the class function to test the result data frame of function > fetch, it all tells factor, can you help identify which part of the > function doing the transformation, in our usage we want to keep some > character column just as characters, bellowing is the source code of > function fetch in package RpgSQL. A guess is that you are seeing the default behavior of data.frame (and likely also as.data.frame) in transforming character vectors into factors. So I would suggest experimenting with this last bit: > ?if (j) > ? ?as.data.frame(l, row.names=1:j) > ?else > ? ?as.data.frame(l) > }) For example: > sapply(as.data.frame(letters[1:3]), class) letters[1:3] "factor" > sapply(as.data.frame(letters[1:3], stringsAsFactors=FALSE), class) letters[1:3] "character" + seth -- Seth Falcon | @sfalcon | http://userprimary.net/ From @eth @end|ng |rom u@erpr|m@ry@net Sun Nov 14 06:24:02 2010 From: @eth @end|ng |rom u@erpr|m@ry@net (Seth Falcon) Date: Sat, 13 Nov 2010 21:24:02 -0800 Subject: [R-sig-DB] Proposal of dbQuoteIdentifier for DBI specification In-Reply-To: <25881C42-50DB-4DF9-8400-78F292B0D5FA@kenroku.kanazawa-u.ac.jp> References: <25881C42-50DB-4DF9-8400-78F292B0D5FA@kenroku.kanazawa-u.ac.jp> Message-ID: Hi there, On Fri, Nov 12, 2010 at 9:31 PM, Tomoaki NISHIYAMA wrote: > We changed that the SQL identifiers be quoted instead of mangling in > RPostgreSQL. ?This allows proper distinction of capital and small letters > and make it possible to use the name as is what is defined in R world. > > This was applied uniformly to tablename argument for > dbWriteTable(), dbExistsTable(), dbReadTable(), and dbRemoveTable() > functions. > > However, this makes difficulty in schema usage, as now '.' (period) > is allowed as a part of tablename and cannot be regarded as a > separator. To allow construction of schema.table structure like > "s.che.ma"."ta.ble", passing a vector c('s.che.ma', 'ta.ble') > instead of string will be perhaps helpful. > Each element of the vector will be quoted and thereafter connected with '.'. > > I am aware that RSQLite had similar discussion for mangling / quoting > about one year ago > https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000753.html > but haven't found what the decision and what the future direction > is. A change was made to the make.db.names.default function in the DBI package to quote (using ") if the allow.keywords argument is set to FALSE. This was announced here: https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000769.html https://stat.ethz.ch/pipermail/r-sig-db/2009q4/000770.html > In addition while identifier quoting for Postgresql is simple, > that looks different in MySQL that it is quote with back quote'`' > rather than double quote and double quote works only under ANSI_QUOTES > option. ?So, if quoting rule is different among backends, > perhaps it is worth to define dbQuoteIdentifier(con, name) > as a common way to quote an identifier. > Perl DBI has quote_identifier and have two forms of synopsis > ?$sql = $dbh->quote_identifier( $name ); > ?$sql = $dbh->quote_identifier( $catalog, $schema, $table, \%attr ); > http://search.cpan.org/~timb/DBI/DBI.pm#quote_identifier > The example given there is interesting that > $id = $dbh->quote_identifier( 'link', 'schema', 'table' ); > should produce "schema"."table"@"link" for oracle. > > So, my proposal is that dbQuoteIdentifier() is defined similary that > with a single string argument that should be quoted. > For a vector argument two multiple strings, the two strings is interpreted > as schema and tablename, and each element are quoted and concatenated > according > to the backend specification. > For a vector argument with three elements, the elements are interpreted as > dbname, schema, and tablename. Each element are quoted and concatenated > using > the rule appropriate for the back-end database management system. > > I think quote everything is the right way to go, > and quoting should be made easier. I think I agree that quoting everything is a reasonable approach. Having a means to determine the correct quote character based on the backend and its current settings seems valuable. I would support a patch along those lines. Care should be taken so that extra layers of function calls are not added to every db operation to determine and apply quoting (IMO). So I'm generally in favor of something along the lines of dbQuoteIdentifier. However, I don't like the semantics you have proposed for the behavior of dbQuoteIdentifier in terms of the return value for vectors based on length. Such a function should do what it says, quote identifiers. Having a separate function to deal with dbname, schema, and tablename concatenation might be useful, but that feels like something to handle separately. + seth -- Seth Falcon | @sfalcon | http://userprimary.net/ From gux|@obo1982 @end|ng |rom gm@||@com Sun Nov 14 12:59:34 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Sun, 14 Nov 2010 19:59:34 +0800 Subject: [R-sig-DB] character to factor transform in package RpgSQL In-Reply-To: References: Message-ID: Hi Seth, You are right, I have change the fetch function to this: setMethod("fetch", signature(res="pgSQLResult", n="numeric"), def=function(res, n, stringsAsFactors = TRUE, ...) { cols <- .jcall(res at md, "I", "getColumnCount") if (cols < 1) return(NULL) l <- list() for (i in 1:cols) { ct <- .jcall(res at md, "I", "getColumnType", i) l[[i]] <- if (ct == -5 | ct ==-6 | (ct >= 2 & ct <= 8)) { numeric() } else if (ct == 91) { structure(numeric(), class = "Date") } else if (ct == 93) { structure(numeric(), class = class(Sys.time())) } else character() names(l)[i] <- .jcall(res at md, "S", "getColumnName", i) } j <- 0 while (.jcall(res at jr, "Z", "next")) { j <- j + 1 for (i in 1:cols) { l[[i]][j] <- if (is.numeric(l[[i]])) { l[[i]][j] <- .jcall(res at jr, "D", "getDouble", i) } else if (inherits(l[[i]], "Date")) { tentativeDate <- .jcall(res at jr, "S", "getString", i) if (length(tentativeDate) == 0 || tentativeDate == "0001-01-01 BC") tentativeDate <- NA l[[i]][j] <- as.Date(tentativeDate) } else { a <- .jcall(res at jr, "S", "getString", i) l[[i]][j] <- if (is.null(a)) NA else a } } if (n > 0 && j >= n) break } if (j) as.data.frame(l, row.names=1:j, stringsAsFactors = stringsAsFactors ) else as.data.frame(l, stringsAsFactors = stringsAsFactors) }) On Sun, Nov 14, 2010 at 12:52 PM, Seth Falcon wrote: > Hi, > > On Sat, Nov 13, 2010 at 8:28 PM, Xiaobo Gu wrote: >> Hi, >> I have looked at the source code of package RpgSQL, it seems the fetch >> function does not transform character column into factors, but when I >> use the class function to test the result data frame of function >> fetch, it all tells factor, can you help identify which part of the >> function doing the transformation, in our usage we want to keep some >> character column just as characters, bellowing is the source code of >> function fetch in package RpgSQL. > > A guess is that you are seeing the default behavior of data.frame (and > likely also as.data.frame) in transforming character vectors into > factors. > > So I would suggest experimenting with this last bit: >> ?if (j) >> ? ?as.data.frame(l, row.names=1:j) >> ?else >> ? ?as.data.frame(l) >> }) > > For example: > > ? ?> sapply(as.data.frame(letters[1:3]), class) > ? ?letters[1:3] > ? ? ? ?"factor" > ? ?> sapply(as.data.frame(letters[1:3], stringsAsFactors=FALSE), class) > ? ?letters[1:3] > ? ? "character" > > > + seth > > > -- > Seth Falcon | @sfalcon | http://userprimary.net/ > From tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp Mon Nov 15 11:35:03 2010 From: tomo@k|n @end|ng |rom kenroku@k@n@z@w@-u@@c@jp (Tomoaki NISHIYAMA) Date: Mon, 15 Nov 2010 19:35:03 +0900 Subject: [R-sig-DB] Proposal of dbQuoteIdentifier for DBI specification In-Reply-To: References: <25881C42-50DB-4DF9-8400-78F292B0D5FA@kenroku.kanazawa-u.ac.jp> Message-ID: <2C554429-ED30-45CE-9159-09C82A770042@kenroku.kanazawa-u.ac.jp> Hi, Thanks for comments. > However, I don't like the semantics you have > proposed for the behavior of dbQuoteIdentifier in terms of the return > value for vectors based on length. Such a function should do what it > says, quote identifiers. Having a separate function to deal with > dbname, schema, and tablename concatenation might be useful, but that > feels like something to handle separately. Ok, that was just following Perl example. But, we can give different name like dbConstructTableReference(name) for a polymorphic one. The utilization is constuction of SQL from name object that is used to create the table through dbWriteTable(con, name, dataframe) like paste('SELECT * from', dbConstructTableReference(con, name), 'LIMIT 10') dbQuoteIdentifier may still have value for constructing quoted column name. >> dbWriteTable(), dbExistsTable(), dbReadTable(), and dbRemoveTable() Have just a single name argument, and its difficult to unambiguously determine the schema if the user wants to specify a schema or the table name contains periods (which is allowed in PostgreSQL but not allowed in MySQL). So, in fact, I intend to extend these functions to work with vector for the name argument to specify a set like c('database', 'schema', 'table'). Surely, there could a different decision to make additional arguments like dbWriteTable(con, name, dataframe, schema='schema', database='dbname') and dbConstructTableReference(con, name, schema='schema', database='dbname') I'm more than happy if you have a better name. I like it be shorter. But, the tradition here seems to write in full. > Care should be taken so that extra layers of > function calls are not added to every db operation to determine and > apply quoting (IMO). I think that the call for table name construction function internally to dbWriteTable etc. will not significantly impact the performance, as the number executed is much lower compared with actual INSERT/ RETRIEVE operation. So, I think user should not required to explicitly write these function when calling dbWriteTable, but better be done automatically by the library. For dbSendQuery and dbGetQeury, these function do not automatically apply, and if the programmer can write the name directly, it should work without the overhead for the quoting. But, if its constructing from a variable the content is determined at runtime, perhaps its best to call the quoting function to ensure that the SQL is not malformed by any special character within the variable. -- Tomoaki NISHIYAMA Advanced Science Research Center, Kanazawa University, 13-1 Takara-machi, Kanazawa, 920-0934, Japan From h@r|@n @end|ng |rom h@rr|@@n@me Thu Nov 18 18:15:37 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Thu, 18 Nov 2010 12:15:37 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) Message-ID: Hi all, I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux (CentOS 5/RHEL 5). As previously established, it's nearly impossible, although I'm awfully close now and just need one bit of help. Here's what works: I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version 11.2.0.2.0). export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib export TNS_ADMIN=/etc/oracle set up /etc/oracle/tnsname.ora The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and Redhat are aware of this and stubbornly refuse to fix it. Therefore I installed easysoft's ODBC Oracle driver. Note that this costs something like $2000 to license, but if you've got Oracle, you can probably pay for it. The easysoft driver installs to /usr/local/easysoft. It includes unixODBC 2.2.12. (I uninstalled the factor 2.2.11 version) I confirmed that it was properly configured and working by running /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and password are in /etc/odbc.ini. I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to /usr/local/lib, /include and /bin, respectively. Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h, and -lodbc. > library(RODBC) > hdl <- odbcConnect('ORACLE') > sqlQuery(hdl, 'select * from catalog.program where rownum < 10') Error in odbcQuery(channel, query, rows_at_time) : Calloc could not allocate (-1 of 22816) memory So close! What am I missing? Thanks! -Harlan [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Thu Nov 18 18:26:15 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Thu, 18 Nov 2010 11:26:15 -0600 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: Message-ID: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: > Hi all, > > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, > although I'm awfully close now and just need one bit of help. > > Here's what works: > > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version > 11.2.0.2.0). > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib > export TNS_ADMIN=/etc/oracle > set up /etc/oracle/tnsname.ora > > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and > Redhat are aware of this and stubbornly refuse to fix it. Therefore I > installed easysoft's ODBC Oracle driver. Note that this costs something like > $2000 to license, but if you've got Oracle, you can probably pay for it. > > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC > 2.2.12. (I uninstalled the factor 2.2.11 version) > > I confirmed that it was properly configured and working by running > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and > password are in /etc/odbc.ini. > > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to > /usr/local/lib, /include and /bin, respectively. > > Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h, > and -lodbc. > >> library(RODBC) >> hdl <- odbcConnect('ORACLE') >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') > Error in odbcQuery(channel, query, rows_at_time) : > Calloc could not allocate (-1 of 22816) memory > > > So close! What am I missing? > > Thanks! > > -Harlan Harlan, glad to see some progress, albeit at a notable cost. Try using 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery(). I have found the need to use that setting, rather than the default 100, in order to successfully retrieve data from our Oracle server on OSX. The smaller value may also help to workaround the memory allocation issue, presuming that something more subtle is not going on. HTH, Marc Schwartz From h@r|@n @end|ng |rom h@rr|@@n@me Thu Nov 18 18:29:59 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Thu, 18 Nov 2010 12:29:59 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> Message-ID: Quick response -- thanks! Nope, doesn't help. > library(RODBC) > hdl <- odbcConnect('ORACLE', rows_at_time=1) > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', rows_at_time=1) Error in odbcQuery(channel, query, rows_at_time) : Calloc could not allocate (-1 of 22816) memory -Harlan On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz wrote: > On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: > > > Hi all, > > > > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux > > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, > > although I'm awfully close now and just need one bit of help. > > > > Here's what works: > > > > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version > > 11.2.0.2.0). > > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ > > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib > > export TNS_ADMIN=/etc/oracle > > set up /etc/oracle/tnsname.ora > > > > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That > version > > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle > and > > Redhat are aware of this and stubbornly refuse to fix it. Therefore I > > installed easysoft's ODBC Oracle driver. Note that this costs something > like > > $2000 to license, but if you've got Oracle, you can probably pay for it. > > > > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC > > 2.2.12. (I uninstalled the factor 2.2.11 version) > > > > I confirmed that it was properly configured and working by running > > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username > and > > password are in /etc/odbc.ini. > > > > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to > > /usr/local/lib, /include and /bin, respectively. > > > > Now I install RODBC from source. It seems to work, finding sql.h, > sqlext.h, > > and -lodbc. > > > >> library(RODBC) > >> hdl <- odbcConnect('ORACLE') > >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') > > Error in odbcQuery(channel, query, rows_at_time) : > > Calloc could not allocate (-1 of 22816) memory > > > > > > So close! What am I missing? > > > > Thanks! > > > > -Harlan > > > Harlan, glad to see some progress, albeit at a notable cost. > > Try using 'rows_at_time = 1' in the calls to both odbcConnect() and > sqlQuery(). > > I have found the need to use that setting, rather than the default 100, in > order to successfully retrieve data from our Oracle server on OSX. > > The smaller value may also help to workaround the memory allocation issue, > presuming that something more subtle is not going on. > > HTH, > > Marc Schwartz > > [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Thu Nov 18 18:52:30 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Thu, 18 Nov 2010 11:52:30 -0600 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> Message-ID: <789BC982-849A-4849-99B3-CB708108EC13@me.com> OK. What is interesting, albeit, it may be a red herring, is the -1 in the error message. Is that an indication of an integer wrap around or something more subtle? Please be sure that you are running 64 bit R. Check the result of: .Machine$sizeof.pointer It should return 8 if you are running 64 bit R. If it returns 4, you are running 32 bit R, which would conflict with the rest of the 64 bit tool chain. If by chance you are running 32 bit R, you will need to install 64 bit R and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code would be compiled as 32 bit programs. Can you also perhaps try a query that should result in a small result set? Perhaps a single column from a single row? Marc On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: > Quick response -- thanks! Nope, doesn't help. > > > library(RODBC) > > hdl <- odbcConnect('ORACLE', rows_at_time=1) > > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', rows_at_time=1) > Error in odbcQuery(channel, query, rows_at_time) : > Calloc could not allocate (-1 of 22816) memory > > -Harlan > > On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz wrote: > On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: > > > Hi all, > > > > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux > > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, > > although I'm awfully close now and just need one bit of help. > > > > Here's what works: > > > > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version > > 11.2.0.2.0). > > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ > > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib > > export TNS_ADMIN=/etc/oracle > > set up /etc/oracle/tnsname.ora > > > > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version > > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and > > Redhat are aware of this and stubbornly refuse to fix it. Therefore I > > installed easysoft's ODBC Oracle driver. Note that this costs something like > > $2000 to license, but if you've got Oracle, you can probably pay for it. > > > > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC > > 2.2.12. (I uninstalled the factor 2.2.11 version) > > > > I confirmed that it was properly configured and working by running > > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and > > password are in /etc/odbc.ini. > > > > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to > > /usr/local/lib, /include and /bin, respectively. > > > > Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h, > > and -lodbc. > > > >> library(RODBC) > >> hdl <- odbcConnect('ORACLE') > >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') > > Error in odbcQuery(channel, query, rows_at_time) : > > Calloc could not allocate (-1 of 22816) memory > > > > > > So close! What am I missing? > > > > Thanks! > > > > -Harlan > > > Harlan, glad to see some progress, albeit at a notable cost. > > Try using 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery(). > > I have found the need to use that setting, rather than the default 100, in order to successfully retrieve data from our Oracle server on OSX. > > The smaller value may also help to workaround the memory allocation issue, presuming that something more subtle is not going on. > > HTH, > > Marc Schwartz > > [[alternative HTML version deleted]] From h@r|@n @end|ng |rom h@rr|@@n@me Thu Nov 18 19:03:03 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Thu, 18 Nov 2010 13:03:03 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: <789BC982-849A-4849-99B3-CB708108EC13@me.com> References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> Message-ID: I wondered about the -1 too... But nope, neither of these suggestions work... > .Machine$sizeof.pointer [1] 8 > hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select name from catalog.program where rownum < 2', rows_at_time=1, as.is=TRUE) Error in odbcQuery(channel, query, rows_at_time) : Calloc could not allocate (-1 of 22816) memory -Harlan On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz wrote: > OK. What is interesting, albeit, it may be a red herring, is the -1 in the > error message. Is that an indication of an integer wrap around or something > more subtle? > > Please be sure that you are running 64 bit R. Check the result of: > > .Machine$sizeof.pointer > > It should return 8 if you are running 64 bit R. If it returns 4, you are > running 32 bit R, which would conflict with the rest of the 64 bit tool > chain. > > If by chance you are running 32 bit R, you will need to install 64 bit R > and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code > would be compiled as 32 bit programs. > > Can you also perhaps try a query that should result in a small result set? > Perhaps a single column from a single row? > > > Marc > > On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: > > Quick response -- thanks! Nope, doesn't help. > > > library(RODBC) > > hdl <- odbcConnect('ORACLE', rows_at_time=1) > > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', > rows_at_time=1) > Error in odbcQuery(channel, query, rows_at_time) : > Calloc could not allocate (-1 of 22816) memory > > -Harlan > > On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz wrote: > >> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: >> >> > Hi all, >> > >> > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux >> > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, >> > although I'm awfully close now and just need one bit of help. >> > >> > Here's what works: >> > >> > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms >> (version >> > 11.2.0.2.0). >> > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ >> > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >> > export TNS_ADMIN=/etc/oracle >> > set up /etc/oracle/tnsname.ora >> > >> > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That >> version >> > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle >> and >> > Redhat are aware of this and stubbornly refuse to fix it. Therefore I >> > installed easysoft's ODBC Oracle driver. Note that this costs something >> like >> > $2000 to license, but if you've got Oracle, you can probably pay for it. >> > >> > The easysoft driver installs to /usr/local/easysoft. It includes >> unixODBC >> > 2.2.12. (I uninstalled the factor 2.2.11 version) >> > >> > I confirmed that it was properly configured and working by running >> > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username >> and >> > password are in /etc/odbc.ini. >> > >> > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to >> > /usr/local/lib, /include and /bin, respectively. >> > >> > Now I install RODBC from source. It seems to work, finding sql.h, >> sqlext.h, >> > and -lodbc. >> > >> >> library(RODBC) >> >> hdl <- odbcConnect('ORACLE') >> >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') >> > Error in odbcQuery(channel, query, rows_at_time) : >> > Calloc could not allocate (-1 of 22816) memory >> > >> > >> > So close! What am I missing? >> > >> > Thanks! >> > >> > -Harlan >> >> >> Harlan, glad to see some progress, albeit at a notable cost. >> >> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and >> sqlQuery(). >> >> I have found the need to use that setting, rather than the default 100, in >> order to successfully retrieve data from our Oracle server on OSX. >> >> The smaller value may also help to workaround the memory allocation issue, >> presuming that something more subtle is not going on. >> >> HTH, >> >> Marc Schwartz >> >> > > [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Thu Nov 18 19:13:36 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Thu, 18 Nov 2010 12:13:36 -0600 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> Message-ID: <7E28F693-D990-4436-B83A-28D737D38318@me.com> OK....the more I think about this, the more I believe that there is an integer overflow problem. This may yet be an indication of a 32/64 bit conflict someplace. The question then is where and why. Which version of R are you running? Provide the output of: sessionInfo() Marc On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: > I wondered about the -1 too... > > But nope, neither of these suggestions work... > > > .Machine$sizeof.pointer > [1] 8 > > hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select name from catalog.program where rownum < 2', rows_at_time=1, as.is=TRUE) > Error in odbcQuery(channel, query, rows_at_time) : > Calloc could not allocate (-1 of 22816) memory > > -Harlan > > On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz wrote: > OK. What is interesting, albeit, it may be a red herring, is the -1 in the error message. Is that an indication of an integer wrap around or something more subtle? > > Please be sure that you are running 64 bit R. Check the result of: > > .Machine$sizeof.pointer > > It should return 8 if you are running 64 bit R. If it returns 4, you are running 32 bit R, which would conflict with the rest of the 64 bit tool chain. > > If by chance you are running 32 bit R, you will need to install 64 bit R and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code would be compiled as 32 bit programs. > > Can you also perhaps try a query that should result in a small result set? Perhaps a single column from a single row? > > > Marc > > On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: > >> Quick response -- thanks! Nope, doesn't help. >> >> > library(RODBC) >> > hdl <- odbcConnect('ORACLE', rows_at_time=1) >> > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', rows_at_time=1) >> Error in odbcQuery(channel, query, rows_at_time) : >> Calloc could not allocate (-1 of 22816) memory >> >> -Harlan >> >> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz wrote: >> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: >> >> > Hi all, >> > >> > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux >> > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, >> > although I'm awfully close now and just need one bit of help. >> > >> > Here's what works: >> > >> > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version >> > 11.2.0.2.0). >> > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ >> > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >> > export TNS_ADMIN=/etc/oracle >> > set up /etc/oracle/tnsname.ora >> > >> > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version >> > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and >> > Redhat are aware of this and stubbornly refuse to fix it. Therefore I >> > installed easysoft's ODBC Oracle driver. Note that this costs something like >> > $2000 to license, but if you've got Oracle, you can probably pay for it. >> > >> > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC >> > 2.2.12. (I uninstalled the factor 2.2.11 version) >> > >> > I confirmed that it was properly configured and working by running >> > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and >> > password are in /etc/odbc.ini. >> > >> > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to >> > /usr/local/lib, /include and /bin, respectively. >> > >> > Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h, >> > and -lodbc. >> > >> >> library(RODBC) >> >> hdl <- odbcConnect('ORACLE') >> >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') >> > Error in odbcQuery(channel, query, rows_at_time) : >> > Calloc could not allocate (-1 of 22816) memory >> > >> > >> > So close! What am I missing? >> > >> > Thanks! >> > >> > -Harlan >> >> >> Harlan, glad to see some progress, albeit at a notable cost. >> >> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery(). >> >> I have found the need to use that setting, rather than the default 100, in order to successfully retrieve data from our Oracle server on OSX. >> >> The smaller value may also help to workaround the memory allocation issue, presuming that something more subtle is not going on. >> >> HTH, >> >> Marc Schwartz >> >> > > From h@r|@n @end|ng |rom h@rr|@@n@me Thu Nov 18 19:23:05 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Thu, 18 Nov 2010 13:23:05 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: <7E28F693-D990-4436-B83A-28D737D38318@me.com> References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> <7E28F693-D990-4436-B83A-28D737D38318@me.com> Message-ID: This is the version of R you get by "yum install R" in CentOS 5 from EPEL. Here you are: > sessionInfo() R version 2.11.1 (2010-05-31) x86_64-redhat-linux-gnu locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 [7] LC_PAPER=en_US.UTF-8 LC_NAME=C [9] LC_ADDRESS=C LC_TELEPHONE=C [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RODBC_1.3-2 loaded via a namespace (and not attached): [1] tools_2.11.1 -Harlan On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz wrote: > OK....the more I think about this, the more I believe that there is an > integer overflow problem. This may yet be an indication of a 32/64 bit > conflict someplace. > > The question then is where and why. > > Which version of R are you running? Provide the output of: > > sessionInfo() > > Marc > > On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: > > > I wondered about the -1 too... > > > > But nope, neither of these suggestions work... > > > > > .Machine$sizeof.pointer > > [1] 8 > > > hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select > name from catalog.program where rownum < 2', rows_at_time=1, as.is=TRUE) > > Error in odbcQuery(channel, query, rows_at_time) : > > Calloc could not allocate (-1 of 22816) memory > > > > -Harlan > > > > On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz > wrote: > > OK. What is interesting, albeit, it may be a red herring, is the -1 in > the error message. Is that an indication of an integer wrap around or > something more subtle? > > > > Please be sure that you are running 64 bit R. Check the result of: > > > > .Machine$sizeof.pointer > > > > It should return 8 if you are running 64 bit R. If it returns 4, you are > running 32 bit R, which would conflict with the rest of the 64 bit tool > chain. > > > > If by chance you are running 32 bit R, you will need to install 64 bit R > and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code > would be compiled as 32 bit programs. > > > > Can you also perhaps try a query that should result in a small result > set? Perhaps a single column from a single row? > > > > > > Marc > > > > On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: > > > >> Quick response -- thanks! Nope, doesn't help. > >> > >> > library(RODBC) > >> > hdl <- odbcConnect('ORACLE', rows_at_time=1) > >> > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', > rows_at_time=1) > >> Error in odbcQuery(channel, query, rows_at_time) : > >> Calloc could not allocate (-1 of 22816) memory > >> > >> -Harlan > >> > >> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz > wrote: > >> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: > >> > >> > Hi all, > >> > > >> > I'm trying again to get the R<->Oracle toolchain to work on 64-bit > Linux > >> > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, > >> > although I'm awfully close now and just need one bit of help. > >> > > >> > Here's what works: > >> > > >> > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms > (version > >> > 11.2.0.2.0). > >> > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ > >> > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib > >> > export TNS_ADMIN=/etc/oracle > >> > set up /etc/oracle/tnsname.ora > >> > > >> > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That > version > >> > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle > and > >> > Redhat are aware of this and stubbornly refuse to fix it. Therefore I > >> > installed easysoft's ODBC Oracle driver. Note that this costs > something like > >> > $2000 to license, but if you've got Oracle, you can probably pay for > it. > >> > > >> > The easysoft driver installs to /usr/local/easysoft. It includes > unixODBC > >> > 2.2.12. (I uninstalled the factor 2.2.11 version) > >> > > >> > I confirmed that it was properly configured and working by running > >> > /usr/local/easysoft/unixODBC/bin/isql and querying my database. > Username and > >> > password are in /etc/odbc.ini. > >> > > >> > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to > >> > /usr/local/lib, /include and /bin, respectively. > >> > > >> > Now I install RODBC from source. It seems to work, finding sql.h, > sqlext.h, > >> > and -lodbc. > >> > > >> >> library(RODBC) > >> >> hdl <- odbcConnect('ORACLE') > >> >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') > >> > Error in odbcQuery(channel, query, rows_at_time) : > >> > Calloc could not allocate (-1 of 22816) memory > >> > > >> > > >> > So close! What am I missing? > >> > > >> > Thanks! > >> > > >> > -Harlan > >> > >> > >> Harlan, glad to see some progress, albeit at a notable cost. > >> > >> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and > sqlQuery(). > >> > >> I have found the need to use that setting, rather than the default 100, > in order to successfully retrieve data from our Oracle server on OSX. > >> > >> The smaller value may also help to workaround the memory allocation > issue, presuming that something more subtle is not going on. > >> > >> HTH, > >> > >> Marc Schwartz > >> > >> > > > > > > [[alternative HTML version deleted]] From m@rc_@chw@rtz @end|ng |rom me@com Thu Nov 18 19:37:56 2010 From: m@rc_@chw@rtz @end|ng |rom me@com (Marc Schwartz) Date: Thu, 18 Nov 2010 12:37:56 -0600 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> <7E28F693-D990-4436-B83A-28D737D38318@me.com> Message-ID: <8D184B68-29BB-49CC-9E9B-177678B33D86@me.com> OK. Looks like 2.12.0 is now available, but not yet in the yum update mirror pipeline. I think that Tom was just in the process of making that available, so it may take a bit before the mirrors pick it up. You can download the RPM directly from here: http://download.fedora.redhat.com/pub/epel/5/x86_64/repoview/R.html and then install via the CLI, if you are comfortable doing that. It would be prudent to be sure that any issues here are not related to something that has been fixed since 2.11.1 was released. Short of that, we may have to wait for Prof. Ripley to offer some comments, since he will be more intimately familiar with the low level issues in RODBC. Marc On Nov 18, 2010, at 12:23 PM, Harlan Harris wrote: > This is the version of R you get by "yum install R" in CentOS 5 from EPEL. Here you are: > > > sessionInfo() > R version 2.11.1 (2010-05-31) > x86_64-redhat-linux-gnu > > locale: > [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C > [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 > [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 > [7] LC_PAPER=en_US.UTF-8 LC_NAME=C > [9] LC_ADDRESS=C LC_TELEPHONE=C > [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C > > attached base packages: > [1] stats graphics grDevices utils datasets methods base > > other attached packages: > [1] RODBC_1.3-2 > > loaded via a namespace (and not attached): > [1] tools_2.11.1 > > -Harlan > > > On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz wrote: > OK....the more I think about this, the more I believe that there is an integer overflow problem. This may yet be an indication of a 32/64 bit conflict someplace. > > The question then is where and why. > > Which version of R are you running? Provide the output of: > > sessionInfo() > > Marc > > On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: > > > I wondered about the -1 too... > > > > But nope, neither of these suggestions work... > > > > > .Machine$sizeof.pointer > > [1] 8 > > > hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select name from catalog.program where rownum < 2', rows_at_time=1, as.is=TRUE) > > Error in odbcQuery(channel, query, rows_at_time) : > > Calloc could not allocate (-1 of 22816) memory > > > > -Harlan > > > > On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz wrote: > > OK. What is interesting, albeit, it may be a red herring, is the -1 in the error message. Is that an indication of an integer wrap around or something more subtle? > > > > Please be sure that you are running 64 bit R. Check the result of: > > > > .Machine$sizeof.pointer > > > > It should return 8 if you are running 64 bit R. If it returns 4, you are running 32 bit R, which would conflict with the rest of the 64 bit tool chain. > > > > If by chance you are running 32 bit R, you will need to install 64 bit R and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code would be compiled as 32 bit programs. > > > > Can you also perhaps try a query that should result in a small result set? Perhaps a single column from a single row? > > > > > > Marc > > > > On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: > > > >> Quick response -- thanks! Nope, doesn't help. > >> > >> > library(RODBC) > >> > hdl <- odbcConnect('ORACLE', rows_at_time=1) > >> > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', rows_at_time=1) > >> Error in odbcQuery(channel, query, rows_at_time) : > >> Calloc could not allocate (-1 of 22816) memory > >> > >> -Harlan > >> > >> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz wrote: > >> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: > >> > >> > Hi all, > >> > > >> > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux > >> > (CentOS 5/RHEL 5). As previously established, it's nearly impossible, > >> > although I'm awfully close now and just need one bit of help. > >> > > >> > Here's what works: > >> > > >> > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version > >> > 11.2.0.2.0). > >> > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ > >> > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib > >> > export TNS_ADMIN=/etc/oracle > >> > set up /etc/oracle/tnsname.ora > >> > > >> > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version > >> > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and > >> > Redhat are aware of this and stubbornly refuse to fix it. Therefore I > >> > installed easysoft's ODBC Oracle driver. Note that this costs something like > >> > $2000 to license, but if you've got Oracle, you can probably pay for it. > >> > > >> > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC > >> > 2.2.12. (I uninstalled the factor 2.2.11 version) > >> > > >> > I confirmed that it was properly configured and working by running > >> > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and > >> > password are in /etc/odbc.ini. > >> > > >> > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to > >> > /usr/local/lib, /include and /bin, respectively. > >> > > >> > Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h, > >> > and -lodbc. > >> > > >> >> library(RODBC) > >> >> hdl <- odbcConnect('ORACLE') > >> >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') > >> > Error in odbcQuery(channel, query, rows_at_time) : > >> > Calloc could not allocate (-1 of 22816) memory > >> > > >> > > >> > So close! What am I missing? > >> > > >> > Thanks! > >> > > >> > -Harlan > >> > >> > >> Harlan, glad to see some progress, albeit at a notable cost. > >> > >> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery(). > >> > >> I have found the need to use that setting, rather than the default 100, in order to successfully retrieve data from our Oracle server on OSX. > >> > >> The smaller value may also help to workaround the memory allocation issue, presuming that something more subtle is not going on. > >> > >> HTH, > >> > >> Marc Schwartz > >> > >> > > > > > > [[alternative HTML version deleted]] From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Thu Nov 18 19:40:11 2010 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Thu, 18 Nov 2010 18:40:11 +0000 (GMT) Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> <7E28F693-D990-4436-B83A-28D737D38318@me.com> Message-ID: One thing I don't see being tried: ?odbcConnect says believeNRows: logical. Is the number of rows returned by the ODBC connection believable? Not true for some Oracle and Sybase drivers, apparently, nor for Actual Technologies' SQLite driver for Mac OS X. Given the hint about Oracle, it looks worth a try, although I think the usual indication is claiming zero rows are available when a positive number are. You can run R under a debugger to find out exactly what is going wrong. On Thu, 18 Nov 2010, Harlan Harris wrote: > This is the version of R you get by "yum install R" in CentOS 5 from EPEL. > Here you are: > >> sessionInfo() > R version 2.11.1 (2010-05-31) > x86_64-redhat-linux-gnu > > locale: > [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C > [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 > [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 > [7] LC_PAPER=en_US.UTF-8 LC_NAME=C > [9] LC_ADDRESS=C LC_TELEPHONE=C > [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C > > attached base packages: > [1] stats graphics grDevices utils datasets methods base > > other attached packages: > [1] RODBC_1.3-2 > > loaded via a namespace (and not attached): > [1] tools_2.11.1 > > -Harlan > > > On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz wrote: > >> OK....the more I think about this, the more I believe that there is an >> integer overflow problem. This may yet be an indication of a 32/64 bit >> conflict someplace. >> >> The question then is where and why. >> >> Which version of R are you running? Provide the output of: >> >> sessionInfo() >> >> Marc >> >> On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: >> >>> I wondered about the -1 too... >>> >>> But nope, neither of these suggestions work... >>> >>>> .Machine$sizeof.pointer >>> [1] 8 >>>> hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select >> name from catalog.program where rownum < 2', rows_at_time=1, as.is=TRUE) >>> Error in odbcQuery(channel, query, rows_at_time) : >>> Calloc could not allocate (-1 of 22816) memory >>> >>> -Harlan >>> >>> On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz >> wrote: >>> OK. What is interesting, albeit, it may be a red herring, is the -1 in >> the error message. Is that an indication of an integer wrap around or >> something more subtle? >>> >>> Please be sure that you are running 64 bit R. Check the result of: >>> >>> .Machine$sizeof.pointer >>> >>> It should return 8 if you are running 64 bit R. If it returns 4, you are >> running 32 bit R, which would conflict with the rest of the 64 bit tool >> chain. >>> >>> If by chance you are running 32 bit R, you will need to install 64 bit R >> and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code >> would be compiled as 32 bit programs. >>> >>> Can you also perhaps try a query that should result in a small result >> set? Perhaps a single column from a single row? >>> >>> >>> Marc >>> >>> On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: >>> >>>> Quick response -- thanks! Nope, doesn't help. >>>> >>>>> library(RODBC) >>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1) >>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10', >> rows_at_time=1) >>>> Error in odbcQuery(channel, query, rows_at_time) : >>>> Calloc could not allocate (-1 of 22816) memory >>>> >>>> -Harlan >>>> >>>> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz >> wrote: >>>> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: >>>> >>>>> Hi all, >>>>> >>>>> I'm trying again to get the R<->Oracle toolchain to work on 64-bit >> Linux >>>>> (CentOS 5/RHEL 5). As previously established, it's nearly impossible, >>>>> although I'm awfully close now and just need one bit of help. >>>>> >>>>> Here's what works: >>>>> >>>>> I installed the 64-bit Oracle Instant Client Basic and ODBC rpms >> (version >>>>> 11.2.0.2.0). >>>>> export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ >>>>> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >>>>> export TNS_ADMIN=/etc/oracle >>>>> set up /etc/oracle/tnsname.ora >>>>> >>>>> The version of unixODBC that's installed on Redhat 5 is 2.2.11. That >> version >>>>> is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle >> and >>>>> Redhat are aware of this and stubbornly refuse to fix it. Therefore I >>>>> installed easysoft's ODBC Oracle driver. Note that this costs >> something like >>>>> $2000 to license, but if you've got Oracle, you can probably pay for >> it. >>>>> >>>>> The easysoft driver installs to /usr/local/easysoft. It includes >> unixODBC >>>>> 2.2.12. (I uninstalled the factor 2.2.11 version) >>>>> >>>>> I confirmed that it was properly configured and working by running >>>>> /usr/local/easysoft/unixODBC/bin/isql and querying my database. >> Username and >>>>> password are in /etc/odbc.ini. >>>>> >>>>> I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to >>>>> /usr/local/lib, /include and /bin, respectively. >>>>> >>>>> Now I install RODBC from source. It seems to work, finding sql.h, >> sqlext.h, >>>>> and -lodbc. >>>>> >>>>>> library(RODBC) >>>>>> hdl <- odbcConnect('ORACLE') >>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') >>>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>> Calloc could not allocate (-1 of 22816) memory >>>>> >>>>> >>>>> So close! What am I missing? >>>>> >>>>> Thanks! >>>>> >>>>> -Harlan >>>> >>>> >>>> Harlan, glad to see some progress, albeit at a notable cost. >>>> >>>> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and >> sqlQuery(). >>>> >>>> I have found the need to use that setting, rather than the default 100, >> in order to successfully retrieve data from our Oracle server on OSX. >>>> >>>> The smaller value may also help to workaround the memory allocation >> issue, presuming that something more subtle is not going on. >>>> >>>> HTH, >>>> >>>> Marc Schwartz >>>> >>>> >>> >>> >> >> > > [[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db > -- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 From h@r|@n @end|ng |rom h@rr|@@n@me Thu Nov 18 19:47:11 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Thu, 18 Nov 2010 13:47:11 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> <7E28F693-D990-4436-B83A-28D737D38318@me.com> Message-ID: Thanks Marc and Prof. Ripley. The believeNRows option doesn't help. I'll try installing the newest R version and see if that helps. Would I have to compile R with debugging symbols enabled to use the debugger? Never done that... -Harlan On Thu, Nov 18, 2010 at 1:40 PM, Prof Brian Ripley wrote: > One thing I don't see being tried: > > ?odbcConnect says > > believeNRows: logical. Is the number of rows returned by the ODBC > connection believable? Not true for some Oracle and Sybase > drivers, apparently, nor for Actual Technologies' SQLite > driver for Mac OS X. > > Given the hint about Oracle, it looks worth a try, although I think the > usual indication is claiming zero rows are available when a positive number > are. > > You can run R under a debugger to find out exactly what is going wrong. > > > On Thu, 18 Nov 2010, Harlan Harris wrote: > > This is the version of R you get by "yum install R" in CentOS 5 from EPEL. >> Here you are: >> >> sessionInfo() >>> >> R version 2.11.1 (2010-05-31) >> x86_64-redhat-linux-gnu >> >> locale: >> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C >> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 >> [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 >> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C >> [9] LC_ADDRESS=C LC_TELEPHONE=C >> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C >> >> attached base packages: >> [1] stats graphics grDevices utils datasets methods base >> >> other attached packages: >> [1] RODBC_1.3-2 >> >> loaded via a namespace (and not attached): >> [1] tools_2.11.1 >> >> -Harlan >> >> >> On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz >> wrote: >> >> OK....the more I think about this, the more I believe that there is an >>> integer overflow problem. This may yet be an indication of a 32/64 bit >>> conflict someplace. >>> >>> The question then is where and why. >>> >>> Which version of R are you running? Provide the output of: >>> >>> sessionInfo() >>> >>> Marc >>> >>> On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: >>> >>> I wondered about the -1 too... >>>> >>>> But nope, neither of these suggestions work... >>>> >>>> .Machine$sizeof.pointer >>>>> >>>> [1] 8 >>>> >>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select >>>>> >>>> name from catalog.program where rownum < 2', rows_at_time=1, as.is >>> =TRUE) >>> >>>> Error in odbcQuery(channel, query, rows_at_time) : >>>> Calloc could not allocate (-1 of 22816) memory >>>> >>>> -Harlan >>>> >>>> On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz >>>> >>> wrote: >>> >>>> OK. What is interesting, albeit, it may be a red herring, is the -1 in >>>> >>> the error message. Is that an indication of an integer wrap around or >>> something more subtle? >>> >>>> >>>> Please be sure that you are running 64 bit R. Check the result of: >>>> >>>> .Machine$sizeof.pointer >>>> >>>> It should return 8 if you are running 64 bit R. If it returns 4, you are >>>> >>> running 32 bit R, which would conflict with the rest of the 64 bit tool >>> chain. >>> >>>> >>>> If by chance you are running 32 bit R, you will need to install 64 bit R >>>> >>> and reinstall your CRAN packages, including RODBC, since any C/FORTRAN >>> code >>> would be compiled as 32 bit programs. >>> >>>> >>>> Can you also perhaps try a query that should result in a small result >>>> >>> set? Perhaps a single column from a single row? >>> >>>> >>>> >>>> Marc >>>> >>>> On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: >>>> >>>> Quick response -- thanks! Nope, doesn't help. >>>>> >>>>> library(RODBC) >>>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1) >>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10', >>>>>> >>>>> rows_at_time=1) >>> >>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>> Calloc could not allocate (-1 of 22816) memory >>>>> >>>>> -Harlan >>>>> >>>>> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz >>>>> >>>> wrote: >>> >>>> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: >>>>> >>>>> Hi all, >>>>>> >>>>>> I'm trying again to get the R<->Oracle toolchain to work on 64-bit >>>>>> >>>>> Linux >>> >>>> (CentOS 5/RHEL 5). As previously established, it's nearly impossible, >>>>>> although I'm awfully close now and just need one bit of help. >>>>>> >>>>>> Here's what works: >>>>>> >>>>>> I installed the 64-bit Oracle Instant Client Basic and ODBC rpms >>>>>> >>>>> (version >>> >>>> 11.2.0.2.0). >>>>>> export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ >>>>>> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >>>>>> export TNS_ADMIN=/etc/oracle >>>>>> set up /etc/oracle/tnsname.ora >>>>>> >>>>>> The version of unixODBC that's installed on Redhat 5 is 2.2.11. That >>>>>> >>>>> version >>> >>>> is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle >>>>>> >>>>> and >>> >>>> Redhat are aware of this and stubbornly refuse to fix it. Therefore I >>>>>> installed easysoft's ODBC Oracle driver. Note that this costs >>>>>> >>>>> something like >>> >>>> $2000 to license, but if you've got Oracle, you can probably pay for >>>>>> >>>>> it. >>> >>>> >>>>>> The easysoft driver installs to /usr/local/easysoft. It includes >>>>>> >>>>> unixODBC >>> >>>> 2.2.12. (I uninstalled the factor 2.2.11 version) >>>>>> >>>>>> I confirmed that it was properly configured and working by running >>>>>> /usr/local/easysoft/unixODBC/bin/isql and querying my database. >>>>>> >>>>> Username and >>> >>>> password are in /etc/odbc.ini. >>>>>> >>>>>> I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to >>>>>> /usr/local/lib, /include and /bin, respectively. >>>>>> >>>>>> Now I install RODBC from source. It seems to work, finding sql.h, >>>>>> >>>>> sqlext.h, >>> >>>> and -lodbc. >>>>>> >>>>>> library(RODBC) >>>>>>> hdl <- odbcConnect('ORACLE') >>>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') >>>>>>> >>>>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>>> Calloc could not allocate (-1 of 22816) memory >>>>>> >>>>>> >>>>>> So close! What am I missing? >>>>>> >>>>>> Thanks! >>>>>> >>>>>> -Harlan >>>>>> >>>>> >>>>> >>>>> Harlan, glad to see some progress, albeit at a notable cost. >>>>> >>>>> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and >>>>> >>>> sqlQuery(). >>> >>>> >>>>> I have found the need to use that setting, rather than the default 100, >>>>> >>>> in order to successfully retrieve data from our Oracle server on OSX. >>> >>>> >>>>> The smaller value may also help to workaround the memory allocation >>>>> >>>> issue, presuming that something more subtle is not going on. >>> >>>> >>>>> HTH, >>>>> >>>>> Marc Schwartz >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> [[alternative HTML version deleted]] >> >> _______________________________________________ >> R-sig-DB mailing list -- R Special Interest Group >> R-sig-DB at stat.math.ethz.ch >> https://stat.ethz.ch/mailman/listinfo/r-sig-db >> >> > -- > Brian D. Ripley, ripley at stats.ox.ac.uk > Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ > University of Oxford, Tel: +44 1865 272861 (self) > 1 South Parks Road, +44 1865 272866 (PA) > Oxford OX1 3TG, UK Fax: +44 1865 272595 > [[alternative HTML version deleted]] From h@r|@n @end|ng |rom h@rr|@@n@me Mon Nov 22 19:04:21 2010 From: h@r|@n @end|ng |rom h@rr|@@n@me (Harlan Harris) Date: Mon, 22 Nov 2010 13:04:21 -0500 Subject: [R-sig-DB] RODBC with Oracle and 64-bit Linux (encore) In-Reply-To: References: <882EC066-31E7-4E4A-9CE2-349356359429@me.com> <789BC982-849A-4849-99B3-CB708108EC13@me.com> <7E28F693-D990-4436-B83A-28D737D38318@me.com> Message-ID: Looping R-Sig-DB back in for posterity... We did manage to get this to work! The answer is that Easysoft offers two versions of their driver. The version you get by default from their web site, "odbc-oracle-3.2.26-linux-x86-64-ul64", is a 64-bit driver with 64-bit SQLLEN and SQLULEN values. That causes (for reasons I don't fully understand) problems with the integration with RODBC. You can request from Easysoft a driver called "odbc-oracle-3.2.26-linux-x86-64" which is a 64-bit driver with 32-bit values for those constants. That driver works (modulo an issue with Oracle timestamp types, which can be dealt with by as.is=TRUE). -Harlan On Thu, Nov 18, 2010 at 1:47 PM, Harlan Harris wrote: > Thanks Marc and Prof. Ripley. > > The believeNRows option doesn't help. I'll try installing the newest R > version and see if that helps. > > Would I have to compile R with debugging symbols enabled to use the > debugger? Never done that... > > -Harlan > > > On Thu, Nov 18, 2010 at 1:40 PM, Prof Brian Ripley wrote: > >> One thing I don't see being tried: >> >> ?odbcConnect says >> >> believeNRows: logical. Is the number of rows returned by the ODBC >> connection believable? Not true for some Oracle and Sybase >> drivers, apparently, nor for Actual Technologies' SQLite >> driver for Mac OS X. >> >> Given the hint about Oracle, it looks worth a try, although I think the >> usual indication is claiming zero rows are available when a positive number >> are. >> >> You can run R under a debugger to find out exactly what is going wrong. >> >> >> On Thu, 18 Nov 2010, Harlan Harris wrote: >> >> This is the version of R you get by "yum install R" in CentOS 5 from >>> EPEL. >>> Here you are: >>> >>> sessionInfo() >>>> >>> R version 2.11.1 (2010-05-31) >>> x86_64-redhat-linux-gnu >>> >>> locale: >>> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C >>> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 >>> [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 >>> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C >>> [9] LC_ADDRESS=C LC_TELEPHONE=C >>> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C >>> >>> attached base packages: >>> [1] stats graphics grDevices utils datasets methods base >>> >>> other attached packages: >>> [1] RODBC_1.3-2 >>> >>> loaded via a namespace (and not attached): >>> [1] tools_2.11.1 >>> >>> -Harlan >>> >>> >>> On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz >>> wrote: >>> >>> OK....the more I think about this, the more I believe that there is an >>>> integer overflow problem. This may yet be an indication of a 32/64 bit >>>> conflict someplace. >>>> >>>> The question then is where and why. >>>> >>>> Which version of R are you running? Provide the output of: >>>> >>>> sessionInfo() >>>> >>>> Marc >>>> >>>> On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote: >>>> >>>> I wondered about the -1 too... >>>>> >>>>> But nope, neither of these suggestions work... >>>>> >>>>> .Machine$sizeof.pointer >>>>>> >>>>> [1] 8 >>>>> >>>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select >>>>>> >>>>> name from catalog.program where rownum < 2', rows_at_time=1, as.is >>>> =TRUE) >>>> >>>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>> Calloc could not allocate (-1 of 22816) memory >>>>> >>>>> -Harlan >>>>> >>>>> On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz >>>>> >>>> wrote: >>>> >>>>> OK. What is interesting, albeit, it may be a red herring, is the -1 in >>>>> >>>> the error message. Is that an indication of an integer wrap around or >>>> something more subtle? >>>> >>>>> >>>>> Please be sure that you are running 64 bit R. Check the result of: >>>>> >>>>> .Machine$sizeof.pointer >>>>> >>>>> It should return 8 if you are running 64 bit R. If it returns 4, you >>>>> are >>>>> >>>> running 32 bit R, which would conflict with the rest of the 64 bit tool >>>> chain. >>>> >>>>> >>>>> If by chance you are running 32 bit R, you will need to install 64 bit >>>>> R >>>>> >>>> and reinstall your CRAN packages, including RODBC, since any C/FORTRAN >>>> code >>>> would be compiled as 32 bit programs. >>>> >>>>> >>>>> Can you also perhaps try a query that should result in a small result >>>>> >>>> set? Perhaps a single column from a single row? >>>> >>>>> >>>>> >>>>> Marc >>>>> >>>>> On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote: >>>>> >>>>> Quick response -- thanks! Nope, doesn't help. >>>>>> >>>>>> library(RODBC) >>>>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1) >>>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10', >>>>>>> >>>>>> rows_at_time=1) >>>> >>>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>>> Calloc could not allocate (-1 of 22816) memory >>>>>> >>>>>> -Harlan >>>>>> >>>>>> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz >>>>> > >>>>>> >>>>> wrote: >>>> >>>>> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote: >>>>>> >>>>>> Hi all, >>>>>>> >>>>>>> I'm trying again to get the R<->Oracle toolchain to work on 64-bit >>>>>>> >>>>>> Linux >>>> >>>>> (CentOS 5/RHEL 5). As previously established, it's nearly impossible, >>>>>>> although I'm awfully close now and just need one bit of help. >>>>>>> >>>>>>> Here's what works: >>>>>>> >>>>>>> I installed the 64-bit Oracle Instant Client Basic and ODBC rpms >>>>>>> >>>>>> (version >>>> >>>>> 11.2.0.2.0). >>>>>>> export ORACLE_HOME=/usr/lib/oracle/11.2/client64/ >>>>>>> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib >>>>>>> export TNS_ADMIN=/etc/oracle >>>>>>> set up /etc/oracle/tnsname.ora >>>>>>> >>>>>>> The version of unixODBC that's installed on Redhat 5 is 2.2.11. That >>>>>>> >>>>>> version >>>> >>>>> is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle >>>>>>> >>>>>> and >>>> >>>>> Redhat are aware of this and stubbornly refuse to fix it. Therefore I >>>>>>> installed easysoft's ODBC Oracle driver. Note that this costs >>>>>>> >>>>>> something like >>>> >>>>> $2000 to license, but if you've got Oracle, you can probably pay for >>>>>>> >>>>>> it. >>>> >>>>> >>>>>>> The easysoft driver installs to /usr/local/easysoft. It includes >>>>>>> >>>>>> unixODBC >>>> >>>>> 2.2.12. (I uninstalled the factor 2.2.11 version) >>>>>>> >>>>>>> I confirmed that it was properly configured and working by running >>>>>>> /usr/local/easysoft/unixODBC/bin/isql and querying my database. >>>>>>> >>>>>> Username and >>>> >>>>> password are in /etc/odbc.ini. >>>>>>> >>>>>>> I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to >>>>>>> /usr/local/lib, /include and /bin, respectively. >>>>>>> >>>>>>> Now I install RODBC from source. It seems to work, finding sql.h, >>>>>>> >>>>>> sqlext.h, >>>> >>>>> and -lodbc. >>>>>>> >>>>>>> library(RODBC) >>>>>>>> hdl <- odbcConnect('ORACLE') >>>>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10') >>>>>>>> >>>>>>> Error in odbcQuery(channel, query, rows_at_time) : >>>>>>> Calloc could not allocate (-1 of 22816) memory >>>>>>> >>>>>>> >>>>>>> So close! What am I missing? >>>>>>> >>>>>>> Thanks! >>>>>>> >>>>>>> -Harlan >>>>>>> >>>>>> >>>>>> >>>>>> Harlan, glad to see some progress, albeit at a notable cost. >>>>>> >>>>>> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and >>>>>> >>>>> sqlQuery(). >>>> >>>>> >>>>>> I have found the need to use that setting, rather than the default >>>>>> 100, >>>>>> >>>>> in order to successfully retrieve data from our Oracle server on OSX. >>>> >>>>> >>>>>> The smaller value may also help to workaround the memory allocation >>>>>> >>>>> issue, presuming that something more subtle is not going on. >>>> >>>>> >>>>>> HTH, >>>>>> >>>>>> Marc Schwartz >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> [[alternative HTML version deleted]] >>> >>> _______________________________________________ >>> R-sig-DB mailing list -- R Special Interest Group >>> R-sig-DB at stat.math.ethz.ch >>> https://stat.ethz.ch/mailman/listinfo/r-sig-db >>> >>> >> -- >> Brian D. Ripley, ripley at stats.ox.ac.uk >> Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ >> University of Oxford, Tel: +44 1865 272861 (self) >> 1 South Parks Road, +44 1865 272866 (PA) >> Oxford OX1 3TG, UK Fax: +44 1865 272595 >> > > [[alternative HTML version deleted]] From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Thu Nov 25 19:15:18 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Thu, 25 Nov 2010 10:15:18 -0800 Subject: [R-sig-DB] Error compiling RMySQL under Vista_x64 Message-ID: <4CEEA7B6.1090608@structuremonitoring.com> Hello: I get "x86_64-w64-mingw32-gcc: not found" from "R CMD check RMySQL_0.7-5.tar.gz" under Vista_x64 immediately after downloading and installing Rtools212.exe. How should I solve this problem? Thanks, Spencer p.s. Below please find "00install.out" from "R CMD check RMySQL_0.7-5.tar.gz". ############################# * installing *source* package 'RMySQL' ... checking for $MYSQL_HOME... C:/PROGRA~2/MySQL/MYSQLS~1.1/ cygwin warning: MS-DOS style path detected: C:/PROGRA~2/MySQL/MYSQLS~1.1/ Preferred POSIX equivalent is: /cygdrive/c/PROGRA~2/MySQL/MYSQLS~1.1/ CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames ** libs Warning: this package has a non-empty 'configure.win' file, so building only the main architecture cygwin warning: MS-DOS style path detected: C:/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf Preferred POSIX equivalent is: /cygdrive/c/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames x86_64-w64-mingw32-gcc -I"C:/Users/sgraves/R/R-212~1.0/include" -I"C:/PROGRA~2/MySQL/MYSQLS~1.1/"/include -O2 -Wall -std=gnu99 -c RS-DBI.c -o RS-DBI.o x86_64-w64-mingw32-gcc: not found make: *** [RS-DBI.o] Error 127 ERROR: compilation failed for package 'RMySQL' * removing 'C:/Users/sgraves/Rpkgs/RMySQL/RMySQL.Rcheck/RMySQL' From gux|@obo1982 @end|ng |rom gm@||@com Fri Nov 26 16:39:11 2010 From: gux|@obo1982 @end|ng |rom gm@||@com (Xiaobo Gu) Date: Fri, 26 Nov 2010 23:39:11 +0800 Subject: [R-sig-DB] FW: R encoding question In-Reply-To: References: Message-ID: <000301cb8d80$1af0a560$50d1f020$@gmail.com> Xiaobo.Gu -----Original Message----- From: Xiaobo Gu [mailto:guxiaobo1982 at gmail.com] Sent: Wednesday, November 24, 2010 10:19 PM To: r-help at r-project.org Subject: R encoding question Hi, I am using RpgSQL to retrieve data from a PostgreSQL database wich is with encoding UTF8, and I have some Chinese character in one of the columns, unfortunately R can't show it correctly. > df <- dbGetQuery(con, "select * from test") df a b 1 1 ????\xa2 2 2 ?? EURO\xa1 I see the following option, do I need to change the encoding option to show the corresponding texts? In my case how to set? $encoding [1] "native.enc" Thanks, Xiaobo Gu From @@nto@h@@r|n|v@@ @end|ng |rom gm@||@com Fri Nov 26 17:56:25 2010 From: @@nto@h@@r|n|v@@ @end|ng |rom gm@||@com (Santosh Srinivas) Date: Fri, 26 Nov 2010 22:26:25 +0530 Subject: [R-sig-DB] RQuantLib for Win 7 64 Bit Message-ID: <4cefe6bf.16958e0a.5ade.ffff9617@mx.google.com> Hello R-Group, I am trying out some of my 32 Bit code on a new Win 7 64 bit installation. Unfortunately, I do not have the binary for RQuantLib which I believe is not readily available. Could someone who has RQuantLib running on Win 7 64 bit send me a compiled binary or instructions of how to get it one? Thanks for your patience with a R newbie. Thank you. From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Fri Nov 26 19:06:41 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Fri, 26 Nov 2010 10:06:41 -0800 Subject: [R-sig-DB] R Tools & Vista_x64: Problem compiling RMySQL? Message-ID: <4CEFF731.2080605@structuremonitoring.com> Hello: What do I need to do to compile R packages (especially RMySQL) for the 64-bit version of R 2.12.0 under Vista_x64? I upgraded to "Rtools212.exe" yesterday but immediately got errors from "R CMD check RMySQL_0.7-5.tar.gz". After the first error, I installed it a second time, then got "undefined reference to" roughly 50 different names beginning "mysql_"; copied below. I see two possible sources for this problem: (1) RTools212 may not be installed properly. (2) RMySQL may be incompatible with R x64 2.12.0, especially with a 32-bit version of MySQL. NOTE: RMySQL worked with R2.11.1 (and MySQL 5.1.50-community server) before I installed R2.12.0. I'm reasonably sure that my local installation of MySQL is only 32-bit. What do you suggest? I use the 32-bit version of R2.12.0? Thanks, Spencer ##################################### * installing *source* package 'RMySQL' ... checking for $MYSQL_HOME... C:/PROGRA~2/MySQL/MYSQLS~1.1/ cygwin warning: MS-DOS style path detected: C:/PROGRA~2/MySQL/MYSQLS~1.1/ Preferred POSIX equivalent is: /cygdrive/c/PROGRA~2/MySQL/MYSQLS~1.1/ CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames ** libs Warning: this package has a non-empty 'configure.win' file, so building only the main architecture cygwin warning: MS-DOS style path detected: C:/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf Preferred POSIX equivalent is: /cygdrive/c/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames x86_64-w64-mingw32-gcc -I"C:/Users/sgraves/R/R-212~1.0/include" -I"C:/PROGRA~2/MySQL/MYSQLS~1.1/"/include -O2 -Wall -std=gnu99 -c RS-DBI.c -o RS-DBI.o x86_64-w64-mingw32-gcc -I"C:/Users/sgraves/R/R-212~1.0/include" -I"C:/PROGRA~2/MySQL/MYSQLS~1.1/"/include -O2 -Wall -std=gnu99 -c RS-MySQL.c -o RS-MySQL.o x86_64-w64-mingw32-gcc -shared -s -static-libgcc -o RMySQL.dll tmp.def RS-DBI.o RS-MySQL.o C:/PROGRA~2/MySQL/MYSQLS~1.1//bin/libmySQL.dll -LC:/Users/sgraves/R/R-212~1.0/bin/x64 -lR RS-MySQL.o:RS-MySQL.c:(.text+0xb0): undefined reference to `mysql_more_results' RS-MySQL.o:RS-MySQL.c:(.text+0x2c6): undefined reference to `mysql_init' RS-MySQL.o:RS-MySQL.c:(.text+0x2d9): undefined reference to `mysql_options' RS-MySQL.o:RS-MySQL.c:(.text+0x2ef): undefined reference to `mysql_options' RS-MySQL.o:RS-MySQL.c:(.text+0x305): undefined reference to `mysql_options' RS-MySQL.o:RS-MySQL.c:(.text+0x338): undefined reference to `mysql_real_connect' RS-MySQL.o:RS-MySQL.c:(.text+0x399): undefined reference to `mysql_close' RS-MySQL.o:RS-MySQL.c:(.text+0x3d1): undefined reference to `mysql_error' RS-MySQL.o:RS-MySQL.c:(.text+0x7a2): undefined reference to `mysql_close' RS-MySQL.o:RS-MySQL.c:(.text+0x80f): undefined reference to `mysql_fetch_fields' RS-MySQL.o:RS-MySQL.c:(.text+0x823): undefined reference to `mysql_field_count' RS-MySQL.o:RS-MySQL.c:(.text+0xae7): undefined reference to `mysql_next_result' RS-MySQL.o:RS-MySQL.c:(.text+0xb0b): undefined reference to `mysql_use_result' RS-MySQL.o:RS-MySQL.c:(.text+0xb16): undefined reference to `mysql_field_count' RS-MySQL.o:RS-MySQL.c:(.text+0xbff): undefined reference to `mysql_affected_rows' RS-MySQL.o:RS-MySQL.c:(.text+0xd27): undefined reference to `mysql_fetch_row' RS-MySQL.o:RS-MySQL.c:(.text+0xd3d): undefined reference to `mysql_fetch_lengths' RS-MySQL.o:RS-MySQL.c:(.text+0xf2e): undefined reference to `mysql_errno' RS-MySQL.o:RS-MySQL.c:(.text+0x1093): undefined reference to `mysql_errno' RS-MySQL.o:RS-MySQL.c:(.text+0x109e): undefined reference to `mysql_error' RS-MySQL.o:RS-MySQL.c:(.text+0x1114): undefined reference to `mysql_fetch_row' RS-MySQL.o:RS-MySQL.c:(.text+0x1121): undefined reference to `mysql_free_result' RS-MySQL.o:RS-MySQL.c:(.text+0x11f0): undefined reference to `mysql_query' RS-MySQL.o:RS-MySQL.c:(.text+0x1200): undefined reference to `mysql_use_result' RS-MySQL.o:RS-MySQL.c:(.text+0x120b): undefined reference to `mysql_field_count' RS-MySQL.o:RS-MySQL.c:(.text+0x128f): undefined reference to `mysql_query' RS-MySQL.o:RS-MySQL.c:(.text+0x12ac): undefined reference to `mysql_error' RS-MySQL.o:RS-MySQL.c:(.text+0x133c): undefined reference to `mysql_affected_rows' RS-MySQL.o:RS-MySQL.c:(.text+0x1539): undefined reference to `mysql_get_client_info' RS-MySQL.o:RS-MySQL.c:(.text+0x172d): undefined reference to `mysql_get_host_info' RS-MySQL.o:RS-MySQL.c:(.text+0x174b): undefined reference to `mysql_get_server_info' RS-MySQL.o:RS-MySQL.c:(.text+0x176d): undefined reference to `mysql_get_proto_info' RS-MySQL.o:RS-MySQL.c:(.text+0x177c): undefined reference to `mysql_thread_id' RS-MySQL.o:RS-MySQL.c:(.text+0x23db): undefined reference to `mysql_fetch_row' RS-MySQL.o:RS-MySQL.c:(.text+0x23fd): undefined reference to `mysql_errno' RS-MySQL.o:RS-MySQL.c:(.text+0x252a): undefined reference to `mysql_fetch_lengths' RS-MySQL.o:RS-MySQL.c:(.text+0x28fd): undefined reference to `mysql_insert_id' RS-MySQL.o:RS-MySQL.c:(.text+0x29cd): undefined reference to `mysql_real_escape_string' RS-MySQL.o:RS-MySQL.c:(.text+0x2a7a): undefined reference to `mysql_get_client_info' RS-MySQL.o:RS-MySQL.c:(.text+0x2ab8): undefined reference to `mysql_get_client_version' RS-MySQL.o:RS-MySQL.c:(.text+0x2afc): undefined reference to `mysql_server_init' RS-MySQL.o:RS-MySQL.c:(.text+0x2b01): undefined reference to `mysql_get_client_version' RS-MySQL.o:RS-MySQL.c:(.text+0x2b29): undefined reference to `mysql_get_client_info' RS-MySQL.o:RS-MySQL.c:(.text+0x2b69): undefined reference to `mysql_server_end' collect2: ld returned 1 exit status ERROR: compilation failed for package 'RMySQL' * removing 'C:/Users/sgraves/Rpkgs/RMySQL/RMySQL.Rcheck/RMySQL' From murdoch@dunc@n @end|ng |rom gm@||@com Fri Nov 26 20:12:06 2010 From: murdoch@dunc@n @end|ng |rom gm@||@com (Duncan Murdoch) Date: Fri, 26 Nov 2010 14:12:06 -0500 Subject: [R-sig-DB] [Rd] R Tools & Vista_x64: Problem compiling RMySQL? In-Reply-To: <4CEFF731.2080605@structuremonitoring.com> References: <4CEFF731.2080605@structuremonitoring.com> Message-ID: <4CF00686.7080601@gmail.com> On 26/11/2010 1:06 PM, Spencer Graves wrote: > Hello: > > > What do I need to do to compile R packages (especially RMySQL) for > the 64-bit version of R 2.12.0 under Vista_x64? The symptoms you're seeing are because the linker can't functions in libmySQL.dll which it is looking for in the somewhat strange path C:/PROGRA~2/MySQL/MYSQLS~1.1//bin/libmySQL.dll Does that file exist? Is it a 64 bit dll, compatible with MinGW? Is it compiled under the same convention as R, where no underscores are used in external names? (The latter two questions can probably be answered by looking at "objdump -x libmySQL.dll". objdump.exe is distributed as part of the MinGW distribution in Rtools.) Duncan Murdoch > > I upgraded to "Rtools212.exe" yesterday but immediately got errors > from "R CMD check RMySQL_0.7-5.tar.gz". After the first error, I > installed it a second time, then got "undefined reference to" roughly 50 > different names beginning "mysql_"; copied below. I see two possible > sources for this problem: > > > (1) RTools212 may not be installed properly. > > > (2) RMySQL may be incompatible with R x64 2.12.0, especially with > a 32-bit version of MySQL. > > > NOTE: RMySQL worked with R2.11.1 (and MySQL 5.1.50-community server) > before I installed R2.12.0. I'm reasonably sure that my local > installation of MySQL is only 32-bit. > > > What do you suggest? I use the 32-bit version of R2.12.0? > > > Thanks, > Spencer > ##################################### > * installing *source* package 'RMySQL' ... > checking for $MYSQL_HOME... C:/PROGRA~2/MySQL/MYSQLS~1.1/ > cygwin warning: > MS-DOS style path detected: C:/PROGRA~2/MySQL/MYSQLS~1.1/ > Preferred POSIX equivalent is: /cygdrive/c/PROGRA~2/MySQL/MYSQLS~1.1/ > CYGWIN environment variable option "nodosfilewarning" turns off this > warning. > Consult the user's guide for more details about POSIX paths: > http://cygwin.com/cygwin-ug-net/using.html#using-pathnames > ** libs > Warning: this package has a non-empty 'configure.win' file, > so building only the main architecture > > cygwin warning: > MS-DOS style path detected: C:/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf > Preferred POSIX equivalent is: > /cygdrive/c/Users/sgraves/R/R-212~1.0/etc/x64/Makeconf > CYGWIN environment variable option "nodosfilewarning" turns off this > warning. > Consult the user's guide for more details about POSIX paths: > http://cygwin.com/cygwin-ug-net/using.html#using-pathnames > x86_64-w64-mingw32-gcc -I"C:/Users/sgraves/R/R-212~1.0/include" > -I"C:/PROGRA~2/MySQL/MYSQLS~1.1/"/include -O2 -Wall -std=gnu99 > -c RS-DBI.c -o RS-DBI.o > x86_64-w64-mingw32-gcc -I"C:/Users/sgraves/R/R-212~1.0/include" > -I"C:/PROGRA~2/MySQL/MYSQLS~1.1/"/include -O2 -Wall -std=gnu99 > -c RS-MySQL.c -o RS-MySQL.o > x86_64-w64-mingw32-gcc -shared -s -static-libgcc -o RMySQL.dll tmp.def > RS-DBI.o RS-MySQL.o C:/PROGRA~2/MySQL/MYSQLS~1.1//bin/libmySQL.dll > -LC:/Users/sgraves/R/R-212~1.0/bin/x64 -lR > RS-MySQL.o:RS-MySQL.c:(.text+0xb0): undefined reference to > `mysql_more_results' > RS-MySQL.o:RS-MySQL.c:(.text+0x2c6): undefined reference to `mysql_init' > RS-MySQL.o:RS-MySQL.c:(.text+0x2d9): undefined reference to `mysql_options' > RS-MySQL.o:RS-MySQL.c:(.text+0x2ef): undefined reference to `mysql_options' > RS-MySQL.o:RS-MySQL.c:(.text+0x305): undefined reference to `mysql_options' > RS-MySQL.o:RS-MySQL.c:(.text+0x338): undefined reference to > `mysql_real_connect' > RS-MySQL.o:RS-MySQL.c:(.text+0x399): undefined reference to `mysql_close' > RS-MySQL.o:RS-MySQL.c:(.text+0x3d1): undefined reference to `mysql_error' > RS-MySQL.o:RS-MySQL.c:(.text+0x7a2): undefined reference to `mysql_close' > RS-MySQL.o:RS-MySQL.c:(.text+0x80f): undefined reference to > `mysql_fetch_fields' > RS-MySQL.o:RS-MySQL.c:(.text+0x823): undefined reference to > `mysql_field_count' > RS-MySQL.o:RS-MySQL.c:(.text+0xae7): undefined reference to > `mysql_next_result' > RS-MySQL.o:RS-MySQL.c:(.text+0xb0b): undefined reference to > `mysql_use_result' > RS-MySQL.o:RS-MySQL.c:(.text+0xb16): undefined reference to > `mysql_field_count' > RS-MySQL.o:RS-MySQL.c:(.text+0xbff): undefined reference to > `mysql_affected_rows' > RS-MySQL.o:RS-MySQL.c:(.text+0xd27): undefined reference to > `mysql_fetch_row' > RS-MySQL.o:RS-MySQL.c:(.text+0xd3d): undefined reference to > `mysql_fetch_lengths' > RS-MySQL.o:RS-MySQL.c:(.text+0xf2e): undefined reference to `mysql_errno' > RS-MySQL.o:RS-MySQL.c:(.text+0x1093): undefined reference to `mysql_errno' > RS-MySQL.o:RS-MySQL.c:(.text+0x109e): undefined reference to `mysql_error' > RS-MySQL.o:RS-MySQL.c:(.text+0x1114): undefined reference to > `mysql_fetch_row' > RS-MySQL.o:RS-MySQL.c:(.text+0x1121): undefined reference to > `mysql_free_result' > RS-MySQL.o:RS-MySQL.c:(.text+0x11f0): undefined reference to `mysql_query' > RS-MySQL.o:RS-MySQL.c:(.text+0x1200): undefined reference to > `mysql_use_result' > RS-MySQL.o:RS-MySQL.c:(.text+0x120b): undefined reference to > `mysql_field_count' > RS-MySQL.o:RS-MySQL.c:(.text+0x128f): undefined reference to `mysql_query' > RS-MySQL.o:RS-MySQL.c:(.text+0x12ac): undefined reference to `mysql_error' > RS-MySQL.o:RS-MySQL.c:(.text+0x133c): undefined reference to > `mysql_affected_rows' > RS-MySQL.o:RS-MySQL.c:(.text+0x1539): undefined reference to > `mysql_get_client_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x172d): undefined reference to > `mysql_get_host_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x174b): undefined reference to > `mysql_get_server_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x176d): undefined reference to > `mysql_get_proto_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x177c): undefined reference to > `mysql_thread_id' > RS-MySQL.o:RS-MySQL.c:(.text+0x23db): undefined reference to > `mysql_fetch_row' > RS-MySQL.o:RS-MySQL.c:(.text+0x23fd): undefined reference to `mysql_errno' > RS-MySQL.o:RS-MySQL.c:(.text+0x252a): undefined reference to > `mysql_fetch_lengths' > RS-MySQL.o:RS-MySQL.c:(.text+0x28fd): undefined reference to > `mysql_insert_id' > RS-MySQL.o:RS-MySQL.c:(.text+0x29cd): undefined reference to > `mysql_real_escape_string' > RS-MySQL.o:RS-MySQL.c:(.text+0x2a7a): undefined reference to > `mysql_get_client_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x2ab8): undefined reference to > `mysql_get_client_version' > RS-MySQL.o:RS-MySQL.c:(.text+0x2afc): undefined reference to > `mysql_server_init' > RS-MySQL.o:RS-MySQL.c:(.text+0x2b01): undefined reference to > `mysql_get_client_version' > RS-MySQL.o:RS-MySQL.c:(.text+0x2b29): undefined reference to > `mysql_get_client_info' > RS-MySQL.o:RS-MySQL.c:(.text+0x2b69): undefined reference to > `mysql_server_end' > collect2: ld returned 1 exit status > ERROR: compilation failed for package 'RMySQL' > * removing 'C:/Users/sgraves/Rpkgs/RMySQL/RMySQL.Rcheck/RMySQL' > > ______________________________________________ > R-devel at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-devel From @@@compute @end|ng |rom y@hoo@com Sat Nov 27 16:57:46 2010 From: @@@compute @end|ng |rom y@hoo@com (bill hastings) Date: Sat, 27 Nov 2010 07:57:46 -0800 (PST) Subject: [R-sig-DB] R DB interfaces and saving charts Message-ID: <200566.68411.qm@web53106.mail.re2.yahoo.com> Hi, Here are a few question I have. Thanks, for any answers. 1) Is there anyway to save a chart(produced by plot) to a data base? I see that most of the R DB methods save output as data.frames or files. 2) If I can not use db's to save charts and graphics, is there a way to save multiple charts in a file? saving the charts as individual files will create thousands of files. I would like to keep the file count down. 3) I plan to use these charts in html web pages, does anyone recommend any particular html editor to layout web pages from R? 4) How can R's screen output be saved in a DB as html or xml for use in web pages? 5) Are there any packages for capturing screen output for entry into a db? Thanks again, Bill [[alternative HTML version deleted]] From edd @end|ng |rom deb|@n@org Sat Nov 27 17:23:54 2010 From: edd @end|ng |rom deb|@n@org (Dirk Eddelbuettel) Date: Sat, 27 Nov 2010 10:23:54 -0600 Subject: [R-sig-DB] R DB interfaces and saving charts In-Reply-To: <200566.68411.qm@web53106.mail.re2.yahoo.com> References: <200566.68411.qm@web53106.mail.re2.yahoo.com> Message-ID: <19697.12442.519620.284238@max.nulle.part> On 27 November 2010 at 07:57, bill hastings wrote: | Here are a few question I have. | Thanks, for any answers. | | 1) Is there anyway to save a chart(produced by plot) | to a data base? | I see that most of the R DB methods | save output as data.frames or files. Most R charts go to 'devices'; most of these devices can save to a file. You can the serialize the file, or save the it as a blob. So yes: you can render plenty of charts 'off-line' and then have, say, a web frontend serve them. Some R charting packages also generate object you could serialize directly. My digest package is a useful interface for serializing / deserialization. | 2) If I can not use db's to save charts and graphics, | is there a way to save multiple charts in a file? | saving the charts as individual files will create | thousands of files. I would like to keep the file count down. Sure. See e.g. help(pdf) and look at the onefile argument. [ The question is kinds off-topic here as it has no bearing on DBs. ] | 3) I plan to use these charts in html web pages, does anyone | recommend any particular html editor to layout web pages | from R? No idea. I like gWidgetsWWW for high-level interfaces from R to (simple enough) web pages. When I need to edit html, emacs is good enough with suitable modes. | 4) How can R's screen output be saved in a DB as html or xml | for use in web pages? Huh? Who said the devices produce html? People do weird things with XML. And the XML package suppports so if you must wrap a binary blob (properly serialized) in XML tags then go for it. | 5) Are there any packages for capturing screen output for | entry into a db? Question 5) is the same as question 1) so also see above. Apart from that, R is a programming language. When a function is missing, we tend to write it. If you need other people to write it for you, there are consultants (and if you contact me offline I even recommend one). Dirk -- Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com From m@|| @end|ng |rom joeconw@y@com Sat Nov 27 17:43:32 2010 From: m@|| @end|ng |rom joeconw@y@com (Joe Conway) Date: Sat, 27 Nov 2010 08:43:32 -0800 Subject: [R-sig-DB] R DB interfaces and saving charts In-Reply-To: <200566.68411.qm@web53106.mail.re2.yahoo.com> References: <200566.68411.qm@web53106.mail.re2.yahoo.com> Message-ID: <4CF13534.5060305@joeconway.com> On 11/27/2010 07:57 AM, bill hastings wrote: > 1) Is there anyway to save a chart(produced by plot) > > to a data base? > > I see that most of the R DB methods > > save output as data.frames or files. Use PL/R with PostgreSQL. http://www.joeconway.com/plr/ For some ideas how to do this see: http://blog.credativ.com/en/2010/07/postgresql-topic-of-the-day---advanced-analytics.html -or- http://bit.ly/9NZ0fz This example shows saving data frames directly as R objects to tables in the database, and also shows returning a chart as binary data (jpg) as a result of a SQL query. It would be a simple matter to store that returned data into a bytea column in PostgreSQL. HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 899 bytes Desc: OpenPGP digital signature URL: From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sat Nov 27 18:01:53 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Sat, 27 Nov 2010 09:01:53 -0800 Subject: [R-sig-DB] R DB interfaces and saving charts In-Reply-To: <4CF13534.5060305@joeconway.com> References: <200566.68411.qm@web53106.mail.re2.yahoo.com> <4CF13534.5060305@joeconway.com> Message-ID: <4CF13981.3060905@structuremonitoring.com> Beyond Dirk's comments, there is an "R2HTML" package. (It has some minor problems and could use maintenance. If you have time and interest, please contact either me or the officially listed maintainer or author.) To produce *.pdf, one can print from a browser to *.pdf (e.g., using CutePDF). To control pagination with that, I've manually edited the index.html file in Open Office, then wrote to *.pdf. Have you considered creating a subdirectory, e.g., called "figs", and write all the individual plots to that subdirectory? That's a more traditional solution, I think. If you'd rather use MySQL than PostgreSQL, the RMySQL package seems to work OK, though it cannot be installed from CRAN on a Windows machine. Instead, you need to download the source in *.tar.gz then do "R CMD INSTALL" from that to tailor it for the version of MySQL you have. Hope this helps. Spencer On 11/27/2010 8:43 AM, Joe Conway wrote: > On 11/27/2010 07:57 AM, bill hastings wrote: >> 1) Is there anyway to save a chart(produced by plot) >> >> to a data base? >> >> I see that most of the R DB methods >> >> save output as data.frames or files. > Use PL/R with PostgreSQL. > http://www.joeconway.com/plr/ > > For some ideas how to do this see: > > http://blog.credativ.com/en/2010/07/postgresql-topic-of-the-day---advanced-analytics.html > > -or- > > http://bit.ly/9NZ0fz > > This example shows saving data frames directly as R objects to tables in > the database, and also shows returning a chart as binary data (jpg) as a > result of a SQL query. It would be a simple matter to store that > returned data into a bytea column in PostgreSQL. > > HTH, > > Joe > > > > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 [[alternative HTML version deleted]] From @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com Sun Nov 28 16:44:34 2010 From: @pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com (Spencer Graves) Date: Sun, 28 Nov 2010 07:44:34 -0800 Subject: [R-sig-DB] R DB interfaces and saving charts In-Reply-To: <988701.22843.qm@web53102.mail.re2.yahoo.com> References: <200566.68411.qm@web53106.mail.re2.yahoo.com> <4CF13534.5060305@joeconway.com> <4CF13981.3060905@structuremonitoring.com> <988701.22843.qm@web53102.mail.re2.yahoo.com> Message-ID: <4CF278E2.8080703@structuremonitoring.com> On 11/28/2010 7:35 AM, bill hastings wrote: > Thanks for your great suggestions, I just looked > at r2html, it looks like just what i need. > > I wanted to do all of this in a scripted fashion(automated). > Each time I run my system(when it's finished)I would be generating thousands of > charts and blocks of text. > > Have you used open office's scripting feature? no. > What do you think of these content management systems or > other scripting languages to automate the production of > web pages from R outputs? I know nothing about them. > The problem I found with the RODBC was that it could not > handle blobs or binary files, only data.frames. > I could not find a way to put a blob into a data.frame. > Do you have any suggestions on that problem? no. > Thanks again, > bill > > > > > ________________________________ > From: Spencer Graves > To: Joe Conway > Cc: bill hastings; r-sig-db at r-project.org > Sent: Sat, November 27, 2010 12:01:53 PM > Subject: Re: [R-sig-DB] R DB interfaces and saving charts > > Beyond Dirk's comments, there is an "R2HTML" package. (It has some > minor problems and could use maintenance. If you have time and interest, > please contact either me or the officially listed maintainer or author.) To > produce *.pdf, one can print from a browser to *.pdf (e.g., using CutePDF). > To control pagination with that, I've manually edited the index.html file in > Open Office, then wrote to *.pdf. > > > > Have you considered creating a subdirectory, e.g., called "figs", and > write all the individual plots to that subdirectory? That's a more > traditional solution, I think. > > > > If you'd rather use MySQL than PostgreSQL, the RMySQL package seems to > work OK, though it cannot be installed from CRAN on a Windows machine. > Instead, you need to download the source in *.tar.gz then do "R CMD INSTALL" > from that to tailor it for the version of MySQL you have. > > > > Hope this helps. > Spencer > > > On 11/27/2010 8:43 AM, Joe Conway wrote: > On 11/27/2010 07:57 AM, bill hastings wrote: >> 1) Is there anyway to save a chart(produced by plot) >>> to a data base? >>> >>> I see that most of the R DB methods >>> >>> save output as data.frames or files. >>> > Use PL/R with PostgreSQL. > http://www.joeconway.com/plr/ > > For some ideas how to do this see: > > http://blog.credativ.com/en/2010/07/postgresql-topic-of-the-day---advanced-analytics.html > > > -or- > > http://bit.ly/9NZ0fz > > This example shows saving data frames directly as R objects to tables in > the database, and also shows returning a chart as binary data (jpg) as a > result of a SQL query. It would be a simple matter to store that > returned data into a bytea column in PostgreSQL. > > HTH, > > Joe > > > > _______________________________________________ R-sig-DB mailing list -- R > Special Interest Group R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > > -- Spencer Graves, PE, PhD President and Chief Operating Officer Structure Inspection and Monitoring, Inc. 751 Emerson Ct. San Jos?, CA 95126 ph: 408-655-4567 From n|ck@torenv||et @end|ng |rom gm@||@com Tue Nov 30 03:34:25 2010 From: n|ck@torenv||et @end|ng |rom gm@||@com (Nick Torenvliet) Date: Mon, 29 Nov 2010 21:34:25 -0500 Subject: [R-sig-DB] Vector Operations Message-ID: This isn't strictly an DB question, but I'll venture out anyways... I've got a vector of sql statements called sql > sql [1] "select date, open, high, low, close, volume from endOfDayData where date>'2009-09-01' and market like 'TSX' and symbol like 'AAB' order by date" . . . [2464] "select date, open, high, low, close, volume from endOfDayData where date>'2009-09-01' and market like 'TSX' and symbol like 'ZUH' order by date" [2465] "select date, open, high, low, close, volume from endOfDayData where date>'2009-09-01' and market like 'TSX' and symbol like 'ZUT' order by date" For each element in sql I'd like to run the queries in sql and have them in another vector of some sort as in the following for loop... for(x in sql){ allocatedSpace <- dbGetQuery(con, statement=x) } Is there a way to do this in a more R-esque vector like fashion/single line? Regards, Nick [[alternative HTML version deleted]] From k@@perd@n|e|h@n@en @end|ng |rom gm@||@com Wed Dec 1 14:27:39 2010 From: k@@perd@n|e|h@n@en @end|ng |rom gm@||@com (Kasper Daniel Hansen) Date: Wed, 1 Dec 2010 08:27:39 -0500 Subject: [R-sig-DB] Vector Operations In-Reply-To: References: Message-ID: This is a pretty basic R question. For you own sake, I would recommend looking into lapply, sapply, mapply In this case you could do allocatedSpace <- lapply(sql, dbGetQuery, con = con) This code assumes that the relevant position of the argument is the second one in dbGetQuery. A more future proof version would be something like allocatedSpace <- lapply(sql, function(state) dbGetQuery(con = con, statement = state)) Kasper On Mon, Nov 29, 2010 at 9:34 PM, Nick Torenvliet wrote: > This isn't strictly an DB question, but I'll venture out anyways... > > I've got a vector of sql statements called sql > >> sql > [1] "select date, open, high, low, close, volume from endOfDayData where > date>'2009-09-01' and market like 'TSX' and symbol like 'AAB' order by date" > . > . > . > [2464] "select date, open, high, low, close, volume from endOfDayData where > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUH' order by date" > > [2465] "select date, open, high, low, close, volume from endOfDayData where > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUT' order by > date" > > For each element in sql I'd like to run the queries in sql and have them in > another vector of some sort as in the following for loop... > > for(x in sql){ > allocatedSpace <- dbGetQuery(con, statement=x) > } > > Is there a way to do this in a more R-esque vector like fashion/single line? > > Regards, > > Nick > > ? ? ? ?[[alternative HTML version deleted]] > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > From @d@v|@2 @end|ng |rom m@||@n|h@gov Wed Dec 1 15:16:36 2010 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Wed, 1 Dec 2010 09:16:36 -0500 Subject: [R-sig-DB] Vector Operations In-Reply-To: References: Message-ID: On Wed, Dec 1, 2010 at 8:27 AM, Kasper Daniel Hansen < kasperdanielhansen at gmail.com> wrote: > This is a pretty basic R question. For you own sake, I would > recommend looking into lapply, sapply, mapply > > In this case you could do > > allocatedSpace <- lapply(sql, dbGetQuery, con = con) > > This code assumes that the relevant position of the argument is the > second one in dbGetQuery. A more future proof version would be > something like > > allocatedSpace <- lapply(sql, function(state) dbGetQuery(con = con, > statement = state)) > > Kasper > > On Mon, Nov 29, 2010 at 9:34 PM, Nick Torenvliet > wrote: > > This isn't strictly an DB question, but I'll venture out anyways... > > > > I've got a vector of sql statements called sql > > > >> sql > > [1] "select date, open, high, low, close, volume from endOfDayData where > > date>'2009-09-01' and market like 'TSX' and symbol like 'AAB' order by > date" > > . > > . > > . > > [2464] "select date, open, high, low, close, volume from endOfDayData > where > > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUH' order by > date" > > > > [2465] "select date, open, high, low, close, volume from endOfDayData > where > > date>'2009-09-01' and market like 'TSX' and symbol like 'ZUT' order by > > date" > > > > For each element in sql I'd like to run the queries in sql and have them > in > > another vector of some sort as in the following for loop... > > > > for(x in sql){ > > allocatedSpace <- dbGetQuery(con, statement=x) > > } > > > > Is there a way to do this in a more R-esque vector like fashion/single > line? > > Hi, Nick. Are you sure that you can't do this on the database side with a single query with something like: select symbol, date, open, high, low, close, volume from endOfDayData where date>'2009-09-01' and market like 'TSX' and symbol in ('ZUH','ZUT','AAB') order by symbol,date Typically (but not always), letting the database do this once will be more performant than several thousand separate queries. On the R side, you can use split() if you need separate data frames per symbol. Also, just a note that using "like" instead of "=" will often disable the use of indexes in the query, perhaps significantly impacting performance. Sean > > Regards, > > > > Nick > > > > [[alternative HTML version deleted]] > > > > _______________________________________________ > > R-sig-DB mailing list -- R Special Interest Group > > R-sig-DB at r-project.org > > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > > > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > [[alternative HTML version deleted]] From dm@||v @end|ng |rom gm@||@com Fri Dec 17 00:47:47 2010 From: dm@||v @end|ng |rom gm@||@com (Daniel) Date: Thu, 16 Dec 2010 21:47:47 -0200 Subject: [R-sig-DB] Help with loop Message-ID: Hello all, Is there a way to get each file from a website list and aggregate in a unique file? Otherwise I have to type 23 thousand web address into a long script like it: base1 <- read.table("site 1", sep=";", header=T, fileEncoding="windows-1252") base2 <- read.table("site 2", sep=";", header=T, fileEncoding="windows-1252") I need to download each .CSV file from each web address and "rbind" all them into one data frame. Also I need to translate each object to UTF-8. Of course many of address maybe be empty, so, my loop can't stops because this. I never type a loop before, so, in my first shot I get an error. Can somebody help me? myresult <- NULL n <-length(mysites) for (i in 1:n) { bases <- read.table(n) bases[i]<-read.table(mysites[[i]], sep=";", header=TRUE, fileEncoding="windows-1252") tudo <- rbind(myresult, bases) } Error in read.table(n) : 'file' must be a character string or connection -- Daniel Marcelino Skype: dmsilv www.sites.google.com/site/politicaevoce/ [[alternative HTML version deleted]] From n||z@b@rro@ @end|ng |rom gm@||@com Sat Dec 18 21:20:19 2010 From: n||z@b@rro@ @end|ng |rom gm@||@com (Nilza BARROS) Date: Sat, 18 Dec 2010 18:20:19 -0200 Subject: [R-sig-DB] R-sig-DB Digest, Vol 74, Issue 2 In-Reply-To: References: Message-ID: Hi, Your mistake is related with "read.table(n)". I haven`t got what you want but I put an example below (where I used loop to read many files). Hope it helps. Anyway, I thougth should have sent your question to . Bye Nilza =====example ==================== nomesout <- dir(dat.dir,pattern="^[s]") #obtem no diretorio de atual todos arquivo iniciado com a letra s OUT <- read.table(paste(dat.dir,nomesout[1] ,sep = ""),header = FALSE, sep = ",", na.strings = c("/","///","////","/////","//////")) for(i in 2:length(nomesout)){ Y <- read.table(paste(dat.dir,nomesout[i] ,sep = ""),header = FALSE, sep = ",") OUT <- rbind(OUT, Y) } OUT <- OUT[-1,] ## Remove linhas em branco On Fri, Dec 17, 2010 at 9:00 AM, wrote: > Send R-sig-DB mailing list submissions to > r-sig-db at r-project.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://stat.ethz.ch/mailman/listinfo/r-sig-db > or, via email, send a message with subject or body 'help' to > r-sig-db-request at r-project.org > > You can reach the person managing the list at > r-sig-db-owner at r-project.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of R-sig-DB digest..." > > > Today's Topics: > > 1. Help with loop (Daniel) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 16 Dec 2010 21:47:47 -0200 > From: Daniel > To: r-sig-db at stat.math.ethz.ch > Subject: [R-sig-DB] Help with loop > Message-ID: > > Content-Type: text/plain > > Hello all, > Is there a way to get each file from a website list and aggregate in a > unique file? > Otherwise I have to type 23 thousand web address into a long script like > it: > > base1 <- read.table("site 1", sep=";", header=T, > fileEncoding="windows-1252") > base2 <- read.table("site 2", sep=";", header=T, > fileEncoding="windows-1252") > > I need to download each .CSV file from each web address and "rbind" all > them > into one data frame. > Also I need to translate each object to UTF-8. Of course many of address > maybe be empty, so, my loop can't stops because this. > > I never type a loop before, so, in my first shot I get an error. Can > somebody help me? > > myresult <- NULL > n <-length(mysites) > for (i in 1:n) { > bases <- read.table(n) > bases[i]<-read.table(mysites[[i]], sep=";", header=TRUE, > fileEncoding="windows-1252") > tudo <- rbind(myresult, bases) > } > > Error in read.table(n) : 'file' must be a character string or connection > -- > Daniel Marcelino > Skype: dmsilv > www.sites.google.com/site/politicaevoce/ > > [[alternative HTML version deleted]] > > > > ------------------------------ > > _______________________________________________ > R-sig-DB mailing list > R-sig-DB at r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-db > > > End of R-sig-DB Digest, Vol 74, Issue 2 > *************************************** > -- Abra?o, Nilza Barros [[alternative HTML version deleted]] From RUEDIGER@LANDSCHEIDT @end|ng |rom ALLIANZ@COM Thu Dec 23 15:33:24 2010 From: RUEDIGER@LANDSCHEIDT @end|ng |rom ALLIANZ@COM (Landscheidt, Ruediger Joachim (AIM SE)) Date: Thu, 23 Dec 2010 15:33:24 +0100 Subject: [R-sig-DB] error: install the oackage "RMySQL" Message-ID: <9AA0409178E2D14DAFBE80D2F7EB278083B0F9FDB7@VAXMUCQ1.wwg00m.rootdom.net> Hello I have a problem. I want to install the package "RMySQL". I was trying to install the package how they describe the installationway in http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL. During building a binary of the source or installing the package, i get this error-message : * installing *source* package 'RMySQL' ... checking for $MYSQL_HOME... C:/Programme/xampp/mysql cygwin warning: MS-DOS style path detected: C:/Programme/xampp/mysql Preferred POSIX equivalent is: /cygdrive/c/Programme/xampp/mysql CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames cp: cannot stat `src/Makevars.win32': No such file or directory <--- especially what is that?! ERROR: configuration failed for package 'RMySQL' * removing 'C:/.../RMYSQL~1.RCH/RMySQL' Also I get this information too: * installing *source* package 'RMySQL' ... checking for $MYSQL_HOME... C:/Programme/xampp/mysql cygwin warning: MS-DOS style path detected: C:/Programme/xampp/mysql Preferred POSIX equivalent is: /cygdrive/c/Programme/xampp/mysql CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames ** libs Warnung: this package has a non-empty 'configure.win' file, so building only the main architecture cygwin warning: MS-DOS style path detected: C:/PROGRA~1/R/R-212~1.1/etc/i386/Makeconf Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-212~1.1/etc/i386/Makeconf CYGWIN environment variable option "nodosfilewarning" turns off this warning. Consult the user's guide for more details about POSIX paths: http://cygwin.com/cygwin-ug-net/using.html#using-pathnames gcc -I"C:/PROGRA~1/R/R-212~1.1/include" -I"C:/Programme/xampp/mysql"/include -O3 -Wall -std=gnu99 -c RS-DBI.c -o RS-DBI.o gcc -I"C:/PROGRA~1/R/R-212~1.1/include" -I"C:/Programme/xampp/mysql"/include -O3 -Wall -std=gnu99 -c RS-MySQL.c -o RS-MySQL.o In file included from RS-MySQL.c:22:0: RS-MySQL.h:32:19: fatal error: mysql.h: No such file or directory compilation terminated. make: *** [RS-MySQL.o] Error 1 ERROR: compilation failed for package 'RMySQL' * removing 'C:/Programme/R/R-2.12.1/library/RMySQL' I dont know how i can correct the error?! So I'm lost and please tell me, where is my problem?! Thanks for your help. R??diger Landscheidt Some backgroundinformation: I am using R.2.12.1, mysql is 5.0.67 and xampp is 1.6.8. Also i am using Eclipse 3.6. with Rtools 212 in C:/Programme/Rtools. I set the environmental variable: MYSQL_HOME= C:/Programme/xampp/mysql. [[alternative HTML version deleted]]