TECH TIP
Accessing data through spreadsheets
By Kriss Rant
 
Recent support for Microsoft Open Database Connectivity (ODBC) in HP
IMAGE/SQL and ALLBASE/SQL allows for database access from many third-party PC
applications currently available on desktop. Two popular spreadsheet
applications, Microsoft Excel 5.0 and Lotus 1-2-3 Release 5 both recently
added support for ODBC.
 
ODBC is a standard application programming interface that enables third-party
Microsoft Windows applications and tools to access IMAGE/SQL, ALLBASE/SQL and
other databases in a client/server environment using structured query
language (SQL) as a standard for accessing data.
 
Getting started
 
Several components are required for implementing a client/server application.
The architecture requires that PCs be networked over a LAN connection to the
HP 3000 servers.
 
On the "client side," PCs are required with Microsoft Windows 3.1 and MS DOS
5.0 or later versions. It also is necessary to have a network protocol
product that supports TCP/IP Windows Sockets (WINSOCK) or SPX/IPX. WINSOCK is
supported by many PC network vendors such as Microsoft, Novell, and WRQ.
Support for the SPX/IPX protocol is provided by Novell Netware. The client
also will need HP ODBC A F0.16 or later. It comes bundled with IMAGE/SQL and
ALLBASE/SQL at no additional cost.
 
On the "server" side, an HP 3000 (MPE/iX 4.0 or later) is required. HP
ThinLan/iX is required for TCP/IP support. If SPX/IPX protocol is being used,
Netware/iX is also required. In addition, the server must have either HP
IMAGE/SQL B.F0.24 or later, or HP ALLBASE/SQL A.F0.67 or later. (ALLBASE/SQL
is available on both the HP 3000 and HP 9000.)
 
Using Microsoft Excel
 
Rather than try to describe how HP client/server technology can be used with
spreadsheet applications for business needs, we've set up a hypothetical
example involving inventory control for a manufacturing company. To monitor
stock levels, we will extract information out of a Parts IMAGE/SQL database
using Microsoft Excel.
 
The following steps outline the query process to extract the required
information out of the database:
 
o     Before starting a query in Microsoft Excel 5.0, it is necessary to set
up data sources. A data source describes the the database to which the user
wants to connect; the server on which the database resides; and the MPE/iX
logon. Data sources must be defined in the ODBC.INI file.
 
For information on how to configure data sources in the ODBC.INI file, see
the "readme.hp" file installed from the HP ODBC Driver Setup Disk. For
systems that use ALLBASE/SQL or IMAGE/SQL on MPE/iX 5.0, refer to the HP PC
API User's Guide.
 
In this example, "PARTS" data source has been configured and describes the
IMAGE/SQL database that contains inventory information.
 
o     Begin by verifying that the Data Access option in Microsoft Excel 5.0
is installed. Start Excel and look under the Data menu. If the last selection
is "Get External Data", then the Data Access option is installed. If it is
not installed, it can be added by running the Excel Setup program and
selecting the Data Access option.
 
o     Once the Data Access option is installed, select "Get External Data"
from the Data menu. As Microsoft Query begins, a dialogue box labeled "Select
Data Source" is displayed. Select "Other". A dialogue box labeled "ODBC Data
Sources" will be displayed with a list of the data sources defined in the
ODBC.INI file.
 
Click on the data source labeled "PARTS" and select "OK". The PARTS data
source name will be added to the Microsoft Query list of data sources in the
dialogue box labeled "Select Data Source". To connect to the PARTS data
source, click on the PARTS data source and select "Use".
 
o     When connected to the database, Query will display a dialogue box
labeled "Add Tables," with a list of available database tables. Click on the
table, "Inventory," and select "Add". Query will display a list of column
names for the table. Select "Close".
 
o     To retrieve all of the records in the underlying table, double-click
the asterisk in the column list. Records to be displayed can be limited by
specifying criteria. For more information, please see the Microsoft Query
User's Guide.
 
o     Once data has been selected, click on "Return Data to Microsoft Excel"
from the File menu. The data can now be manipulated in an Excel spreadsheet.
The data, however, is local to the spreadsheet and any change here will not
affect the data in the database.
 
Kriss Rant is a software engineer specializing in client/server in HP's
Commercial Systems Division's Research and Development Laboratory.
--------------------------------------