Grokbase Groups R r-help May 2012
FAQ

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

Shivam
May 9, 2012 at 9:58 pm
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
reply

Search Discussions

5 responses

  • Gabor Grothendieck at May 9, 2012 at 11:15 pm

    On Wed, May 9, 2012 at 5:58 PM, Shivam 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
  • Shivam at May 9, 2012 at 11:24 pm
    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?

    Thanks and Regards,
    Shivam
    On Thu, May 10, 2012 at 4:45 AM, Gabor Grothendieck wrote:
    On Wed, May 9, 2012 at 5:58 PM, Shivam 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


    --
    *Victoria Concordia Crescit*
  • Gabor Grothendieck at May 9, 2012 at 11:52 pm

    On Wed, May 9, 2012 at 7:24 PM, Shivam wrote:
    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
  • Shivam at May 10, 2012 at 11:43 am
    Thanks Gabor, Jim, POSIXct is working fine :)

    Regards,
    Shivam
    On Thu, May 10, 2012 at 5:22 AM, Gabor Grothendieck wrote:
    On Wed, May 9, 2012 at 7:24 PM, Shivam wrote:
    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


    --
    *Victoria Concordia Crescit*
  • Jim holtman at May 9, 2012 at 11:25 pm
    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 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.

Related Discussions

Discussion Navigation
viewthread | post