Problem with SQLDF - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such table:

An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120510/bd3c2e44/attachment.pl>
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
An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120510/9ab94085/attachment.pl>
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

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.
Ohh ... Thanks Gabor. I have a few related queries then, kindly have a look:

1. Does it only hinder the sqldf package or are there other issues with
using POSIXlt in a dataframe? Am asking because I have a few dataframes with
columns of class(POSIXlt ).

2. I have columns containing 'date+timestamp', something like '2011-01-03
09:07:07' which are of class POSIXlt. I need to perform some arithmetic
operations on these columns. Which class would be most appropriate for such
kind of data?

Its not just sqldf.  You will have other problems too if you put
POSIXlt objects in data frames too.

See R News 4/1.
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120510/98e511ff/attachment.pl>