Chapter 14

ODBC Interface

In order to utilize the features shown in this section, it is assumed that required environment is in place on the user’s computer. The elements of this environment include an ODBC Driver Manager, a database server such as SQL server, appropriate accounts with password, etc.

The basic element of the OmniBasic to ODBC interface is the DATASET Object. All communication to and from the ODBC connection is through an instance of this object.

An instance of the DATASET Object is created using the CONSTRUCT Statement as follows:

CONSTRUCT MyDataObject AS DATASET

The first step to making the ODBC connection is to log on as follows:

MyDataObject.LOGON “UID=sa; PWD=; DSN=my_db.”

Once the LOGON has established the connection, the normal SQL commands are sent to the server and data transfers and queries occur as desired.

To conclude the connection, the LOGOFF method is used as follows:

MyDataObject.LOGOFF

The other properties and methods of the DATASET Object are the basic SQL commands expressed in object.method notation. These are:

MyDataObject.DROP “drop…”

MyDataObject.CREATE “create…”
MyDataObject.INSERT “insert…”
MyDataObject.SELECT “select…”
MyDataObject.DELETE “delete…”
MyDataObject.UPDATE “update…”

In the above commands the string in quotes indicates a legal SQL command string and may be expressed as a string variable or quoted literal. To send the value of a numeric variable, you must build the command string using concatenation and convert your variable value using the STR$() function.

To extract data from the server, the SELECT Statement is used to initiate the transfer. Next a loop is typically set up which tests for end-of-file (EOF) and then extracts the data one element at a time. The extraction of each element is accomplished as follows:

MyDataObject.GETDATA VarName

Where VarName is a simple dimensioned variable of the type required to receive the incoming data. If the variable receiving the data is of the wrong type, an error will occur.

To detect an error after any ODBC operation, simply test the special variable STATUS. A -1 indicates an error on the last operation. The normal result is 0.

Code snippet from actual working program to illustrate some of the commands covered.

************ Initialize ************
CONSTRUCT ZipData AS DATASET' Construct the datase object
ZipData.LOGON "UID=sa;PWD=;DSN=my_db"' Logon to database
IF STATUS<>0 THEN
   MSGBOX ERROR,"LOGON ODBC","Connection Failed"
   END
ENDIF

************ Events ************
FindButton.CLICK
CityText.TEXT=""
StateText.TEXT=""
EnterStr=ZipText.TEXT
TempStr="select city, state from zipcode where zip='"+EnterStr+"'"
ZipData.SELECT TempStr' Send SQL Command to ODBC
IF STATUS<>0 THEN
   MSGBOX ERROR,"SELECT ODBC","Select Failed"
    END
ENDIF
WHILE NOT(EOF(ZipData)) DO' Loop to pull out the data
   ZipData.GETDATA EnterStr
   IF STATUS<>0 THEN
      MSGBOX ERROR,"GETDATA ODBC","GETDATA Failed"
      END
   ENDIF
   CityText.TEXT=EnterStr
   ZipData.GETDATA EnterStr
   IF STATUS<>0 THEN
      MSGBOX ERROR,"GETDATA ODBC","GETDATA Failed"
      END
   ENDIF
   StateText.TEXT=EnterStr
ENDWHILE
EXIT EVENT

ExitButton.CLICK
ZipData.LOGOFF
END
EXIT EVENT

Next Page: Blank
Table of Contents