From Peter@Soren@en2 @end|ng |rom @gr@c|@dk Fri Oct 13 19:44:38 2006 From: Peter@Soren@en2 @end|ng |rom @gr@c|@dk (=?iso-8859-1?Q?Peter_S=F8rensen_=28HAG=29?=) Date: Fri, 13 Oct 2006 19:44:38 +0200 Subject: [R-sig-DB] RMySQL for windows References: <2AB7346A3227A74BB97F9A0D79E3E65A03F26D@mailserver.kalyptorisk.com> Message-ID: Dear List, Uptill now I have been using RMySQL on Windows and installed it by downloading the precompiled (binary) version of RMySQL for windows from http://stat.bell-labs.com/RS-DBI/download/. It works great. I have now upgraded R to 2.4.0 and would like to install RMySQL again, but the binary version (RMySQL 0.5-7 ) downloaded from the above website does not work with R2.4.0 ( but it did work with R version 2.4.0 Under development (unstable) (2006-07-25 r38698)). I get the following error message with R2.4.0: > library(RMySQL) > drv <- dbDriver("MySQL", max.con = 25) > con <- dbConnect(drv, host="localhost",user="root", password="pomp06", dbname="ggdb2r") Error in function (classes, fdef, mtable) : unable to find an inherited method for function "dbConnect", for signature "MySQLDriver" > I have looked for a windows binary of RMySQL for R2.4.0 at the R/Bioconductor websites, but could not find any. Is there a windows binary avaliable somewhere? I then tried to install the package from source ( using the commands pasted below) on Windows, but run into the following problem: " installation of package 'RMySQL' had non-zero exit status in: install.packages("RMySQL", type = "source") " I have followed the instructions "6 Add-on packages" from the R-help, but this is the first time I tried to install a package from source so maybe I am doing something wrong. I will continue to try to solve problem, but if someone would have seen something similar and perhaps give me some directions on how to solve the problem I would of course appreciate that. I hope that my questions are clear and that this is the right place to send these questions. Thanks Peter > install.packages("RMySQL", type="source") trying URL 'http://www.biometrics.mtu.edu/CRAN/src/contrib/RMySQL_0.5-9.tar.gz' Content type 'application/x-gzip' length 142529 bytes opened URL downloaded 139Kb The downloaded packages are in C:\Documents and Settings\sor\Local Settings\Temp\RtmpEVzIqb\downloaded_packages updating HTML package descriptions Warning message: installation of package 'RMySQL' had non-zero exit status in: install.packages("RMySQL", type = "source") > > sessionInfo() R version 2.4.0 (2006-10-03) i386-pc-mingw32 locale: LC_COLLATE=English_United States.1252;LC_CTYPE=English_United States.1252;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252 attached base packages: [1] "methods" "stats" "graphics" "grDevices" "utils" "datasets" "base" other attached packages: DBI "0.1-10" From @|@|con @end|ng |rom |hcrc@org Fri Oct 13 23:03:37 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Fri, 13 Oct 2006 14:03:37 -0700 Subject: [R-sig-DB] RMySQL for windows In-Reply-To: (Peter =?iso-8859-1?Q?S=F8rensen's?= message of "Fri, 13 Oct 2006 19:44:38 +0200") References: <2AB7346A3227A74BB97F9A0D79E3E65A03F26D@mailserver.kalyptorisk.com> Message-ID: Hi Peter, "Peter S?rensen (HAG)" writes: > Uptill now I have been using RMySQL on Windows and installed it by > downloading the precompiled (binary) version of RMySQL for windows > from http://stat.bell-labs.com/RS-DBI/download/. It works great. I > have now upgraded R to 2.4.0 and would like to install RMySQL again, > but the binary version (RMySQL 0.5-7 > > ) downloaded from the above website does not work with R2.4.0 ( but > it did work with R version 2.4.0 Under development (unstable) > (2006-07-25 r38698)). The S4 class system, which is used by RMySQL, went through heavy revision during the R 2.4.0 dev cycle. So I'm pretty sure that explains why the binary package worked with an R from late July and doesn't work with the release. > I have looked for a windows binary of RMySQL for R2.4.0 at the > R/Bioconductor websites, but could not find any. Is there a windows > binary avaliable somewhere? I don't know of one, but we will see what we can do here to build one and make it available. > I hope that my questions are clear and that this is the right place > to send these questions. I think this is the right place. Thanks for the report. + seth From @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com Mon Oct 16 07:17:20 2006 From: @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com (Ashish Kulkarni) Date: Mon, 16 Oct 2006 10:47:20 +0530 Subject: [R-sig-DB] RSQLite enhancements Message-ID: <2AB7346A3227A74BB97F9A0D79E3E65A064F81@mailserver.kalyptorisk.com> Hello, I've been working on some enhancements for RSQLite, and here they are :-) CHANGES ======= Version 0.4-5 * Update to the SQLite 3 API for fetching records. This means that the records are pulled from the database as required, and not cached in memory as was previously done. * dbConnect() now has a parameter "loadable.extensions" which will allow loading of extensions. See the loadable extensions documentation: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions The source and binary packages are available at: http://puggy.symonds.net/~ashish/downloads/R/ I'm planning to add more features to RSQLite next week: * use actual types from SQLite and remove the type.convert calls * implement DBIPreparedStatment and friends, similar to ROracle * implement dbApply (if time permits .. this will be tough). BTW, the SQLite tarball for 0.4-4 contains a ELF-binary copy of SQLite (I assume it's Linux) in the inst/ folder. This get copied on even windows binary packages, increasing its size from roughly 356kB -> 1.5M. Hope this helps, Ashish From @|@|con @end|ng |rom |hcrc@org Mon Oct 16 23:17:00 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Mon, 16 Oct 2006 14:17:00 -0700 Subject: [R-sig-DB] RSQLite enhancements In-Reply-To: <2AB7346A3227A74BB97F9A0D79E3E65A064F81@mailserver.kalyptorisk.com> (Ashish Kulkarni's message of "Mon, 16 Oct 2006 10:47:20 +0530") References: <2AB7346A3227A74BB97F9A0D79E3E65A064F81@mailserver.kalyptorisk.com> Message-ID: Hi Ashish, "Ashish Kulkarni" writes: > Hello, > > I've been working on some enhancements for RSQLite, and here they > are :-) Wow, these changes look great. I will plan on applying the patch you posted. > I'm planning to add more features to RSQLite next week: > > * use actual types from SQLite and remove the type.convert calls > * implement DBIPreparedStatment and friends, similar to ROracle > * implement dbApply (if time permits .. this will be tough). Sounds good. I think there are many users who will appreciate having access to prepared statements. > BTW, the SQLite tarball for 0.4-4 contains a ELF-binary copy of > SQLite (I assume it's Linux) in the inst/ folder. This get copied on > even windows binary packages, increasing its size from roughly 356kB > -> 1.5M. Oops, will take care of that in the next push to CRAN. While there _is_ an ELF-binary that doesn't belong, the size is not going to be 356kB. The sqlite source tarball is 1.7MB alone. + seth From @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com Tue Oct 17 18:14:03 2006 From: @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com (Ashish Kulkarni) Date: Tue, 17 Oct 2006 21:44:03 +0530 Subject: [R-sig-DB] RSQLite enhancements In-Reply-To: Message-ID: <2AB7346A3227A74BB97F9A0D79E3E65A06503A@mailserver.kalyptorisk.com> Hello, I have another (large) set of enhancements :-) All of these are relative to the previous version. I haven't made a patch for this, as I updated to SQLite 3.3.8 (some export symbols were missing on windows for 3.3.7) resulting in too many diffs. The source, windows binary and PDF are at the same place: http://puggy.symonds.net/~ashish/downloads/R/ Re: the file size, I was talking of the windows binary size not the source tarball -- as a comparison: RSQLite_0.4-6.zip => 375kB RSQLite_0.4-4.zip => 1.5M while the source is again smaller, as I've removed the ELF-binary: RSQLite_0.4-6.tar.gz => 2.9M RSQLite_0.4-4.tar.gz => 3.7M I won't be doing dbPreparedStatement, as dbExecuteQuery() along with transactions provides good enough performance for me. The rest I will be doing as time permits... Regards, Ashish Version 0.4-6 ============= * Upgraded to SQLite 3.3.8 * Use .SQLitePkgName instead of hard-coding the package name when using .Call * The SQLite driver has a new logical parameter "shared.cache" to enable the shared-cache mode, which allows multiple connections to share a single data and schema cache. See http://www.sqlite.org/sharedcache.html * Implemented dbCommit() and dbRollback(). There is also a new generic method dbBeginTransaction(), which begins a transaction. Note that this is an extension to the DBI interface. * Added a new generic method dbExecuteQuery(). It takes an extra "bind.data" parameter, which is a data.frame. It returns the resultset, similiar to dbSendQuery(). For each column in the data frame, the SQL statement is searched for named parameters in the form of ":name" or "@name". If no such parameter is found, it is assumed to be a positional parameter in the form of "?", and the index of the column is used to specify the parameter. Please note that no validation is done that some parameters are there is no overlap in the two, so always use either valid names or positional parameters. An example: rs <- dbExecuteStatement(con, "INSERT INTO test(str_val) VALUES (:val)", data.frame(val=c("a", "b")) ) Also, setting options(stringsAsFactors=FALSE) if you are using a large number of strings is advisable, as they will have to be converted from factors to strings. See ?data.frame for more details. From dr|e @end|ng |rom port|o||o|nte|||gence@com Thu Oct 19 15:17:10 2006 From: dr|e @end|ng |rom port|o||o|nte|||gence@com (Dan Rie) Date: Thu, 19 Oct 2006 09:17:10 -0400 Subject: [R-sig-DB] RMySQL problem - inherited dbConnect method missing for MySQL Message-ID: <000b01c6f380$e3d3cd70$0ac0a8c0@MightyMini> I am hoping that you can help me resolve a problem I am having using RMySQL. I've searched the R help news archives, but RMySQL appears to be so trouble free that there are remarkably few problems reported there, and none that appear to be related to mine. I am trying to use RMySQL_0.5-7 on a Windows XP-Pro machine running R 2.4.0 and MySQL v5.0.26. I believe that MySQL is installed and working properly (I an able to use MySQL in Python through MySQLdb without any difficulty), but it's possible that I have some configuration issue causing the problem described below. The MySQL server is running on the same machine that is running R. I have been working from the windows binary installation package RMySQL_0.5-7.zip. I have installed it from the R Packages menu using "Install package(s) from local zip files...". I have tried various combinations of adding Path references pointing to the folder with libmysql.dll and libmysql.lib using references pointing to the MySQL distribution version of these files and/or pointing to the RMySQL version of the files (which are a bit older than those in C:\Program Files\MySQL\MySQL Server 5.0\lib\opt). I have also tried copying the three dll files to ...\R-2.3.0\bin. The problem if first evident when I attempt to create a connection, but may actually start when I attempt to create the driver variable. Here is the R transcript after loading the RMySQL package: # initialize and create MySQL driver variable > m<-dbDriver("MySQL") #does not create output like example code > m An object of class "MySQLDriver" Slot "Id": [1] 3376 # create a DBI connection variable - Error results > con <- dbConnect(m,host="localhost", user="Dan", password="abcdef", dbname="test") Error in function (classes, fdef, mtable) : unable to find an inherited method for function "dbConnect", for signature "MySQLDriver" # summary of m indicates another failure to find MySQL specific DBI function > summary(m) Error in function (classes, fdef, mtable) : unable to find an inherited method for function "dbGetInfo", for the signature "MySQLDriver" # m in expired state, but perhaps that is natural for uninitialized driver > mysqlDriverInfo(m) Error in mysqlDriverInfo(m) : expired MySQLDriver # some diagnostic checks: > z<-.packages() > z [1] "RMySQL" "DBI" "methods" "stats" "graphics" "grDevices" [7] "utils" "datasets" "base" > objects(2) [1] "dbApply" "dbBuildTableDefinition" [3] "ErrorClass" "isIDCurrent" [5] "MySQL" "mysqlCloneConnection" [7] "mysqlCloseConnection" "CloseDriver" [9] "mysqlCloseResult" "mysqlConnectionInfo" [11] "mysqlDataType" "mysqlDBApply" [13] "mysqlDescribeConnection" "mysqlDescribeDriver" [15] "mysqlDescribeFields" "mysqlDescribeResult" [17] "mysqlDriverInfo" "mysqlExecStatement" [19] "mysqlFetch" "mysqlImportFile" [21] "mysqlInitDriver" "mysqlNewConnection" [23] "mysqlQuickSQL" "mysqlReadTable" [25] "mysqlResultInfo" "mysqlWriteTable" [27] "safe.write" "usingR" I have uninstalled, then reinstalled the RMySQL package, but the error persists. I'm kind of at a loss as to what to do to fix this problem or even where to look for clues. It looks like perhaps the RMySQL.dll is not being found, but it is in the directory that the zip file indicated (c:\Program Files\R\R-2.4.0\library\RMySQL\libs), and at least for some tests, the Path string includes that directory. I've tried copying the file RMySQL.dll to the ....\R-2.4.0\bin directory, but no joy there either. Any help or suggestions that you can provide will be greatly appreciated. Regards, Dan Rie Scituate, MA From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Fri Oct 20 10:30:16 2006 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Fri, 20 Oct 2006 09:30:16 +0100 (BST) Subject: [R-sig-DB] RMySQL problem - inherited dbConnect method missing for MySQL In-Reply-To: <000b01c6f380$e3d3cd70$0ac0a8c0@MightyMini> References: <000b01c6f380$e3d3cd70$0ac0a8c0@MightyMini> Message-ID: Where did you get RMySQL_0.5-7.zip from? Was it built for R 2.4.0? The symptoms are those of using S4-using packages which have not been reinstalled for R 2.4.0. The current version is RMySQL_0.5-9.tar.gz on CRAN, and I suggest you compile that from the sources. On Thu, 19 Oct 2006, Dan Rie wrote: > I am hoping that you can help me resolve a problem I am having using RMySQL. > I've searched the R help news archives, but RMySQL appears to be so trouble > free that there are remarkably few problems reported there, and none that > appear to be related to mine. Yours is a recent phenomenon. > I am trying to use RMySQL_0.5-7 on a Windows XP-Pro machine running > R 2.4.0 and MySQL v5.0.26. > > I believe that MySQL is installed and working properly (I an able to use > MySQL in Python through MySQLdb without any difficulty), but it's possible > that I have some configuration issue causing the problem described below. > The MySQL server is running on the same machine that is running R. > > I have been working from the windows binary installation package > RMySQL_0.5-7.zip. I have installed it from the R Packages menu using > "Install package(s) from local zip files...". I have tried various > combinations of adding Path references pointing to the folder with > libmysql.dll and libmysql.lib using references pointing to the MySQL > distribution version of these files and/or pointing to the RMySQL version of > the files (which are a bit older than those in C:\Program Files\MySQL\MySQL > Server 5.0\lib\opt). I have also tried copying the three dll files to > ...\R-2.3.0\bin. > > The problem if first evident when I attempt to create a connection, but may > actually start when I attempt to create the driver variable. > Here is the R transcript after loading the RMySQL package: > > # initialize and create MySQL driver variable > > m<-dbDriver("MySQL") #does not create output like example code > > m > An object of class "MySQLDriver" > Slot "Id": > [1] 3376 > # create a DBI connection variable - Error results > > con <- dbConnect(m,host="localhost", user="Dan", password="abcdef", > dbname="test") > Error in function (classes, fdef, mtable) : > unable to find an inherited method for function "dbConnect", > for signature "MySQLDriver" > # summary of m indicates another failure to find MySQL specific DBI function > > summary(m) > Error in function (classes, fdef, mtable) : > unable to find an inherited method for function "dbGetInfo", > for the signature "MySQLDriver" > # m in expired state, but perhaps that is natural for uninitialized driver > > mysqlDriverInfo(m) > Error in mysqlDriverInfo(m) : expired MySQLDriver > > # some diagnostic checks: > > z<-.packages() > > z > [1] "RMySQL" "DBI" "methods" "stats" "graphics" > "grDevices" > [7] "utils" "datasets" "base" > > > objects(2) > [1] "dbApply" "dbBuildTableDefinition" > [3] "ErrorClass" "isIDCurrent" > [5] "MySQL" "mysqlCloneConnection" > [7] "mysqlCloseConnection" "CloseDriver" > [9] "mysqlCloseResult" "mysqlConnectionInfo" > [11] "mysqlDataType" "mysqlDBApply" > [13] "mysqlDescribeConnection" "mysqlDescribeDriver" > [15] "mysqlDescribeFields" "mysqlDescribeResult" > [17] "mysqlDriverInfo" "mysqlExecStatement" > [19] "mysqlFetch" "mysqlImportFile" > [21] "mysqlInitDriver" "mysqlNewConnection" > [23] "mysqlQuickSQL" "mysqlReadTable" > [25] "mysqlResultInfo" "mysqlWriteTable" > [27] "safe.write" "usingR" > > I have uninstalled, then reinstalled the RMySQL package, but the error > persists. I'm kind of at a loss as to what to do to fix this problem or > even where to look for clues. It looks like perhaps the RMySQL.dll is not > being found, but it is in the directory that the zip file indicated > (c:\Program Files\R\R-2.4.0\library\RMySQL\libs), and at least for some > tests, the Path string includes that directory. I've tried copying the > file RMySQL.dll to the ....\R-2.4.0\bin directory, but no joy there either. > > Any help or suggestions that you can provide will be greatly appreciated. > > Regards, > > Dan Rie > Scituate, MA > > _______________________________________________ > 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|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com Fri Oct 20 17:11:23 2006 From: @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com (Ashish Kulkarni) Date: Fri, 20 Oct 2006 20:41:23 +0530 Subject: [R-sig-DB] RSQLite enhancements Message-ID: <2AB7346A3227A74BB97F9A0D79E3E65A06511C@mailserver.kalyptorisk.com> Hello, Seth created a branch for me on which I've made merged the changes, which is at: https://hedgehog.fhcrc.org/compbio/r-dbi/branches/RSQLite-ak/ Regards, Ashish Version 0.4-7 ============= * Upgraded to SQLite 3.3.8 * Use .SQLitePkgName instead of hard-coding the package name when using .Call * The SQLite driver has a new logical parameter "shared.cache" to enable the shared-cache mode, which allows multiple connections to share a single data and schema cache. See http://www.sqlite.org/sharedcache.html * dbConnect() now has a logical parameter "loadable.extensions" which will allow loading of extensions. See the Loadable Extensions documentation: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions * Implemented dbCommit() and dbRollback(). There is also a new generic method dbBeginTransaction(), which begins a transaction. Note that this is an extension to the DBI interface. * Update to the SQLite 3 API for fetching records. This means that the records are pulled from the database as required, and not cached in memory as was previously done. * Added generic methods dbSendPreparedQuery() and dbGetPreparedQuery() which are similiar to dbSendQuery() and dbGetQuery(), but take an extra "bind.data" parameter, which is a data frame. The statement is assumed to contain bind variables. Bind variables are either for a column name (":name" or "@name") or for a column index ("?") in the data frame. See http://sqlite.org/capi3ref.html for more details. dbGetPreparedQuery(con, "INSERT INTO table1 VALUES (:col1)", data.frame(col1=c(1, 2)) ) Each bind variable in the query has to be bound only once, either via named or positional parameters. If it is not bound or is bound more than once (due to a mix or positional/named parameters) an error is thrown. Any extra columns in the data frame are ignored. If you are having a lot of string parameters, ensuring that they are wrapped via calls to I() would reduce allocated memory, as by default string vectors are converted to factors. See ?I and ?data.frame for more details. From @|@|con @end|ng |rom |hcrc@org Wed Oct 25 23:01:09 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Wed, 25 Oct 2006 14:01:09 -0700 Subject: [R-sig-DB] RSQLite enhancements In-Reply-To: <2AB7346A3227A74BB97F9A0D79E3E65A06511C@mailserver.kalyptorisk.com> (Ashish Kulkarni's message of "Fri, 20 Oct 2006 20:41:23 +0530") References: <2AB7346A3227A74BB97F9A0D79E3E65A06511C@mailserver.kalyptorisk.com> Message-ID: Hi Ashish, "Ashish Kulkarni" writes: > Seth created a branch for me on which I've made merged the > changes, which is at: > > https://hedgehog.fhcrc.org/compbio/r-dbi/branches/RSQLite-ak/ > > Regards, > Ashish > > Version 0.4-7 > ============= Sorry for the delay. The changes look good. I was just testing out a merge into trunk... If you can cleanup the doc, I will merge the changes into trunk. Here's what R CMD check is showing: * checking for code/documentation mismatches ... WARNING Codoc mismatches from documentation object 'SQLite': SQLite Code: function(max.con = 16, fetch.default.rec = 500, force.reload = FALSE, shared.cache = FALSE) Docs: function(max.con = 16, fetch.default.rec = 500, force.reload = FALSE) Argument names in code not in docs: shared.cache * checking Rd \usage sections ... WARNING Documented arguments not in \usage in documentation object 'sqliteSupport': limit Functions with \usage entries need to have the appropriate \alias entries, and all their arguments documented. The \usage entries must correspond to syntactically valid R code. See the chapter 'Writing R documentation files' in manual 'Writing R Extensions'. Best, + seth From @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com Thu Oct 26 08:14:04 2006 From: @@h|@h@ku|k@rn| @end|ng |rom k@|yptor|@k@com (Ashish Kulkarni) Date: Thu, 26 Oct 2006 11:44:04 +0530 Subject: [R-sig-DB] RSQLite enhancements In-Reply-To: Message-ID: <2AB7346A3227A74BB97F9A0D79E3E65A065155@mailserver.kalyptorisk.com> > Sorry for the delay. The changes look good. I was just testing out a > merge into trunk... > > If you can cleanup the doc, I will merge the changes into trunk. > > Here's what R CMD check is showing: > > [snip] Fixed the documentation, please review it and let me know if I missed anything. Regards, ashish From @|@|con @end|ng |rom |hcrc@org Thu Oct 26 22:59:37 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Thu, 26 Oct 2006 13:59:37 -0700 Subject: [R-sig-DB] Updates for DBI, RSQLite, RMySQL, ROracle Message-ID: Hello all, Updates to the above named packages have been pushed to CRAN and should be available in the next few days. In the meantime, you can get the latest here: http://www.bioconductor.org/packages/misc/ We've built Windows binaries for RSQLite and RMySQL and posted them at the above location. I'm not sure what a more permanent solution is to distributing the Windows binaries; this is a temporary measure. Changes ======= Generics make.db.names and isSQLKeyword in DBI now enforce the implied interface in terms of additional named arguments. This required minor changes to the R packages. You should not notice anything different on the user side of things. RSQLite has been updated to the latest SQLite release, 3.3.8. Best Wishes, + seth -- Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org From @|@|con @end|ng |rom |hcrc@org Thu Oct 26 23:03:47 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Thu, 26 Oct 2006 14:03:47 -0700 Subject: [R-sig-DB] RSQLite enhancements In-Reply-To: <2AB7346A3227A74BB97F9A0D79E3E65A065155@mailserver.kalyptorisk.com> (Ashish Kulkarni's message of "Thu, 26 Oct 2006 11:44:04 +0530") References: <2AB7346A3227A74BB97F9A0D79E3E65A065155@mailserver.kalyptorisk.com> Message-ID: I wrote: >> Sorry for the delay. The changes look good. I was just testing out a >> merge into trunk... And I spoke a bit too soon it seems. R is freezing when testing the changes introduced in r200. Seems memory related as the hang only occurs after a number of calls to RSQLite code. "Ashish Kulkarni" writes: > Fixed the documentation, please review it and let me know if I > missed anything. The doc fixes look good, thanks. I have merged up to r195 without trouble and will wait on the rest until some fixes are available. Best, + seth From @|@|con @end|ng |rom |hcrc@org Sun Nov 5 00:04:00 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Sat, 04 Nov 2006 15:04:00 -0800 Subject: [R-sig-DB] RSQLite enhancements UPDATED In-Reply-To: (Seth Falcon's message of "Thu, 26 Oct 2006 14:03:47 -0700") References: <2AB7346A3227A74BB97F9A0D79E3E65A065155@mailserver.kalyptorisk.com> Message-ID: Hi all, I have applied Ashish's enhacement patches (with some important fixes on top). I would love to hear feedback from anyone who can try out the new package before we push it to CRAN. The new stuff is in svn: https://hedgehog.fhcrc.org/compbio/r-dbi/trunk/SQLite/RSQLite user: readonly pass: readonly Or you can grab 0.4-11 from here: http://www.bioconductor.org/packages/misc This update includes two significant speed improvements: 1. Transactions. By default, SQLite operates in auto-commit mode. This means that each SQL statement sent to the DB is immediately committed. If you are doing many INSERTS or UPDATES, it is _much_ faster to start a transaction (which turns of auto-commit), do the changes, and then commit. Here's an example: dbBeginTransaction(db) ## execute many update queries here dbCommit(db) Note also that if an error occurs along the way, you can also use dbRollback to undo to where you issued the begin transaction. 2. Prepared queries and bound parameters. This will also speed inserts and updates. Instead of looping on calls to dbGetQuery, you can instead collect the data in a data.frame and make one call to the DB. Here's a simple example: df <- data.frame(a=x, b=y, c=z) dbBeginTransaction(db) dbGetPreparedQuery(db, "update points set xpos = :a, ypos = :b where flag = :c", bind.data=df) dbCommit(db) Best Wishes, + seth From bourgon @end|ng |rom eb|@@c@uk Mon Nov 6 14:33:58 2006 From: bourgon @end|ng |rom eb|@@c@uk (Richard Bourgon) Date: Mon, 6 Nov 2006 13:33:58 +0000 Subject: [R-sig-DB] Segmentation faults using RSQLite Message-ID: <49B2A836-C817-4FEE-B2E9-F269134C3061@ebi.ac.uk> I'm relatively new to accessing databases from within R, and have been having trouble with segmentation faults when using RSQLite. My application requires a single table of doubles with 225 columns and about 6M rows. The following session transcript mimics this table, and consistently produces errors: sometimes an immediate seg fault, and sometimes one of several odd but non-critical error messages, an example of which appears below. (In the latter case, if I try to rerun the bit that generated the error, then I usually get the seg fault.) Small numbers of retrievals always work fine (i.e., the single "get_table()" call below, or looping only 50 or 100 times), while large numbers lead to the errors. If I reduce the number of columns in the table, I also seem to avoid the errors. Some version info is given below, but please let me know if anything else would be helpful. I didn't use the fancy options when installing the RSQLite package, so as best I can tell, it's using the compiled library that gets built in the sqlite/lib directory, inside the package's own directory. Any advice would be greatly appreciated. - Richard **** R session transcript > version _ platform x86_64-unknown-linux-gnu arch x86_64 os linux-gnu system x86_64, linux-gnu status major 2 minor 4.0 year 2006 month 10 day 03 svn rev 39566 language R version.string R version 2.4.0 (2006-10-03) > installed.packages()[installed.packages()[,"Package"] %in% c ("RSQLite","DBI"), c(1,3,10)] Package Version Built DBI "DBI" "0.1-11" "2.4.0" RSQLite "RSQLite" "0.4-9" "2.4.0" > library( RSQLite ) Loading required package: DBI > drv <- SQLite() > con <- dbConnect( drv, "testing.db" ) > table.name <- "testing" > data <- as.data.frame( matrix( rnorm( 225000 ), ncol = 225 ) ) > dbWriteTable( con, "testing", data, row.names = F, overwrite = T ) [1] TRUE > get_data <- function( indices, con, table_name ) { + query <- paste( + "select * from", + table_name, + "where _ROWID_ in (", + paste( indices, collapse = "," ), + ")" + ) + dbGetQuery( con, query ) + } > get_data( 1:3, con, table.name )[,1:5] V1 V2 V3 V4 V5 1 -1.8803772 1.00073121 -0.3496548 1.027102 -0.7882639 2 -0.9140920 -0.02242254 -1.4682685 0.375063 -0.6137175 3 0.4243143 -0.15502231 1.6846590 1.210313 -0.4726484 > # No error with just one interation > temp <- lapply( 1:50, function(i) get_data( 1:3, con, table.name ) ) > # No error with 50 iterations > temp <- lapply( 1:2000, function(i) get_data( 1:3, con, table.name ) ) Error in sqliteResultInfo(dbObj, ...) : SET_STRING_ELT() can only be applied to a 'character vector', not a 'NULL' > # Rerunning now typically generates a segmentation fault > temp <- lapply( 1:2000, function(i) get_data( 1:3, con, table.name ) ) *** caught segfault *** address 0x2, cause 'memory not mapped' Traceback: 1: sqliteResultInfo(dbObj, ...) 2: dbGetInfo(res, "completed") 3: dbGetInfo(res, "completed") 4: .class1(object) 5: .class1(object) 6: is(object, Cl) 7: .valueClassTest(standardGeneric("dbHasCompleted"), "logical", "dbHasCompleted") 8: dbHasCompleted(rs) 9: sqliteQuickSQL(conn, statement, ...) 10: dbGetQuery(con, query) 11: dbGetQuery(con, query) 12: get_data(1:3, con, table.name) 13: FUN(c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, , 1998, 1999, 2000)[[152]], ...) 14: lapply(1:2000, function(i) get_data(1:3, con, table.name)) Possible actions: 1: abort (with core dump) 2: normal R exit 3: exit R without saving workspace 4: exit R saving workspace Selection: 3 Process R exited abnormally with code 70 at Mon Nov 6 12:22:14 2006 From bourgon @end|ng |rom eb|@@c@uk Mon Nov 6 15:05:32 2006 From: bourgon @end|ng |rom eb|@@c@uk (Richard Bourgon) Date: Mon, 6 Nov 2006 14:05:32 +0000 Subject: [R-sig-DB] Segmentation faults using RSQLite In-Reply-To: <454F3B39.7020701@ebi.ac.uk> References: <2866AE9DFC6DE24B9338B6CA00DA6634A15794@dkfzex2.ad.dkfz-heidelberg.de> <454F3B39.7020701@ebi.ac.uk> Message-ID: library( RSQLite ) ######## Open drv <- SQLite() con <- dbConnect( drv, "testing.db" ) table.name <- "testing" ######## Create a test database data <- as.data.frame( matrix( rnorm( 225000 ), ncol = 225 ) ) dbWriteTable( con, "testing", data, row.names = F, overwrite = T ) ######## My retrieval function get_data <- function( indices, con, table_name ) { query <- paste( "select * from", table_name, "where _ROWID_ in (", paste( indices, collapse = "," ), ")" ) dbGetQuery( con, query ) } ######## Testing get_data( 1:3, con, table.name )[,1:5] temp <- lapply( 1:50, function(i) get_data( 1:3, con, table.name ) ) temp <- lapply( 1:2000, function(i) get_data( 1:3, con, table.name ) ) On 6 Nov 2006, at 13:40, Wolfgang Huber wrote: > Great. Just a tiny little piece of comment - as an extra service, you > might attach the script that reproduces the problem without the ">" > and > "+" so that people can directly copy-paste it. > Cheers > Wolfgang > >> ------------------------------------------- >> From: r-sig-db-bounces at stat.math.ethz.ch on behalf of Richard >> Bourgon[SMTP:BOURGON at EBI.AC.UK] >> Sent: Monday, November 06, 2006 2:33:58 PM >> To: r-sig-db at stat.math.ethz.ch >> Subject: [R-sig-DB] Segmentation faults using RSQLite >> Auto forwarded by a Rule >> >> I'm relatively new to accessing databases from within R, and have >> been having trouble with segmentation faults when using RSQLite. >> >> My application requires a single table of doubles with 225 columns >> and about 6M rows. The following session transcript mimics this >> table, and consistently produces errors: sometimes an immediate seg >> fault, and sometimes one of several odd but non-critical error >> messages, an example of which appears below. (In the latter case, if >> I try to rerun the bit that generated the error, then I usually get >> the seg fault.) >> >> Small numbers of retrievals always work fine (i.e., the single >> "get_table()" call below, or looping only 50 or 100 times), while >> large numbers lead to the errors. If I reduce the number of columns >> in the table, I also seem to avoid the errors. >> >> Some version info is given below, but please let me know if anything >> else would be helpful. I didn't use the fancy options when installing >> the RSQLite package, so as best I can tell, it's using the compiled >> library that gets built in the sqlite/lib directory, inside the >> package's own directory. >> >> Any advice would be greatly appreciated. >> >> - Richard >> >> **** R session transcript >> >>> version >> _ >> platform x86_64-unknown-linux-gnu >> arch x86_64 >> os linux-gnu >> system x86_64, linux-gnu >> status >> major 2 >> minor 4.0 >> year 2006 >> month 10 >> day 03 >> svn rev 39566 >> language R >> version.string R version 2.4.0 (2006-10-03) >>> installed.packages()[installed.packages()[,"Package"] %in% c >> ("RSQLite","DBI"), c(1,3,10)] >> Package Version Built >> DBI "DBI" "0.1-11" "2.4.0" >> RSQLite "RSQLite" "0.4-9" "2.4.0" >>> library( RSQLite ) >> Loading required package: DBI >>> drv <- SQLite() >>> con <- dbConnect( drv, "testing.db" ) >>> table.name <- "testing" >>> data <- as.data.frame( matrix( rnorm( 225000 ), ncol = 225 ) ) >>> dbWriteTable( con, "testing", data, row.names = F, overwrite = T ) >> [1] TRUE >>> get_data <- function( indices, con, table_name ) { >> + query <- paste( >> + "select * from", >> + table_name, >> + "where _ROWID_ in (", >> + paste( indices, collapse = "," ), >> + ")" >> + ) >> + dbGetQuery( con, query ) >> + } >>> get_data( 1:3, con, table.name )[,1:5] >> V1 V2 V3 V4 V5 >> 1 -1.8803772 1.00073121 -0.3496548 1.027102 -0.7882639 >> 2 -0.9140920 -0.02242254 -1.4682685 0.375063 -0.6137175 >> 3 0.4243143 -0.15502231 1.6846590 1.210313 -0.4726484 >>> # No error with just one interation >>> temp <- lapply( 1:50, function(i) get_data( 1:3, con, table.name ) ) >>> # No error with 50 iterations >>> temp <- lapply( 1:2000, function(i) get_data( 1:3, con, >> table.name ) ) >> Error in sqliteResultInfo(dbObj, ...) : SET_STRING_ELT() can only be >> applied to a 'character vector', not a 'NULL' >>> # Rerunning now typically generates a segmentation fault >>> temp <- lapply( 1:2000, function(i) get_data( 1:3, con, >> table.name ) ) >> >> *** caught segfault *** >> address 0x2, cause 'memory not mapped' >> >> Traceback: >> 1: sqliteResultInfo(dbObj, ...) >> 2: dbGetInfo(res, "completed") >> 3: dbGetInfo(res, "completed") >> 4: .class1(object) >> 5: .class1(object) >> 6: is(object, Cl) >> 7: .valueClassTest(standardGeneric("dbHasCompleted"), "logical", >> "dbHasCompleted") >> 8: dbHasCompleted(rs) >> 9: sqliteQuickSQL(conn, statement, ...) >> 10: dbGetQuery(con, query) >> 11: dbGetQuery(con, query) >> 12: get_data(1:3, con, table.name) >> 13: FUN(c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, , 1998, 1999, >> 2000)[[152]], ...) >> 14: lapply(1:2000, function(i) get_data(1:3, con, table.name)) >> >> Possible actions: >> 1: abort (with core dump) >> 2: normal R exit >> 3: exit R without saving workspace >> 4: exit R saving workspace >> Selection: 3 >> >> Process R exited abnormally with code 70 at Mon Nov 6 12:22:14 2006 >> >> _______________________________________________ >> 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 > > > -- > ------------------------------------------------------------------ > Wolfgang Huber EBI/EMBL Cambridge UK http://www.ebi.ac.uk/huber > From @|@|con @end|ng |rom |hcrc@org Mon Nov 6 16:31:51 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Mon, 06 Nov 2006 07:31:51 -0800 Subject: [R-sig-DB] Segmentation faults using RSQLite In-Reply-To: <49B2A836-C817-4FEE-B2E9-F269134C3061@ebi.ac.uk> (Richard Bourgon's message of "Mon, 6 Nov 2006 13:33:58 +0000") References: <49B2A836-C817-4FEE-B2E9-F269134C3061@ebi.ac.uk> Message-ID: Hi Richard, Richard Bourgon writes: > My application requires a single table of doubles with 225 columns > and about 6M rows. The following session transcript mimics this > table, and consistently produces errors: sometimes an immediate seg > fault, and sometimes one of several odd but non-critical error > messages, an example of which appears below. (In the latter case, if > I try to rerun the bit that generated the error, then I usually get > the seg fault.) > > Small numbers of retrievals always work fine (i.e., the single > "get_table()" call below, or looping only 50 or 100 times), while > large numbers lead to the errors. If I reduce the number of columns > in the table, I also seem to avoid the errors. > > Some version info is given below, but please let me know if anything > else would be helpful. I didn't use the fancy options when installing > the RSQLite package, so as best I can tell, it's using the compiled > library that gets built in the sqlite/lib directory, inside the > package's own directory. > > Any advice would be greatly appreciated. You've identified a bug. Thanks for the sample code. We'll take a closer look and try to have a fix available in a few days. I've also noticed that in the patched version of RSQLite (see recent message to this list), dbWriteTable is broken. + seth From @|@|con @end|ng |rom |hcrc@org Tue Nov 7 08:12:32 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Mon, 06 Nov 2006 23:12:32 -0800 Subject: [R-sig-DB] Segmentation faults using RSQLite In-Reply-To: (Seth Falcon's message of "Mon, 06 Nov 2006 07:31:51 -0800") References: <49B2A836-C817-4FEE-B2E9-F269134C3061@ebi.ac.uk> Message-ID: > Richard Bourgon writes: >> My application requires a single table of doubles with 225 columns >> and about 6M rows. The following session transcript mimics this >> table, and consistently produces errors: sometimes an immediate seg >> fault, and sometimes one of several odd but non-critical error >> messages, an example of which appears below. (In the latter case, if >> I try to rerun the bit that generated the error, then I usually get >> the seg fault.) I believe that I've found and fixed the bug causing the segfault you reported. I've posted an updated version, RSQLite 0.4-13, here: http://www.bioconductor.org/packages/misc/ Could you give it a try and confirm that it works for you? Thanks again for the bug report. Best, + seth From bourgon @end|ng |rom eb|@@c@uk Tue Nov 7 13:32:57 2006 From: bourgon @end|ng |rom eb|@@c@uk (Richard Bourgon) Date: Tue, 7 Nov 2006 12:32:57 +0000 Subject: [R-sig-DB] Segmentation faults using RSQLite In-Reply-To: References: <49B2A836-C817-4FEE-B2E9-F269134C3061@ebi.ac.uk> Message-ID: <4A76F262-2879-40B4-8EB6-57129D6A255E@ebi.ac.uk> Seth, The updated version runs without errors. Thanks for the quick repair! - Richard On 7 Nov 2006, at 07:12, Seth Falcon wrote: >> Richard Bourgon writes: >>> My application requires a single table of doubles with 225 columns >>> and about 6M rows. The following session transcript mimics this >>> table, and consistently produces errors: sometimes an immediate seg >>> fault, and sometimes one of several odd but non-critical error >>> messages, an example of which appears below. (In the latter case, if >>> I try to rerun the bit that generated the error, then I usually get >>> the seg fault.) > > I believe that I've found and fixed the bug causing the segfault you > reported. > > I've posted an updated version, RSQLite 0.4-13, here: > > http://www.bioconductor.org/packages/misc/ > > Could you give it a try and confirm that it works for you? > > Thanks again for the bug report. > > Best, > > + seth From @|@|con @end|ng |rom |hcrc@org Wed Nov 8 18:57:09 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Wed, 08 Nov 2006 09:57:09 -0800 Subject: [R-sig-DB] RSQLite_0.4-13 uploaded to CRAN Message-ID: Hi all, I've just uploaded RSQLite_0.4-13.tar.gz to CRAN. Should be available on a mirror near you within a few days. Summary: Add sqliteQuckColumn, an experimental function to retrieve an entire column from a table as quickly as possible. Implement support for transactions as per DBI specs (with some extensions) Add support for prepared statements, and use the SQLite3 API for fetching Fix bug on x86_64 when bind.data not specified Fixed bug in dbListTables, handle empty DBs properly Fix segfault in RS_SQLite_resultSetInfo Best, + seth From p@myth @end|ng |rom y@hoo@com Thu Nov 16 22:29:56 2006 From: p@myth @end|ng |rom y@hoo@com (Phil Smyth) Date: Thu, 16 Nov 2006 13:29:56 -0800 (PST) Subject: [R-sig-DB] Executing Stored Procedures from Microsoft SQL Server Message-ID: <20061116212956.76808.qmail@web33912.mail.mud.yahoo.com> I am running R 2.3.0 on Windows 2000 connecting to MSSQL 8 via RODBC. When I try to execute a stored procedure via sqlQuery it returns no data. Regular queries work fine and the same query returns the desired results when pasted into the SQL query analyzer. Is it possible to execute a stored procedure? Thanks, Phil Smyth From @e@npor @end|ng |rom @cm@org Fri Nov 17 09:10:17 2006 From: @e@npor @end|ng |rom @cm@org (Sean O'Riordain) Date: Fri, 17 Nov 2006 08:10:17 +0000 Subject: [R-sig-DB] Executing Stored Procedures from Microsoft SQL Server In-Reply-To: <20061116212956.76808.qmail@web33912.mail.mud.yahoo.com> References: <20061116212956.76808.qmail@web33912.mail.mud.yahoo.com> Message-ID: <8ed68eed0611170010m36cb8e8dr59572704a20a67f6@mail.gmail.com> Hi Phil, The first thing I'd check is whether the stored-procedure is actually returning two things, rather than a single "set" - I've been caught by this before! Could you have a print statement hanging around from debugging? Are you using "return" or are you printing the results in the stored-procedure? I suggest that you create a small stored procedure which only contains a simple "select top 10 * from SimpleTable" to verify that it works. cheers, Sean O'Riordain On 16/11/06, Phil Smyth wrote: > I am running R 2.3.0 on Windows 2000 connecting to MSSQL 8 via RODBC. When I try to execute a stored procedure via sqlQuery it returns no data. Regular queries work fine and the same query returns the desired results when pasted into the SQL query analyzer. Is it possible to execute a stored procedure? > > Thanks, > > Phil Smyth > > _______________________________________________ > 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 @@m@y@@@r @end|ng |rom gm@||@com Tue Dec 5 09:51:29 2006 From: @@m@y@@@r @end|ng |rom gm@||@com (d. sarthi maheshwari) Date: Tue, 5 Dec 2006 14:21:29 +0530 Subject: [R-sig-DB] binding variable in sqlQuery Message-ID: Dear Sir/Madam, How can we bind a variable value with sqlQuery. For example: i am trying to run following command :: library(RODBC) inchannel <- odbcConnect("my_server", uid="samay") sqlQuery(inchannel, 'select item1, item2 from _my_table where item3 >= 10 and dbid = 10') however for introducing flexibility i want to write this command as my_value = 10 library(RODBC) inchannel <- odbcConnect("my_server", uid="samay") sqlQuery(inchannel, 'select item1, item2 from _my_table where item3 >= 10 and dbid = my_value') How can I bind the my_value with the query in sqlQuery? Please suggest and help me. I am in need. -- ...I am learning from my life... s..a..r [[alternative HTML version deleted]] From ggorj@@@34155694 m@iii@g oii biogii@es@com Tue Dec 5 11:36:43 2006 From: ggorj@@@34155694 m@iii@g oii biogii@es@com (ggorj@@@34155694 m@iii@g oii biogii@es@com) Date: 5 Dec 2006 10:36:43 -0000 Subject: [R-sig-DB] binding variable in sqlQuery Message-ID: <1165315003.2628635600.404.sendItem@bloglines.com> Try paste() i.e. my_value = 10 library(RODBC) inchannel <- odbcConnect("my_server", uid="samay") query <- paste("select item1, item2 from _my_table where item3 >= 10 and dbid = ", my_value) sqlQuery(inchannel, query) From @d@v|@2 @end|ng |rom m@||@n|h@gov Tue Dec 5 14:49:17 2006 From: @d@v|@2 @end|ng |rom m@||@n|h@gov (Sean Davis) Date: Tue, 5 Dec 2006 08:49:17 -0500 Subject: [R-sig-DB] binding variable in sqlQuery In-Reply-To: <1165315003.2628635600.404.sendItem@bloglines.com> References: <1165315003.2628635600.404.sendItem@bloglines.com> Message-ID: <200612050849.17898.sdavis2@mail.nih.gov> On Tuesday 05 December 2006 05:36, ggorjan.34155694 at bloglines.com wrote: > Try paste() i.e. > > my_value = 10 > library(RODBC) > inchannel <- odbcConnect("my_server", > uid="samay") > query <- paste("select item1, item2 from _my_table > > where item3 >= 10 > and dbid = ", my_value) > > sqlQuery(inchannel, > query) I've been using sprintf more recently; I find it makes the queries much easier to read when there are multiple values that need to be inserted. Sean From r|p|ey @end|ng |rom @t@t@@ox@@c@uk Wed Dec 6 12:04:24 2006 From: r|p|ey @end|ng |rom @t@t@@ox@@c@uk (Prof Brian Ripley) Date: Wed, 6 Dec 2006 11:04:24 +0000 (GMT) Subject: [R-sig-DB] binding variable in sqlQuery In-Reply-To: References: Message-ID: Simply in R: use paste() to prepare the query, On Tue, 5 Dec 2006, d. sarthi maheshwari wrote: > Dear Sir/Madam, > > How can we bind a variable value with sqlQuery. For example: > > i am trying to run following command :: > > library(RODBC) > inchannel <- odbcConnect("my_server", uid="samay") > sqlQuery(inchannel, 'select item1, item2 from _my_table > where item3 >= 10 > and dbid = 10') > > however for introducing flexibility i want to write this command as > > my_value = 10 > library(RODBC) > inchannel <- odbcConnect("my_server", uid="samay") > sqlQuery(inchannel, 'select item1, item2 from _my_table > where item3 >= 10 > and dbid = my_value') > > > How can I bind the my_value with the query in sqlQuery? > > Please suggest and help me. I am in need. > > -- 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 @|@|con @end|ng |rom |hcrc@org Fri Dec 15 07:45:39 2006 From: @|@|con @end|ng |rom |hcrc@org (Seth Falcon) Date: Thu, 14 Dec 2006 22:45:39 -0800 Subject: [R-sig-DB] RSQLite release candidate Message-ID: Hello all, I've made a number of improvements to RSQLite and would welcome any brave testers out there to give it a try before we push the update to CRAN. Candidate is available here (source and Windows binary): http://www.bioconductor.org/packages/misc/ Summary of changes: 1. Improved behavior when multiple connections are made to the same database file. At least one case that resulted in an unclosable DB connection is now fixed. I'm not sure we have this 100% yet, but I think we are on the right track. Feedback, especially with reproducible examples, is welcome. 2. New implementation for dbWriteTable. Instead of writing to temp files, use prepared queries. This resolves issues related to embedded separator characters and eol issues on Windows. There is also a nice performance benefit as the timings below demonstrate. For a data.frame containing 1e6 rows and 3 columns: current: > system.time(dbWriteTable(db, "t1", df, row.names=FALSE)) [1] 27.062 1.680 28.768 0.000 0.000 new: > system.time(dbWriteTable(db, "t1", df, row.names=FALSE)) [1] 3.524 0.200 3.721 0.000 0.000 + seth -- Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org