Watch, Follow, &
Connect with Us

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


ID: 20791, dbexpca400

by Peter Sawatzki Email: Anonymous


DBEXPCA400.DLL is a driver for connecting to iSeries (AS/400) databases via IBM's iSeries Access through Borlands dbExpress database components with Delphi for .NET
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 8.0  to 8.0 136 downloads
Copyright: No significant restrictions


Size: 3,101,813 bytes
Updated on Mon, 08 Dec 2003 01:00:58 GMT
Originally uploaded on Sun, 19 Oct 2003 06:26:18 GMT
SHA1 Hash: 8A47C5E22E412506EE6F0933BCC08B1275C72700
MD5 Hash: F27A1FAB2F60947C4B57A71B45DCCCD2

    Explore the files in this upload

Description
IBM Client Access/400 driver for dbExpress
DBEXPCA400 Delphi for .NET, Delphi 7 or 6 and C++Builder 6 Release Notes
========================================================================

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

Version: 2.0.92
Date: 08.12.2003

(see end of document for detailed development history)

Abstract
--------
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 for .NET, Delphi version 7, 6, 6.03 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, SI08894
Client Access Express V5R1, SI06804
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 V5R2: SF99502
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 V5R2. 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

Sorting
-------

/sorttype=-1 specifies how the server sorts records before sending them to the client.
-1 = default setting, use what is defined for the job/user
0 = Sort based on hexadecimal values of the EBCDIC character
1 = Shared-Weight sort based on the language set in /languageid property
(uppercase and lowercase characters sort as the same character)
2 = Unique-Weight sort based on the language set in /languageid property
(uppercase and lowercase characters sort as different characters)
3 = Sort based on the sort sequence table set in the SORTTABLE property

/sorttable=ENU if /sorttype is 1 or 2, specifies a 3-character language id to use for selection
of a sort sequence:
AFR Afrikaans CSY Czech
SQI Albanian DAN Danish
ARA Arabic NLD Dutch
NLB Belgian Dutch ENA English Australian
FRB Belgian French ENP English Upper Case
PTB Brazilian Portuguese FAR Farsi
BGR Bulgarian FIN Finnish
BEL Byelorussian FRA French
FRC Canadian French DEU German
CAT Catalan ELL Greek
HRV Croatian HEB Hebrew
GAE Irish Gaelic HUN Hungarian
ITA Italian ISL Icelandic
JPN Japanese Katakana SRL Serbian Latin
KOR Korean CHS Simplified Chinese
MKD Macedonian SKY Slovakian
NOR Norwegian - Bokmal SLO Slovenian
NON Norwegian - Nynorsk ESP Spanish
PLK Polish SVE Swedish
PTG Portuguese FRS Swiss French
RMS Rhaeto-Romanic DES Swiss German
ROM Romanian ITS Swiss Italian
RUS Russian THA Thai
SRB Serbian Cyrillic CHT Traditional Chinese
ENU US English (default) TRK Turkish
ENG UK English

if /sorttype=3, specifies a user defined sort table name. May be further qualified by /sortlib=

/sortlib= Specifies the library of a sort sequence table stored on the iSeries server.
This property has no effect unless the /sorttype property is set to 3.

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.

Make sure to link with the correct version for your compiler. For Delphi 6 Update 2
make sure that you also applied the 2.8 MB "Delphi 6 RunTime Library Update 2" in addition
to the "General Update 2" and the 1.8 MB d6_rtl3.zip update because the supplied
dbexpca400.dcu and clientaccess.dcu files in the 6.03 directory were compiled with the
latest Delphi6 update files.

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
2.0.66 fixed CCSID bug in queries text
2.0.66 fixed one off problem for parameter queries
2.0.68 remove CRLF from queries for MIDAS to work correctly
2.0.70 implement national language sort options
2.0.71 fix bug in Catalog FileAttr
2.0.72 add CL/RPG sample for SP, circumvent SP parameter bug in SqlExpr
2.0.80 Modifications to support a common codebase for BDP/C#Builder
2.0.81 fix Open/Close cursor bug
2.0.84 release for C#Builder
2.0.86 fix CLOB bug
2.0.87 fix CLOB LOCATOR bug with params
2.0.88 logon fix for V3R2, cursorname for Stored Procedures fix
2.0.92 release for Delphi8 for .NET

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

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: ETNACDC03