RODBCext (version 0.3.2)

sqlExecute: Executes an already prepared query

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)

Value

see details

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

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