sqlExecute: Executes an already prepared query

In RODBCext: Parameterized Queries Extension for RODBC

Description

Executes a parameterized query.

Optionally (fetch=TRUE) fetches results using sqlGetResults.

Optionally (query=NULL) uses query already prepared by sqlPrepare.

Usage

sqlExecute(channel, query = NULL, data = NULL, fetch = FALSE,
  errors = TRUE, rows_at_time = attr(channel, "rows_at_time"),
  force_loop = FALSE, query_timeout = NULL, ...)

Arguments

channel	ODBC connection obtained by odbcConnect
query	a query string (NULL if query already prepared using sqlPrepare)
data	data to pass to sqlExecute (as data.frame)
fetch	whether to automatically fetch results (if data provided)
errors	whether to display errors
rows_at_time	number of rows to fetch at one time - see details of sqlQuery
force_loop	whether to execute queries in the explicit loop with separate query planing for each iteration (usefull if executing a query invalidates its plan, e.g. EXEC queries on Ms SQL Server)
query_timeout	the query timeout value in seconds (0 means "no timeout", NULL does not change the default value)
...	parameters to pass to sqlGetResults (if fetch=TRUE)

Details

Return value depends on the combination of parameters:

• if there were errors during query preparation or execution or fetching results return value depends on errors parameter - if errors=TRUE error is thrown, otherwise -1 will be returned

• if fetch=FALSE and there were no errors invisible(1) will be returned

• if fetch=TRUE and there were no errors a data.frame with results will be returned

Value

see details

Examples

## Not run: 
  conn = odbcConnect('MyDataSource')
  
  # prepare, execute and fetch results separately
  sqlPrepare(conn, "SELECT * FROM myTable WHERE column = ?")
  sqlExecute(conn, NULL, 'myValue')
  sqlGetResults(conn)
  
  # prepare and execute at one time, fetch results separately
  sqlExecute(conn, "SELECT * FROM myTable WHERE column = ?", 'myValue')
  sqlGetResults(conn)
  
  # prepare, execute and fetch at one time
  sqlExecute(conn, "SELECT * FROM myTable WHERE column = ?", 'myValue', TRUE)
  
  # prepare, execute and fetch at one time using multiple wildcards for data passthrough
  sqlExecute(
  conn, 
  query="SELECT * FROM table WHERE column1 = ? AND column2 = ?", 
  data=data.frame('column1value', 'column2value'), 
  fetch=TRUE
  )
  
  # prepare, execute and fetch at one time, pass additional parameters to sqlFetch()
  sqlExecute(
    conn, 
    "SELECT * FROM myTable WHERE column = ?", 
    'myValue', 
    fetch = TRUE, 
    stringsAsFactors = FALSE
  )
  
  # prepare, execute and fetch at one time using a query timeout value
  sqlExecute(conn, "SELECT * FROM myTable WHERE column = ?", 'myValue', TRUE, query_timeout=45)
  
  # execute a simple statement without parameters using a query timeout value
  sqlExecute(con, "SELECT * FROM myTable", fetch = TRUE, query_timeout = 60)

## End(Not run)

RODBCext documentation built on Feb. 6, 2020, 5:10 p.m.