ODBCLink/SE is a replacement for the current 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 ALLBASE/SQL on HP 9000, in a client/server environment.
ODBCLink/SE is a part of 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 HP-UX
11.0 and subsequent releases.
ODBCLink/SE server runs under HP-UX Versions 9.x, 10.x and
11.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 will be 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 9000 Series 7xx or 8xx with ALLBASE/SQL provides
the relational database environment on the server.
Security is provided by the HP-UX logon system and ALLBASE/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 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 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
on the server. The tested client applications include the following
as well as many more:
Table 2-1 Title not available (Client/Server Interface)
| Cognos Axiant | Microsoft Access | Visual Basic |
| Cognos Impromptu | Microsoft Query | Visual C++ |
| Jetform | Paradox | Visual FoxPro |
| Lotus 1-2-3 | PowerBuilder | |
Requirements
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 9000
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 from HP PC API to ODBCLink/SE. Once the 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 functions and commands supported
by ODBCLink/SE:
The complete ALLBASE/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 2-2 Title not available (Areas Not Fully Implemented)
| 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 2-3 Title not available (Areas Not Fully Implemented)
| 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 O1S02)
Supported Data Types |
|
ODBCLink/SE supports ALLBASE/SQL types.
The following table shows the correspondence between ALLBASE/SQL
data types and ODBC data types.
Table 2-4 Title not available (Supported Data Types)
| ALLBASE/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 |
| FLOAT(24), REAL | SQL_REAL |
| FLOAT(53), DOUBLE PRECISION | 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 result 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 restrictions on the ODBC grammar.
Table 2-5 Title not available (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 9000 uses 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, 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 Statements |
|
There are several reasons why some ALLBASE/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.
Statements that relate to functions, such as connection,
that have been replaced by functions resident on the client.
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 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 statements function only
with embedded SQL, they are not supported by ODBCLink/SE:
Table 2-6 Title not available (Statements that Work Only with Embedded SQL)
| 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 functions have been moved from the server
to the client with ODBCLink/SE
The following ALLBASE/SQL statements are not supported because
they have been replaced by other functions resident on the client:
Table 2-7 Title not available (Statements Replaced by Functions on the Client)
| 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 DLLs
The following server tools are available to allow monitoring
of the ODBCLink/SE listener process:
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 files, "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 CISAM files
Read/Write access to byte stream files
Support for PowerHouse dictionaries including PDL
and PowerHouse subfiles
Access to Oracle databases
Access to Multiple DBEnvironments
Access to procedures in a proclib, 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 www.mbfoster.com.
Documentation |
|
For further information on ODBCLink/SE, refer to the ODBCLink/SE
Reference Manual.
