Shivam
2012-May-09 21:58 UTC
[R] Problem with SQLDF - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table:
Hi All, I am having trouble executing SQL statements on a few dataframes, but the funny thing is that I am able to execute the statement on some other dataframes. To test, I have 2 very small dataframes (6 rows and some columns). One is 'lessliq', the dput is given below.> dput(head(lessliq))structure(list(V1 = c(50464677L, 50464846L, 50432581L, 50426614L, 50504329L, 50504735L), V2 = c("TATASTEEL", "TATASTEEL", "TATASTEEL", "TATASTEEL", "TATASTEEL", "TATASTEEL"), V3 = c("OPTSTK", "OPTSTK", "OPTSTK", "OPTSTK", "OPTSTK", "OPTSTK"), V4 = structure(c(15029, 15029, 15029, 15029, 15029, 15029), class = "Date"), V5 = c("CE", "CE", "CE", "CE", "CE", "CE"), V6 = c(0L, 0L, 0L, 0L, 0L, 0L), V7 = c(700, 700, 700, 700, 700, 700), V8 = c("14:15:45", "14:15:51", "13:51:12", "13:45:13", "14:39:53", "14:40:08" ), V9 = c(37, 37, 37.75, 37, 37.5, 37.5), V10 = c(500L, 500L, 500L, 500L, 2000L, 500L), V11 = structure(c(14977, 14977, 14977, 14977, 14977, 14977), class = "Date"), V12 = c(52, 52, 52, 52, 52, 52)), .Names = c("V1", "V2", "V3", "V4", "V5", "V6", "V7", "V8", "V9", "V10", "V11", "V12"), row.names = c(NA, 6L), class = "data.frame") I run the below command:> new2 = sqldf("select * from lessliq")This works fine. But on many other dataframes it is not working. I have a dataframe 'testeq'. dput given below:> dput(head(testeq))structure(list(NAME = c("DLF", "DLF", "DLF", "DLF", "DLF", "DLF" ), TMSTMP = c("09:07:07", "09:15:03", "09:15:03", "09:15:03", "09:15:03", "09:15:04"), PRICE = c(295, 294.5, 293.9, 294.9, 295, 294.5), DATE = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label c("2011-01-03", "2011-01-04", "2011-01-05", "2011-01-06", "2011-01-07", "2011-01-10", "2011-01-11", "2011-01-12", "2011-01-13", "2011-01-14", "2011-01-17", "2011-01-18", "2011-01-19", "2011-01-20", "2011-01-21", "2011-01-24", "2011-01-25", "2011-01-27", "2011-01-28", "2011-01-31", "2011-02-01", "2011-02-02", "2011-02-03", "2011-02-04", "2011-02-07", "2011-02-08", "2011-02-09", "2011-02-10", "2011-02-11", "2011-02-14", "2011-02-15", "2011-02-16", "2011-02-17", "2011-02-18", "2011-02-21", "2011-02-22", "2011-02-23", "2011-02-24", "2011-02-25", "2011-02-28", "2011-03-01", "2011-03-03", "2011-03-04", "2011-03-07", "2011-03-08", "2011-03-09", "2011-03-10", "2011-03-11", "2011-03-14", "2011-03-15", "2011-03-16", "2011-03-17", "2011-03-18", "2011-03-21", "2011-03-22", "2011-03-23", "2011-03-24", "2011-03-25", "2011-03-28", "2011-03-29", "2011-03-30", "2011-03-31", "2011-04-01", "2011-04-04", "2011-04-05", "2011-04-06", "2011-04-07", "2011-04-08", "2011-04-11", "2011-04-13", "2011-04-15", "2011-04-18", "2011-04-19", "2011-04-20", "2011-04-21", "2011-04-25", "2011-04-26", "2011-04-27", "2011-04-28", "2011-04-29", "2011-05-02", "2011-05-03", "2011-05-04", "2011-05-05", "2011-05-06", "2011-05-09", "2011-05-10", "2011-05-11", "2011-05-12", "2011-05-13", "2011-05-16", "2011-05-17", "2011-05-18", "2011-05-19", "2011-05-20", "2011-05-23", "2011-05-24", "2011-05-25", "2011-05-26", "2011-05-27", "2011-05-30", "2011-05-31", "2011-06-01", "2011-06-02", "2011-06-03", "2011-06-06", "2011-06-07", "2011-06-08", "2011-06-09", "2011-06-10", "2011-06-13", "2011-06-14", "2011-06-15", "2011-06-16", "2011-06-17", "2011-06-20", "2011-06-21", "2011-06-22", "2011-06-23", "2011-06-24", "2011-06-27", "2011-06-28", "2011-06-29", "2011-06-30"), class = "factor"), DTTM = structure(list(sec = c(7, 3, 3, 3, 3, 4), min = c(7L, 15L, 15L, 15L, 15L, 15L), hour = c(9L, 9L, 9L, 9L, 9L, 9L ), mday = c(3L, 3L, 3L, 3L, 3L, 3L), mon = c(0L, 0L, 0L, 0L, 0L, 0L), year = c(111L, 111L, 111L, 111L, 111L, 111L), wday = c(1L, 1L, 1L, 1L, 1L, 1L), yday = c(2L, 2L, 2L, 2L, 2L, 2L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("sec", "min", "hour", "mday", "mon", "year", "wday", "yday", "isdst" ), class = c("POSIXlt", "POSIXt"))), .Names = c("NAME", "TMSTMP", "PRICE", "DATE", "DTTM"), row.names = c(NA, 6L), class = "data.frame") I run the above command again but this time I get an error:> new3 = sqldf("select * from testeq")Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table: testeq) In addition: Warning message: In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double' Can anyone guide me if there is any difference in the structure of the two dataframes or what else can be the issue? Thanks in advance, Regards, Shivam Singh [[alternative HTML version deleted]]
Gabor Grothendieck
2012-May-09 23:15 UTC
[R] Problem with SQLDF - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table:
On Wed, May 9, 2012 at 5:58 PM, Shivam <shivamsingh at gmail.com> wrote:> Hi All, > > I am having trouble executing SQL statements on a few dataframes, but the > funny thing is that I am able to execute the statement on some other > dataframes. >Never put POSIXlt objects into data frames. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com
jim holtman
2012-May-09 23:25 UTC
[R] Problem with SQLDF - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table:
I think your problem is caused by DTTM in testeq being POSIXlt. This is a strange class to have in a dataframe. Try is as POSIXct. Example:> str(testeq)'data.frame': 6 obs. of 5 variables: $ NAME : chr "DLF" "DLF" "DLF" "DLF" ... $ TMSTMP: chr "09:07:07" "09:15:03" "09:15:03" "09:15:03" ... $ PRICE : num 295 294 294 295 295 ... $ DATE : Factor w/ 124 levels "2011-01-03","2011-01-04",..: 1 1 1 1 1 1 $ DTTM : POSIXlt, format: "2011-01-03 09:07:07" "2011-01-03 09:15:03" "2011-01-03 09:15:03" ...> sqldf("select * from testeq")Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table: testeq) In addition: Warning message: In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double' Enter a frame number, or 0 to exit 1: sqldf("select * from testeq") 2: dbGetQuery(connection, xi) 3: dbGetQuery(connection, xi) 4: sqliteQuickSQL(conn, statement, ...) 5: sqliteExecStatement(con, statement, bind.data) Selection: sqldf("select * from testeq")>> testeq$DTTM <- as.POSIXct(testeq$DTTM) > sqldf("select * from testeq") # this worksNAME TMSTMP PRICE DATE DTTM 1 DLF 09:07:07 295.0 2011-01-03 2011-01-03 14:07:07 2 DLF 09:15:03 294.5 2011-01-03 2011-01-03 14:15:03 3 DLF 09:15:03 293.9 2011-01-03 2011-01-03 14:15:03 4 DLF 09:15:03 294.9 2011-01-03 2011-01-03 14:15:03 5 DLF 09:15:03 295.0 2011-01-03 2011-01-03 14:15:03 6 DLF 09:15:04 294.5 2011-01-03 2011-01-03 14:15:04>On Wed, May 9, 2012 at 5:58 PM, Shivam <shivamsingh at gmail.com> wrote:> Hi All, > > I am having trouble executing SQL statements on a few dataframes, but the > funny thing is that I am able to execute the statement on some other > dataframes. > > To test, I have 2 very small dataframes (6 rows and some columns). One is > 'lessliq', the dput is given below. > >> dput(head(lessliq)) > structure(list(V1 = c(50464677L, 50464846L, 50432581L, 50426614L, > 50504329L, 50504735L), V2 = c("TATASTEEL", "TATASTEEL", "TATASTEEL", > "TATASTEEL", "TATASTEEL", "TATASTEEL"), V3 = c("OPTSTK", "OPTSTK", > "OPTSTK", "OPTSTK", "OPTSTK", "OPTSTK"), V4 = structure(c(15029, > 15029, 15029, 15029, 15029, 15029), class = "Date"), V5 = c("CE", > "CE", "CE", "CE", "CE", "CE"), V6 = c(0L, 0L, 0L, 0L, 0L, 0L), > ? ?V7 = c(700, 700, 700, 700, 700, 700), V8 = c("14:15:45", > ? ?"14:15:51", "13:51:12", "13:45:13", "14:39:53", "14:40:08" > ? ?), V9 = c(37, 37, 37.75, 37, 37.5, 37.5), V10 = c(500L, 500L, > ? ?500L, 500L, 2000L, 500L), V11 = structure(c(14977, 14977, > ? ?14977, 14977, 14977, 14977), class = "Date"), V12 = c(52, > ? ?52, 52, 52, 52, 52)), .Names = c("V1", "V2", "V3", "V4", > "V5", "V6", "V7", "V8", "V9", "V10", "V11", "V12"), row.names = c(NA, > 6L), class = "data.frame") > > I run the below command: > >> new2 = sqldf("select * from lessliq") > This works fine. > > But on many other dataframes it is not working. I have a dataframe > 'testeq'. dput given below: > >> dput(head(testeq)) > structure(list(NAME = c("DLF", "DLF", "DLF", "DLF", "DLF", "DLF" > ), TMSTMP = c("09:07:07", "09:15:03", "09:15:03", "09:15:03", > "09:15:03", "09:15:04"), PRICE = c(295, 294.5, 293.9, 294.9, > 295, 294.5), DATE = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label > c("2011-01-03", > "2011-01-04", "2011-01-05", "2011-01-06", "2011-01-07", "2011-01-10", > "2011-01-11", "2011-01-12", "2011-01-13", "2011-01-14", "2011-01-17", > "2011-01-18", "2011-01-19", "2011-01-20", "2011-01-21", "2011-01-24", > "2011-01-25", "2011-01-27", "2011-01-28", "2011-01-31", "2011-02-01", > "2011-02-02", "2011-02-03", "2011-02-04", "2011-02-07", "2011-02-08", > "2011-02-09", "2011-02-10", "2011-02-11", "2011-02-14", "2011-02-15", > "2011-02-16", "2011-02-17", "2011-02-18", "2011-02-21", "2011-02-22", > "2011-02-23", "2011-02-24", "2011-02-25", "2011-02-28", "2011-03-01", > "2011-03-03", "2011-03-04", "2011-03-07", "2011-03-08", "2011-03-09", > "2011-03-10", "2011-03-11", "2011-03-14", "2011-03-15", "2011-03-16", > "2011-03-17", "2011-03-18", "2011-03-21", "2011-03-22", "2011-03-23", > "2011-03-24", "2011-03-25", "2011-03-28", "2011-03-29", "2011-03-30", > "2011-03-31", "2011-04-01", "2011-04-04", "2011-04-05", "2011-04-06", > "2011-04-07", "2011-04-08", "2011-04-11", "2011-04-13", "2011-04-15", > "2011-04-18", "2011-04-19", "2011-04-20", "2011-04-21", "2011-04-25", > "2011-04-26", "2011-04-27", "2011-04-28", "2011-04-29", "2011-05-02", > "2011-05-03", "2011-05-04", "2011-05-05", "2011-05-06", "2011-05-09", > "2011-05-10", "2011-05-11", "2011-05-12", "2011-05-13", "2011-05-16", > "2011-05-17", "2011-05-18", "2011-05-19", "2011-05-20", "2011-05-23", > "2011-05-24", "2011-05-25", "2011-05-26", "2011-05-27", "2011-05-30", > "2011-05-31", "2011-06-01", "2011-06-02", "2011-06-03", "2011-06-06", > "2011-06-07", "2011-06-08", "2011-06-09", "2011-06-10", "2011-06-13", > "2011-06-14", "2011-06-15", "2011-06-16", "2011-06-17", "2011-06-20", > "2011-06-21", "2011-06-22", "2011-06-23", "2011-06-24", "2011-06-27", > "2011-06-28", "2011-06-29", "2011-06-30"), class = "factor"), > ? ?DTTM = structure(list(sec = c(7, 3, 3, 3, 3, 4), min = c(7L, > ? ?15L, 15L, 15L, 15L, 15L), hour = c(9L, 9L, 9L, 9L, 9L, 9L > ? ?), mday = c(3L, 3L, 3L, 3L, 3L, 3L), mon = c(0L, 0L, 0L, > ? ?0L, 0L, 0L), year = c(111L, 111L, 111L, 111L, 111L, 111L), > ? ? ? ?wday = c(1L, 1L, 1L, 1L, 1L, 1L), yday = c(2L, 2L, 2L, > ? ? ? ?2L, 2L, 2L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("sec", > ? ?"min", "hour", "mday", "mon", "year", "wday", "yday", "isdst" > ? ?), class = c("POSIXlt", "POSIXt"))), .Names = c("NAME", "TMSTMP", > "PRICE", "DATE", "DTTM"), row.names = c(NA, 6L), class = "data.frame") > > I run the above command again but this time I get an error: > >> new3 = sqldf("select * from testeq") > Error in sqliteExecStatement(con, statement, bind.data) : > ?RS-DBI driver: (error in statement: no such table: testeq) > In addition: Warning message: > In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double' > > Can anyone guide me if there is any difference in the structure of the two > dataframes or what else can be the issue? > > Thanks in advance, > > Regards, > Shivam Singh > > ? ? ? ?[[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.-- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.