r/adbcdrivermanager: Get result schema for parametrized query before binding/executing

[nbenn]

DBI requires me to return a zero-row data.frame when binding zero-length parameters in a parametrized query. For something like

library(DBI)
con <- dbConnect(adbi::adbi("adbcsqlite"), uri = ":memory:")
dbWriteTable(con, "swiss", datasets::swiss)
res <- dbSendQuery(con, "SELECT * from swiss WHERE Agriculture < ?")
dbBind(res, list(integer()))
dbFetch(res)

I'd need to return something like

swiss[0, ]
#> [1] Fertility        Agriculture      Examination      Education
#> [5] Catholic         Infant.Mortality
#> <0 rows> (or 0-length row.names)

but instead I currently have an error Error parsing schema->format: Expected a null-terminated string but found NULL.

One way for me to do this is by catching the zero-length parameter binding and constructing the zero-length data.frame from the result schema. To do this, I'd need to access the result schema without binding/executing.

We have the exported function adbc_statement_execute_schema() available. Does that possibly do what I need it to? Unfortunately, I get

library(adbcdrivermanager)

db <- adbc_database_init(adbcsqlite::adbcsqlite(), uri = ":memory:")
con <- adbc_connection_init(db)

write_adbc(datasets::swiss, con, "swiss", temporary = TRUE)

stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from swiss WHERE Agriculture < ?")
adbc_statement_prepare(stmt)

adbc_statement_execute_schema(stmt)
#> Error in adbc_statement_execute_schema(stmt) : NOT_IMPLEMENTED

[lidavidm]

That is supposed to do what you need, but as with other things the SQLite driver doesn't yet implement it. PostgreSQL should support it.

[krlmlr]

In duckdb, we see the following error message: array_stream->get_schema(): [1]

Happy to share a reprex.

[lidavidm]

A reproduction would be appreciated. It's possible DuckDB hasn't yet implemented this.

[krlmlr]

With duckdb 1.0.0, I'm seeing:

library(adbcdrivermanager)

db <- adbc_database_init(duckdb::duckdb_adbc(), path = ":memory:")
con <- adbc_connection_init(db)

write_adbc(datasets::swiss, con, "swiss", temporary = TRUE)

stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from swiss WHERE Agriculture < ?")
adbc_statement_prepare(stmt)

adbc_statement_execute_schema(stmt)
#> Error in adbc_statement_execute_schema(stmt): NOT_IMPLEMENTED: [Driver Manager] AdbcStatementExecuteSchema not implemented

^{Created on 2024-07-01 with reprex v2.1.0}

Same behavior as in SQLite, it seems.

CC @pdet.

[CurtHagenlocher]

DuckDB doesn't seem to have implemented any of the 1.1 functionality yet.