From th|erry@onke||nx @end|ng |rom |nbo@be Sun Oct 25 11:56:47 2015 From: th|erry@onke||nx @end|ng |rom |nbo@be (Thierry Onkelinx) Date: Sun, 25 Oct 2015 11:56:47 +0100 Subject: [R-sig-DB] SQLite problem with random() Message-ID: Dear all, It seems that the sorting goes wrong on columns filled with the result of the random() function in SQLite. See the example below, run on R version 3.2.2 (2015-08-14), Platform: i386-w64-mingw32/i386 (32-bit). Replacing random() with random() % .Machine$max.integer solves the problem. So I expect that the problem is that the integers generated by random() are larger than .Machine$max.integer. But I don't see why that would affect the order. Isn't the order determined in SQLite? Best regards, library(RSQLite) filename <- tempfile(fileext = ".db") conn <- dbConnect(SQLite(), filename) sql <- c("CREATE TABLE Base (BaseValue);", "INSERT INTO Base (BaseValue) VALUES (1);", "INSERT INTO Base (BaseValue) VALUES (2);", "INSERT INTO Base (BaseValue) VALUES (3);", "INSERT INTO Base (BaseValue) VALUES (4);") junk <- lapply(sql, dbGetQuery, conn = conn) dbReadTable(conn, "Base") sql <- "CREATE TABLE GroupRanking AS SELECT x1.BaseValue AS Level, x2.BaseValue AS Parent, x3.BaseValue AS Child, random() AS Ranking FROM Base AS x1 CROSS JOIN Base AS x2 CROSS JOIN Base AS x3; " dbGetQuery(conn, sql) sql <- " SELECT x.Level, x.Parent, x.Ranking, y.minr, y.maxr FROM GroupRanking AS x INNER JOIN ( SELECT Level, Parent, min(Ranking) AS minr, max(Ranking) AS maxr FROM GroupRanking GROUP BY Level, Parent ) AS y ON x.Level = y.Level AND x.Parent = y.Parent ORDER BY x.Level, x.Parent, x.Ranking; " dbGetQuery(conn, sql) sql <- paste("CREATE TABLE GroupRanking AS SELECT x1.BaseValue AS Level, x2.BaseValue AS Parent, x3.BaseValue AS Child, random() %", .Machine$integer.max, " AS Ranking FROM Base AS x1 CROSS JOIN Base AS x2 CROSS JOIN Base AS x3; ") dbGetQuery(conn, sql) ir. Thierry Onkelinx Instituut voor natuur- en bosonderzoek / Research Institute for Nature and Forest team Biometrie & Kwaliteitszorg / team Biometrics & Quality Assurance Kliniekstraat 25 1070 Anderlecht Belgium To call in the statistician after the experiment is done may be no more than asking him to perform a post-mortem examination: he may be able to say what the experiment died of. ~ Sir Ronald Aylmer Fisher The plural of anecdote is not data. ~ Roger Brinner The combination of some data and an aching desire for an answer does not ensure that a reasonable answer can be extracted from a given body of data. ~ John Tukey [[alternative HTML version deleted]] From @t@jt| @end|ng |rom gm@||@com Tue Nov 17 14:40:07 2015 From: @t@jt| @end|ng |rom gm@||@com (=?UTF-8?Q?Andr=C3=A1s_Tajti?=) Date: Tue, 17 Nov 2015 14:40:07 +0100 Subject: [R-sig-DB] sourcing dbGetQuery - external pointer invalid Message-ID: Dear all, I have a script, which connects to a MySQL database with RMySQL, then makes several queries through the dbGetQuery function. If I copy-paste the script to the R console, it works. However, if I source() the script, it will throw an error when the first query sent: "Error in eval(expr, envir, enclos) : external pointer is not valid" I use R 3.2.2 on Ubuntu 14.04 (platform: x86_64-pc-linux-gnu (64-bit)), and connect to the db through SSH. Any help appreciated. Andras Tajti [[alternative HTML version deleted]] From @coyoc @end|ng |rom gm@||@com Wed Dec 9 23:16:49 2015 From: @coyoc @end|ng |rom gm@||@com (Matthew Van Scoyoc) Date: Wed, 9 Dec 2015 15:16:49 -0700 Subject: [R-sig-DB] Error appending data using RODBC, sqlSave() Message-ID: I need to update tables in an MS Access database, but sqlSave(..., append = TRUE) keeps returning errors. I've spent the better part of the last hour on Google, but haven't found an adequate answer. Here's my code. #+ Munge ---- > # Call in resampled plots from CRC_Data.DB > resample <- sqlFetch(CRC_Data.DB, "tblResampledPlots") > glimpse(resample) Observations: 58 Variables: 7 $ PlotID (fctr) C057, C085, C091, C128, C133, C233, C284, C311, C312, C401, C418, ... $ Re-sampleDate (time) 2014-07-24, 2014-08-29, 2014-09-27, 2014-10-01, 2014-08-30, 2014-0... $ Notes (str) NA, NA, Something important about the plot visit... $ SppRichStartTime (time) 13:09:00, 11:23:00, 13:54:00... $ SppRichEndTime (time) 13:21:00, 11:38:00, 14:15:00... $ SppRichNotes (fctr) NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA... $ SppRichTotalTime (time) 00:12:00, 00:15:00, 00:21:00... > > # Call in resampled plots from Summaries.DB > plots.sum = sqlFetch(Summaries.DB, "tblPlotsResampled") > glimpse(plots.sum) Observations: 52 Variables: 3 $ PlotID (fctr) C021, C057, C085, C091, C128, C133, C233, C284, C311, C312, C401, C41... $ Re-sampleDate (time) 2014-08-29, 2014-07-24, 2014-08-29, 2014-09-27, 2014-10-01... $ Notes (fctr) NA, NA, NA, Something important about the plot visit... > > # Select the new data > plots.write = resample %>% + filter(!(PlotID %in% plots.sum$PlotID)) %>% + select(PlotID:Notes) > glimpse(plots.write) Observations: 7 Variables: 3 $ PlotID (fctr) D098, D104, D105, D605, D607, D608, D649 $ Re-sampleDate (time) 2015-10-14, 2015-09-29, 2015-09-30, 2015-10-16, 2015-10-15... $ Notes (fctr) NA, NA, NA, NA, NA, NA, NA > > #+ AppendData ---- > sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append = TRUE) Error in sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append = TRUE) : unable to append to table ?tblPlotsResampled? Thanks, MVS ===== Matthew Van Scoyoc ===== Think SNOW! [[alternative HTML version deleted]] From th|erry@onke||nx @end|ng |rom |nbo@be Fri Dec 11 13:14:21 2015 From: th|erry@onke||nx @end|ng |rom |nbo@be (Thierry Onkelinx) Date: Fri, 11 Dec 2015 13:14:21 +0100 Subject: [R-sig-DB] Error appending data using RODBC, sqlSave() In-Reply-To: References: Message-ID: Dear Matthew, Have you tried converting the factors into characters? Best regards, ir. Thierry Onkelinx Instituut voor natuur- en bosonderzoek / Research Institute for Nature and Forest team Biometrie & Kwaliteitszorg / team Biometrics & Quality Assurance Kliniekstraat 25 1070 Anderlecht Belgium To call in the statistician after the experiment is done may be no more than asking him to perform a post-mortem examination: he may be able to say what the experiment died of. ~ Sir Ronald Aylmer Fisher The plural of anecdote is not data. ~ Roger Brinner The combination of some data and an aching desire for an answer does not ensure that a reasonable answer can be extracted from a given body of data. ~ John Tukey 2015-12-09 23:16 GMT+01:00 Matthew Van Scoyoc : > I need to update tables in an MS Access database, but sqlSave(..., append = > TRUE) keeps returning errors. I've spent the better part of the last hour > on Google, but haven't found an adequate answer. Here's my code. > > #+ Munge ---- > > # Call in resampled plots from CRC_Data.DB > > resample <- sqlFetch(CRC_Data.DB, "tblResampledPlots") > > glimpse(resample) > Observations: 58 > Variables: 7 > $ PlotID (fctr) C057, C085, C091, C128, C133, C233, C284, C311, > C312, C401, C418, ... > $ Re-sampleDate (time) 2014-07-24, 2014-08-29, 2014-09-27, 2014-10-01, > 2014-08-30, 2014-0... > $ Notes (str) NA, NA, Something important about the plot > visit... > $ SppRichStartTime (time) 13:09:00, 11:23:00, 13:54:00... > $ SppRichEndTime (time) 13:21:00, 11:38:00, 14:15:00... > $ SppRichNotes (fctr) NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, > NA, NA, NA, NA, NA... > $ SppRichTotalTime (time) 00:12:00, 00:15:00, 00:21:00... > > > > # Call in resampled plots from Summaries.DB > > plots.sum = sqlFetch(Summaries.DB, "tblPlotsResampled") > > glimpse(plots.sum) > Observations: 52 > Variables: 3 > $ PlotID (fctr) C021, C057, C085, C091, C128, C133, C233, C284, > C311, C312, C401, C41... > $ Re-sampleDate (time) 2014-08-29, 2014-07-24, 2014-08-29, 2014-09-27, > 2014-10-01... > $ Notes (fctr) NA, NA, NA, Something important about the plot > visit... > > > > # Select the new data > > plots.write = resample %>% > + filter(!(PlotID %in% plots.sum$PlotID)) %>% > + select(PlotID:Notes) > > glimpse(plots.write) > Observations: 7 > Variables: 3 > $ PlotID (fctr) D098, D104, D105, D605, D607, D608, D649 > $ Re-sampleDate (time) 2015-10-14, 2015-09-29, 2015-09-30, 2015-10-16, > 2015-10-15... > $ Notes (fctr) NA, NA, NA, NA, NA, NA, NA > > > > #+ AppendData ---- > > sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append = TRUE) > Error in sqlSave(Summaries.DB, plots.write, "tblPlotsResampled", append = > TRUE) : > unable to append to table ?tblPlotsResampled? > > Thanks, > > MVS > ===== > Matthew Van Scoyoc > ===== > Think SNOW! > > [[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 [[alternative HTML version deleted]] From k|r|||@mue||er @end|ng |rom |vt@b@ug@ethz@ch Thu Dec 31 02:59:53 2015 From: k|r|||@mue||er @end|ng |rom |vt@b@ug@ethz@ch (=?UTF-8?Q?Kirill_M=c3=bcller?=) Date: Thu, 31 Dec 2015 02:59:53 +0100 Subject: [R-sig-DB] Improving DBI Message-ID: <56848C19.2070809@ivt.baug.ethz.ch> Hi I have prepared a proposal for improving DBI, and three backends to open-source databases: http://bit.ly/1QZNNrC (current version), http://bit.ly/1Uhn1ZC (version at the time of writing). Among other things, I plan to improve support for data types, parametrized queries, and database schemas. Ultimately, DBI will be formally specified by a test suite and a written description. Before submitting it to the R Consortium, I'd be glad to receive further input. Are there other design issues that need to be addressed? Other points I have missed? Issues you might want to see resolved as part of this project? See also the GitHub issue trackers for DBI [1] (which also contains the design discussion [2]), RMySQL [3], RPostgres [4] and RSQLite [5]. Please note that the deadline for submitting the proposal is already January 10. Thank you for your attention. Best regards Kirill [1] https://github.com/rstats-db/DBI/issues [2] https://github.com/rstats-db/DBI/issues?q=is%3Aopen+is%3Aissue+label%3Aaction%3Adesign [3] https://github.com/rstats-db/RMySQL/issues [4] https://github.com/rstats-db/RPostgres/issues [5] https://github.com/rstats-db/RSQLite/issues