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 statement 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.
| | |
| "GetAsStrings" | False | return the results as strings |
| "MaxRows" | Automatic | set the maximum number of rows returned |
| "ShowColumnHeadings" | False | whether to return headings with the results |
| "Timeout" | Automatic | set the timeout for a query |
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
DatabaseExamples` package, as described in
"Using the Example Databases".
The option
"GetAsStrings" can retrieve data without converting it to a
Mathematica type.
Out[3]//InputForm= |
| |  |
The option
"MaxRows" can limit the number of rows returned.
| Out[4]= |  |
The option
"ShowColumnHeadings" can retrieve the column headings with the results.
Out[5]//TableForm= |
| |  |
The option
"Timeout" can be used to cancel a query if it takes too long to execute.
This closes the connection.
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.
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.
| Out[9]= |  |
This closes the connection.
It should be noted that
SQLArgument is not supported in
Mathematica-based queries.
