Creating Tables
SQLCreateTable creates a new table in a database. An alternative, using raw SQL, is described in
"Creating Tables 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
DatabaseExamples` package, as described in
"Using the Example Databases".
When creating a table, the result of
SQLCreateTable is an integer specifying the number of rows affected by the query. If the table is created correctly, this integer will always be zero as no rows are affected when creating a new table.
Creating a table in a database.
Here is an example that creates a table.
This loads
DatabaseLink and connects to the
demo database.
SQLCreateTable creates a table. The columns are given as a list of
SQLColumn expressions. In the following example, a new table,
DATATYPESTABLE, is created that has one column for each of the data types returned from
SQLDataTypeNames. The column,
TINYINTCOL, is configured so that it cannot be set to
Null. However, each binary column can be set to
Null. The database default for
"Nullable" is used for every other column that does not specify the
"Nullable" option. The character-based columns are limited to a specific data length; other columns use the default data length for their type.
| Out[3]= |  |
| Out[4]= |  |
SQLTableNames verifies that the table exists in the database.
| Out[5]= |  |
SQLColumnNames verifies the columns in the table.
| Out[6]= |  |
SQLCreateTable accepts one option.
| | |
| "Timeout" | Automatic | set the timeout for a query |
Option of SQLCreateTable.
"Timeout" can be used to cancel a query if it takes too long to execute.
This drops the table and closes the connection.
Certain databases support further options for columns, such as whether a column is a key or whether it auto-increments. If these options are desired, then a raw SQL statement should be used to create the table.
"Creating Tables with Raw SQL" has some ideas and examples.
