Retrieve data to a data.frame

This function sends SQL to the server, and returns the results.

querySql(
  connection,
  sql,
  errorReportFile = file.path(getwd(), "errorReportSql.txt"),
  snakeCaseToCamelCase = FALSE,
  integerAsNumeric = getOption("databaseConnectorIntegerAsNumeric", default = TRUE),
  integer64AsNumeric = getOption("databaseConnectorInteger64AsNumeric", default = TRUE)
)

Arguments

connection

The connection to the database server created using either connect() or DBI::dbConnect().

sql

The SQL to be send.

errorReportFile

The file where an error report will be written if an error occurs. Defaults to 'errorReportSql.txt' in the current working directory.

snakeCaseToCamelCase

If true, field names are assumed to use snake_case, and are converted to camelCase.

integerAsNumeric

Logical: should 32-bit integers be converted to numeric (double) values? If FALSE 32-bit integers will be represented using R's native Integer class.

integer64AsNumeric

Logical: should 64-bit integers be converted to numeric (double) values? If FALSE 64-bit integers will be represented using bit64::integer64.

Value

A data frame.

Details

Fields will be automatically converted for improved consistenty in these situations:

• SQLite: Fields with names ending in _date will be converted to DATE fields. Rationale: SQLite does not support DATE fields.

• SQLite: Fields with names ending in _datetime will be converted to POSIXct fields. Rationale: SQLite does not support DATETIME fields.

• BigQuery and Snowflake: Integer fields will be converted to Integer if it fits in an integer, or will remain Integer64 otherwise. Rationale: these platforms do not distinguish between INT and BIGINT.

This function sends the SQL to the server and retrieves the results. If an error occurs during SQL execution, this error is written to a file to facilitate debugging. Null values in the database are converted to NA values in R.

Examples

if (FALSE) { # \dontrun{
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
count <- querySql(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
} # }