split and execute multiple SQL queries one by one R

Question

I try to split a long sql code and have a function run it statement by statement.

a <- str_split("select top 10 * from abc; select top 20 * from xyz", pattern = ";")
lapply(a, function(x) dbGetQuery(con,x))

This unfortunately errors out with

...Expecting a single string value: [type=character; extent=2]

I can do something like following which makes above lapply work but seems clunky

b <- as.list(unlist(a))

EDIT

After further inspection I assume it is due to the fact that dbGetQuery returns a dataframe for each query and can't write two dataframes into the same list element. The 2nd approach b puts each query into a separate list element and is able to put the results into separate list elements as well. Still curious if there is a more elegant way around this.

Answer 1

You've already answered your own question basically, but my recommendation would be:

library(stringr)
a <- str_split("select top 10 * from abc; select top 20 * from xyz", pattern = ";")  |> unlist()

Basically the as.list() is not needed and the native pipe (available in R 4.0 and above) can be used to reduce dependencies (though I guess you're using dplyr anyway).

EDIT: Seems this should also work, at least for your example.

str_split("select top 10 * from abc; select top 20 * from xyz", pattern = ";",simplify = T)

This returns a 1 row matrix instead of a list and lapply will automatically treat each column as a list.