ODBC Sample Programs

From CometWiki

Sample Programs

Signature Systems has created several sample Internet Basic programs that demonstrate some of the features in the Comet ODBC Client Gateway. These programs are available from the Signature web site (www.signature.net/download/odbc) and are contained in the ODBCDEMO.ZIP file.

The ODBCDEMO.ZIP file includes:

1.	#EMP		(sample Internet Basic source program, text file)
2.	#INQ		(sample Internet Basic source program, text file)
3.	#NEWTBL	        (sample Internet Basic source program, text file)
4.	#XL		(sample Internet Basic source program, text file)
5.	PEOPLE.XLS	(sample Excel spreadsheet)

We suggest that you place these files in a dedicated Comet directory. The source programs contain compiler commands with the directory name “ODB”, but you can change this to suit your needs.

Add the source programs to the QDIR (using Comet Utility #13) and compile the programs.

The first three sample programs connect with the “Northwind Traders” database, so make sure to configure that data source (as explained in the “Getting Started” section above).

The fourth program (#XL) retrieves data from an Excel spreadsheet. See below for more information on configuring that data source.

Here is a brief description of the sample programs. For more information, see the comments in the source programs.

1. #EMP

This program retrieves data from the “Employees” table in the “Northwind Traders” database, and displays that data on the Comet screen.

This very simple program demonstrates the most basic of ODBC commands, including dbOpen, dbClose, dbDefineRecordset, rsOpen, rsMoveNext, etc.

The rsOpen command shows how SQL commands can be imbedded in an Internet Basic program. In this case, the following SQL command retrieves and sorts data from the “Employees” table:

SELECT LastName,FirstName,HomePhone FROM Employees ORDER BY LastName

This is coded in the sample program as follows:

Cmd$ = "rsOpen:SELECT LastName,FirstName,HomePhone " +_ "FROM Employees ORDER BY LastName" Result$ = CONTROL(ODBC,Cmd$)

2. #INQ This program retrieves data from several tables in the “Northwind Traders” database, including Suppliers, Products, Customers, Orders, and Order Details.

This program reads records from Northwind and outputs them to an HTML file. When the retrieval process is done, this program launches the HTML file and your browser displays the results.

Note that the HTML file is created on a Comet directory specified by the WorkDir$ symbolic constant.

3. #NEWTBL

This program creates a new table in the “Northwind Traders” database. When you run this program for the first time, you'll get an error telling you that the table doesn't exist. Acknowledge the error and the program will continue.

4. #XL This program, which is nearly identical to #EMP, retrieves data from an Excel spreadsheet.

The spreadsheet is named PEOPLE.XLS, and it contains a subset of the data in the Employees table of the “Northwind Traders” database.

To configure this spreadsheet as a data source:

a.	go to the Control Panel
b.	select ODBC Data Sources (32-bit)
c.	at the User DSN tab, choose the Add option
d.	select the Microsoft Excel Driver and click on the Finish button
e.	in the Data Source Name field, type: People
f.	click on the Select Workbook button
g.	locate the PEOPLE.XLS spreadsheet, then click on the OK button
h.	exit from the Control Panel

As you can see, a single Excel spreadsheet is treated the same as an entire Access database. Since the Data Source Name is People, the following statements are included in the #XL program to open this data source.

Cmd$ = "dbOpen:DSN=People"
Result$ = CONTROL(ODBC,Cmd$)

Within a spreadsheet, you can create named areas that are the equivalent of database tables. The column headings in a named area are the ODBC field names.

To create a named area in Excel:

a.	highlight the cells that you want to include in the named area, including the column headings (these must be the first row of the named area)
b.	from the Insert menu, choose Name  Define
c.	type a name for the highlighted area and click on the OK button

The following spreadsheet shows a rectangular area named Employees. Notice that the column headings (LastName, FirstName, and HomePhone) are exact matches to the ODBC field names specified in the #XL program.