DATABASELINK TUTORIAL

Selecting Data
This feature is not supported on the Wolfram Cloud.

SQLSelect selects and returns data from a database. An alternative, using raw SQL, is described in "Selecting Data with Raw SQL".

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".

SQLSelect[conn,table,opts]select all data from the table
SQLSelect[conn,{tables},{columns}]select data in certain columns from the table
SQLSelect[conn,{tables},{columns},condition,opts]
select data in certain columns from the table meeting the condition

Retrieving data from a database.

This loads DatabaseLink and connects to the publisher database.

In[1]:=
Click for copyable input

This retrieves all data within the table ROYSCHED.

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

The third parameter of SQLSelect can be used to select only certain columns. In this example, only the TITLE_ID and ROYALTY columns are selected.

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

The results of the database operation can immediately be used in the Wolfram Language.

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

There are a number of options that can be given to SQLSelect.

option name
default value
"Distinct"Falsewhether to return only distinct results
"FetchSize"AutomaticJDBC driver hint for filling result sets
"GetAsStrings"Falsewhether to return the results as strings
"MaxFieldSize"Automaticbyte limit for variable-length column types
"MaxRows"Automaticset the maximum number of rows returned
"ShowColumnHeadings"Falsewhether to return headings with the results
"SortingColumns"Nonehow to sort the data
"ColumnSymbols"Nonesymbols to be associated with results
"Timeout"$SQLTimeoutset the timeout for a query

Options of SQLSelect.

It is possible to select data from multiple columns in multiple tables. You can select multiple tables by giving a second argument that is a list of the table names. A list of column names should be used as the third parameter, as shown previously. You can also associate a specific table with a column by pairing a column name with a table name in a list in the third argument. This is important if the same column name is used in more than one table. The following example of a data join generates an outer product of the data in the two tables and it uses the option to show only the first five results.

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

The following example repeats the previous query, adding a condition that the TITLE_ID in the two tables must be equal. Using a condition is often a useful way to narrow the search results.

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

You may specify that a column value must be between certain values.

In[7]:=
Click for copyable input
Out[7]=
In[8]:=
Click for copyable input
Out[8]=

You may specify that a column value must be equal to a certain value.

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

You may specify that a column value must not be equal to a certain value.

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

You may specify that a column value must be greater than a certain value.

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

You may specify that a column value must be less than a certain value.

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

You may specify that a column value must be greater than or equal to a certain value.

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

You may specify that a column value must match a certain pattern using the metacharacters for matching zero or more characters and for matching a single character.

In[14]:=
Click for copyable input
Out[14]=
In[15]:=
Click for copyable input
Out[15]=

You may specify that a column value must be contained as a member of a list.

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

You may specify that a column value must be less than or equal to a certain value.

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

You may also combine any conditions using And or Or.

In[18]:=
Click for copyable input
Out[18]=
In[19]:=
Click for copyable input
Out[19]=

The option can retrieve data without converting it to a Wolfram Language type. This repeats the previous query without converting the data.

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

You may also use the option to specify how to sort the data. This option takes a list of rules. The left side of the rule specifies the column, and the right side specifies whether to sort that data in ascending or descending order. The first item in the list takes precedence over the supplemental items.

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

The following plot shows that the data is now sorted.

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

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

This closes the connection.

In[23]:=
Click for copyable input

The details of how Wolfram Language expressions are mapped to types stored in the database is discussed in "Data Type Mapping".