SheerPower 4GL supports all ODBC compatible database engines such as Microsoft ACCESS and Oracle. ODBC stands for "Open Database Connectivity". ODBC is a universal database interface used to access a wide range of databases.

To access ANY ODBC database, the following are required:

• The appropriate ODBC database driver.
• A data source file that associates that driver and database.

All of the normal database structure statements are supported when accessing an ODBC database. See Chapter 15, Data Structure Statements.

To use ODBC in SheerPower 4GL, using Windows, you must first setup an ODBC data source. This must be done for every database that you want SheerPower to have access to.

The following example will illustrate how to setup an ODBC data source where the source is a Microsoft Access database. This example uses the "Contacts" table in a "MyContacts.mdb" sample database found inside the SheerPower\samples folder.

On Windows XP and Windows 2000, click on the Start menu button, then choose Settings, then go into the Control Panel.

Next, double-click on the Administrative Tools icon inside the Control Panel window.

Then double-click on the Data Sources (ODBC) shortcut inside the Administrative Tools window. The ODBC Data Source Administrator window will appear:

Example 17-1 ODBC Data Source Setup - ODBC Data Source Administrator

Inside the User DSN tab, click on the Add button on the right. The Create New Data Source window will appear:

Example 17-2 ODBC Data Source Setup - Create New Data Source

Select the Microsoft Access Driver (*.mdb) driver from the data source list, then click on the Finish button. The ODBC Microsoft Access Setup window will appear:

Example 17-3 ODBC Data Source Setup - ODBC Microsoft Access Setup

Inside the Data Source Name field, type in the name of the data source you are using. In this example, the name is MyContacts. You can then enter a description for the database inside the Description field:

Example 17-4 ODBC Data Source Setup - Data Source Name and Description

Next, click on the Select button to select the database you want SheerPower to access.

Example 17-5 ODBC Data Source Setup - Select Database

The Select Database window will appear.

For this example, we will select the MYCONTACTS.MDB sample database inside the SheerPower Samples folder. The default location is:

c:\sheerpower\samples

Use the Directories window on the right to browse for this database. Once the SheerPower Samples folder is open, MyContacts.mdb will appear in the Database Name window on the left:

Example 17-6 ODBC Data Source Setup - Select MyContacts Database

Highlight MyContacts.mdb, then click on the OK button.

Inside the ODBC Microsoft Access Setup window, click on the OK button:

Example 17-7 ODBC Data Source Setup - ODBC Microsoft Access Setup Complete

The MyContacts database will now appear in the list of User Data Sources inside the ODBC Data Source Administrator window. Click on the OK button to close out the window:

Example 17-8 ODBC Data Source Setup - ODBC Microsoft Access Setup Complete 2

SheerPower will now be able to access this database.

To access an ODBC database with SheerPower, you just specify the table to access inside the database in the OPEN STRUCTURE statement of the source code.

For example, to access the Contacts table inside the MyContacts.mdb database:

Example 17-9 Accessing ODBC Database in SheerPower OPEN STRUCTURE Statement

open structure con: name 'contacts in mycontacts' extract structure con sort ascending by con(LastName) end extract print 'Contact List' print for each con print con(FirstName); ' '; con(LastName), con(MobilePhone), con(EmailName) next con close structure con end Contact List Steven Buchanan (206) 555-1856 StevenB@hfs.com Nancy Davolio (425) 555-9811 nancy@anywhere.com Andrew Fuller (206) 555-6666 andrewf@anywhere.com Janet Leverling (206) 555-7777 janetl@anywhere.com Margaret Peacock (206) 555-5555 margiep@anywhere.com

17.2 Accessing Password Protected ODBC Databases

If the ODBC database you are using requires a username and password to access it, the following format is used in SheerPower:

?user=xxx&password=yyy

The code could look like the following:

dbuser$ = 'dbadmin' dbpass$ = 'dbpassword' mycontacts$ = 'Contact in MyContact?user=' + dbuser$ + '&password=' + dbpass$ open structure d: name mycontacts$

SheerPower Internet Services (SPINS) Webserver comes bundled with SheerPower 4GL. The SPINS webserver allows anyone to make web-based applications, even on their own local computers not connected to the Internet, without having to purchase a webserver license. And, of course, the SPINS webserver is EASY to install and use!

For more on writing network and web-based applications with SheerPower 4GL, see Section 19.3, Webserver CGI Interface.

By default, the SPINS webserver (spins_webserver.exe) is installed to the following directory:

Example 18-1 SPINS Location

\sheerpower\sphandlers\

This will assume that the root folder is:

Example 18-2 Default Root Folder Location

\sheerpower\sphandlers\wwwroot\

SPINS_webserver expects the directory structure to be:

Example 18-3 Expected Directory Structure

[wherever SPINS_webserver.exe is] | [wwwroot]--> things like INDEX.HTML | | [images] [scripts]

If you are replacing IIS with the SPINS_webserver, you would do the following inside the Command Prompt program:

Example 18-4 Replacing IIS with SPINS

c:> \sheerpower\sphandlers\spins_webserver.exe -wwwroot "c:\inetpub\wwwroot"

The -wwwroot option tells the SPINS webserver where the root folder is.

To run SPINS webserver, the Microsoft IIS webserver must be stopped or a different port specified for either webserver. Before running SPINS webserver for the first time:

• Stop Microsoft IIS webserver:
  
  Start --> Settings --> Control Panel --> Administrative Tools --> Internet Information Services
  
  Right-click on your Web Site from the list on the left (or Default Web Site) and choose Stop
  
  The web site name will now display in the list with a red circle with a white "x" through it to show that it's stopped.
  
  
• Delete the old /sheerpower/sphandlers/spiis.gblpool file.
  
  
• Open the Command Prompt program and type in the following command:
  
  c:> iisreset
  
  The Command Prompt window will display:
  
  

  Attempting stop... Internet services successfully stopped Attempting start... Internet services successfully restarted

  
  
  But the IIS webserver will remain stopped.
  
  

• Run SPINS_webserver by double-clicking on \sheerpower\sphandlers\spins_webserver.exe. Currently SPINS runs in a Command Prompt window.

To specify a different port number for SPINS to use, see Section 18.1.3, Specify a Different Port Number.