dbSendQueryArrow | R Documentation |
The dbSendQueryArrow()
method only submits and synchronously executes the
SQL query to the database engine.
It does not extract any
records — for that you need to use the dbFetchArrow()
method, and
then you must call dbClearResult()
when you finish fetching the
records you need.
For interactive use, you should almost always prefer dbGetQueryArrow()
.
Use dbSendQuery()
or dbGetQuery()
instead to retrieve the results
as a data frame.
dbSendQueryArrow(conn, statement, ...)
conn |
A DBIConnection object, as returned by
|
statement |
a character string containing SQL. |
... |
Other parameters passed on to methods. |
This method is for SELECT
queries only. Some backends may
support data manipulation queries through this method for compatibility
reasons. However, callers are strongly encouraged to use
dbSendStatement()
for data manipulation statements.
dbSendQueryArrow()
returns
an S4 object that inherits from DBIResultArrow.
The result set can be used with dbFetchArrow()
to extract records.
Once you have finished using a result, make sure to clear it
with dbClearResult()
.
This section gives a complete overview over the flow for the execution of queries that return tabular data as an Arrow stream.
Most of this flow, except repeated calling of dbBindArrow()
or dbBind()
,
is implemented by dbGetQueryArrow()
,
which should be sufficient
unless you have a parameterized query that you want to reuse.
This flow requires an active connection established by dbConnect()
.
See also vignette("dbi-advanced")
for a walkthrough.
Use dbSendQueryArrow()
to create a result set object of class
DBIResultArrow.
Optionally, bind query parameters with dbBindArrow()
or dbBind()
.
This is required only if the query contains placeholders
such as ?
or $1
, depending on the database backend.
Use dbFetchArrow()
to get a data stream.
Repeat the last two steps as necessary.
Use dbClearResult()
to clean up the result set object.
This step is mandatory even if no rows have been fetched
or if an error has occurred during the processing.
It is good practice to use on.exit()
or withr::defer()
to ensure that this step is always executed.
An error is raised when issuing a query over a closed
or invalid connection,
or if the query is not a non-NA
string.
An error is also raised if the syntax of the query is invalid
and all query parameters are given (by passing the params
argument)
or the immediate
argument is set to TRUE
.
The following arguments are not part of the dbSendQueryArrow()
generic
(to improve compatibility across backends)
but are part of the DBI specification:
params
(default: NULL
)
immediate
(default: NULL
)
They must be provided as named arguments. See the "Specification" sections for details on their usage.
No warnings occur under normal conditions.
When done, the DBIResult object must be cleared with a call to
dbClearResult()
.
Failure to clear the result set leads to a warning
when the connection is closed.
If the backend supports only one open result set per connection,
issuing a second query invalidates an already open result set
and raises a warning.
The newly opened result set is valid
and must be cleared with dbClearResult()
.
The param
argument allows passing query parameters, see dbBind()
for details.
immediate
argumentThe immediate
argument supports distinguishing between "direct"
and "prepared" APIs offered by many database drivers.
Passing immediate = TRUE
leads to immediate execution of the
query or statement, via the "direct" API (if supported by the driver).
The default NULL
means that the backend should choose whatever API
makes the most sense for the database, and (if relevant) tries the
other API if the first attempt fails. A successful second attempt
should result in a message that suggests passing the correct
immediate
argument.
Examples for possible behaviors:
DBI backend defaults to immediate = TRUE
internally
A query without parameters is passed: query is executed
A query with parameters is passed:
params
not given: rejected immediately by the database
because of a syntax error in the query, the backend tries
immediate = FALSE
(and gives a message)
params
given: query is executed using immediate = FALSE
DBI backend defaults to immediate = FALSE
internally
A query without parameters is passed:
simple query: query is executed
"special" query (such as setting a config options): fails,
the backend tries immediate = TRUE
(and gives a message)
A query with parameters is passed:
params
not given: waiting for parameters via dbBind()
params
given: query is executed
For updates: dbSendStatement()
and dbExecute()
.
Other DBIConnection generics:
DBIConnection-class
,
dbAppendTable()
,
dbAppendTableArrow()
,
dbCreateTable()
,
dbCreateTableArrow()
,
dbDataType()
,
dbDisconnect()
,
dbExecute()
,
dbExistsTable()
,
dbGetException()
,
dbGetInfo()
,
dbGetQuery()
,
dbGetQueryArrow()
,
dbIsReadOnly()
,
dbIsValid()
,
dbListFields()
,
dbListObjects()
,
dbListResults()
,
dbListTables()
,
dbQuoteIdentifier()
,
dbReadTable()
,
dbReadTableArrow()
,
dbRemoveTable()
,
dbSendQuery()
,
dbSendStatement()
,
dbUnquoteIdentifier()
,
dbWriteTable()
,
dbWriteTableArrow()
Other data retrieval generics:
dbBind()
,
dbClearResult()
,
dbFetch()
,
dbFetchArrow()
,
dbFetchArrowChunk()
,
dbGetQuery()
,
dbGetQueryArrow()
,
dbHasCompleted()
,
dbSendQuery()
# Retrieve data as arrow table
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQueryArrow(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetchArrow(rs)
dbClearResult(rs)
dbDisconnect(con)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.