renderTranslateQuerySql function

Render, translate, and query to data.frame

This function renders, and translates SQL, sends it to the server, and returns the results as a data.frame.

renderTranslateQuerySql(
  connection,
  sql,
  errorReportFile = file.path(getwd(), "errorReportSql.txt"),
  snakeCaseToCamelCase = FALSE,
  oracleTempSchema = NULL,
  tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"),
  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.
• oracleTempSchema: DEPRECATED: use tempEmulationSchema instead.
• tempEmulationSchema: Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created.
• 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.
• ...: Parameters that will be used to render the SQL.

Returns

A data frame.

Details

This function calls the render and translate functions in the SqlRender package before calling querySql().

Examples

## Not run:

connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
persons <- renderTranslatequerySql(conn,
  sql = "SELECT TOP 10 * FROM @schema.person",
  schema = "cdm_synpuf"
)
disconnect(conn)
## End(Not run)