Error in rsqlite_send_query

Question

I have the below table names lco_act_data

head(lco_act_data)

X   SMZ_N     ACRES HH2030 ENR2030 RE2030 OFF2030 OTH2030 TOT2030
1       1 1603.5114  11325    1706    407    1368    1162    3085
2       2  907.2274   2079    1062    332    1028    1190    2654
3       3  758.8701   1407    1447     60    1949    1315    3829
4       4  170.1726    868       0     76     737     128     964
5       5  820.4855   5338     304    198    1343    1597    3249
6       6 1596.5201   4584    3272    346    1351    1411    3195

When I'm trying to alter the table using sqldf function below its throwing me the below error and I can't seem to understand why.

lco_act_data_edited <- sqldf("ALTER TABLE lco_act_data 
ADD Jurisdiction varchar(20),
State varchar(20),
Region varchar(20),
Subregion varchar(20)")

Error in rsqlite_send_query(conn@ptr, statement) : near ",": syntax error

So you're using sqlite? Be sure to look at the alter table syntax for that DB: http://stackoverflow.com/questions/6172815/sqlite-alter-table-add-multiple-columns-in-a-single-statement. Your statement is invalid. — MrFlick, Apr 04 '17 at 00:10
yes, I'm using SQLite. When I try to add only a single column at a time like below lco_act_data_edited <- sqldf("ALTER TABLE lco_act_data ADD Jurisdiction varchar(20)") I'm getting a data frame with 0 columns and 0 rows — Lester Ignatius Pereira, Apr 04 '17 at 00:18
I wouldn't think an alter statement would return anything. Does it change `lco_act_data`? — MrFlick, Apr 04 '17 at 00:19

score 1 · Accepted Answer · answered Apr 04 '17 at 03:43

By default, the sqldf function runs without side-effects to it won't change existing objects. If you want to alter a table, you'll need to return it in the same command. You can pass in a vector of SQL statements. For example

lco_act_data_edited  <- sqldf(c(
    "ALTER TABLE lco_act_data ADD Jurisdiction varchar(20)", 
    "ALTER TABLE lco_act_data ADD State varchar(20)", 
    "select * from lco_act_data"
))

Related to sqldf FAQ #8

Error in rsqlite_send_query

1 Answers1

Linked