Calling AS/400 CL Programs from VB - OLEDB or ODBC

[prasanth...@gmail.com]

Hi
I am having difficulty in calling AS/400 CL Programs from VB6 using
OLEDB. The server returns SQL Error. But, everything works fine with
ODBC. Has any one faced this problem earlier?
I am on Windows XP with V5R2 of Client Access.

Thanks and Regards
Prasanth

[Patrick Bielen]

Hi Prasanth,

<prasanth...@gmail.com> schreef in bericht
news:1109825084.3...@z14g2000cwz.googlegroups.com...

> I am having difficulty in calling AS/400 CL Programs from VB6 using
> OLEDB. The server returns SQL Error. But, everything works fine with
> ODBC. Has any one faced this problem earlier?

Can you give an example ?

Cause calling cl program from oledb is working perfectly.
I guess you have wrong syntax.

Best Regards,

Patrick
MCP / SCJP

[prasanth...@gmail.com]

Hi
Here is the code that I am using:

dim objAS400Conn As New OleDbConnection
dim str as string = "Provider=IBMDA400.DataSource.1;Persist Security
Info=True;User ID=TEST;Password=TEST;"
str &= "Data Source=XX.XX.XX.XX;Transport Product=Client
Access;SSL=DEFAULT;Force Translate=00037;Default Collection=MYLIB;
CATALOG LIBRARY LIST = MYLIB"
objAS400Conn.ConnectionString = str
objAS400Conn.Open()

Dim objAS400Cmd As new OleDbCommand
With objAS400Cmd
.Connection = objAS400Conn
.CommandTimeout = 15
.CommandType = CommandType.Text
.CommandText = "{call " & "MYLIB" &
".SSC2233(?,?,?,?,?)}"
.Parameters.Add(New OleDbParameter)
.Parameters.Add(New OleDbParameter)
.Parameters.Add(New OleDbParameter)
.Parameters.Add(New OleDbParameter)
.Parameters.Add(New OleDbParameter)
End With
With objAS400Cmd.Parameters(0)
.Direction = ParameterDirection.Input
.Value = TableName
.OleDbType = OleDbType.Char
.Size = 10
End With
With objAS400Cmd.Parameters(1)
.Direction = ParameterDirection.Input
.Value = FieldList
.OleDbType = OleDbType.Char
.Size = 255
End With
With objAS400Cmd.Parameters(2)
.Direction = ParameterDirection.Input
.Value = FieldValues
.OleDbType = OleDbType.Char
.Size = 255
End With
With objAS400Cmd.Parameters(3)
.Direction = ParameterDirection.Input
.Value = StoreNumber
.OleDbType = OleDbType.Char
.Size = 5
End With
With objAS400Cmd.Parameters(4)
.Direction = ParameterDirection.Output
.Size = 2
.Value = " "
.OleDbType = OleDbType.Char
End With
'Execute SP
objAS400Cmd.ExecuteNonQuery()

[Patrick Bielen]

Hi Prasanth,

<prasanth...@gmail.com> schreef in bericht
news:1110347985.3...@g14g2000cwa.googlegroups.com...

> Here is the code that I am using:

> .CommandText = "{call " & "MYLIB" & ".SSC2233(?,?,?,?,?)}"

I can track two errors in your code.

But to start with, here a quote from the ibm redbook for Ole DB...

=======================================================

You can use the Client Access OLE DB provider to send commands to the
AS/400 system or call AS/400 programs. IBMDA400 allows you to interface with
existing AS/400 CL commands and programs. The SDK Visual Basic wizard
provides support for working with commands and program calls. You can call
an
AS/400 command and pass command parameters. You can use the
CommandText property to specify the command. The following syntax is used to
invoke commands:

{{<command> <command parameters>}}

The two braces around the whole command are required. You can specify the
command and the required paramters using this syntax. Command parameters
values are optional. They are required depending on the CL command that you
are invoking.

========================================================

As you can see you have to use two brackets instead of one, one bracket
is for stored procedures, while you are doing a CL-command call.

=========================================================

For commands, you may use IFS or *SYS naming convention. *SQL naming is
not allowed.

=========================================================

As you can see, your second error is that you're using *SQL naming
convention,
and for CL-commands that doesn't work.

So your command should be created like this...

.CommandText = "{{call MYLIB/SSC2233(?,?,?,?,?)}}"

Test it out and let us know if it works.

Best Regards,

Patrick Bielen
MCP / SCJP

[prasanth...@gmail.com]

Hi Patrick,
Thanks a lot for your time.
I tried your suggestions. But, the application raised an exception from
System.Data (Object reference not set to the instance of an object).
I think there is some issue with the libraries that are being used. I
found an article in ibm.com in this regard.
http://www-1.ibm.com/support/docview.wss?uid=nas12554db904e5a601b862565c2007cc4b8

The program uses a file in AS/400 and writes into it.
When we debugged in AS/400, we found that with OLEDB connection, AS/400
tries to look for this file in some wrong library (with the same name
as the user id I use in the connection). But with ODBC, this behaviour
does not happen.
Any idea why this is happening?
I am using V5R2M0 Service Level SI09809.
If you don't mind, can you please let me know the version of Client
Access that you are using?
Also, do you specify any library in your connection string?

Thanks
Prasanth

[Patrick Bielen]

Hi Prasanth,

<prasanth...@gmail.com> schreef in bericht
news:1110455105.6...@z14g2000cwz.googlegroups.com...

> Thanks a lot for your time.

No Problem

> AS/400 tries to look for this file in some wrong library (with the same
> name as the user id I use in the connection).

> Any idea why this is happening?

Donno, i never use libraries in my connectionstrings.
I use and oledb datasource i created with the odbc manager.
There you can config a library if needed.

> I am using V5R2M0 Service Level SI09809.
> If you don't mind, can you please let me know the version of Client
> Access that you are using?

I use iSeries access for windows V5R3M0 because of the .net
managed driver it contains, while our iseries is on V5R2M0.
The service level of iSeries Access V5R3M0 is SI16496.

Anyway for V5R2M0 the service release is SI16136 in
meanwhile, so time to upgrade i would say... you can
find the upgrades here...

http://www-1.ibm.com/servers/eserver/iseries/access/casp.htm

> Also, do you specify any library in your connection string?

Nope never, i just connect and in the query i use the right
library i need. Or you can put the library in the config of the
udl file or the odbc-manager where you can create an odbc
data-source and give the needed libs.

Hope this helps, anyway, keep me informed.

[Steve Landess]

Prasanth -

Unlike ODBC, OLE DB has no concept of library lists...

Read this article for more information:
http://www-1.ibm.com/support/docview.wss?uid=nas19d3e03ceba505db4862567ef0037496b&rs=110

Does this answer your questions?

You'll either need to qualify your files to the libraries (not a good idea),
or supply a default collection in your connection string.

Regards,
Steve Landess
Austin, Texas
(512) 423-0935

[prasanth...@gmail.com]

Hi
I tired supplying a default library along with the connection string.
Still, the program is going to the wrong library. We are going to
approach IBM with this issue. Will keep you posted.

Thanks a lot for the help.

Thanks and Regards
Prasanth

[Elvis]

set schema mylibrary;

before you execute your sql query

[prasanth...@gmail.com]

We contacted IBM. As per IBM, there is no way we can set the library
list using OLE DB. So, we are going to change CL programs to set the
libraries.

Thanks
Prasanth

[Peter Kinsman]

If you use IBMDA400 as the provider, can you not put CL commands within
double braces?

Set cnAS400 = New ADODB.Connection
cnAS400.Open "Provider=IBMDA400; Data Source=sys_name;", "UID", "PWD"
cnAS400.Execute "{{addlible library_name}}", 0, adCmdText

Peter Kinsman

<prasanth...@gmail.com> wrote in message
news:1111403956....@f14g2000cwb.googlegroups.com...