RSQLite db created on Mac OSX not inserting data on Windows

Question

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.

Great, this worked. But I still have an issue with dbSendQuery, I updated the question accordingly. — valuenaut, Nov 02 '14 at 11:51

score 1 · Accepted Answer · answered Nov 04 '14 at 01:03

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.

RSQLite db created on Mac OSX not inserting data on Windows

1 Answers1