SQLExecute
SQLExecute allows SQL statements to be executed. Statements can be used to select data, create tables, insert data, update data, remove data, and drop tables. The statement used by SQLExecute is a string that can contain all arguments. However, it is also possible to give the arguments separately, which makes the statement a prepared statement. SQLExecute can also be used to execute a batch of prepared statements with different arguments, as described in "Batch Input".
| SQLExecute[conn,statement,opts…] | execute an SQL statement |
| SQLExecute[conn,statement,{args…},opts…] | execute a prepared statement with arguments |
| SQLExecute[conn,statement,{{args…}…},opts…] | execute a batch of prepared statements with different arguments |
The following sections show how to use SQL statements to carry out different types of manipulations.
There are a number of options that can be given to SQLExecute.
option name | default value | |
| "ColumnSymbols" | None | symbols to be associated with results |
| "EscapeProcessing" | True | translate escaped JDBC function syntax |
| "FetchSize" | Automatic | JDBC driver hint for filling result sets |
| "GetAsStrings" | False | whether to return the results as strings |
| "GetGeneratedKeys" | False | return keys associated with updated records |
| "MaxFieldSize" | Automatic | byte limit for variable-length column types |
| "MaxRows" | Automatic | set the maximum number of rows returned |
| "ShowColumnHeadings" | False | whether to return headings with the results |
| "Timeout" | $SQLTimeout | set the timeout for a query |
| "BatchSize" | 1000 | queries with parameters will be processed in batches of this size |
| "JavaBatching" | True | perform parameter batching in Java layer instead of Wolfram Language layer |
Options of SQLExecute.
Here is an example of these options. This loads DatabaseLink and connects to the demo database. If you find that the examples in this tutorial 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".
The option "GetAsStrings" can retrieve data without converting it to a Wolfram Language type.
The option "MaxRows" can limit the number of rows returned.
The option "ShowColumnHeadings" can retrieve the column headings with the results.
The option "Timeout" can be used to cancel a query if it takes too long to execute.
Argument Sequences in SQL‐Style Queries
If you want to use one argument in an SQL statement that holds a sequence of several values, you can use SQLArgument. This is particularly useful for selects and inserts in tables that have many columns. With selects, you can dynamically specify multiple tables and columns, and with inserts you can dynamically specify multiple columns and values.
| SQLArgument | a sequence of arguments to a command |
To demonstrate this, load DatabaseLink and connect to the publisher database.
Now, you can execute a select query using SQLArgument. Notice how the statement refers to two arguments as `1` arguments. This makes the statement simpler since it saves having to number the arguments individually.
It should be noted that SQLArgument is not supported in Wolfram Language-based queries.