Render, translate, execute SQL code — renderTranslateExecuteSql • DatabaseConnector

This function renders, translates, and executes SQL consisting of one or more statements.

renderTranslateExecuteSql(
  connection,
  sql,
  profile = FALSE,
  progressBar = TRUE,
  reportOverallTime = TRUE,
  errorReportFile = file.path(getwd(), "errorReportSql.txt"),
  runAsBatch = FALSE,
  oracleTempSchema = NULL,
  tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"),
  ...
)

Arguments

connection: The connection to the database server created using either connect() or dbConnect().
sql: The SQL to be executed
profile: When true, each separate statement is written to file prior to sending to the server, and the time taken to execute a statement is displayed.
progressBar: When true, a progress bar is shown based on the statements in the SQL code.
reportOverallTime: When true, the function will display the overall time taken to execute all statements.
errorReportFile: The file where an error report will be written if an error occurs. Defaults to 'errorReportSql.txt' in the current working directory.
runAsBatch: When true the SQL statements are sent to the server as a single batch, and executed there. This will be faster if you have many small SQL statements, but there will be no progress bar, and no per-statement error messages. If the database platform does not support batched updates the query is executed as ordinarily.
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.
...: Parameters that will be used to render the SQL.

Details

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

Examples

if (FALSE) {
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
renderTranslateExecuteSql(connection,
  sql = "SELECT * INTO #temp FROM @schema.person;",
  schema = "cdm_synpuf"
)
disconnect(conn)
}