Database classes overview

wxCLIPS function groups: Database, Recordset

wxCOOL classes: wxDatabase, wxRecordSet

wxCLIPS provides a set of classes for accessing a subset of Microsoft's ODBC (Open Database Connectivity) product. Currently, this wrapper is available under MS Windows only, although ODBC may appear on other platforms, and a generic or product-specific SQL emulator for the ODBC classes may be provided in wxWindows at a later date.

ODBC presents a unified API (Application Programmer's Interface) to a wide variety of databases, by interfacing indirectly to each database or file via an ODBC driver. The language for most of the database operations is SQL, so you need to learn a small amount of SQL as well as the wxCLIPS ODBC wrapper API. Even though the databases may not be SQL-based, the ODBC drivers translate SQL into appropriate operations for the database or file: even text files have rudimentry ODBC support, along with dBASE, Access, Excel and other file formats.

The run-time files for ODBC are bundled with many existing database packages, including MS Office.

The minimum you need to distribute with your application is odbc.dll, which must go in the Windows system directory. For the application to function correctly, ODBC drivers must be installed on the user's machine. If you do not use the database classes, odbc.dll will be loaded but not called (so ODBC does not need to be setup fully if no ODBC calls will be made).

A sample is distributed with wxCLIPS in examples/odbc.

Procedures for writing an ODBC application
Examples
Database overview
Recordset overview
ODBC SQL data types
A selection of SQL commands

Procedures for writing an ODBC application

You first need to create a Database object. If you want to get information from the ODBC manager instead of from a particular database (for example using recordset-get-data-sources), then you do not need to call database-open. If you do wish to connect to a datasource, then call database-open. You can reuse your Database object, calling database-close and database-open multiple times.

Then, create a Recordset object for retrieving or sending information. For ODBC manager information retrieval, you can create it as a dynaset (retrieve the information as needed) or a snapshot (get all the data at once). If you are going to call recordset-execute-sql, you need to create it as a snapshot. Dynaset mode is not yet implemented for user data.

Having called a function such as recordset-execute-sql or recordset-get-data-sources, you may have a number of records associated with the recordset, if appropriate to the operation. You can now retrieve information such as the number of records retrieved and the actual data itself. Use functions such as recordset-get-int-data or recordset-get-char-data to get the data, passing a column index or name. The data returned will be for the current record. To move around the records, use recordset-move-next, recordset-move-prev and associated functions.

You can use the same recordset for multiple operations, or delete the recordset and create a new one.

Note that when you delete a Database, any associated recordsets also get deleted, so beware of holding onto invalid pointers.

Examples

Here's an example of a function that updates a value in a database.

;;; Function for updating a field in a record in the incident.dbf demo
;;; file.
;;; E.g. (demo-update-integer "BD34" "X" 999)
;;; The key is the ASSET column, BD34 in the example. Record(s) matching
;;; this key will be changed.
;;; "X" is the name of the column to be updated.
;;; 999 is a value to replace the current value.
;;;
;;; You must have previously registered the file incident.dbf
;;; with ODBC (e.g. from the control panel), with the source
;;; name "wxCLIPS demo". You can check if the file has changed
;;; by using Microsoft Query.

(deffunction demo-update-integer (?asset ?col ?value)
  (bind ?database (database-create))

  ;; Open data source
  (if (eq 0 (database-open ?database "wxCLIPS demo")) then
   (bind ?msg (database-get-error-message ?database))
   (printout t ?msg crlf)
   (return 0)
  )

  ;; Create a recordset
  (bind ?recordset (recordset-create ?database "wxOPEN_TYPE_SNAPSHOT"))

  ;; Construct an SQL statement
  (bind ?sql (str-cat "UPDATE Incident SET " ?col " = " ?value " WHERE ASSET = '" ?asset "'"))
  (printout t ?sql crlf)

  ;; Execute the SQL.
  (if (eq 0 (recordset-execute-sql ?recordset ?sql)) then

   (bind ?msg (database-get-error-message ?database))
   (printout t ?msg crlf)
   (return 0)
  )

  (recordset-delete ?recordset)
  (database-close ?database)
  (database-delete ?database)
  (return 1)
)

;;; Function for returning the value of an integer field.
;;; E.g. (demo-get-integer "BD34" "X")
;;; The key is the ASSET column, BD34 in the example. The first record matching
;;; this key will be returned.
;;; "X" is the name of the column whose value is to be returned.

(deffunction demo-get-integer (?asset ?col)
  (bind ?database (database-create))

  ;; Open data source
  (if (eq 0 (database-open ?database "wxCLIPS demo")) then
   (bind ?msg (database-get-error-message ?database))
   (printout t ?msg crlf)
   (return 0)
  )

  ;; Create a recordset
  (bind ?recordset (recordset-create ?database "wxOPEN_TYPE_SNAPSHOT"))

  ;; Construct an SQL statement
  (bind ?sql (str-cat "SELECT * FROM Incident WHERE ASSET = '" ?asset "'"))
  (printout t ?sql crlf)

  ;; Execute the SQL.
  (if (eq 0 (recordset-execute-sql ?recordset ?sql)) then

   (bind ?msg (database-get-error-message ?database))
   (printout t ?msg crlf)
   (return 0)
  )

  ;; Get the relevant field of the first record
  (bind ?data (recordset-get-int-data ?recordset ?col))

  (recordset-delete ?recordset)
  (database-close ?database)
  (database-delete ?database)
  (return ?data)
)

  (bind ?*database* (database-create))
  (bind ?*recordset* (recordset-create ?*database* "wxOPEN_TYPE_SNAPSHOT"))

  ;;; Get the list of currently-defined ODBC sources
  (if (eq 0 (recordset-get-data-sources ?*recordset*)) then

   (show-database-error) else

   ;;; Loop through all the source names (one per record)
   (bind ?cont 1)
   (while (eq ?cont 1)
    ;;; The source name is at the first column (0) in the record
    (bind ?data (recordset-get-char-data ?*recordset* 0))
    (list-box-append ?*sources-listbox* ?data)
    (bind ?cont (recordset-move-next ?*recordset*))
   )
  )

Database overview

Database classes overview

Function group/class: Database/wxDatabase

Every database object represents an ODBC connection. To do anything useful with a database object you need to create a Recordset object. All you can do with Database is opening/closing connections and getting some info about it (users, passwords, and so on).

Recordset overview

Database classes overview

Function group/class: Recordset/wxRecordSet

Each Recordset represents a database query. You can make multiple queries at a time by using multiple Recordsets with a Database or you can make your queries in sequential order using the same Recordset.

If Recordset is of the type wxOPEN_TYPE_DYNASET, there will be only one field for each column, which will be updated every time you call functions like recordset-move or recordset-goto. If Recordset is of the type wxOPEN_TYPE_SNAPSHOT, all records returned by an ODBC function will be loaded at once.

ODBC SQL data types

Database classes overview

These are the data types supported in ODBC SQL. Note that there are other, extended level conformance types, not currently supported in wxCLIPS.

CHAR(n)	A character string of fixed length n.
VARCHAR(n)	A varying length character string of maximum length n.
LONG VARCHAR(n)	A varying length character string: equivalent to VARCHAR for the purposes of ODBC.
DECIMAL(p, s)	An exact numeric of precision p and scale s.
NUMERIC(p, s)	Same as DECIMAL.
SMALLINT	A 2 byte integer.
INTEGER	A 4 byte integer.
REAL	A 4 byte floating point number.
FLOAT	An 8 byte floating point number.
DOUBLE PRECISION	Same as FLOAT.

These data types correspond to the following ODBC identifiers:

SQL_CHAR	A character string of fixed length.
SQL_VARCHAR	A varying length character string.
SQL_DECIMAL	An exact numeric.
SQL_NUMERIC	Same as SQL_DECIMAL.
SQL_SMALLINT	A 2 byte integer.
SQL_INTEGER	A 4 byte integer.
SQL_REAL	A 4 byte floating point number.
SQL_FLOAT	An 8 byte floating point number.
SQL_DOUBLE	Same as SQL_FLOAT.

A selection of SQL commands

Database classes overview

The following is a very brief description of some common SQL commands, with examples.

Create

Creates a table.

Example:

CREATE TABLE Book
 (BookNumber     INTEGER     PRIMARY KEY
 , CategoryCode  CHAR(2)     DEFAULT 'RO' NOT NULL
 , Title         VARCHAR(100) UNIQUE
 , NumberOfPages SMALLINT
 , RetailPriceAmount NUMERIC(5,2)
 )

Insert

Inserts records into a table.

Example:

INSERT INTO Book
  (BookNumber, CategoryCode, Title)
  VALUES(5, 'HR', 'The Lark Ascending')

Select

The Select operation retrieves rows and columns from a table. The criteria for selection and the columns returned may be specified.

Examples:

SELECT * FROM Book

Selects all rows and columns from table Book.

SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0

Selects columns Title and RetailPriceAmount from table Book, returning only the rows that match the WHERE clause.

SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'

Selects all columns from table Book, returning only the rows that match the WHERE clause.

SELECT * FROM Book WHERE CatCode IS NULL

Selects all columns from table Book, returning only rows where the CatCode column is NULL.

SELECT * FROM Book ORDER BY Title

Selects all columns from table Book, ordering by Title, in ascending order. To specify descending order, add DESC after the ORDER BY Title clause.

SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0

Selects records where RetailPriceAmount conforms to the WHERE expression.

Update

Updates records in a table.

Example:

UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'

This example sets a field in column 'X' to the number 123, for the record where the column ASSET has the value 'BD34'.