How to execute several RSQLite commands at once or how to reset the whole file?

Question

Working with RSQLite to create a SQLite database I want to send several statements at a time - is this possible?

Why this does not work:

sql <- readLines("createtables.sql") dbSendQuery(con,sql) 

... and ...

 sql <- paste(readLines("createtables.sql"),collapse="") dbSendQuery(con,sql) 

... and ...

 sql <- paste(readLines("createtables.sql"),collapse="\n") dbSendQuery(con,sql) 

while they do:

 sql <- "CREATE TABLE birthdays ( nameid INTEGER PRIMARY KEY AUTOINCREMENT , firstname VARCHAR(100) NOT NULL , lastname VARCHAR(100) NOT NULL , birthday DATE ) ; " dbSendQuery(con,sql) sql <- "/* table def: foodtypes */ CREATE TABLE foodtypes ( foodid INTEGER PRIMARY KEY AUTOINCREMENT , foodname VARCHAR(100) NOT NULL, healthy INTEGER, `kcal/100g` float );" dbSendQuery(con,sql) 

createtables.sql contents:

 /* table def: birthdays */ CREATE TABLE birthdays ( nameid INTEGER PRIMARY KEY AUTOINCREMENT , firstname VARCHAR(100) NOT NULL , lastname VARCHAR(100) NOT NULL , birthday DATE ) ; /* table def: foodtypes */ CREATE TABLE foodtypes ( foodid INTEGER PRIMARY KEY AUTOINCREMENT , foodname VARCHAR(100) NOT NULL, healthy INTEGER, `kcal/100g` float ); 

Answer 1

Since they simply do not try to convince RSQLite-functions to execute several commands at once, I wrote two functions that solve this problem:

(1) sqlFromFile() reads in SQL files and converts the text so that each statement spans exactly one row.

(2) dbSendQueries() similar to dbSendQuery() provided by RSQLite, but applies a query function to each line (each vector element) of the provided text so that a whole bun of statements can be launched.

 # read in sql-statements and preformat them sqlFromFile <- function(file){ require(stringr) sql <- readLines(file) sql <- unlist(str_split(paste(sql,collapse=" "),";")) sql <- sql[grep("^ *$", sql, invert=T)] sql } # apply query function to each element dbSendQueries <- function(con,sql){ dummyfunction <- function(sql,con){ dbSendQuery(con,sql) } lapply(sql, dummyfunction, con) } # solution for example in question dbSendQueries( con, sqlFromFile("createtables.sql") )