query function

Execute a SQL statement on a database connection.

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().

## 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)
}