DatabaseLink`
DatabaseLink`

SQLExecute

SQLExecute[conn,command]

executes a command in an SQL connection.

SQLExecute[conn,command,args]

passes arguments to the command.

SQLExecute[SQLSelect[conn,...]]

manages the opening and closing of conn.

Details and Options

  • To use SQLExecute, you first need to load DatabaseLink using Needs["DatabaseLink`"].
  • Use this function with caution, as you can lose data permanently!
  • Options support and behavior varies with driver, driver version, and RDBMS type.
  • Returns an integer specifying the number of rows affected by the query. Returns $Failed if an error is encountered when dropping a table.
  • The following options can be given:
  • "ColumnSymbols" Nonesymbols to be associated with results
    "EscapeProcessing" Truetranslate escaped JDBC function syntax
    "FetchSize" AutomaticJDBC driver hint for filling result sets
    "GetAsStrings" Falsereturn all results as strings
    "GetGeneratedKeys" Falsereturn keys associated with updated records
    "MaxFieldSize" Automaticbyte limit for variable-length column types
    "MaxRows" Automaticthe maximum number of rows to return
    "ShowColumnHeadings" Falsewhether to return headings with the results
    "Timeout" $SQLTimeoutthe timeout for the query
    "BatchSize" 1000queries with parameters will be processed incrementally in batches of this size
    "JavaBatching" Trueperform parameter batching in Java layer instead of Wolfram Language layer

Examples

open allclose all

Basic Examples  (1)

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the DatabaseLink`DatabaseExamples` package, as described in Using the Example Databases.

Open a connection:

Select data:

Create a table:

Insert data:

Select data:

Update data:

Delete data:

Drop the table:

Scope  (5)

Selecting Data  (1)

Select data matching a condition:

Select the same data using a prepared statement:

Specify a column in a prepared statement:

Give a sequence of arguments in a prepared statement:

Apply a database function to the selected data:

Select only distinct values:

Group values:

Select a range of results:

Join data from multiple tables:

Creating Tables  (1)

Updating Data  (1)

Update data matching a condition:

Use a prepared statement:

Deleting Data  (1)

Delete data matching a condition:

Use a prepared statement:

Working with Dates and Times  (1)

Date and time data may be given in a query using DateObject, TimeObject or SQLDateTime. Create a table with DATE, TIME, and DATETIME column types:

Insert one row:

Selected date and time data will return with head SQLDateTime:

Express the selected data in its original form:

Generalizations & Extensions  (1)

Read from a data source, allowing DatabaseLink to open and close the connection:

Options  (11)

"ColumnSymbols"  (1)

Supply a list of symbols for query results to be assigned to:

Create symbols automatically:

Supply a function to operate on column names and results. If column headings have not been requested, the first argument to the function will be Null:

"EscapeProcessing"  (1)

The JDBC specification defines an escape call sequence for authoring queries that would otherwise require vendor-specific SQL. Set the "EscapeProcessing" option to employ defined escape syntax:

Issue a query using escaped SQL:

Issuing the query without escape processing results in an error:

SQL native to this RDBMS may be used without escape processing:

Issue a query using escaped SQL:

Use SQL native to this RDBMS:

Section 13.4 of the JDBC 4.0 Specification contains further information on escape processing and supported functions.

"FetchSize"  (1)

The "FetchSize" option suggests to the JDBC driver a number of rows to fetch on each visit to the database. Increasing this parameter reduces the amount of network traffic required to execute a query, at the cost of memory used:

Compute memory in use, including both kernel and Java layers:

Fetch a large result with small fetch size:

Reset:

Fetch a large result with large fetch size:

"GetAsStrings"  (1)

Retrieve data without converting it to Wolfram Language types:

"GetGeneratedKeys"  (1)

Open a connection to a data source that supports generated key retrieval:

Create a table with an auto-incrementing primary key:

Insert rows, returning keys:

Contents of the primary key column:

"MaxFieldSize"  (1)

For some variable-length column types the "MaxFieldSize" option can be used to limit the number of bytes returned for a field:

Create a table with a VARCHAR field:

Insert string data:

Select string contents, limiting byte length of results:

"MaxRows"  (1)

Limit the number of rows returned in a result:

"ShowColumnHeadings"  (1)

Include column names as the first row of the results:

The column headings are equivalent to the unqualified column names:

"Timeout"  (1)

Set a timeout value for a particular query:

"BatchSize"  (1)

Adjust batch size to trade off memory use and speed when issuing queries with long parameter lists:

Generate random data for insertion:

Compute memory in use, including both kernel and Java layers:

Run a query with a small batch size, resulting in relatively light Java-side memory use:

Reset:

Rerun with larger batch size, necessitating fewer server trips:

Java memory use is higher in this case:

"JavaBatching"  (1)

Switch parameter batching from Java layer to Wolfram Language layer to trade speed for memory:

Generate random data for insertion:

Compute memory in use, including both kernel and Java layers:

Run query with Java-side batching:

Reset:

Run query with Wolfram Language-side batching:

Total memory use is lower in this case:

Possible Issues  (1)

Retrieving generated keys from batch operations has driver- and RDBMS-dependent behavior. In many cases, only the last generated key is returned:

Create a table with an auto-incrementing primary key:

When using a parameterized SQLExecute with this RDBMS and driver, only the last generated key is returned: