You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "krlmlr (via GitHub)" <gi...@apache.org> on 2023/06/07 10:01:38 UTC
[GitHub] [arrow-adbc] krlmlr opened a new issue, #734: r: Understand parameter binding
krlmlr opened a new issue, #734:
URL: https://github.com/apache/arrow-adbc/issues/734
I see how parameter binding helps with SQL ingestion. However, I couldn't make it work (with the SQLite driver) for querying. Is this a SQLite problem, or a more general problem?
``` r
library(adbcdrivermanager)
# Use the driver manager to connect to a database
db <- adbc_database_init(adbcsqlite::adbcsqlite(), uri = ":memory:")
con <- adbc_connection_init(db)
# Write a table
flights <- nycflights13::flights
# (timestamp not supported yet)
flights$time_hour <- NULL
stmt <- adbc_statement_init(con, adbc.ingest.target_table = "flights")
adbc_statement_bind(stmt, flights)
adbc_statement_execute_query(stmt)
#> [1] 336776
adbc_statement_release(stmt)
# March flights
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = 3")
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)
result
#> # A tibble: 28,834 × 18
#> year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2013 3 1 4 2159 125 318 56
#> 2 2013 3 1 50 2358 52 526 438
#> 3 2013 3 1 117 2245 152 223 2354
#> 4 2013 3 1 454 500 -6 633 648
#> 5 2013 3 1 505 515 -10 746 810
#> 6 2013 3 1 521 530 -9 813 827
#> 7 2013 3 1 537 540 -3 856 850
#> 8 2013 3 1 541 545 -4 1014 1023
#> 9 2013 3 1 549 600 -11 639 703
#> 10 2013 3 1 550 600 -10 747 801
#> # ℹ 28,824 more rows
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> # hour <dbl>, minute <dbl>
# March flights with a parameter
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = ?")
adbc_statement_bind_stream(stmt, data.frame(a = 3))
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)
result
#> # A tibble: 1 × 18
#> year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2013 3 1 4 2159 125 318 56
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> # hour <dbl>, minute <dbl>
# March flights with multiple parameters
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = ?")
adbc_statement_bind_stream(stmt, data.frame(a = 2:4))
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)
result
#> # A tibble: 3 × 18
#> year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2013 2 1 456 500 -4 652 648
#> 2 2013 3 1 4 2159 125 318 56
#> 3 2013 4 1 454 500 -6 636 640
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> # hour <dbl>, minute <dbl>
# Clean up
adbc_connection_release(con)
adbc_database_release(db)
```
<sup>Created on 2023-06-07 with [reprex v2.0.2](https://reprex.tidyverse.org)</sup>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on issue #734: r: Understand parameter binding
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #734:
URL: https://github.com/apache/arrow-adbc/issues/734#issuecomment-1580630232
Hmm, it looks like only one row of the result is being returned for each parameter? That's probably a driver-specific issue...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on issue #734: r: Understand parameter binding
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #734:
URL: https://github.com/apache/arrow-adbc/issues/734#issuecomment-1580795228
```c
sqlite3_mutex_enter(sqlite3_db_mutex(reader->db));
while (batch_size < reader->batch_size) {
if (reader->binder) {
char finished = 0;
struct AdbcError error = {0};
AdbcStatusCode status = AdbcSqliteBinderBindNext(reader->binder, reader->db,
reader->stmt, &finished, &error);
if (status != ADBC_STATUS_OK) {
reader->done = 1;
status = EIO;
if (error.release) {
strncpy(reader->error.message, error.message, sizeof(reader->error.message));
reader->error.message[sizeof(reader->error.message) - 1] = '\0';
error.release(&error);
}
break;
} else if (finished) {
reader->done = 1;
break;
}
}
int rc = sqlite3_step(reader->stmt);
```
I think there's a mistake here; we shouldn't bind until we exhaust the current stream.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm closed issue #734: r: Understand parameter binding
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm closed issue #734: r: Understand parameter binding
URL: https://github.com/apache/arrow-adbc/issues/734
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on issue #734: r: Understand parameter binding
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #734:
URL: https://github.com/apache/arrow-adbc/issues/734#issuecomment-1580779506
I tested this in Python and it happened too, so this looks like a problem with the driver itself.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org