 |
» |
|
|
|
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: 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 Cognos Axiant | Paradox | Cognos Impromptu | PowerBuilder | Jetform | Visual Basic | Lotus 1-2-3 | Visual | Microsoft Access | Visual FoxPro | Microsoft Query | |
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 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
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: SQLColAttributes All functions will receive a return value of: except the following supported options: Table 10-4 SQLColAttributes
Supported Functions SQL_COLUMN_COUNT | SQL_COLUMN_NAME | SQL_COLUMN_TYPE | SQL_COLUMN_LENGTH | SQL_COLUMN_PRECISION | SQL_COLUMN_SCALE | SQL_COLUMN_DISPLAY_SIZE | SQL_COLUMN_NULLABLE |
SQLSetConnectOptions All functions will return this message: Driver not Capable (SQLState S1C00) |
except the following supported options: Table 10-5 SQLSetConnect
Supported Options SQL_AUTOCOMMIT | 1=ON (default) 0=OFF | SQL_OPT_TRACE | supported by Driver Manager | SQL_OPT_TRACEFILE | supported by Driver Manager | SQL_TRANSLATE_DLL | supported | SQL_TRANSLATE_OPTION | supported | SQL_TXN_ISOLATION | supported | SQL_TXN_READ_UNCOMMITTED | Read Uncommitted (RU) | SQL_TXN_READ_COMMITTED | Read Committed (RC) | SQL_TXN_REPEATABLE_READ | Repeatable Read (RR) | SQL_TXN_SERIALIZABLE | Repeatable Read (RR) | SQL_TXN_VERSIONING | Not Implemented |
SQLSetStmtOptions are not supported. The driver will return either of these messages: Driver not Capable (SQLState S1C00)
Option value changed (SQLState 01S02) |
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 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 | 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 translation DLL's.
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.
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). |
