Column Structure

Column Description

This section discusses commands that get information about database columns.

If you find that the examples in this section 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".

SQLColumnNames[conn]list all column names within a data source
SQLColumnNames[conn,name,opts]list all column names that match name within a data source
SQLColumns[conn]list all columns within a data source
SQLColumns[conn,name,opts]list all columns that match name within a data source
SQLColumnInformation[conn]list all column information for tables within a data source
SQLColumnInformation[conn,name,opts]list all column information for columns that match name within a data source

Functions for retrieving information about columns.

This loads DatabaseLink and connects to the demo database.

In[5]:=
Click for copyable input

SQLColumnNames returns a list of the column names within a database as a list of pairs of table and column names. For HSQLDB, it returns information from many of the SYSTEM tables.

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

It is possible to use metacharacters that will match names. The metacharacters are "%" for zero or more characters and "_" for a single character. The following command matches columns in tables that have names starting with "SA".

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

SQLColumns returns a list of SQLColumn expressions. SQLColumn expressions are sometimes useful for structural arguments in database commands, as described in "SQLExecute: Argument Sequences in SQL-Style Queries", because they contain information on the table name, column name, data type, whether an entry can be set to Null, and the data length.

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

SQLColumnInformation returns more information about the columns.

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

You can filter the names of the columns by providing a list of metacharacters to match the table and column names. The following command searches in all tables to return all columns that start with "V".

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

You can find all the columns in a single table by specifying the table name.

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

You can also give an SQLTable argument.

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

SQLColumnNames returns a list where each entry is a list of the table name and the column names. If you want a list of just the column names, you can use Mathematica part notation, entered with [[All, 2]], to extract just the second elements.

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

In addition, you can give an SQLColumn argument.

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

SQLColumns, SQLColumnNames, and SQLColumnInformation take a number of options.

option name
default value
"Catalog"Nonedatabase catalog to use
"Schema"Nonedatabase schema to use
"ShowColumnHeadings"Falsewhether to return headings with the results (SQLColumnInformation option only)

SQLColumns, SQLColumnNames, and SQLColumnInformation options.

The option can be used with SQLColumnInformation to return the column headings.

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

This closes the connection.

In[13]:=
Click for copyable input

If the database was designed with particular schema and catalogs, you can also select columns by using the and options.

Column Representation

SQLColumn expressions hold information about the columns in a database.

If you find that the examples in this section 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".

SQLColumn[{table,col},opts]expression that represents a column in an SQL table

Object for representing a column.

SQLColumn accepts a number of options.

option namedefault value
"DataTypeName"Nonetype of the entry
"Nullable"Nonewhether the entry can be null
"DataLength"Nonemaximum length for variable length data

SQLColumn options.

Here is an example demonstrating SQLColumn expressions. This loads DatabaseLink and connects to the demo database.

In[14]:=
Click for copyable input

SQLColumns returns a list of the columns in the database as SQLColumn expressions. In this example, a pattern is given to pick out just the SAMPLETABLE1 table.

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

This closes the connection.

In[4]:=
Click for copyable input

SQLColumn expressions can also be used in commands, as discussed in "Selecting Data" and "Creating Tables". "Creating Tables" discusses one particularly important use.

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