Selecting Data
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 "DatabaseLink`DatabaseExamples`" 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.
This retrieves all data within the table ROYSCHED.
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.
The results of the database operation can immediately be used in the Wolfram Language.
There are a number of options that can be given to SQLSelect.
option name | default value | |
| "Distinct" | False | whether to return only distinct results |
| "FetchSize" | Automatic | JDBC driver hint for filling result sets |
| "GetAsStrings" | False | whether to return the results as strings |
| "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 |
| "SortingColumns" | None | how to sort the data |
| "ColumnSymbols" | None | symbols to be associated with results |
| "Timeout" | $SQLTimeout | set 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 "MaxRows" to show only the first five results.
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.
You may specify that a column value must be between certain values.
You may specify that a column value must be equal to a certain value.
You may specify that a column value must not be equal to a certain value.
You may specify that a column value must be greater than a certain value.
You may specify that a column value must be less than a certain value.
You may specify that a column value must be greater than or equal to a certain value.
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.
You may specify that a column value must be contained as a member of a list.
You may specify that a column value must be less than or equal to a certain value.
You may also combine any conditions using And or Or.
The option "GetAsStrings" can retrieve data without converting it to a Wolfram Language type. This repeats the previous query without converting the data.
You may also use the option "SortingColumns" 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.
The following plot shows that the data is now sorted.
The option "Timeout" can be used to cancel a query if it takes too long to execute.
The details of how Wolfram Language expressions are mapped to types stored in the database is discussed in "Data Type Mapping".