HP ODBCLink-SE Reference Guide

ODBCLink/SETM

Version F.00

Head Office: 82 Main Street South

Chesterville, Ontario K0C 1H0 Canada

Telephone: (613) 448-2333

Fax: (613) 448-2588

www.mbfoster.com

November 2000

NOTICE

The information in this manual is subject to change without notice.

LIMITATIONS ON WARRANTIES AND LIABILITY

M.B. Foster Software Labs makes no warranties, either express or implied, regarding this manual or the computer software package described in this manual, its merchantability or its fitness for any particular purpose. The exclusion of implied warranties is not permitted by some states.

This manual is copyrighted by M.B. Foster Associates Limited, with all rights reserved. Under the copyright laws, this manual may not be copied, in whole or in part, without the written consent of M.B. Foster Associates Limited. Under the law, copying includes translating to another language.

ODBCLink/SE is a trademark of M. B. Foster Software Labs, Inc.

Impromptu, PowerPlay, Axiant and PowerHouse PDL are trademarks of Cognos Incorporated.

Jetform is a trademark of Jetform Corporation.

PowerBuilder is a trademark of Powersoft Corporation.

Reflection, Reflection for Windows, Reflection Network Series and PPL (Process-to-Process Link) are trademarks of WRQ, Inc.

IMAGE/SQL, ALLBASE/SQL, Query, MPE/XL and MPE/iX are trademarks of the Hewlett-Packard Company.

Windows, Windows for Workgroups, Microsoft Access, Visual Basic, Visual C++, Visual FoxPro, Excel 5.0, MS-Query and Word for Windows are trademarks of Microsoft Corporation.

ODBCLink/SE offers the following features:

⊗ ODBC Level 3 compliance (except SQLBrowseConnect,

SQLExtendedFetch and SQLSetPos)

⊗ Access to IMAGE/SQL database environments (DBE) ⊗ Access to ALLBASE/SQL DBE ⊗ Access to a single DBE ⊗ DSN and DSNless connections

The additional features of DataExpress for Client-Server are:

⊗ Connectivity to TurboIMAGE and Oracle databases ⊗ Serial/Modem connection ⊗ Create a mini-dictionary for access to Image data sets, KSAM or

MPE files.

⊗ Read/Write KSAM, MPE and Suprtool files ⊗ Support for PowerHouse PDL dictionaries ⊗ Support for TPI keyword indexes used by Omnidex and Superdex ⊗ Enhanced security via field level access controls ⊗ Multi-threaded access to host-based data ensures fast and reliable

data access

⊗ Use-able in MTS/IIS environments ⊗ Secure reliable access to data through browser clients

Introducing MBF-Console

For monitoring and controlling ODBCLink/SE or MBF-UDALink activities such as:

⊗ Providing a list of each client connection to the server ⊗ Changing logging levels in real time ⊗ Start/stop logging for a single process ⊗ Kill a single process as required

Inquire about available upgrading programs to DataExpress Client Server at

sales@mbfoster.com

or globalsales@mbfoster.com

)

If you require any of the following features:

•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

•Read/Write access to Suprtool files

•Support for PowerHouse dictionaries including PDL and PowerHouse subfiles support

•Support for TPI keyword indexes used by Omnidex and Superdex

•Access to Oracle databases on HP3000 and HP9000 platforms

•Access to Multiple DBE’s

•Access to HP intrinsics and procedures via a remote procedure call mechanism

•Enhanced security via field level access controls

Please contact M. B. Foster Associates at 613-448-2333 or 1-800-ANSWERS (800267-9377) or use the enclosed faxback sheet to order a fully functional evaluation copy of MBF-UDALink.

FAX BACK

M. B. FOSTER ASSOCIATES LIMITED

613-448-2588

NAME _________________________________________________________ TITLE _________________________________________________________ COMPANY _________________________________________________________ ADDRESS _________________________________________________________ CITY/STATE _________________________________________________________ ZIP _________________________________________________________ COUNTRY _________________________________________________________ E-MAIL _________________________________________________________ PHONE _________________________________________________________ FAX _________________________________________________________

Please check the features that you are most interested in:

__Serial/Modem connection __KSAM file access __MPE file access __TurboIMAGE access __PowerHouse PDL support __Suprtool file access __Oracle access __TPI interface support __Multthreading __Enhanced security (field level) __MBF-Console

I am using the following desktop product(s):

__MS Access __Visual Basic __Fox Pro __MS Query __Paradox __Jetform __Impromptu __Axiant __PowerBuilder __Lotus __Excel __Crystal Reports

__Other_____________________________________________________________________________

__I am interested in a demo, please send one immediately!

HPCPUNAME: _______________________ HPSUSAN NUMBER: _______________________ Media Required:__DAT__Tape

IN A HURRY? GET YOUR QUESTIONS READY AND CALL 1-800-ANSWERS (267-9377) NOW! We can also be reached at: PHONE: 613-448-2333 FAX: 613-448-2588 sales@mbfoster.com

Notes

DataExpress Administration Guide Table of Contents

Chapter 1 Overview and Specifications

ODBC Compliancy Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Client Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Server Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

The Connecting Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Microsoft ODBC Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

ODBCLink/SE System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Chapter 2 Preparing the Database Server

Verify Software Version Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Verify the Connection Assurance Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Starting and Stopping the Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Chapter 3 Installing ODBCLink/SE on the Client PC

Downloading ODBCLink/SE to the Client PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Extracting the ODBCLink/SE file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Running the SETUP program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Notes on 32-bit Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Chapter 4 Configuring Data Sources

Setup for MPE/iX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Setup for HP-UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Select Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

ODBC Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Successful Completion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Migrating HP PCAPI Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Chapter 5 Application Development

Using Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

Performance Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Turning AutoCommit On/Off . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Supported Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Supported Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

DataExpress ©M.B. Foster Associates Limited 1995-2000 i

Table of Contents DataExpress Administration Guide

Note on Using BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Chapter 6 Using ODBCLink/SE With ODBC Applications

Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

Using Cognos Impromptu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Special Notes for Users of Impromptu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Using Lotus 123 Release 5.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Using Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Notes for Users of MS-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Using MS-Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Using Visual Basic (4.0 or higher) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Connection Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Connect Using SQL API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49

Connect Using SQLAPI in VB 4.0-6.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Connecting with DAO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Connecting with RDO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

Connecting with ADO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Chapter 7 Troubleshooting

Terminating an ODBC Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Using Microsoft ODBC Test (32-bit) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Verifying Software Version Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Verifying the Client-Side Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Deleting a Translation DLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Tools on the Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Host Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Tools on the Client PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

ODBC Call Tracing using ODBCLink.LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

ODBC Call Tracing using Dr. DeeBee Spy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Modifying the System Registry and ODBC.INI Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Appendix A Implementation Notes

Primary key name returned by SQLStatistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Reading or writing to ALLBASE/SQL LongVarBinary items . . . . . . . . . . . . . . . . . . . . . . . . . 67

Maximum number of statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

ALLBASE/SQL and IMAGE/SQL Restrictions on the ODBC Grammar . . . . . . . . . . . . . . . . 68

DataExpress

ii ©M.B. Foster Associates Limited 1995-2000

DataExpress Administration Guide Table of Contents

Using the ANSI Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Unsupported ALLBASE/SQL and IMAGE/SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 69

ALLBASE/SQL and IMAGE/SQL Statements That Work Only with Embedded SQL . . . . . 70

ALLBASE/SQL and IMAGE/SQL Statements Replaced by Functions on the PC Client . . . 70

Appendix B Creating a DBEnvironment

On the HP3000 Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

On the HP9000 Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Appendix C List of Installed Files

32 Bit Driver Client - For Windows 95 and Greater . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

32 Bit Driver Client - For Windows NT 4.0 and Greater . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

MPE/iX Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

HP-UX 9.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

HP-UX 10.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

Appendix D ODBCLink/SE Companion Product MBF-Console

MBF-Console Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81

How MBF-Console Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Running MBF-Console . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81

File Menu Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Connect Menu Options: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83

Connections Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84

Server Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

“View” Menu Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

“Window” Menu Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .87

Help Menu Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Installing MBF-Console for MBF-UDALink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Installing MBF-Console for ODBCLink/SE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Indexi

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

DataExpress ©M.B. Foster Associates Limited 1995-2000 iii

Table of Contents DataExpress Administration Guide

Notes

DataExpress

iv ©M.B. Foster Associates Limited 1995-2000

ODBCLink/SE Reference Manual Overview and Specifications

Overview and Specifications

ODBCLink/SE 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 HP3000 and ALLBASE/SQL on the HP3000 and HP9000, in a client/server environment.

In this environment, application developers and end-users can take advantage of the Client 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.

ODBCLink/SE runs under MPE/iX 5.0 or higher on the HP3000 server, or under HP-UX Versions 9.x, 10.x and 11.x on the HP9000 Series 7xx and 8xx servers. ODBCLink/SE runs under Windows95, or WindowsNT, on the client. Connection via Winsock is available regardless of the host environment.

ODBCLink/SE can be used in two ways: either by direct calls to the Windows DLL from a Windows program (Foxbase, etc.) or else through an ODBC-compliant application such as Microsoft Access, Visual Basic, etc.

ODBCLink/SE supports the ODBC LongVarBinary data type for binary large objects (BLOBs), such as compressed photographs or document images. These are stored on the host as ALLBASE/SQL LongVarBinary items.

ODBCLink/SE ©M.B. Foster Associates Limited 1995-2000 1

Overview and Specifications ODBCLink/SE Reference Manual

ODBC Compliancy Level

ODBCLink/SE is ODBC Level 3 with the following exceptions:

SQLBrowseConnect, SQLExtendedFetch, SQLSetPos are not supported. Scalar Functions are not support.

SQL_BIT, SQL_TINYINT and SQL_BIGINT data types are not supported.

Client Environment

ODBCLink/SE and application software reside on the PC client. Using ODBCLink/ SE and applications software you can develop SQL applications, generate reports, and query ALLBASE/SQL or IMAGE/SQL on the database server. The tested client applications include:

Cognos Axiant Cognos Impromptu Jetform Cold Fusion Lotus 123 PowerBuilder Crystal Reports MSExcel MSAccess MSQuery Visual Basic Visual C++ Visual FoxPro Paradox and many more.

Server Environment

The networked HP 3000 Series 900 with ALLBASE/SQL and/or IMAGE/SQL or the HP9000 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.

The Connecting Link

ODBCLink/SE links the client to the server with a collection of dynamic link libraries (DLLs) and other files that reside on the client and run under Microsoft Windows95 and greater or Microsoft WindowsNT. ODBCLink/SE routes requests, made from the PC client application, over the network to an ALLBASE/SQL or IMAGE/SQL database on the database server, and returns replies to the client application.

A listener process establishes the connection between the PC client application and the target database on the server. The listener works with HP ThinLAN 3000/iX on the HP3000 or ARPA Services on the HP9000.

ODBCLink/SE

2 ©M.B. Foster Associates Limited 1995-2000

ODBCLink/SE Reference Manual Overview and Specifications

Microsoft ODBC Overview

ODBC applications make functions calls into ODBC32.DLL, the Microsoft driver manager. Since a single application can make calls to more than one ODBC database (e.g. Sybase, Microsoft SQL server) the driver manager is in charge of routing ODBC calls to the appropriate driver. The ODBC.INI file contains a list of drivers and associated data sources (one driver may have several data-sources). In Windows 95 or NT, some of this information is kept in the system registry.

To connect to an ODBC database, you call SQLDriverConnect (or you let your ODBC-compliant application call it for you) and specify your data-source name in the connection string variable, which is in the form "DSN=data_source”. The driver manager reads ODBC.INI or the registry, to find out which driver handles that data source and passes the call on to the driver. The driver opens the communication channel and does all necessary initialization.

The ODBC.INI file (or the ODBC System Registry) is maintained through the ODBC Control Panel application ODBCADM.EXE , or the 32-bit ODBC Control Panel application ODBCAD32.EXE. The control panel application will call the setup DLL for the driver it is accessing ODBCLS32.DLL, in the case of ODBCLink/SE, which will update ODBC.INI or the system registry.

ODBCLink/SE ©M.B. Foster Associates Limited 1995-2000 3

Overview and Specifications ODBCLink/SE Reference Manual

ODBCLink/SE System Requirements

HP3000 Server Requirements:

Operating System Requirements:

MPE/iX Version 5.0 or greater

Database Requirements :

IMAGE/SQL Version B.G1.10 ALLBASE/SQL Version A.G1.15

Network Requirements:

ThinLANLink/XL must be configured and turned on.

HP9000 Server Requirements:

Operating Systems Requirements

HP-UX Version 9.0 or greater

Database Requirements:

ALLBASE/SQL Version A.G1.15

Network Requirements:

ARPA Services must be configured and turned on.

Client Requirements:

Platform:

486 processor at a minimum, preferably a Pentium 8 mg memory at a minimum, preferably 16 mg 5 mgs of free disc space

Operating Systems:

Windows 95 and greater Windows NT Version 3.51 and greater

Network Requirements

Winsock or other TCP/IP software

Software provided:

Server program for the HP3000 or HP9000 as appropriate Client programs

ODBCLink/SE

4 ©M.B. Foster Associates Limited 1995-2000

ODBCLink/SE Reference Manual Overview and Specifications

Field types supported:

All data types supported by Allbase and Image/SQL.

SQL Commands supported:

ODBCLink/SE supports the complete ALLBASE/SQL and IMAGE/SQL syntax dynamic SQL.

ODBCLink/SE ©M.B. Foster Associates Limited 1995-2000 5

Overview and Specifications ODBCLink/SE Reference Manual

Notes

ODBCLink/SE

6 ©M.B. Foster Associates Limited 1995-2000

ODBCLink/SE Reference Manual Preparing the Database Server

Preparing the Database Server

Before you install ODBCLink/SE on the PC client, perform the following steps to prepare the database server:

⊗ Verify the software version numbers. ⊗ Verify the Connection Assurance Parameters. ⊗ Starting and Stopping the listener. ⊗ Gather information. ⊗ Create the DBEnvironment

Verify Software Version Numbers

Note: If you are using ALLBASE/SQL, version A.G1.15 or higher is

On the HP3000 enter:

: SQLVER.PUB.SYS

required.

Note: If you are using IMAGE/SQL, the version number should be

On the HP9000 enter:

$sqlver

:IMAGESQL.PUB.SYS >>exit

Note: If both ALLBASE/SQL and IMAGE/SQL are installed, both

B.G1.10 or higher. Type:

must be either the respective version state above or newer in order to use ODBCLink/SE.

Verify the Connection Assurance Parameters

When the connected PC client application terminates abnormally or aborts, connection assurance parameters, set under MPE/iX, determine the length of time that the server takes to time out the connection. Because the server process may be holding locks when the PC aborts, it is important that these parameters are set properly.

Your network administrator should verify that the settings for Connection Assurance Interval and Maximum Connection Assurance Retransmissions parameters are set appropriately. The Connection Assurance Interval specifies the amount of time between each polling event. The default value is 600 seconds.

The Maximum Connection Assurance Retransmissions specifies the number of retries the server is polled after a PC client abort has been detected by the polling event. The default value is four times.

After the connection abort, the server polls the connection five times (the polling event plus four retries). The connection times out after 4 ten-minute intervals plus the amount of time between the polling event and the first retry after the connection aborted.

ODBCLink/SE ©M.B. Foster Associates Limited 1995-2000 7

Preparing the Database Server ODBCLink/SE Reference Manual

To change the network assurance parameters, you must bring the network down. The sequence of commands used to set network assurance parameters are as follows:

:HELLO MANAGER.SYS :NMMGR

Next, press the function keys in the sequence shown in the following example (these function key labels are for MPE/ iX 5.0):

F1 Open Config F2 NS F2 Unguided Config F1 Go To Netxport F2 Go To GPROT F2 Go To TCP :

Modify values for Connection Assurance Interval (e.g. 60) and Maximum Connection Assurance Retransmissions (e.g. 2).

: F6 Save Data

Note: Connection assurance parameter values control polling

times on all active server LAN connections (not just ODBCLink/SE connections). More frequent polling uses more server CPU time and increases LAN traffic.

Starting and Stopping the Listener

To start the listener on the HP3000 data base server, enter:

STREAM ODBCJOB.ODBCSE.SYS

Note: PM and NM capability is required to start the listener job,

To start the listener on the HP9000 database server, enter:

/usr/bin/odbcse/odbclnse server On a 9.x server /opt/allbase/bin/odbcse/odbclnse server On a 10.x server and 11.x server

Note: You should start the listener as root user-id preferably in

Note: When the server is started it makes a call to sqlver and puts

therefore it is normal for the job to be started by MANAGER.SYS.

your system startup file.

the output into a file called odbcver in the same directory that the server is running from. For this reason, upon startup, it may take 20 or more seconds for the program to return. The odbcver file is used to determine the DBMS version for a SQLGetInfo call. If the odbcver file is deleted the SQLGetInfo call for DBMS version will return “UNKNOWN”

ODBCLink/SE

8 ©M.B. Foster Associates Limited 1995-2000

ODBCLink/SE Reference Manual Preparing the Database Server

To determine whether or not the listener job is running:

On the HP3000 database server, enter:

SHOWJOB JOB=@J

The resulting display will besimilar to the following:

JOBNUM STATE IPRI JIN JLIST SCHEDULED-INTRO JOB NAME

#Jnnn SCHED 8 10S LP 12/ 6/00 4:00 ODBCLNSE,MANAGER.SYS

On the HP9000 database server, enter:

# ps -ef | grep odbclnse

The resulting display will be something like this:

root 7223 1 0 15:37:15 ? 0:00 /usr/bin/odbcse server paw 7226 7223 0 15:40:56 ? 0:00 /usr/bin/odbcse server root 7238 7230 1 15:41:29 ttys2 0:00 grep odbcse

The first line shows the server running. The second line shows a user with a login of “paw” is using the server and the last line shows the command you just entered.

To stop the listener:

On the HP3000 database server, enter:

ODBCLNSE.ODBCSE.SYS STOP

Note: If there is a connection open when the stop command is

On the HP9000 database server, enter:

/usr/bin/odbcse/odbclnse stop On HP-UX 9.x /opt/allbase/bin/odbcse/odbclnse stop On HP-UX 10.x and 11.x

Note that in Unix, the STOP command will prevent any new connections but will not affect connections that were active at the time the command was issued.

issued the process will not be stopped. If you are not able to stop the listener in the normal way, or if you want to terminate active ODBC client sessions, you may abort the listener Job with the :ABORTJOB command

You may also use the following command to stop the listener. It is, however, strongly suggested that you use the “odbcse stop” command to stop the listener.

kill -16 “pid” where “-16" is the “siguser1" and “pid” is the process ID number

Preparing the Database Server ODBCLink/SE Reference Manual

In the example above, if the “pid” was 7223" then “kill -16 7223" would stop the server. Using a “kill -16" is less dangerous than using “kill (pid)”. If you issue a kill command without the -16 and enter the wrong “pid” number then you may stop a server process that does not belong to you. Using “kill -16" will prevent this from happening.

Gather Information

When you install ODBCLink/SE on the PC client, you will need database server information. Now is a good time to Note it. The database administrator will have information about the database.

⊗ Host or node name of the HP3000 or HP9000 database server, or its IP

address (For example: HP3000 or HP9000 or 123.456.789.123)

To obtain the host name used by the ARPA interface:

⊗ On the HP3000 check the HOSTS.NET.SYS file. If the hosts file does not

exist, check the domain name service file. This file contains the names of the systems that have hosts files.

⊗ On the HP9000 check the /etc/hosts file. If the hosts file does not exist, check

the domain name service file, /etc/resolv.conf. This file contains the names of the systems that have hosts files.

⊗ Fully qualified name or the absolute pathname of the DBEnvironment on

the server: (For example: PartsDBE.SomeGrp.SomeAcct on the HP3000 or /usr/hpsql/sampledb/PartsDBE on the HP9000)

⊗ Logon string and password used to connect to the DBEvironment:(For

example: SomeUser/passwd.SomeAcct/AcctPass,SomeGrp/GrpPass on the HP3000 orUser1,passwd on the HP9000)

Create the DBEnvironment

For details, see Appendix B - Creating a DBEnvironment.

ODBCLink/SE

ODBCLink/SE Reference Manual Installing ODBCLink/SE on the Client PC

Installing ODBCLink/SE on the Client PC

ODBCLink/SE is contained in a self-extracting archive files. Installing ODBCLink/SE requires:

⊗ Downloading the ODBCLink/SE file to a temporary sub-directory. ⊗ Extracting the files on your Client PC. ⊗ Running the SETUP program to install the driver.

Once the driver has been successfully installed, the sub-directories created as part of the install may be deleted.

Downloading ODBCLink/SE to the Client PC

In the following section, replace Server with the name of your HP3000 or HP9000 server and nn with 32.

Using Reflection® software:

From your Client PC:

⊗ Create a directory called ODBCLinkSE

⊗ Start Reflection and copy the appropriate ODBCLink/SE self-extracting file

to your Client PC.

For the HP3000 server, press Alt/Y to open the Command Window and enter

receive \odbcsenn\odbcclnn.exe from odbcclnn.odbcse.sys binary

For the HP9000 server use any login as the Username:

For HP-UX Version 9.x

receive \odbcsenn\odbcclnn.exe from \usr\bin\odbcse\odbcclnn binary

Installing ODBCLink/SE on the Client PC ODBCLink/SE Reference Manual

For HP-UX Version 10.x:

receive\odbcsenn\odbcclnn.exe from \opt\allbase\bin\odbcse\odbcclnn binary

Using ARPA Services (FTP):

From your Client PC:

⊗ Create a directory:

•ODBCSEnn

⊗ Copy the appropriate ODBCLink/SE self-extracting file to your Client PC

For the HP3000 server, use MANAGER.SYS,ODBCSE as the Username. In ODBCSEnn, nn refers to 16 or 32 bit.

C:\ODBCSEnn> ftp Server binary get odbcclnn odbcclnn.exe quit

For the HP9000 server use any login as the Username:

C:\ODBCSEnn> ftp Server binary get /usr/bin/odbcse/odbcclnn odbcclnn.exe (for HP-UX V9.x)

get /opt/allbase/bin/odbcse/odbcclnn odbcclnn.exe (for HP-UX V10.x and 11.x)

Using NS Services (DSCOPY) on the HP3000:

From your Client PC:

⊗ Create a directory:

⊗ Copy the appropriate ODBCLink/SE self-extracting file to your Client PC

dscopy -r -F -B -L256 Server#manager.sys,odbcse# odbcclnn odbcclnn.exe

•ODBCSEnn

ODBCLink/SE

ODBCLink/SE Reference Manual Installing ODBCLink/SE on the Client PC

Extracting the ODBCLink/SE file

From your Client PC, double click the self-extracting file.

Note: If you are installing ODBCLink/SE on a number of Client

PC’s, after extracting the files you might wish to create installation disks by copying the extracted files onto a diskette.

Running the SETUP program

Note: A problem associated with Microsoft ODBC 2.10 setup

causes the setup on a WindowsNT 4.0 system to install as if it was a Windows95 system. We have therefore created two information files to be used with the 32-bit setup program. A batch file copies the appropriate file to odbc.inf depending on the parameters given.

On a WindowsNT system run “infsetup wnt”

On a Windows95 systems run “infsetup w95"

The batch file will read the parameter entered and copy the appropriate file to odbc.inf. Entering infsetup with no parameter specified will simply describe the usage but will not copy the file.

Once the above has been accomplished proceed with the setup program as follows:

Select: Start, Run. The Run window is displayed.

Type: C:\ODBCLinkSE\SETUP.EXE in the command field

Note: C:\ODBCLinkSE\SETUP.EXE is the directory the file was

extracted into.

Installing ODBCLink/SE on the Client PC ODBCLink/SE Reference Manual

The Driver Setup Program will display this window.

Select NEXT

ODBCLink/SE

ODBCLink/SE Reference Manual Installing ODBCLink/SE on the Client PC

Select NEXT to accept the default folder or Browse to select an anternate

Installing ODBCLink/SE on the Client PC ODBCLink/SE Reference Manual

Select NEXT to accept the MBFoster Program Folder or select an alternate Program Folder.

Select NEXT

ODBCLink/SE

ODBCLink/SE Reference Manual Installing ODBCLink/SE on the Client PC

The Progress Bar Is Displayed

Click Finish

Installing ODBCLink/SE on the Client PC ODBCLink/SE Reference Manual

The driver has been successfully installed. You may now create data sources for use in applications that use this driver. See Section 4 for a complete description of creating data sources.

Notes on 32-bit Access

32-bit data-source information is kept in the registry, and maintained by the 32-bit ODBC control panel application ODBCAD32.EXE.

You do not normally make entries manually into either the registry or the ODBC.INI file. However information has been provide in the manual to help you do this.

ODBCLink/SE

ODBCLink/SE Reference Manual Configuring Data Sources

Configuring Data Sources

Before you access data defined to ALLBASE/SQL or IMAGE/SQL you must configure one or more data sources for each Client PC you wish to access your server.

A data source includes the name you wish to give for a database environment and the information necessary to find it.

You configure data sources with the "ODBC" application in the Windows Control Panel.

⊗ In Windows95 or greater and WindowsNT 4.0 or greater , click on Start,

Settings, Control Panel and double click on ODBC.

Data sources created in HP ALLBASE/SQL PCAPI can be migrated to ODBCLink/SE using the Data Set Migration Too l.

Select Add

Configuring Data Sources ODBCLink/SE Reference Manual

Scroll down to ODBCLink/SE-32 Driver ODBC3. Click Finish

Note: The ODBCLink/SE-32 Driver ODBC3 driver is ODBC 3

compliant. The previously installed driver, which is ODBC 2 compliant is still available.

ODBCLink/SE

+ 90 hidden pages