Hp Mpe Ix 6 X Operating System Cli Reference Guide Page
2015-01-05
: Hp Hp-Mpe-Ix-6-X-Operating-System-Cli-Reference-Guide-156109 hp-mpe-ix-6-x-operating-system-cli-reference-guide-156109 hp pdf
Open the PDF directly: View PDF 
.
Page Count: 120
| Download | |
| Open PDF In Browser | View PDF |
ODBCLink/SE
Version F.00
November 2000
Head Office:
82 Main Street South
Chesterville, Ontario
K0C 1H0 Canada
Telephone:
(613) 448-2333
Fax:
(613) 448-2588
www.mbfoster.com
TM
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.
COPYRIGHT
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.
Dr. DeeBee Spy © 1995 Syware, Inc., All rights reserved.
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
to
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
__MPE file access
__PowerHouse PDL support
__Oracle access
__Multthreading
__MBF-Console
__KSAM file access
__TurboIMAGE access
__Suprtool file access
__TPI interface support
__Enhanced security (field level)
I am using the following desktop product(s):
__MS Access
__MS Query
__Impromptu
__Lotus
__Visual Basic
__Paradox
__Axiant
__Excel
__Fox Pro
__Jetform
__PowerBuilder
__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
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
ii
DataExpress
©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
iv
DataExpress
©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
Jetform
Lotus 123
Crystal Reports
MSAccess
Visual Basic
Visual FoxPro
and many more.
Cognos Impromptu
Cold Fusion
PowerBuilder
MSExcel
MSQuery
Visual C++
Paradox
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.
2
ODBCLink/SE
©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
4
ODBCLink/SE
©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
6
ODBCLink/SE
©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
required.
On the HP3000 enter:
: SQLVER.PUB.SYS
Note:
If you are using IMAGE/SQL, the version number should be
B.G1.10 or higher. Type:
On the HP9000 enter:
$sqlver
:IMAGESQL.PUB.SYS
>>exit
Note:
If both ALLBASE/SQL and IMAGE/SQL are installed, both
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,
therefore it is normal for the job to be started by
MANAGER.SYS.
To start the listener on the HP9000 database server, enter:
/usr/bin/odbcse/odbclnse server
/opt/allbase/bin/odbcse/odbclnse server
Note:
On a 9.x server
On a 10.x server and 11.x server
You should start the listener as root user-id preferably in
your system startup file.
Note: When the server is started it makes a call to sqlver and puts
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”
8
ODBCLink/SE
©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
#Jnnn
SCHED
8
JLIST
10S LP
SCHEDULED-INTRO
JOB NAME
12/ 6/00
ODBCLNSE,MANAGER.SYS
4:00
On the HP9000 database server, enter:
# ps -ef | grep odbclnse
The resulting display will be something like this:
root
paw
root
7223
7226
7238
1 0
7223 0
7230 1
15:37:15
15:40:56
15:41:29
?
?
ttys2
0:00
0:00
0:00
/usr/bin/odbcse server
/usr/bin/odbcse server
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
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
On the HP9000 database server, enter:
/usr/bin/odbcse/odbclnse stop
/opt/allbase/bin/odbcse/odbclnse stop
On HP-UX 9.x
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.
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
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
9
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.
10
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
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
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
11
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)
or
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:
•ODBCSEnn
⊗
Copy the appropriate ODBCLink/SE self-extracting file to your Client PC
dscopy -r -F -B -L256 Server#manager.sys,odbcse# odbcclnn odbcclnn.exe
12
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
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.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
13
Installing ODBCLink/SE on the Client PC
ODBCLink/SE Reference Manual
The Driver Setup Program will display this window.
Select NEXT
14
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Installing ODBCLink/SE on the Client PC
Select NEXT to accept the default folder or Browse to select an anternate
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
15
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
16
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Installing ODBCLink/SE on the Client PC
The Progress Bar Is Displayed
Click Finish
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
17
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.
18
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
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
Tool.
Select Add
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
19
Configuring Data Sources
ODBCLink/SE Reference Manual
Scroll down to ODBCLink/SE-32 Driver ODBC3. Click Finish
Note:
20
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
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Configuring Data Sources
On this screen you give the database environment a name and indicate where it is located. The Data Source Name
may be any combination of alphanumeric characters including blanks.
The maximum length is 30 characters.
The Description is optional and may contain any string of characters you wish. It may be longer than the display
window.
The Database Name is the name of the ALLBASE/SQL Database Environment you want to access with this data
source. The name will usually include the location of the DBE as shown in the sample screen. If the logon provided
in the next screen does not include the same logon group, the location MUST be provided. A sample HP/UX name
might be:
/usr/users/data/PartsDBE
The Server Name or IP Address is the name or address of the computer where your database environment resides. If
you don't know this name, contact your network administrator.
The Server Type radio button indicates the type of computer on which the DBE is located. Click on:
MPE/iX if it is an HP3000
HP-UX if it is an HP9000
Select Continue when you are finished
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
21
Configuring Data Sources
ODBCLink/SE Reference Manual
Setup for MPE/iX
The SessionID, User Name, Acct Name, Group Name, User Password, Acct Password and Group Password entries
correspond to those used if you were manually connecting to the HP3000 or HP9000. If you don't know this
information, contact your System Manager.
If you want the client application to prompt for the passwords at run-time, enter a “?” in the password field. If you put
a question mark in any password field, you will get a dialogue box and be prompted to enter the password every time
you run your application and request a connection. For an attached table in MS-Access, ODBCLink/SE will not
store the password anywhere within the application, so you will have to enter the password the first time you open the
table. However, many applications, including Access, connect more than once to the same data-source, and if you
have already entered the passwords and created a new connection, you will not normally have to enter it again for
additional connections to the same data-source.
Note:
22
As you might expect, "mandatory" means you must enter a
value. However, "optional" doesn't necessarily mean you
don't have to enter a value; it means there may not be a
value required. For example, on the MPE/iX logon, "Session
ID" is any 8 characters you choose, starting with an
alphabetic character with no embedded blanks. You may
choose not to enter a "Session ID". On the other hand,
"Group Name" will default you to your "home group" so may
be left blank; if your "User Name" does not have a "home
group" this field is required or the connection will fail.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Configuring Data Sources
Setup for HP-UX
Note:
On HP-UX, the Login name and password are case sensitive.
If you want the client application to prompt for the passwords at run-time, enter a “?” in the password field. If you put
a question mark in any password field, you will get a dialogue box and be prompted to enter the password every time
you run your application and request a connection. For an attached table in MS-Access, ODBCLink/SE will not
store the password anywhere within the application, so you will have to enter the password the first time you open the
table. However, many applications, including Access, connect more than once to the same data-source, and if you
have already entered the passwords and created a new connection, you will not normally have to enter it again for
additional connections to the same data-source.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
23
Configuring Data Sources
ODBCLink/SE Reference Manual
Setup Options
Encoded passwords: Check this box to encrypt the connection passwords in the ODBC.INI file or system registry.
For security reasons once the passwords are encoded if you decide to un-click the box you must DELETE the current
passwords first.
AutoCommit transactions: Check this box to have UPDATE and INSERT transactions committed as soon as they
are executed. If this box is not checked, transactions must be explicitly committed by the client application.
Note:
It is recommended that AutoCommit be left enabled
Trace ODBC calls on client: Check this box to have client calls to ODBC logged in the file ODBCLINK.LOG on
the Client PC.
Trace ODBC calls on server: check this box to have server calls to ODBC logged in the file
ODBCLOG.ODBCSE.SYS on the HP3000 or /tmp/odbclog on the HP-UX.
Isolation levels allow you to control the degree of concurrency by regulating the extent to which operations
performed by one user in a multi-user environment can be affected by operations performed by other users.
Note:
24
The recommended isolation level is “RU” for Read
Uncommitted. This minimizes the number of locks that are
held on your database.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Configuring Data Sources
Select Translator
ODBC Translator
Select “Roman8 to PC-ANSI Translator” if you want extended characters on your HP3000 (such as foreign accented
characters) to appear correctly on your Client PC. The translation goes both ways. For example; extended characters
are entered on the Client PC and sent to the host in an SQL update or Insert operation will be translated.
Note:
Roman9 to PC-ANSI Translator will recognize the symbol for
the European Currency Symbol, known as the EURO.
Successful Completion
The new data source is now ready for use in your client application.
Some applications may allow you to go directly to the "Data Sources" menu without going to Control Panel to click
on the ODBC Administrator. Check you client application documentation for details.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
25
Configuring Data Sources
ODBCLink/SE Reference Manual
Migrating HP PCAPI Data Sources
Data sources created in the HP ALLBASE/SQL PCAPI can be migrated to ODBCLink/SE by running the data set
migration tool DXNMIG16.EXE either from the RUN window or in an MS-DOS prompt window.
Click on all the data source names you wish to migrate and then click OK. Data sources will be migrated to
ODBCLink/SE data sources with the same name. Copies of the PCAPI data sources will be made with the same name
followed by “-orig”.
When the copies of the originals are no longer required, they may be deleted with the 16-bit ODBC Administrator
program.
26
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Application Development
Application Development
If you are developing applications that use the ODBC interface, you may want to order the Microsoft Developer
Network (MSDN) Professional (Level 2) from Microsoft. The MSDN contains the ODBC Software Developers Kit
(SDK), which includes the ODBC API Reference, sample ODBC applications, and other technical information to
assist you in developing your applications. There are also many good books written on the use of ODBC.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
27
Application Development
ODBCLink/SE Reference Manual
Using Stored Procedures
ODBCLink/SE implements all types of ALLBASE/SQL stored procedures, including those which return multipleformat results sets. You can list the stored procedures available to your ODBC session by calling SQLProcedures and
you can get parameter information for stored procedures by calling SQLProcedureColumns.
Stored procedures return data either through bound parameters or through one or more results sets. To get a return
value that is a procedure parameter, prepare an SQL statement (with SQLPrepare) of the form:
{?=call owner.procedue (?,?..)}
Then bind the parameters with SQLBindParameter, specifying an output parameter where appropriate
(SQLProcedureColumn will tell you which are input, which are output, and which are input/output parameters). Then
call SQLExecute. The return value(s) will be copied to the storage locations that you specify. Note that all stored
procedures return an integer return status called RETURN_STATUS.
If your stored procedure also returns one or more result sets, you call SQLNumResultCols, SQLDescribeCol,
SQLFetch, SQLGetData in the normal way you would for any Select statement. If the procedure returns more than 1
result set, you call SQLMoreResults when you get to the end of the first result set, and, if successful, you can then
retrieve another result set. It is permissible to call SQLMoreResults() at any time to switch to a different result set.
28
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Application Development
Performance Considerations
Many users find ODBC access to their server very fast. You should however be aware of the following:
⊗
If you are reading and sorting a large table, the host may not return control
to the client until the entire data-base has been read and sorted. If this
occurs, even restarting your Client PC will have no effect and you will have
to manually abort the server, or terminate the connection with the ISQL
“Terminate User” command.
⊗
When you are opening a large table in MS-Access, the program typically
displays a screenful of data and then seems to wait for user input. In many
cases, however, it will actually be downloading data to the client in the
background. You should think about this when giving your users ODBC
access to large tables on the server. You may give or withhold access to
certain tables with the ALLBASE/SQL GRANT and REVOKE commands.
There is no way currently of limiting the number of rows or the time the
server is allowed to execute a request.
⊗
As a rule of thumb, ODBCLink/SE will not complete any request faster than
ISQL will, and could be considerably slower due to network overhead. If
you are writing your own SQL, you may want to verify in ISQL how
ALLBASE/SQL optimizes your queries by reading the SYSTEM.PLAN
pseudo-table.
⊗
If you are writing your own SQL to do multi-row inserts or updates, you can
speed up your application by using dynamic substitution parameters. See
the ODBC SDK manual (from Microsoft Press) for details.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
29
Application Development
ODBCLink/SE Reference Manual
Turning AutoCommit On/Off
AutoCommit “on” is the default value for all ODBC drivers. Turning AutoCommit on/off is normally accomplished
by the application calling SQLSetConnectOption. The AutoCommit on/off setting in ODBC Administrator is there
for historical reasons only and should not be used. Setting AutoCommit “off:” in the ODBC Administrator is not a
good idea as the application can get confused. For instance, it can incorrectly assume that AutoCommit is on, and
then emit SQL that never gets committed.
30
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Application Development
Supported Functions
The ODBC functions are listed here for application software developers. These functions are supported by the
ODBCLink/SE driver at the time of publication of this manual. Refer to the README.HLP file included with
ODBCLink/SE for further developments.
You can use the ODBC SQLGetFunctions call for a list of the supported ODBCLink/SE driver functions.
Descriptions and conformance designations are listed in ODBC API Reference included in the Microsoft Software
Development Kit (SDK).
Functions that are not completely implemented are noted by an R in parenthesis and are discussed below this list.
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLFindParam
SQLCancel
SQLColAttributes (R1)
SQLColumns
SQLColumnPrivileges
SQLConnect
SQLDataSources
SQLDescribeCol
SQLDisconnect
SQLDriverConnect
SQLError
SQLExecute
SQLExecDirect
SQLFetch
SQLForeignKeys
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt
SQLGetConnectOption
SQLGetCursorName
SQLGetFunctions
SQLGetInfo (R3)
SQLGetTypeInfo
SQLNumResultCols
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLRowCount
SQLSetConnectOption (R2)
SQLSetCursorName
SQLSetParam
SQLSetStmtOption (R4)
SQLSpecialColumns
SQLStatistics
SQLTablePrivileges
SQLTables
SQLTransact
⊗
R1:
The following SQLColAttributes options are implemented; the
others will receive a return value of 'Not Implemented'.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
31
Application Development
ODBCLink/SE Reference Manual
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
⊗
R2:
The following SQLSetConnectOptions are supported. All other will
return a “Driver not Capable (SQLState S1C00)”.
SQL_AUTOCOMMIT
SQL_MAX_ROWS
SQL_OPT_TRACE
SQL_OPT_TRACEFILE
SQL_QUERY_TIMEOUT
SQL_TRANSLATE_DLL
SQL_TRANSLATE_OPTION
SQL_TXN_ISOLATION
SQL_TXN_READ_UNCOMMITTED
SQL_TXN_READ_COMMITTED
SQL_TXN_REPEATABLE_READ
SQL_TXN_SERIALIZABLE
SQL_TXN_VERSIONING
32
1=ON (default) 0=OFF
0=All Rows (default)
supported by Driver Manager
supported by Driver Manager
0=No Timeout (default) Use ALLBASE
Timeout
supported
supported
supported: Cursor Stability isolation level
(CS) is not supported by
SQLSetConnectOption but you may set
it from the ODBC Administrator
(Read Uncommitted (RU))
(Read Committed (RC))
(Repeatable Read (RR))
(Note: Sets to Repeatable Read (RR))
Not Implemented - DO NOT USE
Note:
For information on isolation levels, refer to the "Isolation
Levels" section in this chapter.
⊗
R3:
All SQLGetInfo Options are supported.
⊗
R4:
SQLSetStmtOptions are not supported. The driver will return
either a “Driver not Capable (SQLState S1C00)” or “Option value changed
(SQLState 01S02)”.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Application Development
Isolation Levels
Isolation levels allow you to control the degree of concurrency by regulating the extent to which operations
performed by one user in a multi-user environment can be affected by operations performed by another user.
ALLBASE/SQL allows four different isolation levels:
⊗
⊗
⊗
⊗
Repeatable Read (RR)
Cursor Stability (CS)
Read Committed (RC)
Read Uncommitted (RU)
ODBCLink/SE uses the isolation level specified in the ODBC setup screen. The application can later change this by
calling SQLSetConnectOptions with SQL_TXN_ISOLATION option.
The recommended isolation level is “RU” for Read Uncommitted. This minimizes the number of locks that are held
on your database.
Note:
Refer to the ALLBASE/SQL Reference Manual for further
information.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
33
Application Development
ODBCLink/SE Reference Manual
Supported Data Types
The following is a table of correspondence between HP data types and ODBC data types.
34
ALLBASE/SQL or Image or
IMAGE/SQL Data Type
ODBC Data Type
Comments
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_LONGVARBINA
RY
VARBINARY <256
SQL_VARBINARY
VARBINARY >=256
SQLLONGVARBINARY
LONG BINARY
SQL_LONGVARBINA
RY
LONG VARBINARY
SQL_LONGVARBINA
RY
INTEGER (32-bit)
SQL_INTEGER
DECIMAL
(Internal
representation is packed decimal)
SQL_DECIMAL
Character representation with leading sign
and decimal point
Image Zoned (Z)
SQL_DECIMAL
Converted by Image/SQL
Image Packed (P)
SQL_DECIMAL
Converted by Image/SQL
Image I3 (48-bit integer)
SQL_DECIMAL
Converted by Image/SQL
Image I4 (64-bit integer)
SQL_DECIMAL
Converted by Image/SQL
FLOAT(24) or REAL or Image
E2
SQL_REAL
FLOAT(53)
or
DOUBLE
PRECISION or Image E4
SQL_DOUBLE
Image R2 (non-IEEE float)
SQL_REAL
Converted by Image/SQL
Image R4 (non-IEEE float)
SQL_DOUBLE
Converted by Image/SQL
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Application Development
DATE
SQL_DATE
6-byte with year, month, day in 2-byte
binary fields
TIME
SQL_TIME
6-byte with hour, minute, second in 2-byte
binary fields
DATETIME
SQL_TIMESTAMP
16-byte made up of year(2), month(2), day
(2), hour (2), minute (2), second (2),
fraction (4)
INTERVAL
SQL_CHAR
Format is “ddddddd hh:mm:ss.fff”
Note:
When creating a table using CHAR and BINARY data types
that are greater than 255 characters the resulting data types
used will be LONGVARCHAR and LONGVARBINARY, which
may not be the data type expected by the the application.
Note on Using BLOBs
It is strongly recommended that when creating an ALLBASE table for storage of BLOBs that you use a LONG
VARBINARY column. ALLBASE will allocate storage space according to the actual size of the BLOB. Eg. Creating
a table with a column defined as LONG VARBINARY (200000000) and writing a 10K BLOB to it will result in only
10K of space being used (not the maximum of 2 GB as specified when the column was created).
Although you can store a BLOB using a LONG BINARY column, this is not recommended, because ALLBASE will
allocate storage space according to the specified column size for each BLOB regardless of the size of the actual data.
Eg. Creating a table with a column defined as LONG BINARY(1000000) and writing a 10K BLOB will result in
1000000 bytes of space being used for each BLOB. You would quickly run out of space in your database.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
35
Application Development
ODBCLink/SE Reference Manual
Notes
36
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Using ODBCLink/SE With ODBC Applications
To access your host through ODBCLink/SE, you will first have to set up one or more data sources via the ODBC
Control Panel.
Updating Data
Most ODBC applications require a unique primary key, existing on a table or data set, to be able to update the table.
For ALLBASE/SQL tables created with a primary key this is not a problem, but in many cases you will have to enter
the primary key name, if one exists.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
37
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Using Cognos Impromptu
Select Catalog, Database, Add. Enter HP3000-1 as the logical database name, ODBC Gateway as the Gateway, and
the name of your data source, e.g. HP3000. Enter your User ID and password, and click OK.
38
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Select Catalog, New. Enter a catalog name and your logical database name (HP3000-1). Impromptu should then
establish the connection and put you into the Edit Tables Screen.
The left side of the Edit Tables screen is a list of table owners. In ALLBASE/SQL, a table owner is the owner name
assigned by the database administrator (default is UserAccount). For a TurboIMAGE dataset, the owner name is the
database name. To get a list of all the tables for a specific owner name, double click it.
You may now select the tables you want included in your catalog. After you click OK, Impromptu will load the table
definition into the catalog.
If you want to add tables from another database to your catalog, select Catalog, Edit Tables, and follow the same
procedure again.
You may now create a report through the File, New command. You will be prompted for the fields to include in your
report, and Impromptu will generate the report on your screen.
Note:
You cannot create summary reports on TurboIMAGE tables
with this version of the driver. The GROUP BY command is
not implemented. You can, however, group rows and
compute subtotals.
Special Notes for Users of Impromptu
Viewing the SQL generated by Impromptu
Enter a new report and select Report, Query, Profile, View SQL. You can also write your own SQL and override that
generated by Impromptu. Search for help on topic AWriting your own SQL@.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
39
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Joins
ODBCLink/SE supports two kinds of joins: inner joins, sometimes called natural joins, and Left Outer joins. An inner
join will return no records if any of the secondary records are not found; a left outer joins will still return the primary
record and give nulls (or zeros) on the secondary table. To add or modify a join definition, select Catalog, Joins. The
following figure illustrates this process by creating a Left Outer join from table MEMBERSHIP to table ACCTHISTORY.
Note:
To do left outer joins on the host (as opposed to Impromptu
downloading both tables and doing the joins locally) you
must
modify
file
COGDMOD.INI
in
directory
\COGNOS\COGAPPS as follows:
[Exception tables]
Joined=T
Impromptu has been known to generate incorrect SQL when joining three tables or more using a left outer join. To
correct, modify the SQL that is generated and re-run the report.
Changing the display format
Impromptu has a limited number of display formats available. To change the display format, highlight the column and
select Format Data. You cannot specify edit masks, however you have a choice of numeric display formats. To add
dashes or other constants to your output, you must build an expression with SUBSTRING functions. To add a
decimal point to a number that is not defined as a decimal, you must create a calculated field by dividing the number
by 100.
40
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Creating Joins Using Calculated Columns
It is possible in Impromptu to create a join using a column that is calculated from two or more fields in one or more
tables. To do this, select Catalog, Edit joins; select your primary table and the table you want to join in the upper left
part of the screen; click AView as Expression@; and then enter an equation such as:
Table3.Column3 = Table1.column1 + Table2.Column2 ....
The left hand side of this expression is the table you want to join to, and the right hand side is the expression to
generate the key. An expression can consist of concatenated fields, SUBSTRING statements, constants, or all of
these.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
41
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Using Lotus 123 Release 5.0
To enable Lotus123 to use ODBC, you will have to add the following line to the file
\LOTUSAPP\DATALENS\LOTUS.BCF. (Note: The following information is summarized from the file
\123R5W\readme.txt):
DN=@ODBC@ DL=@DLODBC@ DD=@All ODBC Sources@;
Once you have done this, you may access ODBC data sources.
42
⊗
Select Tools, Database, New Query, External, ODBC.
⊗
Select a data-source from the list box.
⊗
Select a table to query.
⊗
You can then choose fields to include in the query, set criteria, enter joins,
etc.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Using Microsoft Access
To access data using MSAccess , open a blank database and click Create, New, Link Table, on the associated screens
that are presented, and select ODBC Databases from the Afiles of type:@ pull down list.
The Data Sources Window is displayed.
If the data source you require is not in the list, click on New and create a new data
source.
Note:
You must click on the name of the data source you wish to
use even if there is only one.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
43
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
You may highlight one or more tables and then click AOK@. This will create an Aattached table@ which you may later
open and modify. An attached table is treated like a local table in every way (you may read it, update it, delete it,
import it, or join it to other local or remote tables.)
Note:
44
Ensure you have checked the SAVE PASSWORD box. This
stores the User-ID returned by the driver in the Table
Properties field and allows you to connect afterwards with
the same User-ID. You should get a list of tables on the
HP3000. Whenyou select one of them, it will load the batle
structure (which you may examine by opening the table in
Design View) and store the table in the database as an
attached table. Once the table is attached, it is treated like a
local table in every way, except that you may not modift the
table structure. You may open the table in taew, run reports
or queries against it, or link it to other local or remote tables.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Notes for Users of MS-Access
⊗
For a table to be updatable in Access, it must have a unique key and Access
must know about it. If the table is defined in ALLBASE/SQL with a unique
key, this will be reported by ODBCLink/SE (in the SQLStatistics call that
Access makes) and the table will be updatable. If Access cannot locate a
primary key, it will prompt for the column or combination of columns that
make up a unique key. You can ignore this prompt, and the table will not be
updatable from Access; or you can specify one or more columns that make a
unique key. If this combination of columns is not unique (i.e. if there are
duplicate records with the same key combination) Access will not work
properly. When you enter a unique key combination, ensure that it really is
unique within the table. To see the column or columns that Access uses as
the unique key, look at the table in Design view. The column will have Akey@
symbol in the left most column.
⊗
Access uses a different algorithm to access a table depending on whether a
unique key exists or not. If it finds a unique key, it downloads the key values
and then issues an SQL statement of the form Aselect...From...Where key=?
Or key=?...@. It then displays a screenful of data and stops. (Note, however,
that it may continue to download the keys in the background so long as the
table is open in Atable view@.) If a unique key is not defined, Access just does
a regular Select statement to read the table. It will display a screenful of data
but will still continue to read the table in the background. You may view the
SQL that Access (and any ODBC application) generates by turning on
logging in the ODBC Setup Screen .
⊗
To update a record in Access, you highlight the column you want to change,
make the change, and then click on any other record. Access uses an
algorithm called AOptimistic concurrency control@ to verify that a record has
not been changed by another user. This is a less secure method than the
SELECT FOR UPDATE used by other applications, however it minimizes
the number of locks that are held on the database. Optimistic concurrency
control works by Access generating and Update statement of the form
AUpdate Table Set column1=?, column2=?,...WHERE column1=? AND
column2=? AND column3=? And ....@. This ensures that the update will fail
if the record has been changed by another user since the time it was last read
in by Access.
•The default type of join used by Access is the Inner Join. When
creating a Query with a join between two tables, you can change this to
a Left Outer Join by double-clicking on the link between the tables. You
may also use the Edit Joins screen to change the default join between
specific tables, so that you don=t have to edit the join every time you
create a new Query.
⊗
You cannot join a local table to a remote table efficiently in Access unless
there is a one-to-one correspondence between records in the local table and
records in the remote table; in other words, you must have all the columns in
the local table that are necessary to form a unique key on the remote table. If
this is not the case, Access will attempt to download the remote table to the
local machine.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
45
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Using MS-Query
To Run MS-QUERY from Microsoft Excel, select Data, Get External Data, New Database Query.
The following screen is displayed.
Select the data-source you are going to use to connect (you must have pre-configured a data source with ODBC
administrator).
After connecting, a list of the tables on your host you have access to is displayed.
46
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Select one or more tables and click OK. You may join tables by dragging the column you want to join on from one
table to another in the upper part of the screen. You may view the data by double-clicking on any column name or by
dragging it from the upper to the lower part of the screen.
Note:
MS QUERY must be installed during the installation of
Microsoft Excel. Do a custom installation selecting MS
Query as one of the installed options.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
47
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Using Visual Basic (4.0 or higher)
You can use ODBCLink/SE either with VB Database controls or with Database and dynaset variables. In addition, of
course, you may call ODBC functions directly from the DLL.
To use VB database controls, define a data control and assign it a Connect property of the form
"DSN=HP3000;UID=users;PWD=password" and a RecordSource property of the form "SELECT * FROM
TableName". If you want to create a SnapShot (no update) set the Options property to 64 (SQL_PASSTHROUGH).
You may then place text controls on the screen, set the DataSource property to the name of the data control, and set
the DataField property to the name of the column in the Select statement. When the application is run, it will
download all records qualified by the Select statement (or all primary key values, depending on the
SQL_PASSTHROUGH option) and display the first record on the screen. You may scroll through the records by
using the "Up", "Down", "Top", "Bottom" buttons on the data control.
A second option is to use dynaset variables which gives the program more control, such as for loading a grid with
data. Define a database object, assign it Connect and Options properties as above, and open it with an OpenDatabase
call, as in SET DBVar=OpenDatabase(...). Then define a dynaset variable (DIM DSVar as DYNASET) and open it
with a statement of the form "Set DSVar=DBVar.OpenDynaset (SQLstatement)." If SQL Statement is a Select
statement, you can read the results from "DSVar.FieldName" and move to the next record with "DSVar.MoveNext".
If SQLStatement is an Insert or Update statement, no results are returned. You can update records with the Update
method, and you can get the selection count by calling SQLRowCount or with a query of the form "Select COUNT(*)
From Table Where Conditions". To update the table, you will of course need a primary key defined and you will need
to disable the SnapShot only button.
A third option is to call ODBC functions directly. Generally you will do an SQLAllocEnv, SQLAllocConnect and
SQLDriverConnect to open the database, followed by SQLExecDirect, followed by multiple SQLFetch and
SQLGetData calls. If you plan to use SQLBindCol to bind fetch results to local variables you must use the following
procedure:
A)
B)
C)
Declare strings variables as: Dim mystr as String
Initialize your string variables before use: mystr=String(255,0)
Use the ByVal keyword when passing the variable to the ODBC driver.
Do not use the ByVal keyword in the AlpbBuf@ parameter except for
character data, as the goal is to pass the address of the parameter, not the
value. Declare SQLBindCol as follows:
Declare Function SQLFindCol Lib Aodbc.dll@ (ByVal hstmt AS long, ByVal col As Integer, By Val wConvType As
Integer, lpdBuf As Any, ByVal dwbuflen As Long, lpcbout As Long) As Integer.
48
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Connection Examples
Connect Using SQL API
1.
2.
3.
4.
5.
6.
7.
8.
9.
DECLARE YOUR VARIABLES FOR THE CONNECTION
CREATE YOUR CONNECTION STRING
ALLOCATE A ENVIROMENT HANDLE
ALLOCATE A CONNECTION HANDLE
CONNECT TO YOUR DSN WITH SQLDriverConnect
ALLOCATE A STATEMENT HANDLE
QUERY THE DATASOURCE USING SQLExecDirect
CALL AN ERROR ROUTINE IF AN ERROR OCCURS See Sub GetError
MAKE SURE YOU RELEASE ANY ENVIROMENT THAT YOU HAVE CREATED
WHEN ERRORS OCCUR(or on exiting)
Connect Using SQLAPI in VB 4.0-6.0
The following example demonstrates how to use SQLAPI in VB 4.0-6.0. It is to be used as a basic guide line for
creating a project to use SQLAPI. This examples assumes you have the necessary SQL Declare functions and
Constants etc.
1.
Global sDSNConnect As String 'Connection string
Global henv As Long 'handle to the environment
Global hdbc As Long 'handle to the connection
Global hstmt As Long 'handle to the statement
Global rc As Integer 'Return code
Dim outstr As String * 256
Dim outlen As Integer
Function DBConnect() As Integer
2.
‘Add your connection string to be used
Example “DSN=YOUR DSN;UID=USERPWD=PASSWORD;”
sDSNConnect = “YOUR_DSN_STRING”
3.
'First Allocate an Environment Handle
rc = SQLAllocEnv(henv)
If rc <> SQL_SUCCESS Then
MsgBox ("SQLAllocEnv failed rc=" + Str(rc))
Exit Function
End If
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
49
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
4.
'Second allocate a connection handle
rc = SQLAllocConnect(henv, hdbc)
If rc <> SQL_SUCCESS Then
MsgBox ("SQLAllocConnect failed rc=" + Str(rc))
Call GetError
Call FreeEnv
Exit Function
End If
5.
'Third allocate the connection and pass in the Connection string
rc = SQLDriverConnect(hdbc, Form1.hWnd, sDSNConnect, Len(sDSNConnect),
outstr, 256, outlen, 3)
If rc <> SQL_SUCCESS Then
If rc = SQL_NO_DATA_FOUND Then
Exit Function 'User cancelled dialogue
End If
Call GetError
Call Freeconnect
Call FreeEnv
Exit Function
End If
6.
'After connecting, allocate a statement handle
rc = SQLAllocStmt(hdbc, hstmt)
If rc <> SQL_SUCCESS Then
Call GetError
Call Disconnect
Exit Function
End If
7.
'Now call an SQL query to select your data
rc = SQLExecDirect("Select * from member.membership")
If rc <> SQL_SUCCESS Then
Call GetError
MsgBox "Unable to connect to the Database Environment!", vbCritical
Call Disconnect
Exit Function
End If
End Function
8.
50
Sub GetError()
Dim error_str As String * 256
Dim SQLState As String * 20
Dim outlen As Integer
Dim NativeError As Long
Dim msg As String
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
rc = SQLError(henv, hdbc, SQL_NULL_HSTMT, SQLState, NativeError, error_str,
256, outlen)sDSNConnect = Space(256)
If rc <> SQL_NO_DATA_FOUND Then
msg = Left(error_str, outlen)
If gl_SQLStatement <> "" Then
msg = msg + " (" + gl_SQLStatement + ")"
End If
MsgBox msg
End If
End Sub
9.
Sub Freeconnect()
rc = SQLFreeConnect(hdbc)
If rc <> SQL_SUCCESS Then
Call GetError
End If
End Sub
Sub FreeEnv()
rc = SQLFreeEnv(henv)
If rc <> SQL_SUCCESS Then
Call GetError
End If
End Sub
Sub Freestmt()
rc = SQLFreeStmt(hstmt, SQL_DROP)
If rc <> SQL_SUCCESS Then
Call GetError
End If
End Sub
Sub Disconnect()
rc = SQLDisconnect(hdbc)
If rc <> SQL_SUCCESS Then
Call GetError
End If
rc = SQLFreeConnect(hdbc)
If rc <> SQL_SUCCESS Then
MsgBox ("SQLFreeConnect failed rc=" + Str(rc))
Exit Sub
End If
rc = SQLFreeEnv(henv)
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
51
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
If rc <> SQL_SUCCESS Then
MsgBox ("SQLFreeEnv failed rc=" + Str(rc))
Exit Sub
End If
End Sub
Connecting with DAO’s
1.
2.
3.
4.
5.
6.
DECLARE YOUR VARIABLES FOR THE CONNECTION
SET THE DAO WORKSPACE
SET AND OPEN THE CONNECTION TO THE DATABASE
SET AND OPEN THE RECORDSET FOR THE TABLE
SE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT
ACORDINGLY
MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED WHEN
ERRORS OCCUR(OR WHEN EXITING)
1.
Dim DAOWS As DAO.Workspace
Dim DAORS As DAO.Recordset
Dim DAODB As DAO.Database
2.
'SET A WORKSPACE ENVIROMENT FOR YOUR CONNECTION
'AND SET THE TYPE TO DBUSEODBC
'Set DAOWS = CreateWorkspace("NewODBCWorkspace", "", "", dbUseODBC)
3.
'OPEN THE CONNECTION PASS IN YOU CONNECTION STRING
'Set DAODB = Workspaces(0).OpenDatabase("", dbDriverComplete, True,
"ODBC;DSN=YOURDSN;UID=YOURUID;PWD=YOURPASSWORD;")
4.
'OPEN THE TABLE AND ENTER THE SQL QUERY
'Set DAORS = DAODB.OpenRecordset("Select * from membership", dbOpenDynaset,
dbSQLPassThrough)
5.
While Not DAORS.EOF
' Insert your query processing code here.
DAORS.MoveNext
Wend
6.
‘Close Resultset
DAORS.Close
'Close Data Base Connection
DAODB.Close
52
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Using ODBCLink/SE With ODBC Applications
Connecting with RDO’s
1.
2.
3.
4.
5.
6.
DECLARE YOUR VARIABLES FOR THE CONNECTION
SET THE RDO ENIVROMENT
SET AND OPEN THE CONNECTION TO THE DATABASE
SET AND OPEN THE RECORDSET FOR THE TABLE
USE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT
ACORDINGLY
MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED
WHEN ERRORS OCCUR(OR WHEN EXITING)
1.
Dim RDOEN As RDO.rdoEnvironment
Dim RDOCN As New RDO.rdoConnection
Dim RDORS As RDO.rdoResultset
Dim RDOCL As RDO.rdoColumn
2.
'Create an RDO Environment and specify the type
'of cursor we want. The Client Side cursor seems
'to give the most accurate results.
Set RDOEN = rdoEngine.rdoEnvironments(0).CursorDriver = rdUseOdbc
3.
'Open a connection to the database
Set RDOCN = RDOEN.OpenConnection(YOURDSN)
4.
‘Open the table
Set RDORS = RDOCN.OpenResultset
(“Select * from Member.Membership”,rdOpenKeyset)
5.
While Not RDORS.EOF
' Insert your query processing code here.
RDORS.MoveNext
Wend
6.
'Close resultset
RDORS.Close
'Close db connection
RDOCN.Close
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
53
Using ODBCLink/SE With ODBC Applications
ODBCLink/SE Reference Manual
Connecting with ADO’s
1.
2.
3.
4.
5.
6.
DECLARE YOUR VARIABLES FOR THE CONNECTION
SET THE CONNECTION STRING
SET AND OPEN THE CONNECTION TO THE DATABASE/TABLE
SET AND OPEN THE RECORDSET FOR THE TABLE
USE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT ACCORDINGLY
MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED
WHEN ERRORS OCCUR(OR WHEN EXITING)
1
Dim ADORS As ADODB.Recordset
Dim ADOCN As ADODB.Connection
Dim strConnect As String
2
strConnect = "DSN=YOURDSN;UID=YOURUID;PWD=YOURPWD;"
3
Set ADOCN = New ADODB.Connection
'Set cursor for the client or server end
ADOCN.CursorLocation = adUseClient
'Open Connection
ADOCN.Open strConnect
4
'Open the Recordset
Set ADORS = New ADODB.Recordset
With ADORS
'Set the cursor type to be used with the recordset
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
'Open the recordset with query, connection
.Open "SELECT * FROM TABLE", strConnect
End With
5
While Not ADORS.EOF
' Insert your query processing code here.
ADORS.MoveNext
Wend
6
'close objects and set to nothing
ADORS.Close
ADOCN.Close
Set ADORS = Nothing
Set ADOCN = Nothing
End Sub
54
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Troubleshooting
Troubleshooting
Terminating an ODBC Connection
Occasionally it may be necessary to terminate an ODBC session that has deadlocked or is attempting to read a very
large table (note that rebooting the Client PC will not be sufficient as the server has control at this point). You may do
this from ISQL with the command TERMINATE USER user@account.
When an ODBC client terminates abnormally, the server process that handles the connection will normally go down
after the timeout interval has expired. This is typically 30 minutes to 2 hours.
On an HP-UX system, you can kill the process manually (if you know the process-id) with the command “kill -16
pid” (pid is the process id).
On MPE/iX you must either use the ISQL TERMINATE USER command or abort the listener job using the
ABORTJOB command.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
55
Troubleshooting
ODBCLink/SE Reference Manual
Using Microsoft ODBC Test (32-bit)
You can use ODBC Test to verify correct functioning of the driver and to enter ad-hoc SQL commands. ODBCTEST
and ODBCTE32 are part of the ODBC SDK that comes with the Microsoft Developers Network.
To run ODBCTE32.EXE:
56
⊗
Select Connect, Full Connect. You must enter a data source name, User-ID
and a password, and click “OK”. You should get a message “Successfully
connected to databasename”.
⊗
You can get a list of tables by selecting Catalog, SQL Tables, then click OK
and then Results, Get Data All.
⊗
You can enter an SQL statement, such as “Select * from tablename” in the
Query Window and then execute it by choosing Statement, SQLExecdirect
and view the results with “Results, Get Data All”.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Troubleshooting
Verifying Software Version Numbers
Verifying the Client-Side Components
To determine which version of ODBCLink/SE is installed, run WHAT32.EXE from the RUN window.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
57
Troubleshooting
ODBCLink/SE Reference Manual
Deleting a Translation DLL
Enter TRNDEL32.EXE in the RUN window.
A welcome screen will be shown first. Click Continue.
Select the translator you want to uninstall and press OK. The translator information will then be deleted from the
registry. If the program is successful it will show a window indicating the successful removal.
58
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Troubleshooting
Once the translator has been deleted it will no longer be displayed in the translator list in the driver setup. To add the
translator back in simply rerun the ODBC driver install program.
Note:
The translator DLL file will still be in the windows directory
after the program is run, but it will not be shown in the
registries translator information.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
59
Troubleshooting
ODBCLink/SE Reference Manual
Tools on the Database Server
Monitoring Activity with HP GlancePlus/XL (HP3000)
Program.
HP GlancePlus/XL allows you to monitor a job, session, or process on MPE/iX. The GlancePlus screens display
statistics on how different system resources are used. For information, refer to the HP GlancePlus/XL User's Manual.
Monitoring Activity with HP GlancePlus/UX (HP9000)
Program.
HP GlancePlus/UX allows you to monitor a login or process on HP-UX. The GlancePlus screens display statistics on
how different system resources are used. For information, refer to the HP GlancePlus/UX User's Manual.
Displaying Active Processes with HP-UX Process
Status (ps) Command.
You can use the HP-UX ps command to display the status of your active processes. For information, refer to the HPUX man page.
Monitoring ALLBASE/SQL Activity with SQLMON.
SQLMON is a component of ALLBASE/SQL and monitors the activity of an ALLBASE/SQL DBEnvironment.
SQLMON summarizes the activity for the entire DBEnvironment, or focuses on individual sessions, programs, or
database components. For information, refer to the ALLBASE/SQL Performance and Monitoring Guidelines.
Checking the Listener Log File on the HP3000
Look for ODBCLOG where the listener is running.
Checking the Listener Log File on the HP9000
Look for odbclog in the /tmp/ directory.
Using the Host Testing Utility ODBCUTSE
If you are having problems connecting or accessing a table on the host, you can access it directly on the host in the
same way the ODBC server accesses it, by running the ODBCUTSE utility.
To run the program on MPE/iX, enter:
ODBCUTSE.ODBCSE.SYS “dbe name”
To run the program on HP/UX, enter:
or
/usr/bin/odbcse/odbcutse “dbe name” (for HP-UX V9.x systems)
/opt/allbase/bin/odbcutse “dbe name” (for HP-UX V10.x and V11.x systems)
60
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Troubleshooting
You should now be connected to the database. To see a menu of available commands, enter HELP:
0>HELP
SHOW
EXIT
SELECT
FETCH
INSERT
UPDATE
DELETE
SQL
COMMIT
PARAM
STMT
ROLLBACK
EXECUTE
QUIT
FOREIGN
TABPRIV
0>
Display tables and table structures
Exit program
SQL Select
SQL Fetch [ALL|##]
SQL Insert
SQL Update
SQL Delete
Execute immediate SQL command
SQL Commit
Parameter substitution on a prepared statement
Change current statement/cursor number
SQL Rollback
Execute stored procedure
Exit program
Display foreign Keys
Display table privileges
Enter the command you wish to use. For example, enter SHOW to see a list of available tables:
>show
Dataset/Table
MANUFDB.SUPPLYBATCHES
MANUFDB.TESTDATA
PURCHDB.PARTS
PURCHDB.INVENTORY
PURCHDB.SUPPLYPRICE
PURCHDB.VENDORS
PURCHDB.ORDERS
PURCHDB.ORDERITEMS
PURCHDB.PARTINFO
PURCHDB.VENDORSTATS
RECDB.CLUBS
RECDB.MEMBERS
RECDB.EVENTS
Database
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
partsdbe.sql.mbftest
Type
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
ALLBASE VIEW
ALLBASE VIEW
ALLBASE TABLE
ALLBASE TABLE
ALLBASE TABLE
To see the list of columns and indexes for a table, enter SHOW followed by the name of the table. For example:
>sho recdb.clubs
Field
ODBC-Datatype
HP-Datatype
CLUBNAME
CHAR(15)
CLUBPHONE SMALLINT
ACTIVITY
CHAR(18)
Length
15
2
18
Offset
0
16
20
Indexed field
Index Type
Index Name
CLUBNAME
CONSTRAINT PRIMARY
CLUBS_PK
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
Nulls
1
1
61
Troubleshooting
ODBCLink/SE Reference Manual
Host Logging
Normally, only connections and errors are logged to the ODBCLOG file on the host. If you check the box labelled
“Trace SQL calls on the server” in the data-source setup dialogue, all the SQL sent from the host and the ALLBASE
operation will be logged as well.
If a listener error occurs, an error message is logged in the ODBCLOG file.
Here is the format of the ODBCLOG file (this file can be found in group and account of the listener job, typically
ODBCLOG.ODBCSE.SYS on MPE systems and /tmp on Unix systems).
00/1000/10/20 14:12:09 068ODBC listener started
00/10/20 14:13:13 068 Listener created new connection as SYS1 (PIN 77), IP=192.9.3.102
00/10/20 14:13:13 077 ODBCLNSE E.52.00 started from sockets
00/10/20 14:13:22 077 Allbase Prepare[49]SELECT NAME, OWNER, TYPE, NPAGES, NROWS FROM
CATALOG.TABLE;
00/10/20 14:13:26 077 Allbase SetParams[49]
00/10/20 14:13:27 077 Allbase Fetch[49] Rows=119 MaxRows=200
00/10/20 14:13:27 077 Allbase Fetch[49] Rows=0 MaxRows=200
00/10/20 14:13:27 077 Allbase Close cursor[49]
00/10/20 14:13:27 077 Allbase Commit
00/10/20 14:13:27 077 Allbase Prepare[49]SELECT NAME,OWNER,NUMP,NUMR,MULTIRESULT FROM
CATALOG.PROCEDURE;
00/10/20 14:13:27 077 Allbase SetParams[49]
00/10/20 14:13:28 077 Allbase Fetch[49] Rows=6 MaxRows=200
00/10/20 14:13:28 077 Allbase Fetch[49] Rows=0 MaxRows=200
00/10/20 14:13:28 077 Allbase Close cursor[49]
00/10/20 14:13:28 077 Allbase Commit
00/10/20 14:13:28 077 Connected to partsdbe, ClientVersion=E.52.00
00/10/20 14:13:35 077 Prepare[0]SELECT _TABLES
00/10/20 14:13:35 077 SetParams[0] nparam=0
00/10/20 14:13:53 077 FreeStmt[0]
00/10/20 14:13:54 077 Normal exit, Received 3 requests (133 bytes), Sent 0 records (5141 bytes), CPU=2
(5.6%)
The first three columns of the list are the date, time, and pin (process id number) of the requesting connection. The
last column is the Allbase operation being performed, with the statement or cursor number in square brackets. The
number is square brackets (as in Allbase prepare[49]) is the statement number.
62
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Troubleshooting
Tools on the Client PC
The preferred method to trace ODBC calls on the client is by setting the “Trace ODBC Calls” check box in ODBC
setup. However, Syware’s Dr. DeeBee Spy is installed with ODBCLink/SE (in the Windows directory). It can be used
to trace calls to any ODBC driver.
ODBC Call Tracing using ODBCLink.LOG
To log all the ODBC calls made by the client, check the box “Trace ODBC calls on the client” on the data-source
setup screen. This will create a file, called ODBCLINK.LOG, of all the ODBC calls made by the client. This file
normally resides in the directory where the ODBC application was started from. Some applications however change
their working directory, for MS-Access for instance look for the log file in “My Documents” directory.
Here is the format of the ODBCLINK.LOG file:
SQLDriverConnect(0) hstmt=0 Ver= 5.52.0000 szConnStrIn=,Completed_string=%$', ...);
Where BLOB is the name of the file on the HP and the '>%$' tells ALLBASE/SQL to copy the contents to a memory
location when retrieving the row. You can also have ALLBASE/SQL copy it to a file during retrieval
('OUTFILE'). In either case, the data can be retrieved in the standard way (SQLFetch followed by
SQLGetData in chunks).
You may also create a binary column on the HP by using ODBC functions call sequence: SQLPrepare,
SQLBindparameter (DATA_AT_EXECUTION), SQLExecute, SQLParamData, SQLPutData.
Maximum number of statements
ODBCLink/SE supports up to 50 concurrent statements, or cursors, per connection. However, SQLGetInfo with
option SQL_MAX_STMT will only report 1 as the maximum number of concurrent statements. This is because
multiple statements (or cursors) in ALLBASE/SQL on the same connection are not truly independent. Having
SQLGetInfo return 1 for SQL_MAX_STMT forces MS-Access, and other applications, to use multiple connections
(multiple SQLDriverConnect’s) instead of multiple statements on the same connection (one SQLDriverConnect and
multiple SQLAllocStmt’s).
You can use multiple statements from within the same connection (in fact, some applications ignore the SQLGetInfo
and do this anyway). There is a performance advantage to doing things this way since a new statement (or cursor) in
ALLBASE/SQL takes much less overhead than a new connection. However you should be aware of the following:
⊗
In ALLBASE/SQL, a COMMIT or ROLLBACK operation closes all open
cursors within the connection. If you have multiple Select statements going
on different statements, the next time you SQLFetch on one statement, after
a Commit on another statement, you will get an error from ALLBASE/SQL
“ALLBASE has closed the cursor for this statement”. You may not get this
error immediately, as ODBCLink/SE caches a certain number of rows
during a fetch.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
67
Appendix A Implementation Notes
ODBCLink/SE Reference Manual
⊗
If you are in AUTOCOMMIT mode ON (this is the default option, set
through SQLSetConnectOptions and in ODBC Administrator), there is an
implicit Commit done whenever you fetch the last row. This is so that locks
held on the database by the open cursor are released. This may destroy other
open cursors. Note also that opening a table for the first time automatically
triggers various initialization calls that cause a commit to be done when
AUTOCOMMIT is ON.
⊗
If you are using prepared statements with substitution parameters to insert
or update you need not be concerned about this, as the prepared statement
will still be valid after a Commit, and you may bind new parameters and
SQLExecute again with no problem. Prepared statements are SQL
statements with question marks ‘?’ that you SQLPrepare and then call
SQLBindParam or SQLBindCol.
If you wish to use multiple statements, but are not writing your own SQL (i.e. you are using a 4GL application) you
can tell ODBCLink/SE to have SQLGetInfo report more than 1 statement per connection. To do this, modify the
ODBC.INI or the registry manually, and add a new parameter “MAXSTMT=50".
ALLBASE/SQL and IMAGE/SQL Restrictions on the ODBC Grammar
The following table summarizes the ALLBASE/SQL restrictions on the ODBC grammar:
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 that is 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.
Using the ANSI Character Set
The HP3000 and HP9000 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 PC client application uses these extended or special characters, then the data from the database server must be
converted from ROMAN8 to ANSI, and the data returning to the database server must be converted from ANSI to
ROMAN8. To activate the conversion, select the “Roman8 to PC-ANSI translation DLL during the data-source
setup. The conversion takes place on the PC client.
Bind variables of datatype CHAR and VARCHAR are also converted. A bind variable in an SQL statement is
associated (bound) to variables defined in a program. A bind variable is another name for a dynamic parameter.
68
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Note:
Appendix A Implementation Notes
Roman9 to PC-ANSI Translator will recognize and convert
the symbol used for the European Currency Symbol, the
EURO.
Some characters cannot be converted to ROMAN8 and then back to ANSI. For example, when the copyright sign in
ANSI is converted to ROMAN8, it is changed to a lowercase "c." When the data is returned to the PC client, the
copyright sign cannot be recognized, as it was converted to a "c." The following characters, sent from the PC client,
cannot be properly converted from ANSI to ROMAN8 in a round-trip fashion. (The hex designation is listed along
with the character name.)
•_A6 broken bar
•_A9 copyright sign
•_AC not sign
•_AE registered trade mark
•_B2 superscript 2
•_B3 superscript 3
•_B8 cedilla
•_B9 superscript 1
•_D7 multiply sign
•_F7 divide sign
Similarly, the following characters, sent from the database server, cannot be properly converted from ROMAN8 to
ANSI in a round-trip fashion.
•_A9 accent grave
•_AA circumflex accent
•_AC tilde accent
•_BE Dutch guilder
•_EB uppercase S caron
•_EC lowercase s caron
•_EE uppercase Y umlaut
•_F6 long dash
•_FC solid box
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. They are explained here:
⊗
⊗
⊗
⊗
Several ALLBASE/SQL statements work only with embedded SQL;
ODBCLink/SE does not support embedded SQL.
Some ALLBASE/SQL statements relate to functions, such as connection,
that have been replaced by functions resident on the PC client.
Some ALLBASE/SQL and IMAGE/SQL 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.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
69
Appendix A Implementation Notes
ODBCLink/SE Reference Manual
ALLBASE/SQL and IMAGE/SQL Statements That Work Only with
Embedded SQL
PC client application software that accesses ALLBASE/SQL and IMAGE/SQL databases through ODBCLink/SE do
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:
CLOSE CURSOR
BEGIN DECLARE SECTION
DECLARE CURSOR
DESCRIBE
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
FETCH
INCLUDE
OPEN
PREPARE
REFETCH
SQLEXPLAIN
WHENEVER
ALLBASE/SQL and IMAGE/SQL Statements Replaced by Functions on the PC Client
Some of the functionality has been moved from the database server to the PC 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 PC client:
CONNECT
DISCONNECT
RELEASE
RESET
SET CONNECT
SET MULTITRANSACTION
START DBE
START DBE NEW
START DBE NEWLOG
STOP DBE
Note:
70
If you attempt to use any of these unsupported statements,
you will receive an error message.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix B Creating a DBEnvironment
Appendix B Creating a DBEnvironment
On the HP3000 Database Server
To enable a connection between a PC client and database server, you must set up an ALLBASE/SQL
DBEnvironment. If you do not have an existing database for testing purposes, you can use the ALLBASE/SQL
SQLSetup tool to create a DBEnvironment called PartsDBE. If you are using IMAGE/SQL, you can use the
IMSQL.SAMPLEDB.SYS command to create an IMAGE/SQL database called MusicDBE. For more information,
refer to Getting Started with HP IMAGE/SQL.
Follow these steps to create the PartsDBE database on the HP3000:
⊗
⊗
Logon to the group and account where you want to create the
DBEnvironment.
From the command prompt, enter the following command:
:SQLSETUP.SAMPLEDB.SYS
A menu like the following will appear on your screen:
Options for Setting Up ALLBASE/SQL Sample DBEnvironments
===============================================================
Choose one:
1.
2.
3.
4.
5.
6.
7.
0.
Create PartsDBE without sample programs
Create PartsDBE, copy, preprocess and compile sample programs
Copy, preprocess and compile sample programs only
Generate a schema for PartsDBE
Display schema for PartsDBE
Purge PartsDBE and sample programs
Help
Exit
===============================================================
Enter your choice=>
⊗
Choose option 1 to create PartsDBE. This option creates the
DBEnvironment, defines all of its tables, views, indexes, and security
structure, and then loads it with data. As the system creates PartsDBE, you
see several messages displayed. At the end of the creation process, you see
the following message:
Creation and Loading of PartsDBE is now complete!
Press Return to continue...
⊗
Choose 0 to exit the menu.
If you have designed your own database for testing purposes, you can consult your database administrator and
complete the following tasks:
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
71
Appendix B Creating a DBEnvironment
⊗
⊗
⊗
⊗
ODBCLink/SE Reference Manual
Plan the security structure of the DBEnvironment. Decide which users will
be granted access to the DBEnvironment, databases within the
DBEnvironment, and tables within the databases.
Create the account where the DBEnvironment is to reside.
Grant authorizations to users who will be using the test DBEnvironment.
Create the DBEnvironment, including databases and tables.
After you setup the DBEnvironment, ensure that you can connect to it. For example,
: isql
isql=> CONNECT TO 'PartsDBE.SomeGrp.SomeAcct';
isql=> SELECT * FROM SYSTEM.TABLE;
:
At this point you should see the system table information.
:
U[p],d[own],l[eft],r[ight],t[op],b[ottom],pr[int],or e[nd]
> e;
isql=> exit;
72
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix B Creating a DBEnvironment
On the HP9000 Database Server
To enable a connection between a PC client and database server, you must set up an ALLBASE/SQL
DBEnvironment. If you do not have an existing database for testing purposes, you can use the ALLBASE/SQL
SQLSetup tool to create a DBEnvironment called PartsDBE.
Follow these steps to create the PartsDBE database on the HP9000:
⊗
⊗
Login to the directory where you want to create the DBEnvironment.
From the C shell, enter the following command:
$ /usr/lib/allbase/hpsql/sqlsetup
•From the Korn shell or Bourne shell, enter the following command:
$ csh /usr/lib/allbase/hpsql/sqlsetup
A menu like the following will appear on your screen:
Options for Setting Up ALLBASE/SQL Sample DBEnvironments
=======================================
Choose one:
1. Create PartsDBE without sample programs
2. Create PartsDBE, copy, preprocess and compile sample programs
3. Copy, preprocess and compile sample programs only
4. Generate a schema for PartsDBE
5. Display schema for PartsDBE
6. Purge PartsDBE and sample programs
7. Help
0. Exit
========================================
Enter your choice=>
⊗
Choose option 1 to create PartsDBE. This option creates the
DBEnvironment, defines all of its tables, views, indexes, and security
structure, and then loads it with data. As the system creates PartsDBE, you
see several messages displayed. At the end of the creation process, you see
the following message:
Creation and Loading of PartsDBE is now complete!
Press Return to continue...
⊗
Choose 0 to exit the menu.
If you have designed your own database for testing purposes, you can consult your database administrator and
complete the following tasks:
⊗
⊗
Plan the security structure of the DBEnvironment. Decide which users will
be granted access to the DBEnvironment, databases within the
DBEnvironment, and tables within the databases.
Create the directory where the DBEnvironment is to reside.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
73
Appendix B Creating a DBEnvironment
⊗
⊗
ODBCLink/SE Reference Manual
Grant authorizations to users who will be using the test DBEnvironment.
Create the DBEnvironment, including databases and tables.
After you setup the DBEnvironment, ensure that you can connect to it. For example,
$ isql
isql=> CONNECT TO '/users/hpsql/sampledb/PartsDBE’;
isql=> SELECT * FROM SYSTEM.TABLE;
:
At this point you should see the system table information.
:
U[p],d[own],l[eft],r[ight],t[op],b[ottom],pr[int],or e[nd]
> e;
isql=> exit;
74
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix C List of Installed Files
Appendix C List of Installed Files
32 Bit Driver Client - For Windows 95 and Greater
Note:
“..\” is a short-hand that represents the location of the
windows installation (e.g. c”\windows\”)
Install Disc
Windows 95
_bootstp.exe
not installed to hard drive
_mssetup.ex_
not installed to hard drive
ctl3d95.dl_
..\system\ctl3d95.dll
DLL for 3D controls in Windows95
b5ccdc32.dl_
..\system\b5ccdc32.dll
Used for character translation for Chinese characters
drdbsp32.ex_
..\system\drdbsp32.exe
32-bit Dr. DeeBee spy used for debugging
drdbsp32.dl_
..\system\drdbsp32.dll
32-bit Dr. DeeBee spy used for debugging
drvstp32.exe
not installed to hard drive
32-bit ODBC driver setup program
dvrdel32.ex_
..\system\dvrdel32.exe
Utility used to delete a 32-bit driver from the registry
ds32gt.dl_
..\system\ds32gt.dll
32-bit Driver Setup Generic Thunking DLL.
mfc30.dl_
..\system\mfc30.dll
Microsoft Foundation Class Library
msvcrt20.dl_
..\system\msvcrt20.dll
Microsoft Run-Time Library
odbc.inf
not installed to hard drive
Installation control file
odbc32.dl_
..\system\odbc32.dll
32-bit ODBC Driver Manager
odbc32gt.dl_
..\system\odbc32gt.dll
32-bit ODBC Generic Thunking DLL
odbcad32.ex_
..\system\odbcad32.exe
ODBC Administrator Application
odbccp32.cp_
..\system\odbccp32.cpl
32-bit ODBC Installer Control Panel Tool
odbccp32.dl_
..\system\odbccp32.dll
32-bit ODBC Control Panel Installer DLL
odbccr32.dl_
..\system\odbccr32.dll
32-bit cursor library
odbcinst.hl_
..\system\odbcinst.hlp
Installation help file
odbcln32.dl_
..\system\odbcln32.dll
32-bit ODBCLink/SE Driver
odbcls32.dll
..\system\odbcls32.dll
32-bit ODBCLink/SE Driver Setup DLL
r8ansi32.dl_
..\system\r8ansi32.dll
32-bit character translation DLL for translations between the
ANSI and roman-8 character sets.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
Description
75
Appendix C List of Installed Files
ODBCLink/SE Reference Manual
r9ansi32.dl_
..\system\r9ansi32.dll
32-bit character translation DLL for translations between the
ANSI and roman-9 character sets.
setup.exe
not installed to hard drive
Microsoft ODBC Setup program drive
setup.lst
not installed to hard drive
trndel32.exe
...\system\trndel32.exe
Translator uninstall utility to “de-register” translator
information
what32.ex_
...\system\what32.exe
Utility to determine what versions of drivers required by
ODBC application are currently loaded on the Client PC
76
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix C List of Installed Files
32 Bit Driver Client - For Windows NT 4.0 and Greater
NOTE: “..\” is a short-hand that represents the location of the
windows installation (e.g. c”\windows\”)
Install Disc
Windows NT
_bootstp.exe
not installed to hard drive
_mssetup.ex_
not installed to hard drive
b5ccdc32.dl_
..\system32\b5ccdc32.dll
Used for character translation for Chinese characters
ctl3dnt.dl_
..\system32\ctl3dnt.dll
DLL for 3D controls in WindowsNT
drdbsp32.ex_
..\system32\drdbsp32.exe
32-bit Dr. DeeBee spy used for debugging
drdbsp32.dl_
..\system32\drdbsp32.dll
32-bit Dr. DeeBee spy used for debugging
drvstp32.exe
not installed to hard drive
32-bit ODBC driver setup program
dvrdel32.ex_
..\system32\dvrdel32.exe
Utility used to delete a 32-bit driver from the registry
ds32gt.dl_
..\system32\ds32gt.dll
32-bit Driver Setup Generic Thunking DLL.
mfc30.dl_
..\system32\mfc30.dll
Microsoft Foundation Class Library
msvcrt20.dl_
..\system32\msvcrt20.dll
Microsoft Run-Time Library
odbc.inf
not installed to hard drive
Installation control file
odbc32.dl_
..\system32\odbc32.dll
32-bit ODBC Driver Manager
odbc32gt.dl_
..\system32\odbc32gt.dll
32-bit ODBC Generic Thunking DLL
odbcad32.ex_
..\system32\odbcad32.exe
ODBC Administrator Application
odbccp32.cp_
..\system32\odbccp32.cpl
32-bit ODBC Installer Control Panel Tool
odbccp32.dl_
..\system32\odbccp32.dll
32-bit ODBC Control Panel Installer DLL
odbccr32.dl_
..\system32\odbccr32.dll
32-bit cursor library
odbcinst.hl_
..\system32\odbcinst.hlp
Installation help file
odbcln32.dl_
..\system32\odbcln32.dll
32-bit ODBCLink/SE Driver
odbcls32.dll
..\system32\odbcls32.dll
32-bit ODBCLink/SE Driver Setup DLL
r8ansi32.dl_
..\system32\r8ansi32.dll
32-bit character translation DLL for translation between the
ANSI and roman-8 character set.
r9ansi32.dl_
..\system32\r9ansi32.dll
32-bit character translation DLL for translation between the
ANSI and roman-9 character set.
setup.exe
not installed to hard drive
Microsoft ODBC Setup program drive
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
Description
77
Appendix C List of Installed Files
ODBCLink/SE Reference Manual
setup.lst
not installed to hard drive
trndel32.exe
...\system32\trndel32.exe
Translator uninstall utility to “de-register” translator
information
what32.ex_
...\system32\what32.exe
Utility to determine what versions of drivers required by
ODBC application are currently loaded on the Client PC
78
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix C List of Installed Files
MPE/iX Host
ODBCJOB.ODBCSE.SYS
ODBCLNSE.ODBCSE.SYS
ODBCXLSE.ODBCSE.SYS
ODBCUTSE.ODBCSE.SYS
ODBCCL32.ODBCSE.SYS
sample jobstream to run the listener process
the listener program
an XL library containing common code
utility for technical support purposes
a self-extracting archive for the 32bit client
software
ODBCLOG.ODBCSE.SYS
log file (when created).
HP-UX 9.x Host
/usr/bin/odbcse/odbclnse
/usr/bin/odbcse/odbcutse
/usr/bin/odbcse/odbccl32
the listener program
utility for technical support purposes
a self-extracting archive of the 32bit client
software
HP-UX 10.x and 11.x Host
/opt/allbase/bin/odbcse/odbclnse
/opt/allbase/bin/odbcse/odbcutse
/opt/allbase/bin/odbcse/odbccl32
the listener program
utility for technical support purposes
a self-extracting archive of the 32bit client
software
/tmp/odbclog
log file (when created).
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
79
Appendix C List of Installed Files
ODBCLink/SE Reference Manual
Notes
80
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Appendix D ODBCLink/SE Companion Product
MBF-Console
MBF-Console Introduction
MBF-Console is a software tool that will increase the visibility of ODBC use. It will:
⊗
Monitor resource utilisation of ODBC connections to a specific HP-3000 or
HP-9000 computer
⊗
Kill “runaway” or inactive server processes
⊗
Start or stop logging of a specific ODBC connection and then view activity
from the host logfile, including all SQL sent from the client
How MBF-Console Works
The listener process creates server processes as needed to handle incoming connection requests. On MPE, these
processes run as children of the listener process and are therefore not visible in SHOWJOB. Normally, the system
administrator has no way of knowing the identity and resource utilization of ODBC users on the system. That is the
purpose of MBF-Console.
MBF-Console creates an ODBC connection to the listener process and to a server. For this it needs a valid datasource configuration (through the ODBC Control Panel). Once connected, it is able to obtain information about other
server processes by reading the statistics file. The statistics file is a shared file on the host that is updated by active
server processes at a maximum interval of 1 minute. The server processes are not interrupted by the MBF-Console
program; rather, they produce statistical information when ODBC requests come in from the client.
MBF-Console displays information for all ODBC server processes on the specific host, even when multiple listeners
are running. If information is required from several hosts, multiple copies of MBF-Console may be run, one for each
host to be monitored.
Note:
MBF-Console requires MBF-UDALink version 5.57.09 or
ODBCLink/SE version E.57 or greater to run on both the host
and client side.
Running MBF-Console
To Start MBF-Console, go to "Startup, Programs, MB Foster, MBF-Console and double click on the MBF-Console
Icon. The MBF-Console screen is displayed. The following pages will discuss the Option Headings located at the
top of the screen.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
81
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
File Menu Options
Set Refresh Interval (in seconds)
The Refresh Interval is the interval of time, in seconds, after which MBF-Console will automatically refresh its
information. The time interval is normally determined by the System Administrator.
Page Setup
Define the Page Setup for the printer using Standard Windows Dialog
82
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Print
Print using Standard Windows Dialog
Exit
Exit from the Program
Connect Menu Options:
Configure DSNs... (Console Data Sources Configuration)
Select: Connect from the top of the screen then Configure DSN” from the
drop down menu.
The following screen is displayed.
⊗
Click the “Add New DSN” button and enter the name of the Data Source
you are going to use.
⊗
Click “Ok”.
Note:
A data-source should be added for every machine for which
you require statistical information.
If you require
information on setting up a Data Source, refer to the MBFUDALink or ODBCLink/SE documentation.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
83
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
Connections Windows
Select: the appropriate “Connect, DSN=” menu to bring up the following
windows:
The top window is the Listener Window. The lower window is the Connections Window.
The Listener Window
The listeners window contains information about ODBCLink listener processes running on your system. It is a
graphic representation of active listener jobs. To view detailed process information in the Connections Window,
double-click anywhere on the table, or use the “Window, Connections” command. The following statistics are
available in the Listeners Windows:
⊗
⊗
⊗
⊗
⊗
⊗
Port Number
Time Process Started
Version
Number of Job
Number of Users
Number of CPU Seconds
The Connections Window
The connections window lists all ODBCLink connections to your system from all listener processes including
Console itself. The following information is presented for each connection:
84
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
⊗
⊗
⊗
⊗
⊗
⊗
⊗
⊗
Appendix D ODBCLink/SE Companion Product
Port Number
Pin Number
Time Process Started
IP Address of Process
Login ID of Process
CPU Seconds Used
Number and Rate of Messages Received
Number and Rate of Messages Sent
To obtain more detailed information about a specific connection, double-click on it. This will open the Server
Window
Server Window:
The server window is displayed by double clicking a connection in the Connections window. In addition to repeating
what is in the connections window, the following statistics are available in the Server Windows:
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
85
Appendix D ODBCLink/SE Companion Product
⊗
⊗
⊗
⊗
⊗
⊗
⊗
⊗
⊗
⊗
ODBCLink/SE Reference Manual
ODBCLOG Setting
Client Version
Statistics for Time Period
Time of Last Server Activity Reported
Bytes Received
Bytes Sent
Number of Select Statements
Number of Records Fetched
Number of Update Statements
Number of Delete Statements
The options available in the Server Window include:
⊗
View Logfile
Click “View logfile” to view the logfile entries for this process. This will display ODBCLink commands sent to the
server.
⊗
Start Logging
From the Server window, click the “Start Logging” button to start logging all SQL statements sent to the host. This
will start logging all ODBCLink commands sent to the server. If the server is doing a long sort or fetching a lot of
data, this may not be immediately reflected in the logfile. There may be a delay of up to 1 minute until these settings
take effect on the server.
Note:
⊗
The “Start logging” command is the same as setting the
ODBCLINK_LOG environment variable on the host. MBFConsole will display the Listeners window and the
Connections window.
Stop Logging
This will stop the logging of ODBC commands sent to the server.
⊗
Kill Process
This command, available from the Server window, will kill an ODBCLink server process. Use it to kill nonresponding or deadlocked server processes, or processes that are using up too many resources on the system
⊗
Help
This will call up the Windows Help subsystem.
⊗
Close Window
This will close the Server window.
86
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
“View” Menu Option
Refresh the Screen with this menu option.
“Window” Menu Option
This will list all open windows. From this list you can navigate between the available windows.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
87
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
Help Menu Options:
Look for help using Standard Windows Dialog.
Note:
88
Context-sensitive help is available at any time by pressing
F1.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Installing MBF-Console for MBF-UDALink
The following example uses Reflection File Transfer from an HP-3000 to obtain the zipped file containing MBFConsole. Similar procedures in other binary transfer methods can be used.
Note:
If you are installing from a CD, go to page 91
Prior to the Reflection File Transfer, you will have to do the following:
⊗
Logon as MANAGER.SYS.
⊗
Restore DXINSTAL.PUB.SYS from the installation tape.
⊗
Run DXINSTAL.PUB.SYS.
You will be prompted throughout the
installation. Put the tape back on line and reply to any requests.
DataExpress will prompt you for a group to reside in. This will be
DX.MBFOSTER by default.
If you encounter problems, contact out Technical Support Department at 1800-ANSWERS or E-Mail to support@mbfoster.com .
⊗
Note:
Create a directory called MBFoster for the client software.
This can be done using MS Explorer.
In the main menu at the top of the Reflection screen,
Select: File from the drop down menu
Select: Transfer… The File Transfer window is displayed.
Move the cursor to the Local File Name field, located at the top left of the File Transfer Window,
and type the name of the.exe file
Type:
c:\mbfoster\odbccons.exe
Note:
If you are installing a demo version of MBF-Console, enter
c:\mbfoster\democons.exe. The demo version will remain
active for 45 days after it has been installed. A demo version
can be downloaded from the MBFoster web page,
www.mbfoster.com, under Products. The demo version
obtained from the web page can be installed by running
democons.exe from the Run option of the Windows 95,
Windows 98 or Windows NT Start button.
Place the cursor in the Host File Names field,
Type:
odbccons.dx/dxdemo.mbfoster
DX or DXDEMO illustrates that both can be used but only one is entered
depending if the version being installed is production or demo. If you are testing a
beta version, use DXBETA.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
89
Appendix D ODBCLink/SE Companion Product
Note:
ODBCLink/SE Reference Manual
Ensure the Transfer Type is Binary
Select: the left side of the Transfer button, located top center of the screen,
to receive from the Host, to the local PC.
The File Transfer in Process screen appears briefly.
When the File Transfer is completed, the zipped file containing MBF-Console is ready to be installed on your PC.
Select: Start, Run. The Run window is displayed.
Type:
90
C:\MBFOSTER\ODBCCONS.EXE and press OK
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
The Self Extract Utility inflates and briefly lists the files on the screen while writing them to the folder selected.
Select: Start, Run. The Run window is displayed, as above.
If you are installing from a CD
Type: E:\MBFOSTER\MBF-CONSOLE\SETUP.EXE
( “E” represents the drive letter of the CD-ROM)
If you followed the tape installation instructions
Type:
C:\MBFOSTER\SETUP.EXE in the command field
MBF-Console prepares for installation. The welcome screen is displayed.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
91
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
Select: NEXT.
92
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
The Destination Location screen is displayed. This screen enables you to install in MBFConsole in the directory of
your choice. When you have decided, click Next.
The next screen requires you to chose a program group for the MBF-Console Icon to reside.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
93
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
When you have decided, press Next.
94
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Setup is ready to install MBFConsole. If all the information you have entered is correct, press Next
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
95
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
The Installation Progress Bar is briefly displayed, followed by the Setup is Complete screen.
Select: Finish to complete the Setup.
Note:
96
Occasionally the installation of MBF-Console will install
newer versions of the .dll files on your PC. If this is the case,
you will be prompted to reboot you computer. Do so prior to
running MBF-Console.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Installing MBF-Console for ODBCLink/SE
In order to test the console product, M. B. Foster has set up an environment where you can run a second listener while
still keeping any older version still running. This is accomplished by utilizing a second socket identifier. This will
give an opportunity to test out the new version and to evaluate the MBFConsole product. The MBFConsole product
will not work with versions of ODBCLink/SE older then version E.57.
The following are instructions for installing the current version of the ODBCLink/SE software, F.00. The material
may come from one of two sources; on tape directly from M B Foster or through the M B Foster web site. When you
receive the file from the web site it will be in a single compressed file. This file will contain the 32-bit client, the
console client, and the host files for both MPE and Unix and command files to help do the install. This file is a selfextracting zip file. Create a new directory called MBFOSTER to be used for the file extract. This can be done using
MS Explorer.
If you are installing on an MPE host:
⊗
⊗
⊗
⊗
Use your terminal emulator to log on to your host and log on as
MANAGER.SYS..
You will be creating a new group MBFOSTER.SYS that will contain the
host files.
If your are using Reflection, execute the RCL file UPLOAD3K.RCL.
If your are running Minisoft, execute the script file UPLOAD3K.S92
If you are installing on an Unix host
⊗
⊗
⊗
⊗
⊗
⊗
Use your terminal emulator to log on to your host and log on as root
You will be creating a new directory /opt/mbfoster/odbcse that will contain
the host files.
If your are using Reflection, execute the RCL file UPLOADUX.RCL.
If your are running Minisoft, execute the script file UPLOADUX.S92
Follow the instructions for installing the ODBC client.
Follow the instructions for installing the MBFConsole.
When a tape is received all the files will be on the tape. Follow the instructions for restoring the tape. You will restore
the host files and then follow the instructions for downloading and installing the ODBC client and MBFConsole.
Note:
If you are installing a demo version of MBF-Console, enter
c:\mbfoster\democons.exe. The demo version will remain
active for 45 days after it has been installed. A demo version
can be downloaded from the MBFoster web page,
www.mbfoster.com, under Products. The demo version
obtained from the web page can be installed by running
democons.exe from the Run option of the Windows 95,
Windows 98 or Windows NT Start button.
Select: Start, Run. The Run window is displayed.
Type:
C:\MBFOSTER\ODBCCONS.EXE and press OK
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
97
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
The Self Extract Utility inflates and briefly lists the files on the screen while writing them to the folder selected.
Select: Start, Run. The Run window is displayed, as above.
Type:
C:\MBFOSTER\SETUP.EXE in the command field
MBF-Console prepares for installation. The welcome screen is displayed.
Select: NEXT.
98
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
The Destination Location screen is displayed. This screen enables you to install in MBFConsole in the directory of
your choice. When you have decided, click Next.
The next screen requires you to chose a program group for the MBF-Console Icon to reside.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
99
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
When you have decided, press Next.
100
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Setup is ready to install MBFConsole. If all the information you have entered is correct, press Next
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
101
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
The Installation Progress Bar is briefly displayed, followed by the Setup is Complete screen.
Select: Finish to complete the Setup.
Note:
102
Occasionally the installation of MBF-Console will install
newer versions of the .dll files on your PC. If this is the case,
you will be prompted to reboot you computer. Do so prior to
running MBF-Console.
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Appendix D ODBCLink/SE Companion Product
Notes
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
103
Appendix D ODBCLink/SE Companion Product
ODBCLink/SE Reference Manual
Notes
104
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Index
Index
A
ADO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ALLBASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ALLBASE / IMAGE Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ANSI Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Application Development
Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Supported Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
54
66
34
68
33
31
B
BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1, 35
Note on Using . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
C
Calculated Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Changing the display format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Client Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Client Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Configuring Data Sources
ODBC Tranlator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Setup for HP-UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Setup for MPE/iX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Setup Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Setup Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Successful Completion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Windows95, . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
WindowsNT 4.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Connect Using SQL API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Connect Using SQLAPI in VB 4.0-6.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Connecting Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Connecting with ADO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Connecting with DAO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Connecting with RDO’s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Creating a DBEnvironment
HP3000 Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
HP9000 Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Creating Joins Using Calculated Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
D
DAO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Data Source Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Deleting a Translation DLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Downloading ODBCLink/SE to the PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using ARPA Services (FTP) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using NS Services (DSCOPY) on the HP3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Reflection® software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Dr. DeeBee Spy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
52
20
58
11
12
12
11
63
105
Index
ODBCLink/SE Reference Manual
E
EURO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25, 69
European Currency Symbol . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25, 69
Extracting the ODBCLink/SE file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
G
Gather Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
GlancePlus/UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
GlancePlus/XL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
H
Host Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
I
Implementation Notes
ALLBASE/SQL and IMAGE/SQL Restrictions on the ODBC Grammar . . . . . . . . . . . . . . . . . . . . 68
ALLBASE/SQL and IMAGE/SQL Statements and Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . 70
ALLBASE/SQL and IMAGE/SQL Statment Functions on Client . . . . . . . . . . . . . . . . . . . . . . . . . 70
ALLBASE/SQL LongVarBinary items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Maximum number of statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Primary key name returned by SQLStatistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Unsupported ALLBASE/SQL and IMAGESQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Using the ANSI Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
ARPA Services (FTP) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Extracting the ODBCLink/SE file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Gather Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
NS Services (DSCOPY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Set the Connection Assurance Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Starting and Stopping the Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Installed Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
For Windows 95 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
For Windows NT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
HP-UX 10.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
HP-UX 9.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
MPE/iX Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
J
Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Joins - Creating . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
L
List of Installed Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
32 Bit Driver Client - Windows NT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
32 Bit Driver Client - Windows95 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
HP-UX 10.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
HP-UX 9.x Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
MPE/iX Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78, 79
Listener - Starting and Stopping
Stopping the listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Listener Log File - HP3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
106
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Index
Listener Log File - HP9000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
M
MBF-Console . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
MBF-Console - Running . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
MBF-Console Installing for MBF-UDALink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
MBF-Console Installing for ODBCLink/SE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Migrating Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PCAPI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
81
81
89
97
26
26
N
Notes for Users of MS-Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
O
ODBC Compliancy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
ODBC Conformance level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
ODBC Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
ODBCAD32.EXE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
ODBCADM.EXE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
ODBCLinkSE
Client Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Compliancy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Field types supported . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Operating Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Software provided . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Specifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
SQL Commands supported . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
ODBCLinkSE Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
ODBCLinkSE Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
ODBCLS32.DLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
ODBCUTIL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
P
PC Client Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
PCAPI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Migrating data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Performance Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Preparing the Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Process Status (ps) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
R
RDO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Running the SETUP program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
S
Set the Connection Assurance Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Setup
HP-UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
MPE/iX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
ODBC Tranlator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
107
Index
ODBCLink/SE Reference Manual
Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Special Notes for Users of Impromptu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
SQLColAttributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
SQLExtendedFetch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
SQLGetFunctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
SQLGetInfo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
SQLMON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
SQLSetConnectOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
SQLSetStmtOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Starting and Stopping the Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Start the listener - HP3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Start the listener HP9000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Stopping the listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Stored Procedures - Using . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Supported Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
T
Terminating an ODBC Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Tools on Database Server
Checking Listener Log FileHP3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Checking Listener Log File - HP9000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using GlancePlus/UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using GlancePlus/XL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using Process Status (ps) Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using SQLMON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Tools on the PC Client
Client logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
ODBC Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Translation DLL
Deleting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Translator - ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Translator - Roman8 to PC-ANSI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Translator - Roman9 to PC-ANSI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25, 69
Troubleshooting
Checking Listener Log File
HP3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
HP9000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using GlancePlus/UX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using GlancePlus/XL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using Microsoft ODBC Test (32-bit) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Using Process Status (ps) Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using SQLMON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using the Host Testing Utility ODBCUTSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
U
Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Cognos Impromptu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using MS-Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using ODBCLink/SE With ODBC Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Visual Basic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
108
37
38
43
46
37
48
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
ODBCLink/SE Reference Manual
Index
V
Verify Software Version Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7, 57
Verifying the client-side components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Viewing the SQL generated by Impromptu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
W
Winsock
WINSOCK.DLL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
109
Index
ODBCLink/SE Reference Manual
Notes
110
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.3 Linearized : Yes Producer : Acrobat Distiller 4.05 for Windows Title : Title Page.book Modify Date : 2000:12:11 14:23:13-05:00 Create Date : 2000:12:11 14:19:44 Creator : FrameMaker 6.0 Page Count : 120 Page Mode : UseOutlinesEXIF Metadata provided by EXIF.tools