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.
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.