ODBC Programming Overview

From CometWiki

Revision as of 08:57, 29 May 2009

ODBC Programming Overview

As mentioned above, ODBC is implemented as an API. This means that ODBC commands are integrated into the application programming language. In Internet Basic, all ODBC commands are invoked via the CONTROL statement.

Here is the syntax for the CONTROL statement with ODBC commands:

<Result> = CONTROL(<Lun>, <CommandString>)

where:

<Lun> is the logical unit number of the ODBC gateway

<CommandString> is an ODBC command followed by optional parameters separated by a semi-colon.

<Result> contains the returned data and/or error string.

•	If a command is successful, <Result> begins with a “+”followed by any other returned information.
•	If a command fails, <Result> begins with a “-“ followed by additional error information.

Example 1:

Here is how to open the sample Northwind Traders database. Your program must use the dbOpen command and specify the data source name. The Internet Basic statements would look like this:

Length 254 & Local Result$
Set ODBC = 1
	Open (ODBC) “G00”	! open ODBC gateway
	Result$ = CONTROL(ODBC,”dbOpen:DSN=Northwind”)

Example 2:

Here is how to set the database options to report StateNativeOrigin error codes and use the cursor library.

Length 254 & Local Result$
Set ODBC = 1

Open (ODBC) “G00” ! open ODBC gateway

	Result$ = Control(ODBC, "dbSetOptions:ErrorCodes;UseCursorLib")

If (Sub(Result$, 1, 1) EQ “-“) Print “Error:”;Result$8 Goto Error Return EndIf

Some ODBC commands must be followed immediately by a Internet Basic WRITE in order to supply additional information to the driver. An example is the dbDefineRecordset command, where the recordset definition map (i.e., record layout) must be provided to the ODBC driver immediately after the dbDefineRecordset command is issued.

A recordset definition map consists of the field names and field sizes (as they exist in the ODBC database) that will be used in the Internet Basic program. The syntax is:

fieldname, size; fieldname, size; fieldname, size (etc.) The following example shows a recordset definition map for three database fields, FirstName (25 characters), LastName (25 characters), and Age (numeric, 3.0). Note that this information is written to the ODBC driver immediately after the recordset has been defined.

Example 3:

 100	! This FORMAT contains the recordset definition map 

 200	RSDefinition: FORMAT “FirstName, 25; LastName, 25 ; Age, 3.0”

 300       Length 254 & Local Result$
 400      Set ODBC = 1

 500	Open (ODBC) “G00”	! open ODBC gateway

 600	! Define a recordset named “MyRecordset”

 700     Result$ = Command(Lun, “dbDefineRecordset:MyRecordset”)

 800     If (Sub(Result$, 1, 1) EQ "+") 	! Successful
 900	! Definition map MUST be provided immediately
1000       Write (Lun, RSDefinition) Excp = BadDefinition
1100       EndIf