Problem updating a table

[kgui...@gmail.com]

Hello everyone,

I've created a table named "mytable" with sqldf but when I try to update it this way:

mytable = sqldf("alter table mytable add column mycolumn number", "select * from main.mytable")

The table "mytable" is replaced with NULL... I can't manage to get updates working either. Do you know what's wrong with my code?

Thanks in advance,
Kevin

[Gabor Grothendieck]

To run multiple statements with one call they must be placed in a vector and passed to sqldf through its first argument.  See ?sqledf :

   sqldf(c("alter table mytable add column mycolumn number", "select * from main.mytable"))

[kgui...@gmail.com]

Thanks for your promptness in replying to me! This works now... I'm sorry, I hadn't seen the subtelty in passing a vector instead of several parameters to the sqldf function...

Anyway, isn't it a way to modify sqldf in a future version so that it could directly update the tables in R? It appears that select's are really straightforward in sqldf but update's are quite cumbersome...

[Gabor Grothendieck]

1. There could be a writeback argument that wrote back every table after every SQL statement:

    sqldf("alter table mytable add column mycolumn number", writeback = TRUE)

This does have the downsides of 

- possibly clobbering existing data frames making it less safe and 

- it could result in overhead of copying back tables that had not changed and

- since R data frames don't have keys if sqldf read the data frame into sqlite, added a key and wrote it back the key would be lost which suggsts we may be getting into an area of increasing complexlity

2. Another possibility is to automatically prefix certain table names with main. although it has these downsides:

- it changes the SQL statement whereas currently that is never the case so you can be absolutely sure what is being passed to the driver

- it is database system speciifc.  For example, PostgreSQL does not use main.

The current system seems conceptually purer but I will think about it a bit more and may experiment with a writeback argument.

[kgui...@gmail.com]

Ok, thanks for your feedback, I see there are technical issues behind such a behavior...