This is how to use ADO / OLE DB with AS/400

[Steve Swett]

I have been using Delphi to access AS/400 tables with ODBC, but ODBC is a
bit of a pain because you have to setup a System DSN on each client PC. I
haven't found a way around that. I've written a utility to extract DSNs to
a file from a "model PC" and then install the DSNs easily on clients without
using the ODBC control panel area, but it's still not ideal.

So I purchased ADO Express (I have Delphi 5) hoping to eliminate the DSN
requirement -- which it does. Initial attempts to use ADO resulted in an
error "The operation requested by the application is not supported by the
provider" when I tried to open the TADOConnection. Recently, I figured out
the problem.

Here are the steps to successfully connect to the AS/400 using the Client
Access ADO / OLE DB Driver. It's assumed that you have already installed
ADO Express and the Client Access OLE DB driver.

1. In your TADOConnection control, go to the ConnectionString property and
click the Build button. On the Provider tab, choose the IBM AS400 provider.
On the Connection tab, specify a Datasource (which is your Connection Name
as setup in Client Access Operations Navigator -- usually your AS400 system
name); then specify to "use a specific name and password."

You may as well leave the password blank, because the AS400 OLE DB Provider
won't accept a password in the connection string anyway. You can specify a
user name, and it will become the default on a user/password dialog box.
You can override the user name at run-time by manipulating the
ConnectionString property, but you can't override or otherwise provide a
password at run-time via the connection string.

2. You can leave the TADOConnection's LoginPrompt property set to false.
The first time a user runs the application, a user/password dialog box will
still pop up. If your PC is set to cache passwords, then the login prompt
will be bypassed for users with cached passwords.

3. You must leave the TADOConnection's DefaultDatabase property blank.
Instead of specifying a database here, you must specifically qualify your
table names in SQL statements. For example, specify MYDB.MYTABLE instead of
just MYTABLE.

4. The AS400 OLE DB Provider doesn't like parameterized queries. If you
are in the habit of using SQL statements that contain parameters -- such as
:StudentID -- you will need to replace that technique by building the
final-form SQL statements in your Delphi source, such as:
with Form1.StudentsADOQuery do
begin
SQL.Clear;
SQL.Append('select dbcnam,dbcdsn,dbcdbn,dbcusr,'+
'dbcpwd,dbcdft,dbcadu,dbcadp from bcsgeneral.pgendbc where dbcnam = '+
#39+StudentsComboBox.Text+#39);
ExecSQL;
end;

I've only recently made this breakthrough. I haven't had time yet to
compare the performance with ODBC, but I have replaced the ODBC controls
with ADO controls in one program (which does SELECTs, INSERTs, UPDATEs and
DELETEs), and it functions fine.