This module provides convenient access to the database classes (Db) of wxWindows. These classes have been donated to the wxWindows library by Remstar International. (Note: these classes are not supported on MacOS X at the time of writing (november 2003)). These database objects support ODBC connections and have been tested with wxWindows on the following databases:

Oracle (v7, v8, v8i), Sybase (ASA and ASE), MS SQL Server (v7 - minimal testing), MS Access (97 and 2000), MySQL, DBase (IV, V) (using ODBC emulation), PostgreSQL, INFORMIX, VIRTUOSO, DB2, Interbase, Pervasive SQL .

The database functions also work with console applications and do not need to initialize the WXCore libraries.

The examples in this document are all based on the pubs database that is available in MS Access 97 and 'comma separated text' format from http://wxhaskell.sourceforge.net/download/pubs.zip. We assume that your system is configured in such a way that pubs is the datasource name of this database. (On Windows XP for example, this is done using the /start - settings - control panel - administrative tools - data sources (ODBC)/ menu.)

The available data sources on your system can be retrieved using dbGetDataSources. Here is an example from my system:

 *Main> dbGetDataSources >>= print
 [("pubs","Microsoft Access Driver (*.mdb)")]

Connections are established with the dbWithConnection call. It takes a datasource name, a user name, a password, and a function that is applied to the resulting database connection:

 dbWithConnection "pubs" "" "" (\db -> ...)

(Note that most database operations automatically raise a database exception (DbError) on failure. These exceptions can be caught using catchDbError.)

The resulting database (Db) can be queried using dbQuery. The dbQuery call applies a function to each row (DbRow) in the result set. Using calls like dbRowGetValue and dbRowGetString, you can retrieve the values from the result rows.

 printAuthorNames
   = do names <- dbWithConnection "pubs" "" "" (\db ->
                  dbQuery db "SELECT au_fname, au_lname FROM authors" 
                    (\row -> do fname <- dbRowGetString row "au_fname"
                                lname <- dbRowGetString row "au_lname"
                                return (fname ++ " " ++ lname)
                    ))
        putStrLn (unlines names)

The overloaded function dbRowGetValue can retrieve any kind of database value (DbValue) (except for strings since standard Haskell98 does not support overlapping instances). For most datatypes, there is also a non-overloaded version, like dbRowGetInteger and dbRowGetString. The dbRowGet... functions are also available as dbRowGet...Mb, which returns Nothing when a NULL value is encountered (instead of raising an exception), for example, dbRowGetIntegerMb and dbRowGetStringMb.

If necessary, more data types can be supported by defining your own DbValue instances and using dbRowGetValue to retrieve those values.

You can use dbRowGetColumnInfo to retrieve column information (ColumnInfo) about a particular column, for example, to retieve the number of decimal digits in a currency value.

Complete meta information about a particular data source can be retrieved using dbGetDataSourceInfo, that takes a data source name, user name, and password as arguments, and returns a DbInfo structure:

 *Main> dbGetDataSourceInfo "pubs" "" "" >>= print
 catalog: C:\daan\temp\db\pubs2
 schema :
 tables :
  ...
  8: name   : authors
     type   : TABLE
     remarks:
     columns:
      1: name   : au_id
         index  : 1
         type   : VARCHAR
         size   : 12
         sqltp  : SqlVarChar
         type id: DbVarChar
         digits : 0
         prec   : 0
         remarks: Author Key
         pkey   : 0
         ptables: []
         fkey   : 0
         ftable :
      2: name   : au_fname
         index  : 2
         type   : VARCHAR
  ...

Changes to the database can be made using dbExecute. All these actions are done in transaction mode and are only comitted when wrapped with a dbTransaction.

Execute a SQL query against a database. Takes a function as argument that is applied to every database row (DbRow). The results of these applications are returned as a list. Raises a DbError on failure.

  do names <- dbQuery db "SELECT au_fname FROM authors" 
                (\row -> dbRowGetString row "au_fname")
     putStr (unlines names)

Execute a SQL query against a database. Takes a function as argument that is applied to every row in the database. Raises a DbError on failure.

  dbQuery_ db "SELECT au_fname FROM authors" 
    (\row -> do fname <- dbRowGetString row "au_fname"
                putStrLn fname)

Execute an IO action as a transaction on a particular database. When no exception is raised, the changes to a database are committed. Always use this when using dbExecute statements that update the database.

 do dbWithConnection "pubs" "" "" $ \db -> 
     dbTransaction db $
       dbExecute db "CREATE TABLE TestTable ( TestField LONG)"

Get the complete meta information of a data source. Takes the data source name, a user id, and password as arguments.

 dbGetDataSourceInfo dsn userid password
   = dbWithConnection dsn userId password dbGetInfo 

Automatically raise a database exception when False is returned. You can use this method around basic database methods to conveniently throw Haskell exceptions.

  dbHandleExn db $ dbExecSql db "SELECT au_fname FROM authors"