|
|
|
by Dawna Whitehead
M.B. Foster Associates Limited
Overview
ODBCLink/SE is a replacement for the HP PC API. It is an implementation of
Microsoft's Open Database Connectivity (ODBC) interface that enables Microsoft
Windows based applications and tools to access IMAGE/SQL on the HP 3000 and
ALLBASE/SQL on the HP 3000 and HP 9000, in a client/server environment.
ODBCLink/SE is a part of IMAGE/SQL and ALLBASE/SQL.
In this environment, application developers and end-users can take advantage
of the PC's graphical user interface (GUI) and processing power, while relying
on the security, integrity, and database management capabilities of
ALLBASE/SQL and IMAGE/SQL.
HP PC API with Gupta can co-exist with ODBCLink/SE, but it will no longer be
supported, and will not be distributed in MPE 6.0 and subsequent releases.
ODBCLink/SE server runs under MPE/iX 5.0 or higher on the HP 3000 server, or
under HP-UX Versions 9.x and 10.x on the HP 9000 Series 7xx and 8xx servers.
The client side runs under Microsoft Windows 3.1 and 3.11, Windows95, or
WindowsNT V3.51 and V4.0. Connection via Winsock is available in both the
16-bit and the new 32-bit version.
The driver can be used in two ways: by direct calls from a Windows program,
such as Foxbase, or through an ODBC-compliant application, such as Microsoft
Access or Visual Basic.
The ODBC LongVarBinary data type is supported for binary large
objects (BLOBs), such as compressed photographs or document images.
ODBC Level 2 Compliance
ODBCLink/SE is 95 percent ODBC Level 2 compliant. These exceptions are not
supported:
- SQLBrowseConnect
- SQLExtendedFetch
- SQLSetPos
- SQLSetStmtOptions
- Scalar functions
- SQL_BIT, SQL_TINYINT, and SQL_BIGINT data types
Client/Server Interface
The networked HP 3000 Series 9xx with ALLBASE/SQL and IMAGE/SQL (or the
HP 9000 Series 7xx or 8xx with ALLBASE/SQL) provide the relational database
environment on the server.
Security is provided by the MPE/iX or HP-UX logon system and ALLBASE/SQL or
IMAGE/SQL.
ODBCLink/SE connects the client to the server with a collection of dynamic
link libraries (DLLs) on the client and runs under Microsoft Windows 3.1 and
3.11, Windows95, or WindowsNT 3.51 and 4.0.
Client application requests are routed over the network to an ALLBASE/SQL or
IMAGE/SQL database on the server, and replies are returned to the client
application. A listener process establishes the connection between the client
application and the target database on the server. The listener works with HP
ThinLAN 3000/iX on the HP 3000 or ARPA Services on the HP 9000.
With ODBCLink/SE and application software on the client, you can develop SQL
applications, generate reports, and query ALLBASE/SQL or IMAGE/SQL on the
server. The tested client applications include the following as well as many
more:
Table 10-3 ODBCLink/SE Tested Client Applications
| Client |
Client |
| Cognos Axiant | Paradox |
| Cognos Impromptu | PowerBuilder |
| Jetform | Visual Basic |
| Lotus 1-2-3 | Visual |
| Microsoft Access | Visual FoxPro |
| Microsoft Query | |
Requirements
HP 3000 server requirements:
- MPE/iX Version 5.0 or 5.5 (or later)
- IMAGE/SQL Version B.G2.03 (or later)
- ALLBASE/SQL Version A.G2.00 (or later)
- ThinLANLink/XL must be configured and turned on
HP 9000 server requirements:
- HP-UX Version 9.0 or 10.0 (or later)
- ALLBASE/SQL Version A.G2.01 (or later)
- ARPA Services must be configured and turned on
Client requirements:
- 486 processor at a minimum, preferably a Pentium
- 8 MB memory at a minimum, preferably 16 MB
- 5 MB of free disk space
- Windows 3.1 or 3.11, Windows95, or WindowsNT V3.51 or 4.0
- Microsoft or WRQ TCP/IP software
Software provided:
- Server program for the HP 3000 or HP 9000 as appropriate
- Client programs for both 16- and 32-bit platforms.
When using a 32-bit operating system, you should use the 32-bit driver
if possible. However, if you have a 16-bit application running under
Win95 or WinNT, you must install the 16-bit version of the driver. On
Windows95 or WindowsNT, you can run both the 16- and 32-bit versions of
ODBCLink/SE. 16- and 32-bit data-sources are maintained separately and
coexist.
- Dr. DeeBeeSpy from Syware Inc. for tracing calls to the ODBC driver
Migration from HP PC API
A data migration tool is provided to migrate data sources created for HP PC
API to ODBCLink/SE. Once the data sources are identified, the translation is
performed automatically.
Performance Considerations
ODBC access to the server is very fast. You should note the following
performance exceptions:
- Reading and sorting a large table may prevent the server from returning
control to the client until the entire database has been read and
sorted.
- Opening a large table in MS-Access will cause a screen full of data to
be displayed and then appear to wait for user input. In many cases, data
is still being downloaded to the client. Careful thought needs to be
employed before granting ODBC access to large tables on the server.
- Using dynamic substitution parameters speeds up your application, if you
are writing your own SQL to do multi-row inserts or updates.
- In general, ODBCLink/SE will not complete any request faster than ISQL
would, and could be slower due to network overhead.
Supported Functions and Commands
These are the ALLBASE/SQL and IMAGE/SQL functions and commands supported by
ODBCLink/SE:
- The complete ALLBASE/SQL and IMAGE/SQL syntax dynamic SQL
- All SQL functions
You can use the ODBC SQLGetFunctions call to obtain a list of the
supported ODBCLink/SE driver functions.
Areas Not Fully Implemented
Some areas of certain functions are not fully implemented. They are as follows:
Supported Data Types
ODBCLink/SE supports ALLBASE/SQL and IMAGE/SQL data types.
The following table shows the correspondence between ALLBASE/SQL or IMAGE/SQL
data types and ODBC data types.
Table 10-6 Supported Data Types
| ALLBASE/SQL or IMAGE/SQL Data Type |
ODBC Data Type |
| CHAR, max length <255 | SQL_CHAR |
| CHAR, max length >=255 | SQL_LONGVARCHAR |
| VARCHAR, max length <255 | SQL_VARCHAR |
| VARCHAR, max length >=255 | SQL_LONGVARCHAR |
| BINARY, length <256 | SQL_BINARY |
| BINARY, length >=256 | SQL_LONGVARBINARY |
| VARBINARY <256 | SQL_VARBINARY |
| VARBINARY >=256 | SQL-LONGVARBINARY |
| LONG BINARY | SQL_LONGVARBINARY |
| LONG VARBINARY | SQL_LONGVARBINARY |
| INTEGER (32-bit) | SQL_INTEGER |
| SMALLINT (16-bit) | SQL_SMALLINT |
| DECIMAL (Internal representation is packed decimal) | SQL_DECIMAL |
| Image Zoned (Z) | SQL_DECIMAL |
| Image Packed (P) | SQL_DECIMAL |
| Image I3 (48-bit integer) | SQL_DECIMAL |
| Image I4 (64-bit integer) | SQL_DECIMAL |
| FLOAT(24), REAL, or Image E2 | SQL_REAL |
| FLOAT(53), DOUBLE PRECISION, or Image E4 | SQL_DOUBLE |
| Image R2 (non-IEEE float) | SQL_REAL |
| Image R4 (non-IEEE float) | SQL_DOUBLE |
| DATE | SQL_DATE |
| TIME | SQL_TIME |
| DATETIME | SQL_TIMESTAMP |
| INTERVAL | SQL_CHAR |
ALLBASE/SQL Storage space for BLOBs should be created using a LONG VARBINARY
column.
Stored Procedures
ODBCLink/SE implements all types of ALLBASE/SQL stored procedures, including
those which return multiple-format results sets. You can list the stored
procedures available to your ODBC session by calling SQLProcedures
. Parameter information for stored procedures is obtained by calling
SQLProcedureColumns.
Features Specific to ODBCLink/SE
The following is a list of features that are unique or specific to ODBCLink/SE
and some details about them:
- Primary key name returned by SQLStatistics
Most ODBC applications including MS Access, Visual Basic, and
PowerBuilder require a unique primary key for a table to be
updatable.
- Reading or writing to ALLBASE/SQL LongVarBinary items.
Any file residing on the Hewlett-Packard server can be inserted into an
ALLBASE/SQL LongVarBinary field with the INSERT INTO
SQL command.
- Maximum number of statements
ODBCLink/SE supports up to 50 concurrent statements, or cursors, per
connection.
To use multiple statements when you are not writing your own SQL
application, tell ODBCLink/SE to have SQLGetInfo report more
than one statement per connection.
ALLBASE/SQL Restrictions
These are the ALLBASE/SQL and IMAGE/SQL restrictions on the ODBC grammar.
These are summarized in the following table:
Table 10-7 ALLBASE/SQL Restrictions
| Statement |
Programming Considerations |
| CREATE TABLE | UNIQUE PRIMARY KEY must follow NOT NULL |
| DROP TABLE | ALLBASE/SQL does not provide CASCADE or RESTRICT |
| REVOKE | ALLBASE/SQL does not provide RESTRICT |
- ALLBASE/SQL provides a DATETIME data type similar to
TIMESTAMP.
- ALLBASE/SQL does not implement optimistic locking.
Optimistic locking means that the DBMS does not lock data until just
before an update is made, thus improving concurrency because locks are
not held for long.
ANSI Character Set
The HP 3000 and HP 9000 both use the default ROMAN8 character set. Most
applications running under Microsoft Windows use the 8-bit ANSI (ISO 8859/1)
character set to provide support for Western European languages (including
American). The first 127 characters are the same for both ROMAN8 and ANSI
character sets, so only the extended and special characters are different.
If your client application uses these extended or special characters, then the
data from the server must be converted from ROMAN8 to ANSI, and the data
returning to the server must be converted from ANSI to ROMAN8. A translation
DLL has been provided to accomplish this task.
Unsupported ALLBASE/SQL and IMAGE/SQL Statements
There are several reasons why some ALLBASE/SQL and IMAGE/SQL statements are
not supported in the ODBCLink/SE environment:
- ODBCLink/SE does not support embedded SQL, therefore statements that
work only with embedded SQL are not supported. See details that
follow.
- Statements that relate to functions, such as connection, that have been
replaced by functions resident on the client. See details that
follow.
- Some functions are best restricted to the DBA; statements which control
these functions are accessible only through Interactive Structured Query
Language (ISQL) on the server.
- ODBCLink/SE internally prepares an SQL statement to be dynamically
preprocessed. Therefore, you cannot use some statements such as
EXECUTE IMMEDIATE.
Statements that Work Only with Embedded SQL
Client application software that accesses ALLBASE/SQL and IMAGE/SQL databases
through ODBCLink/SE does not support embedded SQL. The application software
calls the database functions directly; there is no need for preprocessing.
Because the following ALLBASE/SQL and IMAGE/SQL statements function only with
embedded SQL, they are not supported by ODBCLink/SE:
Table 10-8 Functions Not Supported by ODBCLink/SE
| Function |
Function |
| CLOSE CURSOR | BEGIN DECLARE SECTION |
| DECLARE CURSOR | DESCRIBE |
| END DECLARE SECTION | EXECUTE |
| EXECUTE IMMEDIATE | FETCH |
| INCLUDE | OPEN |
| PREPARE | REFETCH |
| SQLEXPLAIN | WHENEVER |
Statements Replaced by Functions on the Client
Some ALLBASE/SQL and IMAGE/SQL functions have been moved from the server to
the client with ODBCLink/SE.
The following ALLBASE/SQL and IMAGE/SQL statements are not supported because
they have been replaced by other functions resident on the client:
Table 10-9 Replaced Functions
| Replaced Function |
Replaced Function |
| CONNECT | DISCONNECT |
| RELEASE | RESET |
| SET CONNECT | SET MULTITRANSACTION |
| START DBE | START DBE NEW |
| START DBE NEWLOG | STOP DBE |
Utilities
The utilities provided with ODBCLink/SE do the following:
- Test and verify that the driver is functioning correctly.
- Determine which version of ODBCLink/SE is running.
- Delete unwanted drivers.
- Delete unwanted translation DLL's.
Tools on the Server
The following server tools are available to allow monitoring of the
ODBCLink/SE listener process:
- HP GlancePlus/XL (HP 3000) Program
- HP GlancePlus/UX (HP 9000) Program
- HP-UX Process Status (ps) Command
- Monitoring ALLBASE/SQL Activity with SQLMON
- ODBCUTSE for testing/troubleshooting the host connection or table
access
- The parameter to have error messages logged to the ODBCLOG file:
Trace SQL calls on the server.
Tools on the Client
The tools on the client that you can use to monitor or troubleshoot the
ODBCLink/SE client process:
- The configuration parameter, Trace ODBC calls on the client,
is used to log information to the ODBCLink.LOG file.
- The application, Dr. DeeBeeSpy, is used to trace calls to the ODBC
driver. Dr. DeeBeeSpy is shipped with ODBCLink/SE.
ODBCLink
ODBCLink/SE is a "Special Edition" of M. B. Foster Associates Limited ODBCLink
option of DataExpress. ODBCLink, the full product, offers the following
features in addition to the features of ODBCLink/SE:
- Support for serial and/or modem connections
- Read/Write access to KSAM files
- Read/Write access to MPE files
- Native read/write access to TurboIMAGE databases
- Support for PowerHouse dictionaries including PDL and PowerHouse
subfiles
- Read/Write access to Suprtool, Dictionary/3000, HP SD, and PowerHouse
Qschemac via DataExpress DataViews
- Support for TPI keyword indexes used by Omnidex and Superdex
- Access to Oracle databases on HP 3000 and HP 9000 platforms
- Access to Multiple DBEnvironments
- Access to HP intrinsics and procedures via remote procedure call
- Enhanced security via field level access controls
You may obtain an upgrade from ODBCLink/SE to the full product ODBCLink by
contacting M. B. Foster Associates at 1-800-ANSWERS, (800-267-9377) or
613-448-2333, or by visiting the M. B. Foster website at
http://www.mbfoster.com.
Documentation
For further information on ODBCLink/SE, refer to the ODBCLink/SE Reference
Manual (36217-90406) (revised June 1998).
|
