Skip to content

RSQLite dbWriteTable: REAL() can only be applied to a 'numeric', not a 'logical'

2 messages · Seth Falcon

Original
Seth Falcon
# 
Hi all,

I've received to problem reports regarding dbWriteTable in RSQLite.
The issue can be reproduced by calling dbWriteTable on a data frame
where a logical column's first row contains logical NA:

      library(RSQLite)
      db = dbConnect(SQLite(), ":memory:")
      df = data.frame(a=1, b=as.logical(NA))
      dbWriteTable(db, "foo", df)
    Error in sqliteTransactionStatement(conn, "ROLLBACK") : 
      connection with pending rows, close resultSet before continuing
    In addition: Warning message:
    In value[[3]](cond) :
      REAL() can only be applied to a 'numeric', not a 'logical'

I'm working on a fix, but it may take me a few days.  In the meantime,
if you convert logical columns to integer, things should work:

      dbClearResult(dbListResults(db)[[1]])
      dbRollback(db)

      df$b = as.integer(df$b)
      dbWriteTable(db, "foo", df)

+ seth
2 days later
Seth Falcon
# 
Hi again,

I believe I have a fix for the issue with logical columns in data
frames.  Before I push an update to CRAN, I would like to hear from
one or two testers...

A source only version is here:

   http://userprimary.net/misc/RSQLite_0.6-4.tar.gz

Details:

When a data frame has a logical column, its values will be converted
to integers when stored in SQLite.  Here is the test that I added:

    df <- data.frame(x=1:3, y=c(NA, TRUE, FALSE))

    dbWriteTable(DATA$db, "t1", df)

    got <- dbGetQuery(DATA$db, "select * from t1")

    checkEquals("integer", typeof(got$y))
    checkEquals(as.integer(NA), got$y[1])
    checkEquals(1L, got$y[2])
    checkEquals(0L, got$y[3])

+ seth