DATABASELINK TUTORIAL

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
"GetAsStrings"Falsereturn the results as strings
"MaxRows"Automaticset the maximum number of rows returned
"ShowColumnHeadings"Falsewhether to return headings with the results
"Timeout"Automaticset 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 package, as described in "Using the Example Databases".

In[1]:=
Click for copyable input

The option can retrieve data without converting it to a Mathematica type.

In[3]:=
Click for copyable input
Out[3]//InputForm=

The option can limit the number of rows returned.

In[4]:=
Click for copyable input
Out[4]=

The option can retrieve the column headings with the results.

In[5]:=
Click for copyable input
Out[5]//TableForm=

The option can be used to cancel a query if it takes too long to execute.

This closes the connection.

In[6]:=
Click for copyable input

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.

SQLArgumenta sequence of arguments to a command

Argument sequences.

To demonstrate this, load DatabaseLink and connect to the publisher database.

In[7]:=
Click for copyable input

Now, you can execute a select query using SQLArgument. Notice how the statement refers to two arguments as arguments. This makes the statement simpler since it saves having to number the arguments individually.

In[9]:=
Click for copyable input
Out[9]=

This closes the connection.

In[10]:=
Click for copyable input

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

New to Mathematica? Find your learning path »
Have a question? Ask support »