search for: dbgetquery

...March 24) some of my packages are generating a Note Note: Method with signature "MySQLConnection#integer" chosen for function "coerce", target signature "TSMySQLConnection#integer". "dbObjectId#integer" would also be valid This is coming from a call to dbGetQuery() in package DBI. The method with the signature "TSMySQLConnection#integer" is generated automatically because TSMySQLConnection inherits from MySQLConnection. (More details below.) Is there a way to pass this information along to coerce when the method is generated, or otherwise supp...

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...

...sion: 2.4.0 OS: Win 2000 Submission from: (NULL) (193.134.202.252) Database queries using the combination DBI + ROracle are handicapped by quirks in the pipeline between the textual representation of the query and the database engine Oracle. Comments and linefeed at start are interfering: dbGetQuery(conn, query): works: dbGetQuery(conn, "select * from dual") dbGetQuery(conn, "select * from dual /* comment */") dbGetQuery(conn, "select /* comment */ * from dual") doesn't: dbGetQuery(conn, "\nselect * from dual") dbGetQuery(conn, "selec...

Dear list, I want to insert a recorde into a SQLite table by dbGetQuery(), but there is a problem when the value contains quotation mark. > dd<-data.frame(txt=c("having both ' and \" in character.","OK")) > library(RSQLite) Loading required package: DBI > con<-dbConnect(dbDriver("SQLite"),":memory:") >...

...iver", ".m2/repository/postgresql/postgresql/9.0-801.jdbc4/postgresql-9.0-801.jdbc4.jar") con <- dbConnect(postgres, "jdbc:postgresql://hq.d8u.us/erm", user = "hdiwan") sql <- "select id, startdate, value from dists where profileid = ?" data1 <- dbGetQuery(conn, sql, param) # Dies here, no matter what the query, tried with "select 1+1" as well as the one above data2 <- dbGetQuery(conn, sql, param2) cor(data1$V3, data2$V3) [[alternative HTML version deleted]]

Hi there, When I extract data from SQLite to R, the data types (or modes) of the extracted data seems to be determined by the value of the first row. Please see the following example. When I put the missing values first, the column extracted is of the mode character. > str(dbGetQuery(sql.industry, + "select pya_var from annual_data3 + order by pya_var")) 'data.frame': 155465 obs. of 1 variable: $ pya_var: chr NA NA NA NA ... When I put the non-missing values first, the column extracted is of the mode numeric. > str(dbGet...

...l > > On Fri, Oct 2, 2020 at 3:49 PM Philip <herd_dog at cox.net> wrote: > >> I?m trying to pull data from one table (batting) in the Lahman Baseball >> database. Notice X2B for doubles and X3B for triples ? fourth and fifth >> from the right. >> >> The dbGetQuery function runs fine when I leave there two out but I get >> error messages (in red) when I include 2B/3B or X2B/X3B. >> >> Can anyone give me some direction? >> >> Thanks, >> Philip Heinrich >> >> *******************************************************...

...that start with a number. If you try to access an R-dataframe column named 2B or 3B with the familiar "$" notation, you'll get an error: > library(DBI) > library(RSQLite) > con2 <- dbConnect(SQLite(), "~/R_Dir/lahmansbaseballdb.sqlite") > Hack12Batting <- dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") > Hack12Batting$AB [1] 664 661 639 632 632 632 626 623 620 618 617 613 606 605 602 > Hack12Batting$3B Error: unexpected numeric constant in "Hack12Batting$3" How to handle? You can rename c...

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...

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...

...you try to access an R-dataframe column named > 2B or 3B with the familiar "$" notation, you'll get an error: > > > library(DBI) > > library(RSQLite) > > con2 <- dbConnect(SQLite(), "~/R_Dir/lahmansbaseballdb.sqlite") > > Hack12Batting <- dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = > 2018 AND AB >600 ORDER BY AB DESC") > > Hack12Batting$AB > [1] 664 661 639 632 632 632 626 623 620 618 617 613 606 605 602 > > Hack12Batting$3B > Error: unexpected numeric constant in "Hack12Batting$3" > &g...

...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...

...me other values but the other values are associated with the ID variable (think of IDrec and IDdes being character strings and ID being numeric) (Imagine the transposed) Table1: ID: 1,2,3,4,... IDrec: A,B,C,D... IDdes: B,C,A,E... So I've created a table with the final form I need it to be in dbGetQuery(db, "CREATE TABLE Map (ID int, IDrec int, IDrec1 int, IDdes int, IDdes1 int)") And the finished table would look something like: Map: ID: 1, 2, 3, 4,... IDrec: 1, 2, 3, 4,... IDrec1: A, B, C, D,... IDdes: 2, 3, 1, 5,.... IDdes1: B, C, A, E,... So I copy i...

...'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...

...quot;), paste(db_server_lgn, sep = ""), paste(db_server_pwd, sep = "")) start_dt <- as.Date('2010-11-01',format="%Y-%m-%d") end_dt <- as.Date('2011-01-31',format="%Y-%m-%d") library(sqldf) target_population <- dbGetQuery(conn, "select distinct a.primary_customer_code as cust_id, a.primary_product_code, a.account_opening_date, b.l4_product_hierarchy_code, b.l5_product_hierarchy_code from account_dim a, product_dim b where a.primary_product_code=b.l5_product_hierarchy_code and a.account_opening_date between st...

Hello, I'm accessing a MySQL database from inside R and had a problem with a function. In the database, there is data from years 1985 to 2007 that I would like to retrieve. I can easily get the data from one year by the following: info1985=dbGetQuery(con, statement='the SQL query') Inside the statement, I have a column that is set to the desired year (e.g. table.column=1985) through a WHERE clause, but when I write a function like the following: getinfo=function(t){dbGetQuery(con,statement='the SQL query')} and instead of using...

...mentación solicitada improcedente para la liquidación de la factura." 11;"Débito médico. Prestación facturada con documentación respaldatoria adjunta a la factura, historia clínica a su disposición en el hospital." Cuando la traigo a R con library("RPostgreSQL"), resulta: dbGetQuery(con, "SELECT * FROM motivodebito") 1 Débito presentado extemporáneamente. 2 Corresponde la aplicación del nomenclador GCBA vigente al momento de la prestación. 3 No registra convenio de prestaciones vigente al momento de la prestación. 4 Débito presentado sin documentación ni f...

...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...

...here, package) 2: getGeneric("coerce", where = where) 3: as(obj, "integer") 4: mysqlConnectionInfo(dbObj, ...) 5: dbGetInfo(conn, "rsId") 6: dbGetInfo(conn, "rsId") 7: dbListResults(con) 8: dbListResults(con) 9: mysqlQuickSQL(conn, statement, ...) 10: dbGetQuery(con, sql) 11: dbGetQuery(con, sql) 12: doTryCatch(return(expr), name, parentenv, handler) 13: tryCatchOne(expr, names, parentenv, handlers[[1L]]) 14: tryCatchList(expr, classes, parentenv, handlers) 15: tryCatch(expr, error = function(e) { call <- conditionCall(e) if (!is.null(call)) {...

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 compress/decompress