FR : bindings to sqlite3_clear_bindings

[arnaud-feldmann]

Hi, If I understand the SQLite doc correctly, there is a function that allows to clear bindings after a reset and hence get a fresh renewed prepared query. Right now, as far as I know, within RSQLité there is no way to keep a prepared query in memory. Could it be possible to make a function that calls reset and clear_bindings on the results without breaking the pointer ?

That would allow a simple way of dealing with prepared queries compiled at connection time, or as lazy singletons. That is, if your application relies on sophisticated queries that are always the same, you just upload/prepare them once.

Thanks, Arnaud

[krlmlr]

Thanks, missed that.

I wonder what the API would look like. Should there be a class whose objects can be used in dbGetQuery() or dbSendQuery(), as in

query <- dbPrepareQuery(con, "SELECT ...")
dbSendQuery(con, query)

?

@paleolimbot: Is there a way in ADBC to reuse a prepared query in two separate fetches, to avoid compiling them multiple times?

[paleolimbot]

In ADBC (and maybe in DBI as well?) you are theoretically allowed to re-use a statement (i.e., you can call adbc_statement_init() |> adbc_statement_set_sql_query() |> adbc_statement_prepare(), followed by any number of adbc_statement_bind() + adbc_statement_execute()). (Apologies if this misses the point!)

library(adbcdrivermanager)

con <- adbcsqlite::adbcsqlite() |> 
  adbc_database_init() |> 
  adbc_connection_init()

con |> 
  execute_adbc(
    "CREATE TABLE testerino (col1 integer);"
  )

stmt <- con |> 
  adbc_statement_init() |> 
  adbc_statement_set_sql_query(
    "INSERT INTO testerino VALUES (?);"
  ) |> 
  adbc_statement_prepare()

for (val in c(493, 3948, 29)) {
  stmt |> 
    adbc_statement_bind(data.frame(val)) |> 
    adbc_statement_execute_query()
}

con |> 
  read_adbc("SELECT * from testerino;") |> 
  as.data.frame()
#>   col1
#> 1  493
#> 2 3948
#> 3   29

[krlmlr]

Thanks. This is slightly different from DBI, where preparing the query and sending it to the server is tightly coupled.