Database access via ODBC

The odbc I/O driver defined by the module mmodbc automatically generates the SQL queries that are required to read in data from an external data source (database) or insert data into this data source. The following modifications need to be made to model Burglar2 to switch to file access via ODBC:

model "Burglar2 (ODBC)"
 uses "mmxprs"

 parameters
  CNCTIO = ''
 end-parameters
 ...
 initializations from CNCTIO
  [VALUE,WEIGHT] as "BurgData"
 end-initializations
 ...
! Insert solutions into database: results from previous runs must be
! removed previously; otherwise the new results will either be appended
! to the existing ones or, if `ITEM' has been defined as key field
! in a database, the insertion may fail.
 initializations to CNCTIO
  SOLTAKE as "SolTake"
 end-initializations
end-model

The connection string CNCTIO indicates the data source, for example:

• CNCTIO = "mmodbc.odbc:burglar.mdb" for the MS Access database burglar.mdb
• CNCTIO = "mmodbc.odbc:DSN=mysql;DB=burglar" for the mysql database burglar

To run this example, the ODBC driver for the corresponding data source must be present. The connection string may vary depending on the installation of the ODBC driver. For further detail on setting up an ODBC connection and working with ODBC in Mosel see the Xpress Whitepaper `Using ODBC and other database interfaces with Mosel'.

In the case of database connections, the data are read from a table called BurgData that contains (at least) a field for the indices (e.g., labeled Item), and the fields Val and Wght. The results are written into the table SolTake, containing a field for the indices (e.g., Item) and a field to receive the solution values (e.g., called IfTake). In a spreadsheet, BurgData and SolTake are the names of named ranges. The columns of the named ranges must bear headers (to be included in the selected range area), these headers are not used by the (generated) SQL commands and may therefore differ from the names used in the model. The range BurgData must have three columns, for instance bearing the headers Item, Val, and Wght, and the range SolTake must have a column to receive the indices (e.g., labeled Item) and a second one to receive the solution values (e.g., IfTake). If a database table has additional fields, or its fields do not appear in the order expected by Mosel, we can append the database field names to the table name, for example:

 initializations from CNCTIO
  [VALUE,WEIGHT] as "BurgData(Item,Val,Wght)"
 end-initializations

The ODBC driver may take several options, see the section `mmodbc' in the `Mosel Language Reference Manual' for further detail.

Alternative implementation using SQL statements

The model Burglar2 may be implemented as follows using standard mmodbc functionality.

model "Burglar2 (SQL)"
 uses "mmxprs", "mmodbc"

 parameters
  CNCT = ''
 end-parameters
 ...
! Reading data from file
 SQLconnect(CNCT)
 SQLexecute("select * from BurgData", [VALUE,WEIGHT])
 SQLdisconnect
 ...
! Solution output
 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")    ! Cleaning up previous results: works
                                      ! only for databases, cannot be used
                                      ! with spreadsheets (instead, delete
                                      ! previous solutions directly in the
                                      ! spreadsheet file)
 SQLexecute("insert into SolTake values (?,?)" , SOLTAKE)
 SQLdisconnect
end-model

As before, the connection string CNCT indicates the data source:

• CNCT = "burglar.mdb" (the full form "DSN=MS Access Database;DBQ=burglar.mdb" is determined automatically) for the MS Access database burglar.mdb
• CNCT = "DSN=mysql;DB=burglar" for the mysql database burglar

All of these functions may be used in conjunction with the ODBC I/O driver. For instance, when working with a database it may be helpful to make the lines

 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")

preceed the insertion of the solution into the database to clean up previous results.