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

Executing SQL statements.

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"Nonesymbols to be associated with results
"EscapeProcessing"Truetranslate escaped JDBC function syntax
"FetchSize"AutomaticJDBC driver hint for filling result sets
"GetAsStrings"Falsewhether to return the results as strings
"GetGeneratedKeys"Falsereturn keys associated with updated records
"MaxFieldSize"Automaticbyte limit for variable-length column types
"MaxRows"Automaticset the maximum number of rows returned
"ShowColumnHeadings"Falsewhether to return headings with the results
"Timeout"$SQLTimeoutset the timeout for a query
"BatchSize"1000queries with parameters will be processed in batches of this size
"JavaBatching"Trueperform 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.

This closes the connection.

Argument Sequences in SQLStyle 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.

SQLArgumenta sequence of arguments to a command

Argument sequences.

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.

This closes the connection.

It should be noted that SQLArgument is not supported in Wolfram Language-based queries.