Advanced example: using SQL queries

Topics covered in this section:

Certain tasks related to database access, such as deletion or update of existing data or the formulation of advanced selection statements, cannot be performed through initializations blocks. The ODBC and OCI modules therefore provide an alternative (lower level) means of accessing databases, namely using standard SQL commands.

ODBC

The module mmodbc defines the following subroutines for accessing external data sources through ODBC.

SQLconnect, SQLdisconnect:

Connect to a database / terminate the active connection.

SQLexecute:

Execute an SQL command.

SQLreadinteger, SQLreadreal, SQLreadstring:

Read an integer or real value, or a string from the database.

SQLupdate

Update the selected data with the provided array(s).

The procedures SQLconnect, SQLdisconnect and SQLread... can be used with any data source. SQLupdate only works if a data source supports positioned updates (this is typically the case for databases, but not for MS Excel). Depending on the data source the use of SQLexecute may be restricted to certain SQL commands: select and insert may be used with all data sources; commands like create, delete, and update will work with databases, but generally not with spreadsheets.

The procedures SQLexecute and SQLupdate allow the user to formulate his own SQL queries (using standard SQL). In this document we give a few examples of such queries, but these are by no means exhaustive. For a more thourough introduction to SQL the reader is refered to SQL tutorials and documentation such as those referenced at the site http://www.thefreecountry.com/documentation/onlinesql.shtml.

Data input with SQL statements

The following Mosel model corresponds to the model we have seen in Section Data input using odbc with the difference that we are now using SQL statements to read the data instead of an initializations block.

model "Duo input (2)"
 uses "mmodbc"

 declarations
  A5: dynamic array(range,range) of real
 end-declarations

! Use SQL statements to read the data
 SQLconnect('data.sqlite')
 if not getparam("SQLsuccess"): exit(1)
 SQLexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 SQLdisconnect

! Print out the data we have read
 writeln('A5 is: ', A5)

end-model 

The SQL statement "select Index_i,Index_j,Value from MyDataTable" says `select fields Index_i, Index_j, and Value from a table called MyDataTable. If this table only contains these three fields and in the given order we might equally use the query "select * from MyDataTable" which says `select everything from the range MyDataTable'. By using SQL statements directly in the Mosel model it is possible to have much more complex selection statements than the ones we have used here (see for instance the SQL queries formulated in Section SQL selection statements).

If we wish to read data from the MS Access database data.mdb, we need to use the connection string 'DSN=MS Access Database;DBQ=C:/xpress/examples/data.mdb' (or its short form 'data.mdb'); similarly, for a MySQL database named data the corresponding string would be 'DSN=mysql;DB=data'.

Data output with SQL statements

The example from Section Data output using odbc that outputs an array to a database via initializations to may be rewritten as follows with SQL statements:

model "Duo output (2)"
 uses "mmodbc"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use SQL statements for writing data
 SQLconnect('data.sqlite')
 if not getparam("SQLsuccess"): exit(1)
 SQLexecute("insert into MyOutTable2 (Index1,Index2,AValue) values (?,?,?)", A)
 SQLdisconnect

end-model 

The insertion command says `write the contents of the array A in the form of value-triples to the fields Index1, Index2, and AValue of the table MyOutTable2'. The question marks are placeholders for the index tuple, followed by the value of the array entry (first question mark = first index value, second question mark = second index value, ..., last question mark = array entry). Their number must correspond to the number of output table columns that are named. It is possible to select which indices/values to output and in which order (see Section Selection of columns/fields). Please note that the third column of the output range has been given the header AValue: when writing data through SQL statements we cannot use the header Value since this is a reserved word for certain data sources. In the version of the example using initializations to the headers of the columns are not used (nevertheless, a header line must be present) and this word therefore does not cause any problems.

As explained for the first version of this example (Section Data output using odbc) we need to make sure that the output range does not contain any data from previous runs by deleting data with the command sequence Edit » Delete » Shift cells up before the model execution.

For the Access database data.mdb the connection string would be 'DSN=MS Access Database;DBQ=C:/xpress/examples/data.mdb' (or simply 'data.mdb'). When writing to a database, we might remove data in the output table by hand, but it is certainly easier to clear the contents of this table by adding the following line to our Mosel model (immediately after the SQLconnect statement):

 SQLexecute("delete from MyOutTable2")

Oracle

The module mmoci defines the following subroutines for accessing Oracle databases via SQL commands.

OCIlogon, OCIlogoff:

Connect to a database / terminate the active connection.

OCIexecute:

Execute a PL/SQL command (select, insert, update, delete, create table, etc.).

OCIreadinteger, OCIreadreal, OCIreadstring:

Read an integer or real value, or a string from the database.

OCIcommit, OCIrollback:

Commit / roll back the current transaction (depending on setting of parameter OCIautocommit).

Please notice that there are some differences (other than the replacement of the prefix SQL by OCI) from the set of subroutines defined by module mmodbc: there is no separate 'update' procedure (data table updates can be formulated with OCIexecute), and an extra feature of this interface is the possibility to roll back transactions.

Data input with SQL statements

The following Mosel model corresponds to the model we have seen in Section Data input using odbc with the difference that we are now using SQL statements to read the data instead of an initializations block.

model "Duo input OCI (2)"
 uses "mmoci"

 declarations
  A5: dynamic array(range,range) of real
 end-declarations

! Use SQL statements to read the data
 OCIlogon("myname/mypassword@dbname")
 if not getparam("OCIsuccess"): exit(1)
 OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 OCIlogoff

! Print out the data we have read
 writeln('A5 is: ', A5)

end-model 

An alternative, equivalent formulation of the database logon statement uses three separate strings for the user name, password, and database name:

 OCIlogon("myname", "mypassword", "dbname") 

The SQL statement "select Index_i,Index_j,Value from MyDataTable" says `select columns Index_i, Index_j, and Value from the table called MyDataTable. If this table contains only these three columns and in the given order we might equally use the query "select * from MyDataTable" which says `select everything from the table MyDataTable'. In any case, we can work with exactly the same statement as in the ODBC version of this model since these are standard SQL queries. By using SQL statements directly in the Mosel model it is possible to have much more complex selection statements than the ones we have used here (see for instance the SQL queries formulated in Section SQL selection statements).

Data output with SQL statements

The example from Section Data output using odbc that outputs an array to an Oracle database via initializations to may be rewritten as follows with SQL statements:

model "Duo output OCI (2)"
 uses "mmoci"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use SQL statements for writing data
 OCIlogon('mmoci.oci:myname/mypassword@dbname')
 if not getparam("OCIsuccess"): exit(1)
 OCIexecute("delete from MyOutTable2")
 OCIexecute("insert into MyOutTable2 (Index1,Index2,AValue) values (:1,:2,:3)", A)
 OCIlogoff

end-model 

The insertion command says `write the contents of the array A in the form of value-triples to the fields Index1, Index2, and AValue of the table MyOutTable2'. The markers :1, :2 etc. are placeholders for the index tuple, followed by the value of the array entry (:1 = first index value, :2 = second index value, :3 = array entry). Their number must correspond to the number of output table columns that are named. By these markers it is possible to select which indices/values to output and in which order (see Section Selection of columns/fields).

As explained for the I/O driver version of this example (Section Data output using oci) we need to make sure that the output table does not contain any data. Instead of removing data from the database table ``by hand'', this SQL implementation clears the contents of the output table by executing a `delete' command in the Mosel model before inserting the new data. Alternatively, we might have chosen to update the existing data in the output table by writing out array A with the following command:

 OCIexecute("update MyOutTable2 set AValue=:3 where Index1=:1 and Index2=:2", A)