[R] Problem with SQLDF - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table:
jim holtman
jholtman at gmail.com
Thu May 10 01:25:40 CEST 2012
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 works
NAME 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.
More information about the R-help
mailing list