% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/14-dbSendQuery.R
\name{dbSendQuery}
\alias{dbSendQuery}
\title{Execute a query on a given database connection}
\usage{
dbSendQuery(conn, statement, ...)
}
\arguments{
\item{conn}{A \linkS4class{DBIConnection} object, as returned by
\code{\link[=dbConnect]{dbConnect()}}.}

\item{statement}{a character string containing SQL.}

\item{...}{Other parameters passed on to methods.}
}
\value{
\code{dbSendQuery()} returns
an S4 object that inherits from \linkS4class{DBIResult}.
The result set can be used with \code{\link[=dbFetch]{dbFetch()}} to extract records.
Once you have finished using a result, make sure to clear it
with \code{\link[=dbClearResult]{dbClearResult()}}.
}
\description{
The \code{dbSendQuery()} method only submits and synchronously executes the
SQL query to the database engine.  It does \emph{not} extract any
records --- for that you need to use the \code{\link[=dbFetch]{dbFetch()}} method, and
then you must call \code{\link[=dbClearResult]{dbClearResult()}} when you finish fetching the
records you need.
For interactive use, you should almost always prefer \code{\link[=dbGetQuery]{dbGetQuery()}}.
Use \code{\link[=dbSendQueryArrow]{dbSendQueryArrow()}} or \code{\link[=dbGetQueryArrow]{dbGetQueryArrow()}} instead to retrieve the results
as an Arrow object.

\Sexpr[results=rd,stage=render]{DBI:::methods_as_rd("dbSendQuery")}
}
\details{
This method is for \code{SELECT} queries only.  Some backends may
support data manipulation queries through this method for compatibility
reasons.  However, callers are strongly encouraged to use
\code{\link[=dbSendStatement]{dbSendStatement()}} for data manipulation statements.

The query is submitted to the database server and the DBMS executes it,
possibly generating vast amounts of data. Where these data live
is driver-specific: some drivers may choose to leave the output on the server
and transfer them piecemeal to R, others may transfer all the data to the
client -- but not necessarily to the memory that R manages. See individual
drivers' \code{dbSendQuery()} documentation for details.
}
\section{The data retrieval flow}{


This section gives a complete overview over the flow
for the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling of \code{\link[=dbBind]{dbBind()}} or \code{\link[=dbBindArrow]{dbBindArrow()}},
is implemented by \code{\link[=dbGetQuery]{dbGetQuery()}}, which should be sufficient
unless you want to access the results in a paged way
or you have a parameterized query that you want to reuse.
This flow requires an active connection established by \code{\link[=dbConnect]{dbConnect()}}.
See also \code{vignette("dbi-advanced")} for a walkthrough.
\enumerate{
\item Use \code{\link[=dbSendQuery]{dbSendQuery()}} to create a result set object of class
\linkS4class{DBIResult}.
\item Optionally, bind query parameters with \code{\link[=dbBind]{dbBind()}} or \code{\link[=dbBindArrow]{dbBindArrow()}}.
This is required only if the query contains placeholders
such as \verb{?} or \verb{$1}, depending on the database backend.
\item Optionally, use \code{\link[=dbColumnInfo]{dbColumnInfo()}} to retrieve the structure of the result set
without retrieving actual data.
\item Use \code{\link[=dbFetch]{dbFetch()}} to get the entire result set, a page of results,
or the remaining rows.
Fetching zero rows is also possible to retrieve the structure of the result set
as a data frame.
This step can be called multiple times.
Only forward paging is supported, you need to cache previous pages
if you need to navigate backwards.
\item Use \code{\link[=dbHasCompleted]{dbHasCompleted()}} to tell when you're done.
This method returns \code{TRUE} if no more rows are available for fetching.
\item Repeat the last four steps as necessary.
\item Use \code{\link[=dbClearResult]{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 \code{\link[=on.exit]{on.exit()}} or \code{\link[withr:defer]{withr::defer()}}
to ensure that this step is always executed.
}
}

\section{Failure modes}{


An error is raised when issuing a query over a closed
or invalid connection,
or if the query is not a non-\code{NA} string.
An error is also raised if the syntax of the query is invalid
and all query parameters are given (by passing the \code{params} argument)
or the \code{immediate} argument is set to \code{TRUE}.



}

\section{Additional arguments}{


The following arguments are not part of the \code{dbSendQuery()} generic
(to improve compatibility across backends)
but are part of the DBI specification:
\itemize{
\item \code{params} (default: \code{NULL})
\item \code{immediate} (default: \code{NULL})
}

They must be provided as named arguments.
See the "Specification" sections for details on their usage.

}

\section{Specification}{


No warnings occur under normal conditions.
When done, the DBIResult object must be cleared with a call to
\code{\link[=dbClearResult]{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 \code{dbClearResult()}.

The \code{param} argument allows passing query parameters, see \code{\link[=dbBind]{dbBind()}} for details.

}

\section{Specification for the \code{immediate} argument}{



The \code{immediate} argument supports distinguishing between "direct"
and "prepared" APIs offered by many database drivers.
Passing \code{immediate = TRUE} leads to immediate execution of the
query or statement, via the "direct" API (if supported by the driver).
The default \code{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
\code{immediate} argument.
Examples for possible behaviors:
\enumerate{
\item DBI backend defaults to \code{immediate = TRUE} internally
\enumerate{
\item A query without parameters is passed: query is executed
\item A query with parameters is passed:
\enumerate{
\item \code{params} not given: rejected immediately by the database
because of a syntax error in the query, the backend tries
\code{immediate = FALSE} (and gives a message)
\item \code{params} given: query is executed using \code{immediate = FALSE}
}
}
\item DBI backend defaults to \code{immediate = FALSE} internally
\enumerate{
\item A query without parameters is passed:
\enumerate{
\item simple query: query is executed
\item "special" query (such as setting a config options): fails,
the backend tries \code{immediate = TRUE} (and gives a message)
}
\item A query with parameters is passed:
\enumerate{
\item \code{params} not given: waiting for parameters via \code{\link[=dbBind]{dbBind()}}
\item \code{params} given: query is executed
}
}
}

}

\examples{
\dontshow{if (requireNamespace("RSQLite", quietly = TRUE)) (if (getRversion() >= "3.4") withAutoprint else force)(\{ # examplesIf}
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(rs)
dbClearResult(rs)

# Pass one set of values with the param argument:
rs <- dbSendQuery(
  con,
  "SELECT * FROM mtcars WHERE cyl = ?",
  params = list(4L)
)
dbFetch(rs)
dbClearResult(rs)

# Pass multiple sets of values with dbBind():
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = ?")
dbBind(rs, list(6L))
dbFetch(rs)
dbBind(rs, list(8L))
dbFetch(rs)
dbClearResult(rs)

dbDisconnect(con)
\dontshow{\}) # examplesIf}
}
\seealso{
For updates: \code{\link[=dbSendStatement]{dbSendStatement()}} and \code{\link[=dbExecute]{dbExecute()}}.

Other DBIConnection generics: 
\code{\link{DBIConnection-class}},
\code{\link{dbAppendTable}()},
\code{\link{dbAppendTableArrow}()},
\code{\link{dbCreateTable}()},
\code{\link{dbCreateTableArrow}()},
\code{\link{dbDataType}()},
\code{\link{dbDisconnect}()},
\code{\link{dbExecute}()},
\code{\link{dbExistsTable}()},
\code{\link{dbGetException}()},
\code{\link{dbGetInfo}()},
\code{\link{dbGetQuery}()},
\code{\link{dbGetQueryArrow}()},
\code{\link{dbIsReadOnly}()},
\code{\link{dbIsValid}()},
\code{\link{dbListFields}()},
\code{\link{dbListObjects}()},
\code{\link{dbListResults}()},
\code{\link{dbListTables}()},
\code{\link{dbQuoteIdentifier}()},
\code{\link{dbReadTable}()},
\code{\link{dbReadTableArrow}()},
\code{\link{dbRemoveTable}()},
\code{\link{dbSendQueryArrow}()},
\code{\link{dbSendStatement}()},
\code{\link{dbUnquoteIdentifier}()},
\code{\link{dbWriteTable}()},
\code{\link{dbWriteTableArrow}()}

Other data retrieval generics: 
\code{\link{dbBind}()},
\code{\link{dbClearResult}()},
\code{\link{dbFetch}()},
\code{\link{dbFetchArrow}()},
\code{\link{dbFetchArrowChunk}()},
\code{\link{dbGetQuery}()},
\code{\link{dbGetQueryArrow}()},
\code{\link{dbHasCompleted}()},
\code{\link{dbSendQueryArrow}()}
}
\concept{DBIConnection generics}
\concept{data retrieval generics}