How to execute multiple RSQLite statements at a time or how to dump the entire file?

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

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

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

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

/* table def: birthdays */
CREATE TABLE birthdays (
nameid INTEGER PRIMARY KEY AUTOINCREMENT ,
firstname VARCH AR(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 );

# 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(s ql,con){
 dbSendQuery(con,sql)
 }
 lapply(sql, dummyfunction, con)
}

# solution for example in question
dbSendQueries( con, sqlFromFile("createtables.sql") )

To build a SQLite database using RSQLite I want to send multiple statements at once-is this possible?

Why these don’t 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)

And these 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)< br />
sql <- "/* table def: foodtypes */
CREATE TABLE foodtypes (
foodid INTEGER PRIMARY KEY AUTOINCREMENT ,
foodname VARCHAR(100) NOT NULL,< br />healthy INTEGER,
`kcal/100g` float );"
dbSendQuery(con,sql)

The content of createtables.sql is:

/* table def: birthdays */
CREATE TABLE birthdays (
nameid INTEGER PRIMARY KEY AUTOINCREMENT ,
firstname VARCHAR(100) NOT NUL L ,
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 );

Because they simply cannot convince the RSQLite function to execute multiple statements at the same time, I wrote two functions to solve this problem:

(1)sqlFromFile() reads the SQL file And convert the text so that each statement contains only one line.

(2) dbSendQueries() is similar to the dbSendQuery() provided by RSQLite, but applies the query function to each line of the provided text (each line of the vector). Elements) so that the entire statement can be run.

# 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") )