Accessing External Database Engines

Sheerpower can access data that is stored outside of Sheerpower. This lets a Sheerpower program read tables from external database engines such as Microsoft Access, Oracle, SQL Server, and any other systems that provide a Windows ODBC driver.

(Show/Hide External Database Access list)

The connection is made through ODBC. ODBC stands for Open Database Connectivity. It is a standard Windows interface that lets a program talk to many different database engines using the same basic style of code.

What Sheerpower Needs

Before Sheerpower can open an external database table, two things must already exist:

• the correct ODBC driver for the database engine
• a Windows data source that points to the database

The ODBC driver knows how to communicate with the database engine. The data source tells Windows which database file or server to use. Once those are set up, Sheerpower can open the external table.

The Big Idea

A Sheerpower program does not need a completely different style of code for every database engine. After the ODBC data source exists, the program opens the external table with an ordinary Sheerpower table statement.

For an external database table, the table name uses this form:

'table in datasource'

The first name is the table name inside the external database. The second name is the Windows ODBC data source name.

For example:

'contacts in mycontacts'

This means: open the Contacts table from the MyContacts ODBC data source.

Creating the ODBC Data Source

The data source is created in Windows. This is normally done once for each external database that Sheerpower needs to access.

Sheerpower is a 64-bit program, so it can only see 64-bit data sources built with 64-bit ODBC drivers. Windows ships with two ODBC administrator programs, one for 32-bit drivers and one for 64-bit drivers. Always use the 64-bit one.

1. Click the Start button and type ODBC.
2. Open ODBC Data Sources (64-bit).
3. Select the User DSN tab.
4. Click Add.
5. Choose the 64-bit ODBC driver for the database engine. For example, to use a Microsoft Access database, choose Microsoft Access Driver (*.mdb, *.accdb).
6. Click Finish.
7. Enter a data source name. The example uses MyContacts.
8. Click Select, then browse to the database file. The example file is:
   c:\sheerpower\samples\MyContacts.mdb
9. Click OK on each window. The new data source now appears under User Data Sources.

After this setup is complete, Sheerpower can use the data source name in an open table statement.

If the Access Driver Is Not Listed

The Microsoft Access driver is not always installed with Windows. If Microsoft Access Driver (*.mdb, *.accdb) does not appear in the driver list, download and install the 64-bit Microsoft Access Database Engine Redistributable from the Microsoft website, then repeat the steps above.

User DSN or System DSN

A User DSN is visible only to the Windows account that created it. If the Sheerpower program will run under a different account — for example, as a service or under SPINS — create the data source on the System DSN tab instead. The steps are the same.

Opening an External Table

The following program opens the Contacts table from the MyContacts ODBC data source. It extracts the records, sorts them by last name, and prints a contact list.

open table con: name 'contacts in mycontacts'

extract table con
  sort by con(LastName)
end extract

print "Contact List"
print

for each con
  print con(FirstName); ' '; con(LastName),              &
        con(MobilePhone), con(EmailName)
next con

close table con
end

How the Program Works

open table con: name 'contacts in mycontacts'

This opens the external table and gives it the local table name con. The phrase 'contacts in mycontacts' tells Sheerpower to open the Contacts table from the MyContacts ODBC data source.

extract table con ... end extract

This creates the collection of records. In this example, all records are extracted.

sort by con(LastName)

This sorts the extracted records ascending by the LastName field.

for each con ... next con

This loops through the extracted records one record at a time.

con(FieldName)

This reads a field from the current record. For example, con(FirstName) reads the FirstName field from the current contact record.

close table con

This closes the table and releases the external table.

Example Output

The output is a contact list sorted by last name:

Contact List

Steven Buchanan    (206) 555-1856    [email protected]
Nancy Davolio      (425) 555-9811    [email protected]
Andrew Fuller      (206) 555-6666    [email protected]

Why This Matters

External database engines can store data that is shared by many tools and applications. By using ODBC, Sheerpower can work with that data without requiring the data to be copied into a native Sheerpower table first.

This makes Sheerpower useful in environments where data already lives in an existing database system. The database engine stores and manages the data. Sheerpower opens the table, extracts records, sorts them, loops through them, and uses the fields just like a regular Sheerpower table.

The Main Point

To access an external database engine from Sheerpower, create an ODBC data source in Windows, then open the external table using this form:

open table  handle: name 'table in datasource'

Once the table is open, the rest of the code uses familiar Sheerpower table statements: extract, sort, for each, field access, close, etc.