TECH TIP
Accessing IMAGE/SQL with Lotus 1-2-3
By Kriss Rant
 
This issue will describe how Lotus 1-2-3 Release 5 can be used access data in
an IMAGE/SQL database.
 
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 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. It 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 ALLBASE/SQL A.F0.67 or later. (ALLBASE/SQL is
available on both the HP 3000 and HP 9000.)
 
Any Microsoft Windows 3.1 application that supports ODBC can be used to
access IMAGE/SQL and ALLBASE/SQL databases using the same client/server
architecture.
 
Using Lotus 1-2-3
 
Let's take a hypothetical example involving inventory control for a
manufacturing company. To monitor stock levels, we will use Lotus 1-2-3 to
extract information out of an IMAGE/SQL database called "PARTS."
 
The following steps outline the query process to extract the required
information out of the database:
 
o     Before starting a query in Lotus 1-2-3, it is necessary to configure
1-2-3 for ODBC access. This requires three steps. The first step is to start
SHARE.EXE before you start Windows. To automatically start SHARE.EXE before
every Windows session, add the following line to your AUTOEXEC.BAT file:
 
C:\DOS\SHARE
 
     (See your DOS documentation for more information about SHARE.EXE.)
 
     The second step is to add the following line to your LOTUS.BCF file to
enable access to all ODBC sources:
 
DN="ODBC" DL="DLODBC" DD="All ODBC sources"
 
     The LOTUS.BCF file is located in \WINDOWS\LOTUSAPP\DATALENS. If you
upgraded to Lotus 1-2-3 Release 5 from a previous version of 1-2-3, this file
will be located in \LOTUSAPP\DATALENS. For more information on configuring
1-2-3 for ODBC access, double-click on the 1-2-3 Updates icon in the Lotus
Applications group in Windows (or the group where you installed 1-2-3).
 
     The last step is to define the "PARTS" data source in the ODBC.INI file.
In this example, the "PARTS" data source describes the name of the IMAGE/SQL
database we are querying, the server on which it resides and the MPE/iX
logon. For information on how to define data sources, please refer to the
documentation that comes with the HP ODBC software.
 
o     Start 1-2-3 and select "Database" from the Tools menu, and then "New
Query..." from the Database menu. A dialogue box labeled "New Query
Assistant" will be displayed. Select "External...." A dialogue box labeled
"Connect to External" will be displayed with a list of drivers that allow you
to connect to an external database. Click on the driver "ODBC" from the list
and select "Continue".
 
o     A list of the data sources defined in your ODBC.INI file will be
displayed in the "Connect to External" dialogue box. Click on the "PARTS"
data source from the list and select "Continue".
 
o     A list of the database tables from the PARTS data source will be
displayed in the "Connect to External" dialogue box . Click on the
"INVENTORY" table from the list; select "Continue"; and then "OK" to connect
to the table. The inventory table will appear in the "Select Database Table
to Query" text box in the "New Query Assistant" dialogue box.
 
o     To retrieve the records in the underlying table, select "OK." To limit
records returned, choose "Set Criteria..." prior to selecting "OK." By
default, 1-2-3 limits the number of records returned to the first 15 that
meet the criteria. For more information on setting criteria, refer to the
Lotus 1-2-3 User's Guide.
 
o     The data can now be manipulated as a 1-2-3 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.
---------------------------------------------------------------