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')
 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')
 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")