Search code examples
rsqlitersqlite

RSQLite db created on Mac OSX not inserting data on Windows


I'm really stuck here. I created a SQLite db on the Mac OSX, and now am trying to INSERT data into it on Windows. I'm doing it in R on both platforms, using the RSQLite library.

I'm trying to insert the data from data frame x:

> str(x)
'data.frame':   6 obs. of  12 variables:
 $ Julian   : int ...
 $ Date     : chr ...
 $ Time     : chr ...
 $ ID       : chr ...
 $ Item     : chr ...
 $ Value    : num ...
 $ Payment  : chr ...
 $ Type     : chr ...
 $ Customer : chr ...
 $ Operator1: chr ...
 $ Operator2: chr ...
 $ Weekday  : int ...

I try the following steps and get the error message below:

> db=dbConnect(dbDriver("SQLite"),dbname=f)
> dbSendQuery(db,"INSERT INTO Entries VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",x)
Error in .local(conn, statement, ...) :
  unused argument (list( ... ))

UPDATE: The suggestion below by vaettchen of using dbWriteTable with append=TRUE worked, thanks. However, there is still an issue with dbSendQuery, see error below when trying to remove all entries with id=74:

> dbSendQuery(db,"DELETE FROM Entries WHERE ID=?",id)
Error in .local(conn, statement, ...) : unused argument (74)

Appreciate any help, it might just be something stupid I can't see. Many thanks.


Solution

  • Here is what works for me in such a situation:

    id <- 74
    sql <- paste( "DELETE FROM Entries WHERE ID=", id )
    dbGetQuery( db, sql )
    

    If you want to remove more than one id, use a loop:

    id <- c( 73, 74, 78 )
    for( i in id )
    {
      sql <- paste( "DELETE FROM Entries WHERE ID=", i )
      dbGetQuery( db, sql )
    }
    

    Disclaimer: I'm only a casual RSQLite user and never used dbSendQuery directly. It may yield performance gains for large DBs or frequent bulk updates.