PS.Data.iSeries (ID #20790)

Peter Sawatzki

PS.Data.iSeries driver for
Delphi for .NET Release Notes
=============================

Copyright © 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
--------
PS.Data.iSeries.dll is a driver for connecting to iSeries (formerly called AS/400) databases via
IBM's iSeries Access (Client Access/400) product through Borlands BDP .NET technology.

Licensing
---------
Usage of PS.Data.iSeries.dll is free for commercial and non-commercial use.
Sourcecode is available for purchase.

Requirements
------------
* Delphi for .NET or C#Builder Version 1.0.
* .NET Framework 1.1.
* An installed version of iSeries Access (formerly called 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.

Quickstart instructions
-----------------------
Extract the contents of the archive into a directory of your choice.
On a command line execute "gacutil /i PS.Data.iSeries.dll" and copy the
bdpiseries.dll to a directory in your path (for example /Windows/system32)

(I'm assuming that your iSeries Access environment was previously set up)

Development environment installation instructions
-------------------------------------------------
To use the driver in your development environment I suggest to add the following
entries to your C#Builder configuration file found in the BDS/1.0/bin directory:

File bdpDataSources.xml
-----------------------

Tables
Procedures
Views

Connection Sample
-----------------

A simple Delphi for .NET/BDP connection sample that works on every iSeries system
would be something like this:

program ConsoleSimple;
{$APPTYPE CONSOLE}

{%DelphiDotNetAssemblyCompiler '../../../../program files/common files/borland shared/bds/2.0/shared assemblies/Borland.Data.Common.dll'}
{%DelphiDotNetAssemblyCompiler '../../../../program files/common files/borland shared/bds/2.0/shared assemblies/Borland.Data.Provider.dll'}

uses
SysUtils,
Borland.Data.Provider;
Const
Conn = 'Database=*;Username=;Password= ;Assembly=PS.Data.iSeries, Version=1.0.0.0, Culture=neutral, PublicKeyToken=df047b757d3f379a';
var
bdpReader: BdpDataReader;
bdpCmd: BdpCommand;
bdpConn: BdpConnection;
begin
Console.Write('Connection to iSeries database system...');
bdpConn:= BdpConnection.Create(Conn);
bdpConn.Open;
Console.WriteLine('Done.');
bdpCmd:= BdpCommand.Create('select * from QIWS.QCUSTCDT', bdpConn);
bdpReader := bdpCmd.ExecuteReader;
Console.WriteLine('CustNo: Name (Street)');
while bdpReader.Read do
Console.WriteLine(Format('%s: %s (%s)', [bdpReader['CUSNUM'], bdpReader['LSTNAM'], bdpReader['STREET']]));
bdpReader.Close;
end.

A simple C#/BDP connection sample that works on every iSeries system
would be something like this:

class ConsoleTestiSeries {
[STAThread]
static void Main(string[] args) {
string SQL = "select * from QIWS.QCUSTCDT"; // this is a table on every iSeries
string Conn = "Database=*;Username=;Password= ;Assembly=PS.Data.iSeries, Version=1.0.0.0, Culture=neutral, PublicKeyToken=df047b757d3f379a";
Console.Write("Connection to iSeries database system...");
BdpConnection bdpConn = new BdpConnection(Conn);
bdpConn.Open();
Console.WriteLine("Done.");
BdpCommand bdpCmd = new BdpCommand(SQL, bdpConn);
BdpDataReader bdpReader = bdpCmd.ExecuteReader();
Console.WriteLine("CustNo: Name (Street)");
while (bdpReader.Read())
Console.WriteLine(bdpReader["CUSNUM"] + ": " + bdpReader["LSTNAM"] + " (" + bdpReader["STREET"]+")");
bdpReader.Close();
}

Notice that password is set to a space to avoid a username/password dialog!

Samples
-------
the following sample applications are included:

ConsoleSimple
GridSimpleCS
ConsoleSimpleCS

(make sure that PS.Data.iSeries.dll and bdpiSeries.dll are in the same directory
as the sample applications)

Options
-------

Connection options are specified in the form "option1=value1;option2=value2" just
like the connectionstring is specified.

The following ConnectionOptions for a bdpConnection 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=true/false enable system naming instead of SQL naming
libs=Lib1,Lib2,..,LibN set the libraries that are searched for metadata
limitmd=300 limit metadata retrieval to n tables (default: 300)
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=true/false right trim spaces from char() columns
role=myrole set role to myrole
QuoteObjects=true/false determines if objects are quoted or not

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.

Tip: specify "libs=*LIBL" or "libs=MAINLIB" to limit metadata retrieval to the
libraries you are interested in (where MAINLIB is your main working LIB).

Release History
---------------
2.0.84 release for C#Builder
2.0.85 add Row property to iSeriesResolver
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

PS.Data.iSeries home page