Jump to content United States-English
HP.com Home Products and Services Support and Drivers Solutions How to Buy
» Contact HP
More options
HP.com home
 ALLBASE/SQL Release G2 Release Notes: HP 9000 Computer Systems > Chapter 2 What's in This Version?

ODBCLink/SE
» 

Technical documentation

Complete book in PDF
» Feedback
Content starts here

 » Table of Contents

spacerspacerspacer
spacer
spacer
  		 
 

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:

  • SQLBrowseConnect

  • SQLExtendedFetch

  • SQLSetPos

  • SQLSetStmtOptions

  • Scalar functions

  • 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:

Cognos AxiantParadox
Cognos ImpromptuPowerBuilder
JetformVisual Basic
Lotus 1-2-3Visual C++
Microsoft AccessVisual FoxPro
Microsoft Query 

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

    • 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 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

  • 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:

  • SQLColAttributes

    All functions will receive a return value of:

    Not Implemented

    except the following supported options:

    SQL_COLUMN_COUNTSQL_COLUMN_NAME
    SQL_COLUMN_TYPESQL_COLUMN_LENGTH
    SQL_COLUMN_PRECISIONSQL_COLUMN_SCALE
    SQL_COLUMN_DISPLAY_SIZESQL_COLUMN_NULLABLE

  • SQLSetConnectOptions

    All functions will return this message:

    Driver not Capable (SQLState S1C00)

    except the following supported options:

    SQL_AUTOCOMMIT1=ON (default) 0=OFF
    SQL_OPT_TRACEsupported by Driver Manager
    SQL_OPT_TRACEFILEsupported by Driver Manager
    SQL_TRANSLATE_DLLsupported
    SQL_TRANSLATE_OPTIONsupported
    SQL_TXN_ISOLATIONsupported
    SQL_TXN_READ_UNCOMMITTEDRead Uncommitted (RU)
    SQL_TXN_READ_COMMITTEDRead Committed (RC)
    SQL_TXN_REPEATABLE_READRepeatable Read (RR)
    SQL_TXN_SERIALIZABLERepeatable Read (RR)
    SQL_TXN_VERSIONINGNot 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.

ALLBASE/SQL DATA TYPEODBC DATA TYPE
CHAR, max length <255SQL_CHAR
CHAR, max length >=255SQL_LONGVARCHAR
VARCHAR, max length <255SQL_VARCHAR
VARCHAR, max length >=255SQL_LONGVARCHAR
BINARY, length <256SQL_BINARY
BINARY, length >=256SQL_LONGVARBINARY
VARBINARY <256SQL_VARBINARY
VARBINARY >=256SQL_LONGVARBINARY
LONG BINARYSQL_LONGVARBINARY
LONG VARBINARYSQL_LONGVARBINARY
INTEGER (32-bit)SQL_INTEGER
SMALLINT (16-bit)SQL_SMALLINT
DECIMAL (Internal representation is packed decimal)SQL_DECIMAL
FLOAT(24), REALSQL_REAL
FLOAT(53), DOUBLE PRECISIONSQL_DOUBLE
DATESQL_DATE
TIMESQL_TIME
DATETIMESQL_TIMESTAMP
INTERVALSQL_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.

STATEMENTPROGRAMMING CONSIDERATIONS
CREATE TABLEUNIQUE PRIMARY KEY must follow NOT NULL
DROP TABLEALLBASE/SQL does not provide CASCADE or RESTRICT
REVOKEALLBASE/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:

CLOSE CURSORBEGIN DECLARE SECTION
DECLARE CURSORDESCRIBE
END DECLARE SECTIONEXECUTE
EXECUTE IMMEDIATEFETCH
INCLUDEOPEN
PREPAREREFETCH
SQLEXPLAINWHENEVER

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:

CONNECTDISCONNECT
RELEASERESET
SET CONNECTSET MULTITRANSACTION
START DBESTART DBE NEW
START DBE NEWLOGSTOP 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/UX 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 files, "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 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 (36217-90406).



Features
  		 


Chapter 3 Compatibility and Installation Requirements  
Printable version
Privacy statement Using this site means you accept its terms Feedback to webmaster
© Hewlett-Packard Development Company, L.P.