query: Execute a SQL statement on a database connection.

In r-dbi/RMariaDB: Database Interface and MariaDB Driver

Execute a SQL statement on a database connection.

Description

To retrieve results a chunk at a time, use DBI::dbSendQuery(), DBI::dbFetch(), then DBI::dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use DBI::dbGetQuery() which sends, fetches and clears for you. For data manipulation queries (i.e. queries that do not return data, such as UPDATE, DELETE, etc.), DBI::dbSendStatement() serves as a counterpart to DBI::dbSendQuery(), while DBI::dbExecute() corresponds to DBI::dbGetQuery().

Usage

## S4 method for signature 'MariaDBResult'
dbBind(res, params, ...)

## S4 method for signature 'MariaDBResult'
dbClearResult(res, ...)

## S4 method for signature 'MariaDBResult'
dbFetch(res, n = -1, ..., row.names = FALSE)

## S4 method for signature 'MariaDBResult'
dbGetStatement(res, ...)

## S4 method for signature 'MariaDBConnection,character'
dbSendQuery(conn, statement, params = NULL, ..., immediate = FALSE)

## S4 method for signature 'MariaDBConnection,character'
dbSendStatement(conn, statement, params = NULL, ..., immediate = FALSE)

Arguments

res	A MariaDBResult object.
params	A list of query parameters to be substituted into a parameterised query.
...	Unused. Needed for compatibility with generic.
n	Number of rows to retrieve. Use -1 to retrieve all rows.
row.names	Either TRUE, FALSE, NA or a string. If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector. A string is equivalent to TRUE, but allows you to override the default name. For backward compatibility, NULL is equivalent to FALSE.
conn	A MariaDBConnection object.
statement	A character vector of length one specifying the SQL statement that should be executed. Only a single SQL statement should be provided.
immediate	If TRUE, uses the mysql_real_query() API instead of mysql_stmt_init(). This allows passing multiple statements (with CLIENT_MULTI_STATEMENTS) and turns off the ability to pass parameters.

Examples

if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbWriteTable(con, "arrests", datasets::USArrests, temporary = TRUE)

  # Run query to get results as dataframe
  dbGetQuery(con, "SELECT * FROM arrests limit 3")

  # Send query to pull requests in batches
  res <- dbSendQuery(con, "SELECT * FROM arrests")
  data <- dbFetch(res, n = 2)
  data
  dbHasCompleted(res)

  dbClearResult(res)
  dbDisconnect(con)
}

r-dbi/RMariaDB documentation built on Dec. 15, 2024, 12:12 p.m.