Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


ID: 18240, IBM Client Access/400 driver for dbExpress

by Peter Sawatzki Email: Anonymous


DBEXPCA400.DLL is a driver for connecting to AS/400 databases via IBM's Client Access/400 through Borlands dbExpress database components. Version 2.00.50
Download Details
FTP  download also available
CDN Login Required to Download. (You will be redirected to the login page if you click on the Download Link)
To download this, you must have registered:
A free membership

For Delphi, Version 6.0  to 7.0 502 downloads
Copyright: No significant restrictions


Size: 2,123,423 bytes
Updated on Wed, 01 Jan 2003 09:16:13 GMT
Originally uploaded on Fri, 05 Jul 2002 14:10:47 GMT
SHA1 Hash: 269831D82BDA5CA3410F258E438E19FC6DA13ADC
MD5 Hash: 8DD5C24214209B2D147D126CCF3E3D74

    Explore the files in this upload

Description
DBEXPCA400.DLL is a driver for connecting to AS/400 (iSeries, AS400) databases via
IBM's Client Access/400 product through Borlands dbExpress database components.

Licensing
---------
Usage of DBEXPCA400.dll and the static variants (dbexpca400.dcu, dbexpca400.obj,
clientaccess.dcu, clientaccess.obj) is free.

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

iSeries Access V5R2, SI05853
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 iSeries Access V5R2 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=
ServerCharSet=
ErrorResourceFile=
LocaleCode=0000
BlobSize=-1
RowsetSize=-1
RoleName=
CA400 TransIsolation=DirtyRead
CommitRetain=True
AutoCommit=True
Custom String=/trace=0
Connection Timeout=-1
Trim Char=False

[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.

Trim Char
---------
This option instructs the driver to right trim spaces when returning CHAR() columns.
The option was introduced in Delphi7 and shows a similar behaviour like the BDE had.
Standard behaviour of dbExpress drivers however is to NOT trim CHAR() fields: it
is exactly returned what is in the database! To trim characters in earlier versions
use RoleName=/trimchar (see Additional options).

ServerCharSet
-------------

(beware: preliminary, subject to change, example)

ServerCharSet=424,1255

Forces the driver to think every table has CCSID 424 (EBCDIC Hebrew) and converts to
Ansi Hebrew. Will be enhanced further.

Additional options supported by the driver
------------------------------------------
Additional options are communicated to the driver through dbExpress "Custom String"
parameter. In earlier versions (pre Delphi7) 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
/limitmd=900 limit metadata retrieval to 900 tables
/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
/trimchar right trim spaces from char() columns

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.

SysNaming, Metadata and full quoting
------------------------------------
If sysnaming is enabled, *unqualified* tables are searched by DB2/400 using the *LIBL
library list. To mirror this for metadata retrieval and especially stored procedure parameter
retrieval, the driver also searches along the *LIBL path. The driver also enumerates by
default all tables within the *LIBL library list. Without the /fullquoting option
unqualified names are returned to the IDE.

It can make sense to work with db2 naming (default), /fullquoting and /libs=*LIBL
or /libs=*USRLIBL. This enables searching metadata along the library list and returns
tablenames fully quoted.

For sysnaming, if /libs= is not explicitly set, it defaults to *LIBL. For
DB2 naming (default) /libs has to be explicitly set.

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 (or dbexpca400-debug.dcu to dbexpca400.dcu for the static builds).

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
2.0.51 important fixes for CCSID support and new CCSID sample app
2.0.52 support for traditional and simplified chinese
2.0.53 implement CCSID override
2.0.54 implement trim char
2.0.55 intermediate release
2.0.56 add Delphi 6.02 and Delphi 6.00 (retail) dcu builds
2.0.57 fixes for stored procedure parameters and stored procedure names
2.0.58 BCD bugs fixed
2.0.59 add *LIBL metadata support
2.0.60 enhance Integer/ShortInt/BigInt interop
2.0.62 fix for stored procedures with no parameters
2.0.64 schema fixes for SPs
2.0.65 fix minor bug in trace options

Link
----
See http://www.sawatzki.de for the latest version

Copyright © 2001-2003 Peter Sawatzki (Peter@Sawatzki.de)
Mail: Buchenhof 3, 58091 Hagen, GERMANY
WWW: http://www.sawatzki.de


For more information, see http://www.sawatzki.de

   Latest Comments  View All Add New

Move mouse over comment to see the full text

Could not retrieve comments. Please try again later.

Server Response from: ETNACDC04