Execute multiple SQL statements (read from file) in R using DBI and RSQLite

Question

I have the following example code:

library(DBI)
library(RSQLite)

conn <- DBI::dbConnect(RSQLite::SQLite(), "test.sqlite" )

########
# ## This is the original code when reading from the file which is 
# ## for convenience commented out here.
# sql <- readLines("test.sql")
# sql <- paste0(sql, collapse = "  ")
########

sql <- "CREATE TABLE `bemovi_mag_25__mean_density_per_ml` ( 
   `timestamp` NUMERIC, 
   `date` NUMERIC, 
   `species` NUMERIC, 
   `composition_id` NUMERIC, 
   `bottle` NUMERIC, 
   `temperature_treatment` NUMERIC, 
   `magnification` NUMERIC, 
   `sample` NUMERIC, 
   `density` NUMERIC 
 );  CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle);"

DBI::dbExecute(conn, sql)
DBI::dbDisconnect(conn)

When running the DBI::dbExecute(), I get the following warning:

> DBI::dbExecute(conn, sql)
[1] 0
Warning message:
Ignoring remaining part of query: CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle);

I understand why this is happening (because of the ;), but my question is:

Is there a DBI command I am missing which will execute the sql code correctly, or do I have to split the sql statement as follows:

sql <- strsplit(sql, ";")
lapply(
  sql[[1]],
  function(s) {DBI::dbExecute(conn, s)}
)

Unfortunately this has been the case for many years in `DBI`, it has been discussed well before Sandwichnick's link in `RSQLite` (including https://github.com/r-dbi/DBI/issues/273, though I know I've talked about it before then). I do not expect this to be changed any time soon. I've been using the same workaround for well over 5 years without issue. The only "risk" is if any portion of the query naturally contains an embedded semicolon (perhaps as part of a string literal), then the query will be incorrectly split. If that's not a concern, then I suggest use this workaround. — r2evans, Jan 19 '22 at 14:41
Does this answer your question? [How to execute more than one RSQLite statement at once or how to dump a whole file?](https://stackoverflow.com/questions/18914283/how-to-execute-more-than-one-rsqlite-statement-at-once-or-how-to-dump-a-whole-fi) — wibeasley, Aug 16 '23 at 03:25

score 4 · Answer 1 · answered Jan 19 '22 at 14:19

4

Unfortunately, this is an ongoing Issue with DBI and RSQlite: https://github.com/r-dbi/RSQLite/issues/313

For now you probably should use your workaround.

answered Jan 19 '22 at 14:19

Sandwichnick

1,379
6
13

Thanks. I will than use my solution and hope that somebody will look into this. – Rainer Jan 19 '22 at 14:23
1

FYI, *"hope that somebody will look into this"* is a bit unrealistic here. StackOverflow is not a stopping place for package devs to fish around and find bugs they need to resolve (though that can happen), so expecting the `DBI`/`RSQLite` devs to happen on this comment and remedy it is a lost cause. If you really think it's important, there are two ways to garner the attention of the maintainers: (1) at a minimum, look for and raise an issue (if not existing yet) on their repo/tracker; (2) pay for support, and ask about it then. Not every FOSS package has formal support, #1 is always a start. – r2evans Jan 20 '22 at 03:21

Execute multiple SQL statements (read from file) in R using DBI and RSQLite

1 Answers1