Home

Reference Manual

1. 27 ODBCLink SE Setup 23 Setup for HP UX 25 Setup for 24 Setup 26 Setup Translator oye sas berba 27 Successful Completion 27 Windows 3 11 21 Windows NT V3 51 21 Windows95 21 WindowsNT 4 0 21 Connecting Link 2 Creating a DBEnvironment HP3000 Database Server 79 HP9000 Database Server 8l Dat SOUFICe e SE EX RON aU Aq te 87 Deleting a Translation DLL 63 Deleting drivers 62 Downloading ODBCLink SE to the PC 13 Using ARPA Services FTP 14 Using NS Services DSCOPY on the HP3000 15 Using Reflection software 13 Drivers deleting 62 fit uy dust es ere 87 Extracting the ODBCLink SE 15 ODBCLink SE M B Foster Software Labs Inc 1996 1998 INDEX Gather Information 10 GlancePlus UX 64 Cane PUS XL vos ose Fir Es 64 Implementation Notes 73 ALLBASE SQL and IMAGE SQL Restrictions on the ODBC Grammar 74 ALLBASE SQL and IMAGE SQL Statements and Embedded SQL 76 T1 ALLBASE SQL and IMAGE SQL Statme
2. 1 ODBCLink SE QVerVIBW 1 ODBC Compliancy Level 2 Client Environment 2 Server Environment 2 The Connecting GNK ursus eae do ea ma le Bg sd ag 2 Microsoft ODBC Overview 3 ODBCLink SE System Requirements 4 Section 2 Preparing the Database Server 7 Verify Software Version Numbers 7 Verify the Connection Assurance Parameters 7 Gather Information s 2 ten ee ee eee pst 10 Create the DBEnvironment 11 Section 3 Installing ODBCLink SE on the 13 Downloading ODBCLink SE to the 13 Extracting the ODBCLink SE file 15 Running the SETUP program 15 Notes ori 16 OF 32 bit ACCESS wan dew wer sass 19 Section 4 Config ring Data SOUICOS cnr enr 2884 riit 888 eee SUCRE ee 21 Data Source Configuration 22 Choosing ODBC 22 ODBGLDIB SE aea a a RR RERO 23 Setup for 24 Set p for HP UX
3. Figure 4 The driver is now installed If this is the first driver you have installed the Data Source name list will be empty You may now click on Close to end the installation Add to add a new Data Source See Section 4 Configuring Data Sources ODBCLink SE Page 17 M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC Setup to modify existing Data Sources See Section 4 Configuring Data Sources Delete to delete the highlighted data source 16 bit only Drivers to do driver maintenance 16 bit only Options to establish ODBC tracing rules See Section 7 Troubleshooting ODBCLink Driver Setup Program gp Setup Succeeded The ODBCLink driver has been successfully installed Figure 5 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 The sub directories ODBCSE16 ODBCSE32 are no longer required and may be deleted if you wish If you need to re install a driver you can always download the files from the host Page 18 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC Notes on 16 or 32 bit Access Notes on 16 or 32 bit Access On a 32 bit operating system Win95 or WinNT you can run both 16 and 32 bit versions of the ODBCLink SE driver The server software runs the same for both You s
4. ges eee 25 Options s is oh is at inte alga Shs erige iei ee 26 Select Translator ost panda utere ad acs 27 ODBC ITAnSIBION o ie eee pie ue VE a eee eae wands 27 Successful Completion 27 Migrating HP PCAPI Data Sources 28 Section 5 Application Development 29 Using Stored Procedures 30 Performance Considerations 31 Turning AutoCommit 32 Supported Functions isses eve RR ee eee eae eee eee eS 33 Isolation Levels x Teori ace Rotor Me One eee Bee aeo d Ree Reels 35 Supported Data oe eas ews ed as wee eee oe 36 Note om Using BLOBS 37 ODBCLink SE Page i M B Foster Software Labs Inc 1996 Section 6 Section 7 Using ODBCLink SE from ODBC Applications 39 D S RES PA TE UN uto Stans oases ver ea ood odas sg ier 40 Importing Database Structure Information into Axiant via ODBCEiINK SE s OAR 40 Creating the screen application 40 Using Cognos Impromptu 41 Special Notes for Users of Impromptu 44 Using Jetform sco eee a
5. Data Base Environment Server Figure 2 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 Windows 3 11 Microsoft Windows95 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 Page 2 Client Environment ODBCLink SE and application software reside on the PC client With ODBCLink SE and applications software you can develop SQL applications generate reports and query ALLBASE SQL or IMAGE SQL database server The tested client applications include Cognos Axiant Cognos Impromptu Jetform Lotus 123 MS Access MS Query Paradox PowerBuilder Crystal Reports Visual Basic Visual C Visual FoxPro and many more Server Environment The networked HP 3000 Series 900 with ALLBASE SQL and or IMAGE SQL or the HP9000 Series 7 8 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 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 1X on the HP3000 or ARPA Services on the HP9000 ODBCL
6. 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 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 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 Page 24 ODBCLink SE Foster Software Labs Inc 1996 1998 Configuring Data Sources Setup for HP UX ODBCLink SE Setup HP UX Login mandatory ET Password optional Will Prompt Figure 10 Data
7. 79 On the HP9000 Database Server 81 Appendix C List ordnstalled IBgS toons uod tpe at dte dob e odd ould 83 16 Bit Driver Client For Windows 3 1 or Windows 3 11 83 32 Bit Driver Client For Windows 95 84 32 Bit Driver Client For Windows NT 85 MPE DCHOSU dere ere a Re CR C rade 86 HP UX OX POSE 4208 owed Dae ed ede ded Beate AA 86 HP UX 10 x Host 86 GLOSSARY uuu ren tae AA ara AA A AAE AM NC AAA AAA AE d 87 INDEX morn ere Re 89 ODBCLink SE Page iii M B Foster Software Labs Inc 1996 THIS PAGE INTENTIONALLY LEFT BLANK Page iv ODBCLink SE M B Foster Software Labs Inc 1996 Overview and Specifications Section 1 Overview and Specifications ODBCLink SE Overview ODBCLink SE is an implementation of Microsofts 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 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
8. Data Source Configuration Configuring Data Sources Setup Options ODBCLink SE 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 X Autocommit transactions Enabled by default Trace ODBC calls on client to fle ODBCLINK LOG Trace SQL calls on the server 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 rServer s Isolation Level Repeatable Read Cursor Stability Read Committed Read Uncommitted Default 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 PC See Section 7 Trouble Shooting Figure 11 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 See Section 7 Trouble Shooting 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 See the ALLBAS
9. Once you have done this you may access ODBC data sources 1 Select Tools Database New Query External ODBC Connect to External iol Select a driver dBASE IV Lotus Notes ODBC Figure 20 2 Select a data source from the list box 3 Select a table to query 4 Youcan then choose fields to include in the query set criteria enter joins etc ODBCLink SE Page 47 M B Foster Software Labs Inc 1996 1998 Using Microsoft Access 2 0 Using ODBCLink SE from ODBC Applications Using Microsoft Access 2 0 To access data through Microsoft Access 2 0 open a new data base and select File Attach External SQL database Data Source Microsoft Access Paradox 3 Paradox 4 X FoxPro 2 5 FoxPro 2 6 FoxPro 2 0 dBASE III dBASE IV Figure 22 Then select the desired data source from the data sources dialog box SQL Data Sources Select Data Source HP 3000 membe HP 3000 Gerry HP 3000 1B HP 3000 member HP 3000 modem HP 3000 multiview HP 3000 parts HP 3000 Phil Figure 23 Page 48 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Attach Tables Tables in SQL Database MANUFDB SUPPLYBATCHES MANUFDB TESTDATA PURCHDB INVENTORY PURCHDB ORDERITEMS PURCHDB ORDERS PURCHDB PARTINFO PURCHDB PARTS PURCHDB REPORTS PURCHDB SUPPLYPRICE PURCHDB VENDORS Save login ID and password locally Figure 23 You will get a li
10. Reference Manual Version E 56 M B Foster Software Labs Inc Head Office 82 Main Street South Chesterville Ontario KOC 1HO Canada Telephone 613 448 2333 Fax 613 448 2588 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 Software Labs Inc 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 Software Labs Inc 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 are trademarks of the Hewlett Packard Company Windows Windows for Workgroups Microsoft Access Visual Basic Visual C Visual
11. L256 Server manager sys odbcse odbcclnn odbcclnn exe Extracting the ODBCLink SE file From your PC type the following to extract the ODBCLink SE files from the self extracting file replace nn with 16 or 32 depending on which driver is being installed C CD ODBCSEnn C ODBCSEnn gt ODBCCLnn EXE NOTE If you are installing ODBCLink SE on a number of 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 Where nn is 16 for the 16 bit driver and 32 for the 32 bit driver 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 To allow the appropriate file to be installed do the following from the setup directory eg ODBCSEnn It is preferable that this procedure be performed at the DOS prompt so that the window will not disappear before you have a chance to read it The process can however be performed from the Program Manager On a WindowsNT system run infsetup wnt On Windows95 systems run infsetup w95 ODBCLink SE Page 15 M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC The batch file will read the parameter entered and cop
12. Versions 9 x and 10 x on the HP9000 Series 7xx and 8xx servers ODBCLink SE runs under Microsoft Windows 3 1 and 3 11 Windows95 or WindowsNT on the client Connection via Winsock is available in 16 and 32 bit versions 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 Image SQL ALLBASE SQL Figure 1 ODBCLink SE Page 1 M B Foster Software Labs Inc 1996 1998 ODBCLink SE Specifications ODBC Compliancy Level Overview and Specification ODBCLink SE is ODBC Level 2 with the following exceptions SQLBrowseConnect SQLExtendedFetch SQLSetPos are not supported Scalar Functions are not support For further details on Supported Functions see Page 33 SQL_BIT SQL_TINYINT and SQL_BIGINT data types are not supported For further details on Supported Data Types see Page 36 Client Application Driver Manager Data Data Source Source LAN Winsock Listener Process
13. 3 Start Reflection and copy the appropriate ODBCLink SE self extracting file to your PC For the HP3000 server press Alt Y to open the Command Window and enter receive odbcsenn odbccinn exe from odbcclnn odbcse sys binary ODBCLink SE Page 13 M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC For the HP9000 server use any login as the Username For HP UX Version 9 x receive odbcsenn odbccinn exe from usr bin odbcse odbcclnn binary For HP UX Version 10 x receive odbcsenn odbcclnn exe from opt allbase bin odbcse odbcclnn binary Using ARPA Services FTP Page 14 From your PC 1 Create a directory MD ODBCSEnn Move to the new directory CD ODBCSEnn Copy the appropriate ODBCLink SE self extracting file to your PC For the HP3000 server use MANAGER SYS ODBCSE as the Username C ODBCSEnn gt ftp Server binary get odbcclnn odbcclnn exe quit For the HP9000 server use any login as the Username C ODBCSEnn gt ftp Server binary get usr bin odbcse odbcclnn odbcclnn exe for HP UX V9 x or get opt allbase bin odbcse odbccInn odbcclnn exe for HP UX V10 x ODBCLink SE M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC Using NS Services DSCOPY on the HP3000 From your PC 1 Create a directory MD ODBCSEnn 2 Move to the new directory CD ODBCSEnn 3 Copy the appropriate ODBCLink SE self extracting file to your PC dscopy r F B
14. 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 2 compliance except SQLBrowseConnect SQLExtendedFetch and SOLSetPos Access to an IMAGE SQL database environment DBE Access to an ALLBASE SQL DBE Access to a single DBE Support for 16 and 32 bit desktop clients The additional features in the DataExpress ODBClink product are Native read write access to TurboIMAGE databases Why add the overhead of IMAGE SQL when you can go direct Support for Serial and or Modem connections for traveling or home office employees Read Write access to KSAM files read and write KSAM files from your desktop application Read Write access to MPE files read and write MPE files from your desktop application Support for PowerHouse PDL dictionaries and subfiles helping you to protect your investment in Cognos products Support for TPI keyword indexes used by Omnidex and Superdex helping you to protect your investment in these products Read Write access to Suprtool files Access to Oracle databases on HP3000 and HP9000 platforms Access to Multiple DBE s multiple listeners not required but they are supported Access to HP intrinsics and procedures via a remote procedure call mechanism Enhanced security via field level access controls making true the statement open but secure Requir
15. 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 Documents directory Here is the format of the ODBCLINK LOG file SQLDriverConnect 0 hstmt 0 Ver 5 52 0000 szConnStrin lt DSN DBA MEMBER UID Admin PWD gt Completed_string lt DSN DBA MEMBER UID mpeix 192 9 3 10 MembrDBE SQL mbftest an DBA dba MBFTEST password work Login lt gt SQLError 100 hstmt 0 pfNativeE 0 52 rrorMsg SQLGetinfo 0 hstmt 0 flnfoT ype 23 rgbInfoValue 1 0 0 0 SQLS etConnectO ption 0 hstmt 0 fO ption 101 vP aram 1 SQLAllocS tmt 0 hstmt 0 SQLGetStmtO ption 0 hstmt 0 ption 0 vP aram 0 SQLS etS tmtO ption 1 hstmt 0 fO ption 0 vP aram 60 SQLError 0 hstmt 0 pfNativeE rror 57 5zE rrorMsg 0 DBCLN32 DLL Driver not capable SQLE rror 100 hstmt 0 pfNativeE rror 0 szE rrorMsg SQLExecDirect 0 hstmt 0 SELECT MEMBERDB ACCTHIST NUMBER MEMBERDB ACCTHIST TRANTYPE MEMBERDB ACCTHIST TRANDATE FROM MEMBERDB ACCTHIST SQLFetch 0 hstmt 0 SQLGetData 0 hstm
16. SQL Follow these steps to create the PartsDBE database on the HP3000 1 Logon to the group and account where you want to create the DBEnvironment 2 From the command prompt enter the following command SSQLSETUP SAMPLEDB SYS A menu like the following will appear on your screen Options for Setting Up ALLBASE SQL Sample DBEnvironments Choose one 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 l 2 3 4 5 6 7 0 Enter your choice gt Figure 38 3 Choose option 1 to create PartsDBE This option creates the DBEnvironment defines all of ODBCLink SE Page 79 M B Foster Software Labs Inc 1996 1998 On the HP3000 Database Server Appendix B Creating a DBEnvironment 4 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 1 2 3 4 Plan the security structure of the DBEnvironment Dec
17. 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 or Userl passwd on the HP9000 5 Create the DBEnvironment For details see Appendix B Creating a DBEnvironment Page 79 ODBCLink SE Page 11 M B Foster Software Labs Inc 1996 1998 Preparing the Database Server THIS PAGE INTENTIONALLY LEFT BLANK Page 12 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC Section 3 Installing ODBCLink SE on the PC ODBCLink SE is contained in self extracting archive files ODBCCL 6 for the 16 bit driver and ODBCCL32 for the 32 bit driver Setting up ODBCLink SE involves Downloading the appropriate ODBCLink SE file to temporary sub directory Extracting the files on your PC client 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 PC In the following section replace Server with the name of your HP3000 or HP9000 server and nn with 16 or 32 depending on which driver is being installed For details see Notes on 16 or 32 bit Access Page 19 Using Reflection software From your PC Create a directory MD ODBCSEnn 2 Move to the new directory CD ODBCSEnn
18. Source Configuration 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 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 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 Foster Software Labs Inc 1996 1998 Page 25
19. are set properly Your network administrator should verify that the settings for Connection Assurance Interval and Maximum Connection Assurance Retransmissions parameters are set appropriately ODBCLink SE Page 7 M B Foster Software Labs Inc 1996 1998 Preparing the Database Server 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 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 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 param
20. 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 SQLGetTypelnfo SQLNumResultCols SQLPrepare SQLPrimaryKeys SQLProcedureColumns SQLProcedures SQLRowCount ODBCLink SE M B Foster Software Labs Inc 1996 1998 Page 33 Supported Functions Application Development RI R2 SQLSetConnectOption R2 SOLSetCursorName SOLSetParam SQLSetStmtOption R4 SQLSpecialColumns SQLStatistics SQLTablePrivileges SQLTables SQLTransact The following SQLColAttributes options are implemented the others will receive a return value of Not Implemented SQL_COLUMN_COUNT SQL_COLUMN_NAME SQL_COLUMN_TYPE SQL_COLUMN_LENGTH SQL_COLUMN_PRECISION SQL_COLUMN_SCALE SQL_COLUMN_DISPLAY_SIZE SQL_COLUMN_NULLABLE The following SQLSetConnectOptions are supported All other will return a Driver not Capa
21. created the listener program utility for technical support purposes a self extracting archive of the 16bit client software a self extracting archive of the 32bit client software the listener program utility for technical support purposes a self extracting archive of the 16bit client software a self extracting archive of the 32bit client software log file when created ODBCLink SE M B Foster Software Labs Inc 1996 1998 Glossary BLOB Column Data Source Entry File ISQL Record Row SQL Table Winsock ODBCLink SE GLOSSARY An acronym for Binary Large OBject This is a logical datatype that is usually associated with large uniquely formatted data considered to be binary in nature An example of this might be a bit mapped graphic image In relational database terms this refers to a data item within a table This term is used to describe a unique instance of the configuration of an ODBC driver For example you could configure a data source as HP3000 SERIAL using the ODBCLink driver as well as a data source called HP3000 LAN using the Hewlett Packard supplied ODBCLink SE driver In relational database terms this refers to a collection of related columns A collection of data within the File System of an operating system such as MPE This is an interactive program supplied with ALLBASE SQL that allows the user to construct ad hoc enquiries against the database A collection of related d
22. data source name gt Click OK and you should connect to the database ODBCLink SE Page 55 M B Foster Software Labs Inc 1996 1998 Using Visual Basic Using ODBCLink SE from ODBC Applications Using Visual Basic 2 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 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 If it s a large table it may take a long time to load perhaps hours you may prefer to use direct calls to ODBC DLL like the VBDEMO application There is no way to tell Visual Basic to stop loading a table once it has started other than by closing the form A second option is to use dyn
23. on the PC Client THIS PAGE INTENTIONALLY LEFT BLANK Page 20 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Configuring Data Sources Section 4 Configuring Data Sources Before you access data defined to ALLBASE SQL or IMAGE SQL you must configure one or more data sources for each 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 Windows 3 11 double click on Main Control Panel and ODBC In Windows95 and WindowsNT 4 0 click on Start Settings Control Panel and double click on ODBC In Windows NT V3 51 double click on Main Control Panel and ODBC for 32 bit data sources For 16 bit data sources use the ODBC Administrator in the ODBC Program Group Data sources created in HP ALLBASE SQL PCAPI can be migrated to ODBCLink SE using the Data Set Migration Tool described later in this section ODBCLink SE Page 21 Foster Software Labs Inc 1996 1998 Data Source Configuration Configuring Data Sources Data Source Configuration Data Sources Driver Setup Delete Options Figure 6 You may now click on Close to return to the Control Panel Add to add a new Data Source Setup to modify existing Data Sources Delete to delete the highlighted data source 16 bit only Drivers to do driver maintena
24. the class name or whatever you have called it You should have a list of the controls on the form including IDC_CAR_NUMBER Select it and then select Add Member Variable In the drop down list you will see a list of your column names Select for instance m_pSet gt m_CarNumber You may now compile and run your application This time you should see data on your main form as you scroll through the records ODBCLink SE Page 57 M B Foster Software Labs Inc 1996 1998 Using Visual FoxPro Using ODBCLink SE from ODBC Applications Using Visual FoxPro 1 Select File New Project enter a name and Save 2 Click on Data 3 Select Database New enter a name and Save 57 Microsoft Visual FoxPro File Edit View Tools Program Project Window Help D gei Sl lal Project Manager Documents Classes sy Databases New Free Tables Queries Description Path NUM caps Figure 30 4 Click on ODBC icon in the database desktop 5 Select New Remote View 6 In Select Connection or Data Source screen click New 7 Select a data source and click 8 Select a connection name and click 9 Select a table which will get you into View Designer 10 Select Columns 11 Click on query button Page 58 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Section 7 Troubl
25. 0 dil Microsoft Run Time Library odbc inf not installed to hard drive Installation control file odbc32 dl system odbc32 dil 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 dil 32 bit ODBC Control Panel Installer DLL odbccr32 dl_ system odbccr32 dil 32 bit cursor library odbcinst hl_ system odbcinst hlp Installation help file odbcin32 d1_ system odbcin32 dil 32 bit ODBCLink SE Driver odbcls32 dll system odbcls32 dil 32 bit ODBCLink SE Driver Setup DLL r8ansi32 dl_ system r8ansi32 dil 32 bit character translation DLL for translations between the ANSI and roman 8 character sets setup exe not PEN to hard drive Microsoft ODBC Setup program drive tmdel2exe exe e stem trndel32 exe Translator uninstall utility to de register translator information cca what32 ex_ system what32 exe Utility to determine what versions of drivers required by ODBC application are currently loaded on the Po application are currently loaded on the PC Page 84 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Appendix C List of Installed Files 32 Bit Driver Client For Windows NT NOTE is a short hand that represents the location
26. 2192 9 3 102 DBCLNSE E 52 00 started from sockets base Prepare 49 SELECT NAME OWNER TYPE NPAGES NROWS FROM CATALOG TABLE base SetP arams 49 base Fetch 49 Rows 119 MaxR ows 200 base Fetch 49 Rows 0 MaxRowsz200 base Close cursor 49 base Commit base Prepare 49 SELECT NAME OWNER NUMP NUMR MULTIRESULT FROM CATALOG PROCEDURE base SetP arams 49 base Fetch 49 Rows 6 MaxR ows 200 base Fetch 49 Rows 0 MaxR ows 200 base Close cursor 49 base Commit onnected to partsdbe ClientVersion E 52 00 repare OJSELECT TABLES 96 11 20 14 13 35 077 SetP arams 0 nparam 0 96 11 20 14 13 53 077 FreeStmt 0 96 11 20 14 13 54 077 N ormal 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 For a discussion on multiple statements refer to Section 5 Application Development Page 29 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Page 67 Tools on the PC Client Troubleshooting Tools on the PC Client 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
27. BC humeurs X MER 69 Translation DLL Deleting eso sus ire tedinsezs 63 Troubleshooting Checking Listener Log File HP3000 64 Checking Listener Log File HP9000 64 Using GlancePlus UX 64 Using GlancePlus XL 64 Using Microsoft ODBC Test 32 bit 60 Using Process Status ps Command 64 Using SQLMON 64 Using the Host Testing Utility ODBCUTSE dde d re Medus RR 64 Verify Software Version Numbers 7 61 ODBCLink SE Foster Software Labs Inc 1996 1998 Index Verifying the client side components 61 Visal Basi dero eda gu PRIN ess da 56 call ODBC functions directly 56 dynaset variables 56 VB database controls 56 VISUAL POXPIO zuo Dad ee od 58 Winsock case eR X RUD eed 87 Winsock WINSOCK DLL 71 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Page 91
28. BC driver setup program system dsnmig exe Migrates 16bit PCAPI clients to ODBCLink SE client _ system drdbsp16 exe 16 bit Dr DeeBee spy used for debugging drdbsp drdbspl amp d dl _ system drdbsp16 dll_ _ 16 bit Dr DeeBee spy used for debugging stem drdbsp16 dll 16 bit Dr DeeBee spy used for debugging as dl Mone EI 16 bit driver manager Microsoft Lodbcinf inf not notinstalled to hard drive to hard drive Setup information file odbc 1 6ut dl_ win system odbc 1 6ut dll 16 bit ODBC Universal Thunking DLL Used by 32 bit applications with 16 bit driver manager odbc32 dl system odbc32 dil 32 bit Thunking Driver manager odbcadm ex_ system odbcadm exe ODBC Administrator application odbccp32 dl system odbcecp32 dll Windows control Panel ODBC Installer Tool odbccurs dl_ system odbccurs dil Microsoft cursor library DLL Can be used by client to implement scrollable cursors odbcinst dl_ system odbcinst dll ODBC administrator DLL Called by ODBC Control Panel Application odbcinst hl_ system odbcinst hlp Help file for odbcinst dll odbcIn16 dl system odbcin16 dil 16 bit version of the ODBCLink SE driver odbcls16 d1_ system odbcls 16 41 16 bit version of ODBCLink SE setup dl system r8ansi16 dil 16 bit character translation DLL for translation between the ANSI and roman 8 character set exe not installed to hard drive Microsoft ODB
29. C Setup program _setup lst not installed to hard drive trndel16 exe system trndel16 exe Used to delete uninstall the 16 bit translation DLL what16 ex_ system what16 exe Utility to determine what versions of drivers required by ODBC application are currently loaded on the PC 32 Bit Driver Client For Windows 95 ODBCLink SE Page 83 M B Foster Software Labs Inc 1996 1998 Appendix C List of Installed Files 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 ctl3d95 dl_ system ctl3d95 dil DLL for 3D controls in Windows95 b5ccdc32 dl_ system b5ccdc32 dll Used for character translation for Chinese characters _ system drdbsp32 exe 32 bit Dr DeeBee spy used for debugging drdbsp32 d__ 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 ds16gt dl_ system ds 1 6gt dll 16 bit Driver Setup Generic Thunking DLL 16 bit applications use this in conjunctions with the 32 bit Installer DLL ds32gt dl_ system ds32gt dil 32 bit Driver Setup Generic Thunking DLL mfc30 dl_ system mfc30 dil Microsoft Foundation Class Library msvcrt20 dl system msvert2
30. DBCLink SE Driver odbcl1s32 dll system32 odbcls32 dil 32 bit ODBCLink SE Driver Setup DLL r8ansi32 dl system32 r8ansi32 dil 32 bit character translation DLL for translation between the ANSI and roman 8 character set setup exe not to hard drive Microsoft ODBC Setup program drive tndel32 exe stem32 trndel32 exe Translator uninstall utility to de register translator information e what32 ex_ system32 what32 exe Utility to determine what versions of drivers required by ODBC application are currently loaded on the Po application are currently loaded on the PC ODBCLink SE Page 85 Foster Software Labs Inc 1996 1998 MPE iX Host ODBCJOB ODBCSE SYS ODBCLNSE ODBCSE SYS ODBCXLSE ODBCSE SYS ODBCUTSE ODBCSE SYS ODBCCLI6 ODBCSE S YS ODBCCL32 ODBCSE S YS ODBCLOG ODBCSE SYS HP UX 9 x Host usr bin odbcse odbcInse usr bin odbcse odbcutse usr bin odbcse odbccl16 lusr bin odbcse odbcc132 HP UX 10 x Host opt allbase bin odbcse odbclnse opt allbase bin odbcse odbcutse opt allbase bin odbcse odbccl 16 lopt allbase bin odbcse odbccl32 tmp odbclog Page 86 Appendix C List of Installed Files 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 16bit client software a self extracting archive for the 32bit client software log file when
31. Driver Client Windows95 84 HP UX 10 Host 86 HP UX 9 8 Coa ihe 86 MPHAX 85 Listener Starting and Stopping Stopping the listener 8 Listener Log File HP3000 64 Listener Log File HP9000 64 n Ap 47 Microsoft Access 41 48 N S 2 coe UL cea aU t Le 48 Version 7 0 1 di sull ERU dps 50 Migrating Data Sources 28 PCAPL Rex Ru 28 MS QUELy za Pa eed d M se s 33 ODBC Compliancy 2 ODBC Conformance level 2 ODBC Data 36 ODBCAD32 EXE 3 3 ODBCLinkSE Field types supported 5 SQL Commands supported 5 Client Requirements 4 Compliancy 2 Field types supported 5 Operating Systems 4 Overview ke ex epa tiM gu xe 1 Software provided 4 Specifications oet e wee se Os aw ae at EA 4 SQL Commands supported 5 ODBCLinkSE Host 71 ODBCLinkSE DLL 71 ODBCLSIODLE 3 ODBCES32 DEE 9 016 aes ad 3 ODBCUTIL Rep X ny YR 64 Paradox i obvio vere Sm cede 54
32. E SQL Reference Manual for details The recommended isolation level is RU for Read Uncommitted This minimizes the number of locks that are held on your database Page 26 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Configuring Data Sources Data Source Configuration Select Translator Select Translator Select which ODBC translator you want to use from the list then choose OK Installed Translators FRERES MS Code Page Translator Romans to PC amp NSI Translator Figure 12 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 PC The translation goes both ways For example extended characters are entered on the PC and sent to the host in an SQL update or Insert operation will be translated Successful Completion Data Sources Data Sources Driver PartsDBE 32 bit ODBCLink SE 32 Driver 32 bit Delete Figure 13 The new data source is now ready for use in your client application It may appear that the 32 bit included in the data source description is redundant as it is also included in the driver name However in a mixed environment some clients show only the data source name Adding 32bit make it easier to distinguish between 32 bit and 16 bit drivers Some applications may allow you to go directly to the Data Sources menu without going to Co
33. EC 10S LP THU 12 01P ODBCLNSE MANAGER SYS On the HP9000 database server enter ps ef grep odbclnse The resulting display will be something like this root 7223 10 15 37 15 0 00 usr bin odbcse server paw 7226 72230 15 40 56 0 00 usr bin odbcse server root 7238 72301 15 41 29 ttys2 0 00 grep odbcse The first line shows the server running The second line shows a user with a login of paw is using the server and the last line shows the command you just entered ODBCLink SE Page 9 M B Foster Software Labs Inc 1996 1998 Preparing the Database Server To stop the listener On the HP3000 database server enter ODBCLNSE ODBCSE SYS STOP 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 by using abort parameters in place of top or alternatively you can use the ABORTJOB command On the HP9000 database server enter usr bin odbcse odbclnse stop or abort On HP UX 9 0 opt allbase bin odbcse odbclnse stop or abort On HP UX 10 0 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 c
34. EN PREPARE REFETCH SQLEXPLAIN WHENEVER Page 76 ODBCLink SE Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes 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 If you attempt to use any of these unsupported statements you will receive an error message ODBCLink SE Page 77 Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes THIS PAGE INTENTIONALLY LEFT BLANK Page 78 ODBCLink SE M B Foster Software Labs Inc 1996 1998 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
35. M t MEMBER TYPE M Data Model File Type Drive or Alias lt User Tables ODBCLINK Figure 28 Then select any of columns you want in your report When done click the Run Query button on the top of the screen Page 54 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using PowerBuilder Using PowerBuilder 3 0 To access HP3000 data from PowerBuilder you first define a database and a database profile If you are working with several ODBC data sources you may find it convenient to add the DB Profiles icon to the PowerBar With the mouse over the PowerBar click the right mouse button and select Customize Then drag the DB Profiles icon from the Selected palette box into Current Toolbar Now click on DB Profiles and create a new database profile Profile Name 3000 1 DBMS onec _Cancet_ User ID DEFAULT Password Database Name Prompt for Database information during Connect CONNECT OPTIONS Server Name op8c3000 Login ID Login Password O DBPARM ConnectSting DSN HP 4 Figure 29 You must enter the following data Profile Name The name of your PowerBuilder database you may have several databases with different User ID s sharing the same data source name DBMS Enter ODBC DBParm This is your ODBC connect string You must enter your data source name DSN lt
36. QL 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 Refer to the PREPARE section of the SQL Statements chapter in the ALLBASE SQL Reference Manual for a list of statements that cannot be prepared 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 OP
37. QLMoreResults 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 Page 30 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Application Development Performance Considerations Performance Considerations Most people 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 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 overhe
38. SQL Tables then click OK and then Results Get Data All 3 Youcan 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 File Edit Connect Statement Results Catalog Misc Tools Window Help TL 1 HP 3000 MEMBER 1 Ox5F1F 0x0000 Select from membership 11445 Steve Lorsby Null Box 15563 Clearwater FL 342794854 9 n 11452 Lewis Ogrady Null 1690 Callens Road Ventura CA 33003 901C 11460 Heathcliff Jones Null 3156 West Twelve Mile Road Berkly MI 4t 11478 Bob Zidek Null Box 1465 Sandy Springs MD 20860 90121 11486 Charlie Moore Null 10520 Ridgeview Court Cupertino CA 35014 11484 Irwin Malcolm Irwin Enterprises 2837 Eastlake Avenue E Seattle V 150 rows fetched from 15 columns Figure 31 Page 60 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Verifying Software Version Numbers Verifying Software Version Numbers Verifying the Client Side Components ODBCLink SE Version Check To determine which version of 24 3 ODBCLink SE is ODBCLink SE Version Check displays ProduciVersion and CompanyName installed run either resources for it s own components and those of the ODBC SDK WHAT 1 6 EXE or This program fir
39. ad 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 Refer to the ALLBASE SQL manual for details 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 Page 31 M B Foster Software Labs Inc 1996 1998 Turning AutoCommit On Off Application Development Turning AutoCommit On Off AutoCommit 15 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 Page 32 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Application Development Supported Functions 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
40. ah A EN NE Api edu 45 Using Lotus 123 Release 5 0 47 Using Microsoft Access 2 0 48 Using Microsoft Access 7 0 50 Notes for Users of MS Access 51 Using MS GQUEI es Sins ae ey re ed a 53 6 tea FOI 54 Using PowerBuilder 3 0 55 Using Visual Basic 2 0 or higher 56 Using Visual C en nie ut e RN IA EN chere 57 SIME Vislal EOXPIO s sie wr a iu posu esos eg Ses iude e Wie wg qug 58 Trogbleshoolitig z 242828 288888 DU TER N eee eee ee ee BL 59 Terminating an ODBC Connection 59 Using Microsoft ODBC Test 32 bit 60 Verifying Software Version Numbers 61 Verifying the Client Side Components 61 Deleting Unwanted Drivers 62 Deleting a Translation DLL 63 Tools on the Database Server 64 Monitoring Activity with HP GlancePlus XL HP3000 Program 64 Monitoring Activity with HP GlancePlus UX HP9000 Program 64 Displaying Active Processes with HP UX Process Status ps Command s LEUR o duse hee ce aret edi 64 Monitoring ALLBASE SQL Activity with SQLMON 64 Checking
41. aset variables which gives the program more control such as when 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 DS Var 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 1 Declare strings variables as Dim mystr as String 2 Initialize your string variables before use mystr String 255 0 3 Use the ByVal keyword when passing the variable to the ODBC driver Do not use the ByVal keyword in the IpbB
42. ata items In relational database terms this refers to an entry within a table See Entry Record An acronym for Structured Query Language This is an emerging industry standard way of constructing commands to a relational database It is often used synonymously with Relational Database although this is not strictly correct A collection of commonly structured data entries This term is usually used when referring to Relational databases See File Set Winsock is a Microsoft standard based on Berkeley sockets It is a definition of a protocol that can be used to communicate between co operating processes Page 87 M B Foster Software Labs Inc 1996 1998 Glossary THIS PAGE INTENTIONALLY LEFT BLANK Page 88 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Index Access 2 0 Microsoft Access 7 0 Microsoft ALLBASE 71 ALLBASE IMAGE Data Types 36 Application Development Isolation Levels 35 Supported Functions 33 ARIANE pix bo Dep zu de soe ee ute 40 BLOB haben BLEU AES 87 BEDBS cob Ge a ea he aee 1 37 Note on Using 37 Client Configuration 21 Client Configuration ODBCADM 21 Client Environment 2 Colunn perece eo or X DR SEHE S 87 Configuring Data Sources Choosing an ODBC Drive 22 ODBC
43. ax dynamic SQL For details see Supported Functions Page 33 ODBCLink SE Page 5 M B Foster Software Labs Inc 1996 1998 ODBCLink SE System Requirements Overview and Specifications THIS PAGE INTENTIONALLY LEFT BLANK Page 6 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Preparing the Database Server Section 2 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 ode ote 1 Verify Software Version Numbers Tf you are using ALLBASE SQL version A G2 00 or higher is required On the HP3000 enter SQLVER PUB SYS On the HP9000 enter sqlver Tf you are using IMAGE SQL the version number should be B G2 03 or higher Type MAGESQL PUB SYS gt gt exit NOTE _ If both ALLBASE SQL and IMAGE SQL are installed both must be either the 3 respective version state above or newer in order to use ODBCLink SE 2 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
44. ble SQLState S1C00 SQL_AUTOCOMMIT 1 ON default O OFF SQL_OPT_TRACE supported by Driver Manager SQL_OPT_TRACEFILE supported by Driver Manager SQL_TRANSLATE_DLL supported SQL TRANSLATE OPTION supported SQL TXN ISOLATION supported Cursor Stability isolation level CS is not supported by SQLSetConnectOption but you may set it from the ODBC Administrator SQL TXN READ UNCOMMITTED Read Uncommitted RU SQL READ COMMITTED Read Committed RC SQL TXN REPEATABLE READ Repeatable Read RR SOL TXN SERIALIZABLE Note Sets to Repeatable Read RR SOL TXN VERSIONING Not Implemented DO NOT USE For information on isolation levels refer to the Isolation Levels section in this chapter R3 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 01802 Page 34 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Application Development Isolation Levels 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 Cursor Stability CS Repeatable Read RR Read Committed RC Read Uncommitted RU ODBCLink SE uses the isolation level specified
45. control panel An example of this is the MAXSTMT options For details see Appendix A Implementation Notes Page 73 To add or change an option either edit the ODBC INI file for 16 bit data sources or run the registry editor regedt32 exe In the registry editor look under HKEY_CURRENT_USERS Software Microsoft ODBC INI Page 70 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Error Messages Error Messages Errors in ODBCLink SE can come from several sources all messages have a prefix which indicates the source of the error as follows ODBCLN16 Generated by the 16 bit client DLL ODBCLN32 Generated by the 32 bit client DLL WINSOCK Comes from the Winsock driver ALLBASE Generated by the ALLBASE SQL command interpreter The first action you should take in case of a problem is to attempt to execute the command manually e g try to login using a terminal emulator or else try issuing the SQL statement that causes a problem in ODBCUTSE or ISQL If you do not know the command causing the error because you are using Microsoft Access for instance turn on host logging and examine your ODBCLOG file Errors from ALLBASE SQL or IMAGE SQL are followed by DBERR or DBWARN and a number These error messages are documented in the ALLBASE SQL Message Manual or in the IMAGE SQL Administration Guide Error messages are returned by ODBCLink SE to the application Each message is followed by a CAUSE w
46. d table structures EXIT Exit program SELECT SQL Select FETCH SQL Fetch ALLI INSERT SQL Insert UPDATE SQL Update DELETE SQL Delete SQL Execute immediate SQL command COMMIT SQL Commit PARAM Parameter substitution on a prepared statement STMT Change current statement cursor number ROLLBACK SQL Rollback EXECUTE Execute stored procedure QUIT Exit program FOREIGN Display foreign Keys TABPRIV Display table privileges 0 Enter the command you wish to use For example enter SHOW to see a list of available tables gt show Dataset Table Database MANUFDB SUPPLYBATCHES partsdbe sql MANUFDB TESTDATA partsdbe sql PURCHDB PARTS partsdbe sql PURCHDB INVENTORY partsdbe sql PURCHDB SUPPLYPRICE partsdbe sql PURCHDB VENDORS partsdbe sql PURCHDB ORDERS partsdbe sql PURCHDB ORDERITEMS partsdbe sql PURCHDB PARTINFO partsdbe sql PURCHDB VENDORSTATS partsdbe sql RECDB CLUBS partsdbe sql RECDB MEMBERS partsdbe sql RECDB EVENTS partsdbe sql ODBCLink SE M B Foster Software Labs Inc 1996 1998 mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest mbftest Type ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE ALLBASE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE VIEW VIEW TABLE TABLE TABLE Page 65 Tools on the Database Server Page 66 Troubleshooting To see the list of col
47. e access the legacy data structures Upgrade to DataExpress ODBCLink before December 31 1998 and receive 35 off the full retail price Please contact M B Foster Associates at 613 448 2333 or 1 800 ANSWERS 800 267 9377 or use the enclosed faxback sheet to order a fully functional evaluation copy of ODBCLink ODBCLink SE 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 KSAM file access access Suprtool access interface support Enhanced security field level Serial Modem connection MPE file access PowerHouse PDL support Oracle access Multiple DBE access Remote Procedure Call Support DLDODOCODLU am using the following desktop product s Q MS Access Q Visual Basic Q Fox Pro Q MS Query Q Paradox Q Jetform Impromptu Q Axiant Q PowerBuilder Q Lotus Q Excel Q Crystal Reports Q Other Q 16 bit 32 bit Q lam interested in an upgrade please send a demo immediately HPCPUNAME HPSUSAN NUMBER Media Required Q DAT Q 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 Table of Contents Section 1 Overview and 5 5
48. e held on the database Optimistic ODBCLink SE Page 51 M B Foster Software Labs Inc 1996 1998 Using Microsoft Access 7 0 Using ODBCLink SE from ODBC Applications Page 52 concurrency control works by Access generating and Update statement of the form Update Table Set column1 column2 WHERE columnl AND column2 AND column3 And This ensures that the update will fair 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 Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using MS Query Using MS Query Run MS QUERY Select File New Query and select a data source y
49. e 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 key 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 select 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 table 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 wills till 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 For details see Section 7 Troubleshooting Page 59 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 Optimistic 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 ar
50. em registry ODBCLink SE Page 3 M B Foster Software Labs Inc 1996 1998 ODBCLink SE System Requirements Overview and Specifications ODBCLink SE System Requirements HP3000 Server Requirements Operating System Requirements Version 5 0 or Version 5 5 Database Requirements IMAGE SQL Version B G2 03 or later ALLBASE SQL Version A G2 00 or later Network Requirements ThinLANLink XL must be configured and turned on HP9000 Server Requirements Operating Systems Requirements HP UX Version 9 0 and Version 10 0 Database Requirements ALLBASE SQL Version A G2 01 or later 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 3 1 Windows 3 11 Windows 95 Windows NT Version 3 51 Windows NT Version 4 0 Network Requirements Winsock or other TCP IP software Software provided Server program for the HP3000 or HP9000 as appropriate Client programs both 16 amp 32 bit platforms Page 4 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Overview and Specifications ODBCLink SE System Requirements Field types supported All data types supported by Allbase and Image SQL For details see Supported Data Types Page 36 SQL Commands supported ODBCLink SE supports the complete ALLBASE SQL and IMAGE SQL synt
51. eshooting 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 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 Q account When an ODBC client terminates abnormally the server process that handles the connection will normally go down after the timeout interval has expired For details see Connection Assurance Parameters Page 7 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 Page 59 M B Foster Software Labs Inc 1996 1998 Using Microsoft ODBC Test 32 bit Troubleshooting 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 1 Select Connect Full Connect You must enter a data source name User ID and a password and click You should get a message Successfully connected to databasename 2 You can get a list of tables by selecting Catalog
52. eter 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 3 Starting and Stopping the Listener To start the listener on the HP3000 data base server enter STREAM ODBCJOB ODBCSE SYS Page 8 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Preparing the Database Server NOTE 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 On a 9 x server opt allbase bin odbcse odbclnse server On a 10 x server NOTE You should start the listener as root user id preferably in your system startup NOTE When the server is started it makes a call to sqlver and puts the output into a file called odbcver for HP UX and verstr for MPE in the same directory that the server is running from For this reason upon startup it may take a few seconds for the program to return The file is used to determine the DBMS version for a SQLGetInfo call If the file is deleted the SQLGetInfo call for DBMS version will return UNKNOWN To determine whether or not the listener job is running On the HP3000 database server enter SHOWJOB JOB J The resulting display will be something like this JOBNUM STATE IPRI JIN JLIST INTRODUCED JOB NAME Jnnn EX
53. g Cognos Impromptu Using ODBCLink SE from ODBC Applications Special Notes for Users of Impromptu Viewing the SQL generated by Impromptu 3 0 Enter a new report and select Report Query Profile View SQL You can also write your own SQL and override the one generated by Impromptu Search for help on topic Writing your own SQL 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 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 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 Creating Joins Using Calculated Columns It is possible in I
54. 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 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 Refer to ODBC SDK for details If you wish to use multiple statements but are not writing your own SQL i e you are using a AGL 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 For details see Modifying the System Registry and ODBC INI Files Page 70 and add a new parameter MAXSTMT 50 ALLBASE SQL and IMAGE SQL Restrictio
55. he 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 ODBCLink SE Page 23 M B Foster Software Labs Inc 1996 1998 Data Source Configuration Configuring Data Sources The Server Type radio button indicates the type of computer on which the DBE is located Click on if it is an HP3000 HP UX if it is an HP9000 Setup for MPE iX ODBCLink SE Setup MPEiX rLogon Information Session ID User Name Acct Name Group Name optional mandatory mandatory optional HELLO Jiohnm Jodbclink User Password Acct Password Group Password will prompt will prompt will prompt Figure 9 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
56. hich gives information as to why the warning or error occurred This is followed by an ACTION which gives information on how to resolve the problem For all other errors refer to the appropriate documentation or manuals ODBCLink SE Page 71 M B Foster Software Labs Inc 1996 1998 Error Messages Troubleshooting THIS PAGE INTENTIONALLY LEFT BLANK Page 72 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes Appendix A Implementation Notes Following is a list of features that are unique or specific to ODBCLink SE and some details about them Primary key name returned by SQLStatistics Most ODBC applications including Microsoft Access Visual Basic and PowerBuilder require a unique primary key for a table to be updatable If a unique key has not be defined in ALLBASE SQL the table will not be updatable Reading or writing to ALLBASE SQL LongVarBinary items Any file residing on the HP can be inserted into an ALLBASE SQL LongVarBinary field with the SQL command INSERT INTO Table VALUES BLOB gt Where BLOB is the name of the file on the HP and the gt 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 lt BLOB gt OUTFILE In either case the data can be retrieved in the standard way SQLFetch followed by SQLGetData in chunks You may also create a binar
57. hould always use the 32 bit driver if you are using a 32 bit operating system For details see Performance Considerations Page 31 In some cases however you may not have a choice since a 16 bit application in Win95 or WinNT cannot use a 32 bit driver If you want ODBC access from a 16 bit application on Win95 or WinNT you must install the 16 bit version of ODBCLink SE 16 and 32 bit data sources are maintained separately and can coexist 32 bit data source information is kept in the registry and maintained by the 32 bit ODBC control panel application ODBCAD32 EXE 16 bit data source information is kept in the ODBC INI and ODBCINST INI files in your windows root directory not the windows system directory and is maintained by the 16 bit control panel application ODBCADM EXE The 16 bit ODBC control panel application will show 32 bit data sources but the 32 bit ODBC control panel will not show 16 bit data sources To distinguish between 16 and 32 bit data sources look at the driver name in the data source window in the ODBC control panel You will either see ODBCLink SE 16 or ODBCLink SE 32 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 For details see Modifying the System Registry and ODBC INI Files Page 70 ODBCLink SE Page 19 M B Foster Software Labs Inc 1996 1998 Notes on 16 or 32 bit Access Installing ODBCLink SE
58. ide 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 gt CONNECT TO PartsDBE SomeGrp SomeAcct isql gt SELECT FROM SYSTEM TABLE At this point you should see the system table information Ulp d own l eft r 1ght t op b ottom pr int n or e nd isql gt exit For a complete explanation of the procedures necessary to create the PartsDBE DBEnvironment refer to the appendix Sample DBEnvironment in the ALLBASE SQL Reference Manual Page 80 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Appendix B Creating a DBEnvironment On the HP9000 Database Server 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 1 Login to the directory where you want to create the DBEnvironment 2 From the C shell enter the following command usr lib allba
59. ill see a list of available Drivers blanks indicate that Axiant does not see any drivers Note Axiant 1 2c and earlier is 16 bit and therefore requires a 16 bit driver iv When you select your driver you will get a list of data sources v Select the data source s that you want Select don t prompt so the applications won t prompt you again for all of the data source information vi Click on the Import key All the structure information from the database s belonging to the data source you gave should bei mported If your data source is set up to access more than one database then you will get a database domain for each database you have access to NOTE You will need the full version of M B Foster s ODBCLink to connect to multiple Creat J i iii iv v vi DBEs ing the screen application Select View List Highlight the application icon and then select Objects Create Screen Applications You should be in the New Screen Creation form Select the table that you want to access and then click on Next If you wish to join two tables select the other table and define the relationship e g 1 1 or 1 10 etc Add the screen items that you want from your table s and hit Next Build the Application click on Build Two objects should be created a form and the code for the form After the application is built you need to Execute it This is done by highlighting the form and selecting E
60. in the ODBC setup screen The application can later change this by calling SQLSetConnectOptions with SOL TXN ISOLATION option The recommended isolation level is RU for Read Uncommitted This minimizes the number of locks that are held on your database Refer to the ALLBASE SQL Reference Manual for further information ODBCLink SE Page 35 Foster Software Labs Inc 1996 1998 Supported Data Types Application Development Supported Data Types The following is a table of correspondence between HP data types and ODBC data types ALLBASE SQL or Image or ODBC Data Type Comments IMAGE SQL Data Type CHAR max length lt 255 SQL_CHAR CHAR max length gt 255 SQL_LONGVARCHAR VARCHAR max length lt 255 SQL BINARY lt 256 EX SQL BINARY BINARY length gt 256 SQL_LONGVARBINARY VARBINARY lt 256 SQL_VARBINARY VARBINARY gt 256 SQL LONGVARBINARY LONG BINARY SQL_LONGVARBINARY LONG VARBINARY SQL_LONGVARBINARY INTEGER 32 bit SQL_INTEGER SMALLINT 16 bit SQL_SMALLINT DECIMAL Internal representation is SQL_DECIMAL Character representation with leading sign and packed decimal decimal point Image Zoned 7 SQL_DECIMAL Converted by Image SQL image Packed FLOAT 24 or REAL or Image E2 SQL_REAL FLOAT 53 or DOUBLE SQL_DOUBLE PRECISION or Image E4 Image R2 non IEEE float SQL REAL Converted by Image SQL Image non IEEE floa
61. ink SE M B Foster Software Labs Inc 1996 1998 Overview and Specifications ODBCLink SE System Requirements Microsoft ODBC Overview ODBC applications make functions calls into ODBC DLL the Microsoft 16 bit ODBC driver manager or ODBC32 DLL the 32 bit 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 ODBCLS16 DLL or ODBCLS32 DLL in the case of ODBCLink SE which will update ODBC INI or the syst
62. lias Name ER Database System 5 Access 2 0 Databas DATABASE TABLE BASE ID Figure 19 ODBCLink SE Page 45 Foster Software Labs Inc 1996 1998 Using Jetform Using ODBCLink SE from ODBC Applications Under Alias Name enter a name for your table or use the Table Name pull down menu to select your table name Then click Add and Ok You are now back in the Database Select Rules screen Click on Match and then match all the column names in the Table Column column with those in the Form Field column You should then enter Select Update and Insert rules Please refer to the Jetform documentation for addition information on this function When you are done click on OK Then compile your form Exit Jetform design and run Jetform filler Open your form and initiate a query by selecting File Database Select Transactions This should fill in your form If you have entered an Update or Insert rule you should also be able to change the data and then select File Database Update Page 46 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using Lotus 123 Using Lotus 123 Release 5 0 To enable Lotus 123 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
63. mpromptu 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 View as Expression and then enter an equation such as Table Column3 Table1 columnl 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 Page 44 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Jetform Using Jetform In Jetform Design select Database Select You will be in the Database Select Rules screen Database Select Rules Type of rule Select O Update O Insert Delete IE _Cancet_ Table Alias M BASE ID amp M BAsE 10 Match table columns to form fields BASE ID lt gt BASE ID Match BASE_NAME lt gt BASE_NAME Match SQL r BASE ID BASE NAME from M BASE ID Additional rules peg record generation Rule 1 insert Delete pee Generate multiple records Customize view in Filler Display all columns Figure 18 Click on Define This will put you in the Define table alias screen Define Table Alias 2 A
64. n 1000000 bytes of space being used for each BLOB You would quickly run out of space in your database ODBCLink SE Page 37 M B Foster Software Labs Inc 1996 1998 Supported Data Types Application Development THIS PAGE INTENTIONALLY LEFT BLANK Page 38 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Section 6 Using ODBCLink SE from ODBC Applications After you have installed the drivers and created the data sources you are ready to use ODBCLink SE in your applications Each client application will have a slightly different way of connecting to the data sources you have set up In this section you will find examples of how to access data sources for many of the popular client tools used today ODBCLink SE Page 39 M B Foster Software Labs Inc 1996 1998 Using Axiant Using ODBCLink SE from ODBC Applications Using Axiant Importing Database Structure Information into Axiant via ODBCLink SE 1 Start Axiant and create a new task D Start Axiant ii Select File New ii You will be prompted Connect new task to a repository Click YES and then in Connect repository click NEW iv the new repository v Click on prepopulate NO then click OK Now you should have an empty task to import the data from a database 1 Select Tools Import Data Definition 1 Now Select the import method probably ODBC ODBC with Qualifier iii You w
65. n psa VG tee ha gen pies 54 Undo e d acp Ped CR es 28 Migrating data sources 28 PowerBuilder 55 Profile Nate asus este Gus 55 Process Status ps 64 Page 90 Query Microsoft 53 Record c1 icc tes 87 ROW sci pitas ie t Pe be URGE ES 87 Running the SETUP program 15 Set the Connection Assurance Parameters 7 Setup ccc vae duque E aU eA Ei QU Y 25 MPE IX So pe Matai es 24 ODBC Tranlator 27 ODBCGIHInBKE SE Re 23 OpUONS eMe etes 26 Translator ee nt eee 27 SOL A ua Cea 87 SQLExtendedFetch 2 SOLMON ES ove e eie Auk 64 Starting and Stopping the Listener 8 Start the listener HP9000 9 Start the listener HP3000 8 Stopping the listener 10 Supported Functions 33 E CREE Ae OLR ES 87 The Connecting Link 2 Tools on Database Server Checking Listener Log File HP9000 64 Checking Listener Log File HP3000 64 Using GlancePlus UX 64 Using GlancePlus XL 64 Using Process Status ps Command 64 Using SQLMON 64 Tools on the PC Client Client logging eee aad 68 OD
66. n the list click on New and create a new data source For details see Section 4 Configuring Data Sources You must click on the name of the data source you wish to use even if there is only one Page 50 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using Microsoft Access 7 0 a Tables MEMBERDB ACCTHIST MEMBERDB MEMBERSHIP Cancel Select All Deselect All Figure 26 You may highlight one or more tables and then click OK This will create an attached 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 The Save password check box is not used by ODBCLink SE Notes for Users of MS Access Foratable 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 sam
67. nce 16 bit only Options to establish ODBC tracing rules See Section 7 Troubleshooting Choosing an ODBC Driver You MUST click on a driver name even if only one name appears in the list Click Close Drivers Installed ODBC Drivers Close MB Foster ODBCLink 32 Microsoft Access Driver mdb Microsoft dBase Driver dbf Microsoft FoxPro Driver dbf ODBCLink Driver ODBCLink SE 32 Driver ODBCLink 32 Driver SQL Server Figure 7 Page22 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Configuring Data Sources Data Source Configuration ODBCLink SE Setup 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 ODBCLink SE Setup Data Source Name mandatory PartsDBE 32 Description optional The sample DBE provided by HP DataBase Name mandatory PartsDBE sampledb sys Server Name or IP Address mandatory marvin Server Type C HP UX Figure 8 The maximum length is 30 characters If 16 and 32 bit applications are being used on the same PC it is useful to know which data source is for which driver it may help to include 16 or 32 in the name You may choose your own naming convention The Description is optional and may contain any string of characters you wish It may be longer than the display window T
68. ns 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 ALLBASE SQL does not provide CASCADE or RESTRICT ALLBASE SQL does not provide RESTRICT DROP TABLE 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 Page 74 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes Using the ANSI Character Set The HP3000 and HP9000 both use the default ROMANS 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 ROMANS 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 ROMANS to ANSI and the data returning to the database server must be converted from ANSI to ROMANS To activate the conversion select the Roman8 to PC ANSI translation DLL during the data source setup The conversion takes place
69. nt Functions on Client 77 ALLBASE SQL LongVarBinary items 73 Maximum number of statements 73 Primary key name returned by SQLStatistics 73 Unsupported ALLBASE SQL and IMAGESQL Statements 76 Using the ANSI Character Set 75 41 Calculated Columns 44 Changing the display format 44 Edit Tables e Xi es 43 JOS Eur d adstat 44 Viewing the SQL generated 44 Installation Gather Information 10 Set the Connection Assurance Parameters 7 Starting and Stopping the Listener 8 The Connecting Link 2 Installation ARPA Services FTP 14 Installation Extracting the ODBCLink SE l sales ete Ae eae as 15 Installation NS Services DSCOPY 15 Installed Piles 83 For Windows 3 1 Windows 3 11 83 For Windows 95 84 For Windows NT 85 HP UX 10x OS ia ei deg 86 HP UX 9 x HOS 25 mien 86 HOSE 4e oS pem RR HR 85 BODA ops egi ROI REI DR pA 87 Jetform i22 2 ERR da ou Dehors 45 pulldown menu 46 USE Ai a a e a aces 44 Page 89 List of Installed Files 83 16 Bit Driver Client 83 132 Bit Driver Client Windows NT 85 32 Bit
70. ntrol Panel to click on the ODBC Administrator Check you client application documentation for details ODBCLink SE Page 27 Foster Software Labs Inc 1996 1998 Data Source Configuration Migrating HP PCAPI Data Sources 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 7 ODBCLink SE DSN Migration Tool 0 x ODBCLink SE DSN Migration Tool x ete Highlight the HP ALLBASE SQL DSNs to migrate to ODBCLink SE in the list below Then click OK or Cancel OK Figure 14 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 Page 28 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Application Development Section 5 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 applicati
71. of the windows installation e g c windows Install Disc Windows NT _bootstp exe not installed to hard drive ae _mssetup ex_ not installed to hard drive _ b5ccdc32 d dl_ Re Used for character translation for Chinese characters cti3dnt dl A drdbsp32 ex EE drdbspi2 dl dl system32 drdbsp32 dil 32 bit Dr DeeBee spy used for debugging 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 ds16gt dl_ system32 ds 1 6gt dll 16 bit Driver Setup Generic Thunking DLL 16 bit applications use this in conjunctions with the 32 bit Installer DLL ds32gt dl_ system32 ds32gt dil 32 bit Driver Setup Generic Thunking DLL mfc30 dl_ system32 mfc30 dil Microsoft Foundation Class Library msvcrt20 dl system32 msvert20 dil Microsoft Run Time Library odbc inf not installed to hard drive Installation control file odbc 16gt dl_ system32 odbc 1 6gt dll 16 bit ODBC Generic Thunking DLL odbc32 dl system32 odbc32 dil 32 bit ODBC Driver Manager odbc32gt dl_ system32 odbc32gt dil 32 bit ODBC Generic Thunking DLL odbcad32 ex_ system32 odbcad32 exe ODBC Administrator Application odbcinst hl_ system32 odbcinst hlp Installation help file odbcln32 d1_ system32 odbcin32 dil 32 bit O
72. ommand to stop the listener kill 16 pid where 16 is the siguserl and pid is the process ID number 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 server process that does not belong to you Using kill 16 will prevent this from happening 4 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 e Host or node name of the HP3000 or HP9000 database server or its IP address For example HP3000 or HP9000 or 192 9 2 10 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 Onthe HP9000 check the etc hosts file If the hosts file does not exist check the Page 10 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Preparing the Database Server 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
73. 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 For more information refer to the ALLBASE SQL Reference Manual A few characters cannot be converted to ROMANS and then back to ANSI For example when the copyright sign in ANSI is converted to ROMANS 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 ROMANS in a round trip fashion The hex designation is listed along with the character name broken bar 9 copyright sign e AC not sign AE registered trade mark e B2 superscript 2 e superscript 3 e B8 cedilla e B9 superscript 1 e D7 multiply sign F7 divide sign Similarly the following characters sent from the database server cannot be properly converted from ROMANS to ANSI in a round trip fashion e A9 accent grave AA circumflex accent AC tilde accent e Dutch guilder EB uppercase S caron ODBCLink SE Page 75 M B Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes e EC lowercase 5 caron EE uppercase Y umlaut long dash e FC solid box Unsupported ALLBASE SQL and IMAGE S
74. ons 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 Page 29 M B Foster Software Labs Inc 1996 1998 Using Stored Procedures Application Development Using Stored Procedures ODBCLink SE implements all types of ALLBASE SQL stored procedures including those which return multiple format results sets You can list the stored procedures available to your ODBC session by calling SQLProcedures 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 S
75. ou can delete unwanted 16 bit drivers from the 16 bit ODBC Administrator on the Control Panel Click on Drivers highlight and unwanted driver and click on Delete To delete unwanted 32 bit drivers run the driver delete tool DRVDEL32 EXE from either the RUN window or an MS DOS prompt window ODBCLink SE DSN Migration Tool ODBCLink SE Driver Delete Highlight the drivers to delete in the list below Then click OK or Cancel Microsoft Access Driver mdb Microsoft dBase Driver dbf Microsoft Excel Driver xls Microsoft FoxPro Driver dbf Microsoft Text Driver 5 Pob Cancel Figure 34 Highlight the drivers you wish to delete and click OK The driver name and all related information will be removed from the system registry Page 62 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Deleting a Translation DLL Deleting a Translation DLL Run TRNDEL16 EXE or TRNDEL32 EXE from Program Manager by selecting FILE then RUN and entering the directory path and program name or from File Manager by double clicking on the file name A welcome screen will be shown first Click on OK to pop up the next screen ODBCLink SE Translator Delete 1 Welcome to ODBCLink SE Translator Delete Select the translator you want to uninstall and press OK Choose Continue to delete The translator information translators will then be deleted from the registry If the program is succe
76. ou must have pre configured a data source with ODBC administrator After connecting it should come up with a list of tables on your host File Edit View Format Table Criteria Records Window Help Se e KP E 512 M M BASE ID Figure 27 Select one or more tables and click OK You will get a screen like the one in the Figure above 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 ODBCLink SE Page 53 M B Foster Software Labs Inc 1996 1998 Using Paradox Using ODBCLink SE from ODBC Applications Using Paradox To configure Paradox to use ODBCLink SE run the Local Settings Utility and enter the path and name of your IDAPI Config file Run the IDAPI Configuration Utility click on New Driver enter ODBCLink SE as the Default driver and enter the name of your data source in the Default data source field Then select File Save From Paradox select File New Query and enter the name of your database alias as entered in the IDAPI Config Utility in the drop down list box Drive or Alias Refer to Figure 19 below The left hand column of the screen should fill up with the names of your tables Eile Name Directories MEMBER ACCT HISTORY f ODBCLINK fMEMBER MEMBERSHIP MEMBER REGION
77. 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 HP UX 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 usr bin odbcse odbcutse dbe name for HP UX V9 x systems or opt allbase bin odbcutse dbe name for HP UX V10 x systems Page 64 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Tools on the Database Server You should now be connected to the database To see a menu of available commands enter HELP O gt HELP SHOW Display tables an
78. se 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 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 1 2 3 4 5 6 7 0 Enter your choice gt Figure 39 3 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 ODBCLink SE Page 81 M B Foster Software Labs Inc 1996 1998 On the HP9000 Database Server Appendix B Creating a DBEnvironment Press Return to continue 4 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 1 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 databa
79. ses 2 Create the account where the DBEnvironment is to reside 3 Grant authorizations to users who will be using the test DBEnvironment 4 Create the DBEnvironment including databases and tables After you setup the DBEnvironment ensure that you can connect to it For example isgl isql gt CONNECT TO users hpsql sampledb PartsDBE isql gt SELECT FROM SYSTEM TABLE At this point you should see the system table information U p d own l eft r 1ght t op b ottom pr int n or e nd gt isql gt exit For a complete explanation of the procedures necessary to create the PartsDBE DBEnvironment refer to the appendix Sample DBEnvironment in the ALLBASE SQL Reference Manual Page 82 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Appendix C List of Installed Files Appendix C List of Installed Files 16 Bit Driver Client For Windows 3 1 or Windows 3 11 NOTE is a short hand that represents the location of the windows installation e g c windows Install Disc_ Dic Windows 3 x Description installed to hard drive not neta o nadae to hard drive cpn16ut dl_ system cpn 1 6ut dll 16 bit installer Universal Thunking DLL Used by 32 bit applications with 16 bit installer DLL ctl3dv2 dl_ system ctl3dv2 dil The DLL for 3D controls drvsetup exe not installed to hard drive 16 bit OD
80. ssful it will show a window indicating successful removal Figure 35 ODBCLink SE Translator Delete Osee eee iuda deleted it will no longer be displayed in the translator list Highlight the translators to delete in 5 the list below Then click OK or in the driver setup To add the Cancel translator back in simply rerun the ODBC driver install Roman8 to PC ANSI Translator program Watcom SQL 4 0 Translator 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 Figure 36 ODBCLink SE Translator Delete 7 ODBCLink SE Translator Delete my Cz succeeded The translators you selected were deleted Figure 37 ODBCLink SE Page 63 M B Foster Software Labs Inc 1996 1998 Tools on the Database Server Troubleshooting 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
81. st looks for a file in the Windows System directory and if WHAT32 EXE from the file is not found the directories specified in PATH are searched the RUN window for DBCLNTE DLL 5 83 0200 MB jates Lid BETA VERSION E from MS DOS ODBCLS16 DLL 5 53 0200 MB Foster Associates Ltd BETA VERSION 1 R8ANS116 DLL 5 42 0000 M B Foster Associates prompt window WINSOCK DLL 3 10 Microsoft Corporation ODBC DLL 2 10 2401 Microsoft Corporation ODBCADM EXE 2 10 2309 Microsoft Corporation ODBCINST DLL 2 10 2401 Microsoft Corporation NOTE Press OK to exit before running the other program when checking both 16 and 32 bit versions Figure 32 DBCLink SE Version Check d ODBCLink SE Version Check displays ProduclVersion and CompanyName resources for it s own components and those of the ODBC SDK This program first looks for a file in the Windows System directory and if the file is not found the directories specified in PATH are searched lates Ltd VERSION ODBCLS32 DLL 5 53 0700 MB Foster Associates Ltd BETA VERSION R8 amp N5I32 DLL 5 42 0000 M B Foster Associates WSOCK32 DLL 4 00 Microsoft Corporation ODBC32 DLL 2 50 3006 Microsoft Corporation DDBCAD32 EXE 2 50 3006 Microsoft Corporation ODBCCP32 DLL 2 50 3005 Microsoft Corporation Figure 33 ODBCLink SE Page 61 M B Foster Software Labs Inc 1996 1998 Deleting Unwanted Drivers Troubleshooting Deleting Unwanted Drivers Y
82. st of tables on the host Using Microsoft Access 2 0 The Save login ID and password locally check box is not used by ODBCLink SE Once the table is attached it 1s treated like a local table in every way except that you may not modify the table structure You may open the table in table view run reports or queries against it or link it to other local or remote tables ODBCLink SE M B Foster Software Labs Inc 1996 1998 Page 49 Using Microsoft Access 7 0 Using ODBCLink SE from ODBC Applications Using Microsoft Access 7 0 To access data through MS Access 7 0 open a database and click on File Get External Data Link Files and select ODBC Databases from the file of Type pull down list Link Look in fa D Database alel EE Advanced Find files that match these criteria File name Text property Files of type Microsoft FoxPro dbf 2 Last modified time dBASE IV dbf Not all file tyyMicrosoft Excel xls 2 add other file types run the Setup program click Add RemovelMicrosoft FoxPro Paradox _ db Text Files csv tab 0 file s founqipy RG EEE EST aa Figure 25 You now see the SQL Data Sources window SQL Data Sources Select Data Source HP 3000 G erry HP 3000 1B HP 2000 member HP 3000 modem HP 3000 multiview HP 3000 parts HP 3000 Phil Figure 25 If the data source you require is not i
83. t SQL_DOUBLE Converted by Image SQL 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 Page 36 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Application Development Isolation Levels 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 i
84. t 0 icol 1 fCType 99 pcbValue 4 data 25 39 0 0 SQLGetData 0 hstmt 0 icol 2 99 pcbValue 3 data INV 73 78 86 0 SQLGetData 0 hstmt 0 icol 3 fCType 99 pcbValue 6 data E 56 7 1 0 Each line starts with the name of the function being called with the return code in brackets followed by the different parameters of the call Refer to the ODBC SDK manual for a description of the parameters of the call Page 68 ODBCLink SE Foster Software Labs Inc 1996 1998 Troubleshooting Tools on the PC Client ODBC Call Tracing using Dr DeeBee Spy Dr DeeBee Spy traces calls to the ODBC driver Dr DeeBee Spy logs each function call along with the input and output values To turn on logging do the following 1 From the Program Manager Menu in Windows click on RUN 2 Type DRDBSP 3 Choose OK 4 Select the Data Source Name to be traced 5 Choose OK The Dr DeeBee Spy icon will appear at the bottom of the screen It will flash when it is logging To turn off logging 1 Click on the Dr DeeBee Spy icon 2 Choose CLOSE The log is located in C WINDOWS DRDEEBEE LOG Dr DeeBee Spy is 1995 Syware Inc All rights reserved ODBCLink SE Page 69 M B Foster Software Labs Inc 1996 1998 Modifying the System Registry and ODBC INI Files Troubleshooting Modifying the System Registry and ODBC INI Files You only need to do this if you want to modify an option that you cannot configure in the ODBC
85. the Listener Log File on the HP3000 64 Checking the Listener Log File on the HP9000 64 Using the Host Testing Utility ODBCUTSE 64 Host Logging s iterii esc eve ELSE eee Dee ee BE ELEC LL ENS 67 Tools on the PC Client 5 seins ner oe or e ck o Eo oe Vea Eee rne 68 ODBC Call Tracing using ODBCLink LOG 68 ODBC Call Tracing using Dr DeeBee Spy 69 Modifying the System Registry and ODBC INI Files 70 Error Messages tcc seein BE eee eee eee RE i 71 Appendix A Implementation NOIGS ui aa tra oan ume e a E e 73 Primary key name returned by SQLStatistics 73 Reading or writing to ALLBASE SQL LongVarBinary items 73 Maximum number of statements 73 Page ii ODBCLink SE M B Foster Software Labs Inc 1996 ALLBASE SQL and IMAGE SQL Restrictions on the ODBC Grammar our eoe rene 74 Using the ANSI Character 75 Unsupported ALLBASE SQL and IMAGE SQL Statements 76 ALLBASE SQL and IMAGE SQL Statements That Work Only with Embedded SQL 76 ALLBASE SQL and IMAGE SQL Statements Replaced by Functions on the PC Client 77 Appendix B Creating a 79 On the HP3000 Database Server
86. uf 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 SQLBindCol Lib odbc dll ByVal hstmt AS long ByVal col As Integer ByVal wConvType As Integer IpdBuf As Any ByVal dwbuflen As Long lpcbout As Long As Integer Page 56 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using Visual C Using Visual C 1 51 Using ODBC with VC is beyond the scope of this manual However the following example which is drawn from the book Using ODBC 2 can be used to test 1 2 Run VC and select Project App Wizard Select Options Use MFC250 DLL You will have to create this DLL by using the make file MSVC MFC SRC MFCDLL MAK and then NMAKE f MFCDLL MAK Then copy it to Windows Systems Select Database options Database support No file support then select a data source and a table Click Create to create an application Select Project Build to compile your application and Project Execute to run it You will get a blank screen but you will be able to scroll through the record set although you won t see any data Select Tools AppStudio to enter the Windows resource editor Select the main form and place a text control on it Name your control IDC_CAR_NUMBER for example then save it and exit AppStudio Select Browse ClassWizard choose VCPPDemoView as
87. umns and indexes for a table enter SHOW followed by the name of the table For example gt sho recdb clubs Field ODBC Datatype HP Datatype CLUBNAME CHAR 15 CLUBPHONE SMALLINT ACTIVITY CHAR 18 Indexed field Index Type CLUBNAME CONSTRAINT PRIMARY Length Offset Nulls 15 0 2 16 1 18 20 1 Index Name CLUBS PK ODBCLink SE M B Foster Software Labs Inc 1996 1998 Troubleshooting Host Logging 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 S YS on MPE systems and tmp on Unix systems 96 11 20 14 12 09 0680 DBC listener started 96 11 20 14 13 13 068 Li 96 11 20 14 13 13 077 0 96 11 20 14 13 22 077A 96 11 20 14 13 26 077 A 96 11 20 14 13 27 077 96 11 20 14 13 27 077 96 11 20 14 13 27 077 96 11 20 14 13 27 077 96 11 20 14 13 27 077 96 11 20 14 13 27 077 96 11 20 14 13 28 077 96 11 20 14 13 28 077 96 11 20 14 13 28 077 96 11 20 14 13 28 077 96 11 20 14 13 28 077 C 96 11 20 14 13 35 077 P stener created new connection as SYS1 PIN 77 IP
88. will make the connection and display the Edit Page 42 Tables Screen ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using Cognos Impromptu Edit Qualification Weighting Database Tables Catalog Tables PURCHDB ORDERITEMS II PARTS La TORDERNUMBER INVENTORY ITEMNUMBER III SUPPLYPRICE VENDPARTNUMBER IIT VENDORS PURCHASEPRICE ORDERS ORDERQTY ORDERITEMS ITEMDUEDATE REPORTS RECEIVEDQTY PARTINFO nm Database WORLD Create Joins C HotFiles Figure 17 4 The left side of the Edit Tables screen is a list of table owners In ALLBASE SQL a table owner 15 the owner name assigned by the database administrator default is User Account To get a list of all the tables for a specific owner name double click on it To return to the list of owners double click on the owner name shown 5 You may now select the tables you want included in your catalog Click OK to load the table definition into the catalog 6 To add tables from another database to your catalog select Catalog Tables and repeat step 4 Double click on Database Name to see all entries 7 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 ODBCLink SE Page 43 M B Foster Software Labs Inc 1996 1998 Usin
89. xecute Page 40 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Using ODBCLink SE from ODBC Applications Using Cognos Impromptu Using Cognos Impromptu Use the following procedure 1 Click on Catalog Databases Add Enter the name you wish to call your database Select ODBC Gateway from the Database type pull down list and the name of your data source from the ODBC Data source pull down list If the data source is not there click on Admin and add it Click on the User ID check box to disable the prompt for user logon information it is in your data source Click OK Database Definition Logical Database Name Database Type Gateway WORLD Gateway s Database Details ODBC Data Source ODBC Connect String 320 User Prompts User ID Password Timeouts Connect o Reply o Use Asynchronous Open Figure 15 2 Click on Catalog New ODBCLink SE Page 41 Foster Software Labs Inc 1996 1998 Using Cognos Impromptu New Catalog Catalog File Name SS Description Catalog Database Mame wow T9 Select Tables Include All Tables Figure 16 Using ODBCLink SE from ODBC Applications 3 Enter a catalog name and then select the name of your database from the Database Name pull down list Click OK Impromptu
90. y column on the HP by using ODBC functions call sequence SQLPrepare SQLBindparameter DATA_AT_EXECUTION SQLExecute SQLParamData SQLPutData Refer to ODBC SDK manual for details 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 ODBCLink SE Page 73 M B Foster Software Labs Inc 1996 1998 Appendix A Implementation Notes 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
91. y the appropriate file to odbc inf Entering infsetup with no parameter specified will simply describe the usage but will not copy the file We have left the original odbc inf file on the disk so if infsetup is not run the install will still work in most cases If the wrong options is entered or your are installing on both a WindowsNT and Windows95 platform simply re run the infsetup bat file with the proper option Once the above has been accomplished proceed with the setup program as follows From the Program Manager Click on File then Run enter ODBCSEnn SETUP EXE From DOS enter ODBCSEnn SETUP EXE The Driver Setup Program will display this window When you click on Continue the Install Drivers window appears ODBCLink Driver Setup Program ete Welcome to the ODBCLink Driver Setup Program Choose Continue to install or Exit to leave without installing ODBCLink Figure 2 Page 16 ODBCLink SE M B Foster Software Labs Inc 1996 1998 Installing ODBCLink SE on the PC Install Drivers Select one more ODBC drivers to install from the list then choose OK Available ODBC Drivers Figure 3 You MUST click on a driver name even if only one driver name appears in the list then click OK The driver names for ODBCLink SE are ODBCLink SE 16 Driver for 16 bit applications ODBCLink SE 32 Driver for 32 bit applications Data Sources Driver Setup Delete a

Reference Manual

Contents

Download Pdf Manuals

Related Search

Related Contents