search for: dbgetquery

Displaying 20 results from an estimated 49 matches for "dbgetquery".

2009 Dec 18
1
The RSQLite version of dbGetQuery drops colums
Hi all, I just noticed (the hard way of course) that when a query returns 0 rows, the columns in the resulting data.frame get dropped as well. See the following example code (where conn is an active connection to an SQLite db): > dbGetQuery(conn, "select 1 as hey, 2 as ho where 1") hey ho 1 1 2 > dbGetQuery(conn, "select 1 as hey, 2 as ho where 0") data frame with 0 columns and 0 rows I believe that the second query should return a 0x2 data.frame instead, that is, the same value as: > dbGetQuery(co...
2010 Jul 09
6
Compress string memCompress/Decompress
Hello, I would like to compress a long string (character vector), store the compressed string in the text field of a SQLite database (using RSQLite), and then load the text back into memory and decompress it back into the the original string. My character vector can be compressed considerably using standard gzip/bzip2 compression. In theory it should be much faster for me to
2008 Jan 26
0
RSQLite 0.6-7 -- changes to dbGetQuery semantics
RSQLite 0.6-7 has been uploaded to CRAN and should hit a mirror near you in the next few days. This version changes the behavior of the dbGetQuery method to make it more consistent with dbSendQuery. Specifically: 1. dbGetQuery now closes a complete result set as dbSendQuery does. 2. If there is an incomplete result set open, dbGetQuery still opens a new temporary connection, but now issues a warning. It is best practice to explicitl...
2008 Jan 26
0
RSQLite 0.6-7 -- changes to dbGetQuery semantics
RSQLite 0.6-7 has been uploaded to CRAN and should hit a mirror near you in the next few days. This version changes the behavior of the dbGetQuery method to make it more consistent with dbSendQuery. Specifically: 1. dbGetQuery now closes a complete result set as dbSendQuery does. 2. If there is an incomplete result set open, dbGetQuery still opens a new temporary connection, but now issues a warning. It is best practice to explicitl...
2017 Oct 24
1
Creating a data table (frame?) from a SQL Statement?
...However, I'm now trying to load the same data via an SQL command, but I can't seem to get the data structure defined so R will like it -- using the included "iris" dataset. I've tried these ways of loading the SQL statement into a compatible R Structure: irisQuery <- data(dbGetQuery(conn, "select * from iris")) irisQuery <- data.frame(dbGetQuery(conn, "select * from iris")) irisQuery <- table(dbGetQuery(conn, "select * from iris")) . . . Followed by: boxM(irisQuery[,-5], irisQuery[,5]) Nothing works work. For example, if i use ... iri...
2017 Oct 24
0
Creating a data table (frame?) from a SQL Statement?
...e ignoring my advice... a good way to get ignored), which this still isn't. There is probably enough here for someone more familiar than I to help you with. I for one find it hard to understand why read.table is successfully figuring out how to make sense of the presumably-valid data frame that dbGetQuery is returning. That is, you really don't need to send everything through read.table in order to make use of it. Maybe try irisDF <- dbGetQuery(conn, "select * from iris") str( irisDF ) -- Sent from my phone. Please excuse my brevity. On October 24, 2017 10:11:28 AM PDT, Morkus...
2009 Jun 04
0
ROracle: cannot insert several columns
...;s a sample of my R (2.4.0) session. my.df<-data.frame(prd_id=c(123,456),vol_factor=c(.123,.456)) > my.df prd_id vol_factor 1 123 0.123 2 456 0.456 > library(ROracle) Loading required package: DBI > conn<-dbConnect("Oracle","***/***@***") > dbGetQuery(conn,"create table mytable (prd_id number, vol_factor number)") > dbGetQuery(conn,"insert into mytable (prd_id,vol_factor) values(123,.123)") > dbGetQuery(conn,"insert into mytable (prd_id,vol_factor) values(456,.456)") > dbGetQuery(conn,"select * from my...
2011 Feb 09
3
Insert variable in RMySQL-Statement
Dear list, I''m working with a MySQL-Table and would like to extract selected ids that are stored in a vector: Ids <- c(1,2,3) How do I insert this variable in a for loop like this: For (a in values) { Temp <- dbGetQuery(con, "select * from db Where id = <ID>";) } My problem is to split the select-statement and insert the current id. How can I handle this problem? Best regards, Arne
2012 Feb 02
14
sqldf for Very Large Tab Delimited Files
Hi All, I have a very (very) large tab-delimited text file without headers. There are only 8 columns and millions of rows. I want to make numerous pieces of this file by sub-setting it for individual stations. Station is given as in the first column. I am trying to learn and use sqldf package for this but am stuck in a couple of places. To simulate my requirement, I have taken iris dataset as an
2010 Nov 07
1
using variable in rmysql query
Dear All, I am using this query it returns id : id <- dbGetQuery(con1,"SELECT id FROM tenants WHERE name LIKE ''%consim%''") But In my case the string "consim" is there in another variable(it is coming from configuration file); > str <- "consim" I am trying to replace the string some this like, but it not wo...
2011 Nov 07
2
RpgSQL row names
...,2) > dimnames(bentest) = list(c(''ra'',''rb''),c(''ca'',''cb'')) > bentest ca cb ra 1 3 rb 2 4 > bentest = cbind(item_name=rownames(bentest),bentest) > dbWriteTable(con, "r.bentest", bentest) [1] TRUE > dbGetQuery(con, "SELECT * FROM r.bentest") item_name ca cb 1 ra 1 3 2 rb 2 4 Thanks, Ben [[alternative HTML version deleted]]
2007 Jun 06
2
Question: RMySQL bulk load/update one column, dbWriteTable()?
Hi, I have a question reading using RMySQL trying to load one R vector into a table column. To be more specifically, the table is there populated. Now I add a new column and want to populate this. Can some colleagues on this list teach me how to do this? I know how to write one R object/table into MYSQL table using dbWriteTable. But in this situation, I just want to do one column.
2008 Apr 11
2
SQL INSERT using RMySQL
...It appears that in this case, the row names on the data frame are still being used for the primary key, and since overwrite is FALSE, the new records are being silently dropped. I did manage to get things working by doing the following: ## get the last used id value (primary key) maxId <- dbGetQuery(con, "SELECT MAX(id) FROM past_purchases")[1,1] maxId if(is.na(maxId)) maxId <- -1 ## add the new unique primary keys as row names rownames(fulldata) <- maxId + 1:nrow(fulldata) ## now write out the data dbWriteTable(con, "past_purchases", value=fulldata, overwrite=FALSE...
2006 Nov 07
3
have I an actual matrix?
...epts to deal with data. I am trying to get data from a MySQL database and plotting it with barplot. Here the code... library(RMySQL) con <- dbConnect(dbDriver ("MySQL"),host=''localhost'',username=''root'',dbname=''ibdona'') rs <- dbGetQuery (con,"select n,year from ibdona.library_location") dbDisconnect(con) Graph <- barplot(rs) And here the error I get... Error in barplot.default(rs) : ''height'' must be a vector or a matrix paste(rs) gives me... [1] "c(307, 65, 2, 28, 3, 229, 81, 5, 7, 558, 134,...
2006 Mar 15
4
"\r" with RSQLite
...3 3 C 4 4 D 5 5 E 6 6 F 7 7 G 8 8 H 9 9 I 10 10 J > library(RSQLite) > drv <- dbDriver("SQLite") > con <- dbConnect(drv, dbname = "Test") > dbWriteTable(con, "DF", df, row.names = FALSE, overwrite = TRUE) [1] TRUE > df2 <- dbGetQuery(con, "SELECT DISTINCT * FROM DF") > dbDisconnect(con) [1] TRUE > df2 a b 1 1 A\r 2 2 B\r 3 3 C\r 4 4 D\r 5 5 E\r 6 6 F\r 7 7 G\r 8 8 H\r 9 9 I\r 10 10 J\r > sessionInfo() R version 2.2.1, 2005-12-20, i386-pc-mingw32 attached base packages: [1] "met...
2006 Mar 15
4
"\r" with RSQLite
...3 3 C 4 4 D 5 5 E 6 6 F 7 7 G 8 8 H 9 9 I 10 10 J > library(RSQLite) > drv <- dbDriver("SQLite") > con <- dbConnect(drv, dbname = "Test") > dbWriteTable(con, "DF", df, row.names = FALSE, overwrite = TRUE) [1] TRUE > df2 <- dbGetQuery(con, "SELECT DISTINCT * FROM DF") > dbDisconnect(con) [1] TRUE > df2 a b 1 1 A\r 2 2 B\r 3 3 C\r 4 4 D\r 5 5 E\r 6 6 F\r 7 7 G\r 8 8 H\r 9 9 I\r 10 10 J\r > sessionInfo() R version 2.2.1, 2005-12-20, i386-pc-mingw32 attached base packages: [1] "met...
2009 Mar 01
0
How to create temporary table in MySQL
...e an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''( x bigint )'' at line 2) [1] FALSE Warning message: In mysqlWriteTable(conn, name, value, ...) : could not create table: aborting mysqlWriteTable > dbGetQuery(con, "create temporary table x ( x int);") # workaround NULL > dbWriteTable(con, "x", x, verbose=TRUE,row.names=FALSE, overwrite=T) [1] TRUE > dbGetQuery(con, "select * from x;") x 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 > dbGetQuery(co...
2010 Jun 23
4
RJDBC vs RMySQL vs ???
I am running a simple SQL SELECT statement that involvs 50k + data points using R and the RJDBC interface. I am facing very slow response times in both the RGUI and the R console. When running this SQL statement directly in a SQL client I have processing times that are a lot lot faster (which means that the SQL statement itself is not the problem). Did any of you compare RJDBC vs RMySQL or is
2007 Sep 27
1
RMySQL NA/NULL value storage error
...)); x[c(1,4),c(2,4)]=NA; x #generate a table with some missing values X1 X2 X3 X4 1 -6 NA 6 NA 2 2 -8 -3 -22 3 -8 5 15 11 4 16 NA 4 NA > If I write that to the database and read it back: > dbWriteTable(test,"x",x,overwrite=T,row.names=F) [1] TRUE > dbGetQuery(test,"SELECT * FROM x") X1 X2 X3 X4 1 -6 NA 6 0 2 2 -8 -3 -22 3 -8 5 15 11 4 16 NA 4 0 The NAs in column 2 are successfully transferred, but the NAs in column 4 are changed to zeros. If I add another column and repeat: > set.seed(1); x=data.frame(matrix(round(r...
2005 May 02
2
RMySQL query: why result takes so much memory in R ?
...and how 12e6 * 3 can fill such a huge range of RAM? Thanks for clarification and potential solutions. ## my code library(RMySQL) drv <- dbDriver("MySQL") ch <- dbConnect(drv,dbname="testdb", user="root",password="mysql") testdb <- dbGetQuery(ch, "select id, group, measurement from mydata") dbDisconnect(ch) dbUnloadDriver(drv) ## end of my code Cheers Christoph