IBM Client Access/400 driver for dbExpress DBEXPCA400 Delphi 7 or 6 and C++Builder 6 Release Notes ======================================================= Copyright © 2002 Peter Sawatzki (Peter@Sawatzki.de) Mail: Buchenhof 3, 58091 Hagen, GERMANY WWW: http://www.sawatzki.de Version: 2.0.50 Date: 9.08.2002 (see end of document for detailed development history) Abstract -------- DBEXPCA400.DLL is a driver for connecting to AS/400 databases via IBM's Client Access/400 through Borlands dbExpress database components. Licensing --------- Usage of DBEXPCA400.dll and the static variants (dbexpca400.dcu, dbexpca400.obj, clientaccess.dcu, clientaccess.obj) is free except for companies whose name begins with "AFI ". Requirements ------------ Delphi version 7 or 6 or C++Builder version 6. An installed version of Client Access/400 is required. The driver has so far been tested with Client Access Express V5R1, SI05361 (01-Aug-2002) Client Access Express V4R5, SF67104 (23-Apr-2002) Client Access Express V4R4, SF65706 (15-May-2001) Client Access V3R2, SF67055 (final release) On the OS/400 side make sure the latest PTFs are applied, especially if you want to use the LOB features. The following DB2 group PTFs are recommended: OS/400 V5R1: SF99501 OS/400 V4R5: SF99105 OS/400 V4R4: SF99104 Currently most of driver testing is done with Client Access V5R1 against OS/400 V4R5. For LOB support at least V5R1 of Client Access and at least OS/400 V4R5 are required. Installation instructions ------------------------- 0) set up your AS/400 connection in Client Access, for this example the connection is called "MYAS400" (usually this is already configured) 1) copy dbexpca400.dll to a directory that is in your path, make sure only one version of dbexpca400.dll is on your system. The Windows\system or WINNT\system32 directory is a good place for the driver. (optional) copy dbexpca400.obj and clientaccess.obj to $(BCB)\Lib for C++Builder 6 (optional) copy dbexpca400.dcu and clientaccess.dcu to $(Delphi)\Lib for Delphi 7 or 6 2) modify dbXDrivers.ini in \Program Files\Common Files\Borland Shared\DBExpress\ [Installed Drivers] CA400=1 [CA400] GetDriverFunc=getSQLDriverCA400 LibraryName=dbexpca400.dll VendorLib=cwbdb.dll Database=MYAS400 User_Name= Password= ErrorResourceFile= LocaleCode=0000 BlobSize=-1 RowsetSize=-1 RoleName= CA400 TransIsolation=DirtyRead CommitRetain=True AutoCommit=True Custom String=/trace=0 Connection Timeout=-1 [CA400 TransIsolation] DirtyRead=0 ReadCommited=1 RepeatableRead=2 3) drop a TSQLConnection on a form, double click the TSQLConnection then choose "+ Add Connection", select driver name "CA400" and give your connection a name, for example "MYCA400CONN". Check that Database is set to the connection you defined in Client Access/400 (MYAS400) 4) set the "LoginPrompt" property of SQLConnection to false if you don't want to be prompted for a password (the connection security is maintained by Client Access/400) 5) drop a TSQLQuery on your form, set it's SQLConnection property to your SQLConnection. Set the SQL property to for example 'select * from "QIWS"."QCUSTCDT"' (QIWS.QCUSTCDT is a standard table on every AS/400 system) Samples ------- the following sample applications are included: Samples\Test400 generic test application for Delphi Samples\Test400bcb generic test application for CBuilder Samples\DataLink application to demonstrate datalink usage Samples\Catalog demonstrates the usage of the catalog feature Samples\StoredProc demonstrates stored procedures with result set For a quick test, start test400.exe, fill in AS/400 system name and press open. Table QCUSTCDT from library QIWS should be displayed (according to IBM delivered with every AS/400 system) If sample applications are included in compiled form, they are statically linked and work without dbexpca400.dll. General information =================== Connection information ---------------------- The following parameters are used for connecting to AS/400: Database: this is the AS/400 system name you have defined in Client Access/400. Setting this to * lets the driver get the default system name. User_Name: this is the user name that is used to connect to AS/400. If the user name is empty, the driver connects with the default user/security that is defined in Client Access/400 environment. Password: this is the password that is transferred if username is given. TIP: if you are using the default connection, you may set Database=* User_Name= Password= and set LoginPrompt of the connection to false. This should connect you via your default profile without beeing asked for username and password, provided you have configured Client Access this way. Connection Timeout ------------------ If using Client Access V5R1 or later and Delphi 7 the connection timeout value is used to indicate a query timeout value. The default value of -1 means *nomax as query timeout. RoleName parameter ------------------ when connecting to DB2, your collection is automatically set to your username. For SQL naming convention (the default in this driver) this means that when using unqualified object names, for example a query of the form 'select * from table' does implicitly prepend your username to the query. The actual query executed is thus: 'select * from myself.table' assuming your username is 'myself', the 'table' would be searched in library 'myself'. Beginning with V5R1 of Client Access Express it is possible to preset the default collection with this driver by using the RoleName option. If you set RoleName for example to QGPL, unqualified object names are searched in QGPL. It is also possible to use system naming convention with this driver (by appending '/sysnaming' to RoleName or Custom String). By design system naming convention looks for all libraries in the user's *LIBL path for unqualified objects. RowSetSize parameter -------------------- RowSetSize is misused as the number of rows dbexpca400 fetches during one round trip to the AS/400 (blockcount). Client Access/400 usually determines this parameter better by itself, but with for example rowsetsize=200 you can fetch 200 lines at once. You should set rowsetsize=-1 in order to let Client Access determine the blockcount. if you do not specify RowSetSize, dbExpress sets RowSetSize to 20. Additional options supported by the driver ------------------------------------------ Additional options are communicated to the driver through dbExpress "Custom String" parameter. In earlier versions of dbExpress this option was not available so the RoleName parameter is used for these version. Either set "Custom String" to or append to RoleName options in the following form: '/option1=value/option2=value..' The following options are supported: /trace=0 disable trace (default) /trace=1 trace to OutputDebugString (see "Event Log" in IDE) /trace=2 trace to SQLMonitor (needs Enterprise SKU) /trace=3 trace to OutputDebugString and SQLMonitor /sysnaming enable system naming instead of SQL naming /fullquoting quote table names returned for metadata (see below: Quoting) /libs=Lib1,Lib2,..,LibN set the libraries that are searched for metadata /lobthreshold=16384 set LOB threshold to 16 KB (LOBs<16KB are returned 'inline') /describe=0 set column name types: 0=ALIAS_NAMES (default), 1=NAMES_ONLY, 2=LABELS Quoting ------- The driver quotes by default all metadata it returns. Tables are correctly quoted in the form "SCHEMA"."TABLE" (or "SCHEMA"/"TABLE" if you are using system naming) because this is the way DB2 quotes. It is not correct to quote in the form "SCHEMA.TABLE" like dbExpress (prior to Delphi7) does. Beginning with Delphi 7 the driver is used to quote table names and this is correctly done in this driver. With the option "/fullquoting" you may enable the behaviour of the driver for earlier dbExpress versions to return fully quoted table names in metadata returned. Be aware that quoted names become case sensitive. So for example a 'select * from "QIWS"."QCUSTCDT"' is the same as 'select * from qiws.qcustcdt' and returns correct data. However 'select * from "qiws"."qcustcdt"' fails because of lowercase charaters in the table name. Notice: Quoting is especially important when working with MIDAS/Datasnap. For usage of dbExpress prior to Delphi 7 it is recommended to 'pre-quoted' every table name! Commitment control ------------------ default for commitment is dirtyread (= no commitmentcontrol). If you change this make sure that for all files that you are trying to access you have enabled journaling (use STRJRNPF for this). LOB support ----------- If using Client Access/400 V5R1 or later and OS/400 V4R5 or later, LOBs may be utilized. By default LOBs are returned as LOB locators from AS/400 and fetched on demand. This is also the default mode implemented in dbexpca400. There is an option implemented in this driver that enables returning of "Inline" BLOBs as part of the result set. This means that the BLOB is always fetched. If it makes sense to your application, you may set a LOB threshold. If you set LOB threshold to for example 16384 by appending /lobthreshold=16384 to ROLENAME parameter, every LOB that is *defined* as being smaller than 16KB, is returned inline and fetched. DataLink support ---------------- dbExpress lacks support for a DataLink type, so DB2/400 DataLink types are mapped to fldZSTRING. See Samples\DataLink for an example of DataLink usage Stored Procedure Support ------------------------ The driver has full support for stored procedures (parameters, multiple result sets). For an example of a stored procedure with two result sets and input/output parameters see Samples\StoredProc Package Support --------------- Package support and enhanced prepare may be enabled for a SQL query by embedding a special comment in the query: a comment of the form /* pkg=MYLIB.MYPACKAGE */ makes the SQL request prepare and use a statement in library MYLIB, package MYPACKAGE. Library and package name length may not exceed ten characters. Compiling with the static libraries ----------------------------------- To avoid deploying dbexpca400.dll with your application you can link with the static libraries supplied. For Delphi simply put "Uses DbExpCa400" in your uses clause and for C++ Builder simply add dbexpca400.obj to your project file for linkage. The IDE always uses dbexpca400.dll. Using debug versions -------------------- The included debug versions output additional information to DebugOutput or SQLMonitor. To use the debug version, rename it from dbexpca400-debug.dll to dbexpca400.dll. Data type mappings ------------------ AS/400 type dbExpress type =========== ============== CWBDB_PCSTRING fldZSTRING/fldstFIXED CWBDB_PCGRAPHIC fldZSTRING/fldstFIXED CWBDB_PCVARSTRING fldZSTRING CWBDB_PCVARGRAPHIC fldZSTRING CWBDB_PCVARDATALINK fldZSTRING CWBDB_PCBIGINT fldINT64 (no support in dbExpress yet) CWBDB_PCLONG fldINT32 CWBDB_PCSHORT fldINT16 CWBDB_PCFLOAT fldFLOAT CWBDB_PCDOUBLE fldFLOAT CWBDB_PCPACKED(1..9,0) fldINT32 CWBDB_PCPACKED fldBCD CWBDB_PCZONED(1..9,0) fldINT32 CWBDB_PCZONED fldBCD CWBDB_SQLDate fldDATE CWBDB_SQLTime fldTIME CWBDB_SQLTimeStamp fldDATETIME CWBDB_PCBLOB fldBLOB/fldstBINARY (needs at least CA V5R1 and OS/400 V4R5) CWBDB_PCBLOBLOCATOR fldBLOB/fldstBINARY (needs at least CA V5R1 and OS/400 V4R5) CWBDB_PCCLOB fldBLOB/fldstMEMO (needs at least CA V5R1 and OS/400 V4R5) CWBDB_PCCLOBLOCATOR fldBLOB/fldstMEMO (needs at least CA V5R1 and OS/400 V4R5) Catalog API ----------- The driver supports AS/400 catalog API. This API is non-standard, only available and specific to AS/400 and the usage as implemented in this driver is documented in catalog.txt. Note ---- Although VendorLib is set to cwbdb.dll changing this value has no effect. In fact cwbdb.dll, cwbnl.dll, cwbsv.dll and cwbsy.dll are used. Release History --------------- 1.0.56 surface logical files as Indices in metadata 1.0.57 corrected readme bug (LocalCode contained an invalid hex code) 1.0.60 fixed several bugs 1.0.65 added support for OS/400 V4R3 and Client Access V4R4 1.0.68 use logical catalog files (QADBLDEP, ..) instead of physical 1.0.69 added automatic conversion of zoned(N,0) and packed(N,0) to int 1.0.70 added parameter binding for zoned(N,0) and packed(N,0) 1.0.75 added affected rows by using a hidden feature in CA/400 1.0.80 added float support 1.0.85 added BCD support 1.0.95 added DATE, TIME and TIMESTAMP support 2.0.00 driver rewritten in Delphi 2.0.05 support LOBs 2.0.10 support long filenames, long field names, system naming 2.0.24 release for C++ Builder 6 2.0.30 add support for service usage, use cwbco.dll for login 2.0.32 add describe option 2.0.33 speed things up by using cwbDB_PrepareDescribe 2.0.34 add support for SP output parameters and multiple result sets 2.0.35 metadata implementation for SPs and their parameters 2.0.36 add transaction support and commitretain 2.0.37 speed things up with SetAmbiguousSelectOption = READONLY 2.0.38 speed up string conversions a lot 2.0.40 add usage of default system 2.0.41 add CATALOG retrieval (see catalog sample) 2.0.42 add DATALINK datatype as string, add version info to DLL 2.0.43 shrink DLL from 250k to 160k by using modified dbxpress 2.0.44 implement quoting support for D7's dbExpress release 2.0.45 add package support 2.0.48 add connection timeout, D7 custom string support 2.0.49 add INT64 support despite no dbExpress support 2.0.50 release for Delphi 7 Link ---- See http://www.sawatzki.de for the latest version