DATABASELINK 教程

Inserting Data with Raw SQL

The SQL command INSERT inserts data into a database. An alternative is to use the Mathematica command SQLInsert, as described in "Inserting Data".

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

When inserting data, the result of SQLExecute is an integer specifying the number of rows affected by the query.

Here is an example that inserts data. This loads DatabaseLink and connects to the demo database.

In[1]:=
Click for copyable input

As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.

In[3]:=
Click for copyable input

This demonstrates an SQL statement that inserts a row into the ADDRESSES table.

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

A SELECT statement verifies that the data has been added to the table.

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

The USERNAME column is made to be a primary key, which means that it must be unique. If you try to insert the same data again, there is an error and the result is $Failed.

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

With this command, the USERNAME parameter is unique, but ADDRESS, CITY, and ZIPCODE are not. These must also be unique, and again there is an error.

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

This inserts unique values of ADDRESS, CITY, and ZIPCODE.

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

A SELECT statement verifies that the data has been added to the table.

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

A prepared statement may be more useful for working with data to insert. In addition, SQLArgument may be useful to reduce the number of argument fields in the prepared statement. SQLArgument is described in "SQL Execute: Argument Sequences in SQL-Style Queries".

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

A SELECT statement verifies that the data has been added to the table.

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

Identity columns are very useful, as they automatically increment their values and do not require a value. They are also the primary key for the table, which means they uniquely identify a row. Identity values should be set to Null in an SQL statement.

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

A SELECT statement verifies that the data has been added to the table.

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

Since USERNAME is a foreign key, its value must be present in ADDRESSES. The following fails because user4 is not present in ADDRESSES.

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

The SENDMAILER column has a default value and is therefore not required when data is inserted.

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

A SELECT statement verifies that the data exists in the database and ties the values together.

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

This deletes the tables and closes the connection.

In[19]:=
Click for copyable input
New to Mathematica? Find your learning path »
Have a question? Ask support »