Execute a store procedure in AS400

HCL Domino Domino Forum
1

Hello,

I want to execute a store procedure in AS400. I am calling the store proc like this from lotus notes,

.

.

status= result.ExecProcedure(“lib1.SP_OVRDBF”) ’ Gives error, Error 720: LS:DO- ODBC could not complete the requested operation.

or

status= result.ExecProcedure(“SP_OVRDBF”) ’ Gives error, Error 720: LS:DO- ODBC could not complete the requested operation.

or

Call result.ExecProcedure(“SP_OVRDBF”) ’ Gives error, Error 720: LS:DO- ODBC could not complete the requested operation.

I can very well see the list of store proc via following code,

tables = con.ListProcedures(“mydsn”, username, password).

I am not using DECS, or LCLSX.

I am using LSXODBC. As per the designer help it says,

status = odbcResultSet.ExecProcedure( procedureName$ [, arg1] [, arg2] … [, arg30] ).

This is exactly I want to perform. Executing a store proc and pass a parameter to it from lotus notes.

Can any one guide me what is the wrong in the code?

Thanks

MS

2

Subject: ANSWER - Execute a store procedure in AS400 - ANSWE

I’ve had this problem too for a while and kept beating at it, and I’ve come up with the solution.

The .execProcedure needs to have a “Stored Procedure” cataloged on the 400. You can do so by programmatically by executing a “Create Procedure” before running the .execProcedure, then executing a “Drop Procedure” afterwords to clean up.

In my as400 program, I was also able to change one of the parms and use that changed parm in the lotus script.

Here’s some sample code:

sql = “Create Procedure Pgmmec074.Stan2 (In Parm1 Char(4), InOut Parm2 Char(7)) Language CL No SQL Parameter Style General”

qry.SQL=sql

res.Execute

cmdLine = “PGMMEC074.STAN2”

Dim parms(1) As Variant

parms(0) = “TEST”

parms(1) = “ABC1234”

Call res.ExecProcedure(cmdLine,DB_PARAM_ARRAY,parms)

sql = “Drop Procedure Pgmmec074.Stan2”

qry.SQL=sql

res.Execute

I hope this helps someone out there.

3

Subject: Execute a store procedure in AS400

We’ve actually had this problem… it turned out to be our password written i lower case in the code, and should be converted to UPPER case…

The code had been running fine before - but somehow it stopped working.

If this doesn’t solve your problem - i have no clue what could be wrong.

/Brian

4

Subject: RE: Execute a store procedure in AS400

Thanks for your reply.

I have other agents which updates and fetches information from AS400 and are working fine. I have those password in lower case.

If you don’t mind will you please send me the sample code of your agent which runs the store proc? Is my approach for running the store proc is correct?

Thanks

MS

5

Subject: RE: Execute a store procedure in AS400

I wouldn’t mind posting the code, but it is “extreemely complex”… that is - a lot of different script libraries are involved, using diffent kinds of classes etc. so it will only be confusing.

We do also have other agents accessing data on the iSeries machine using lower-case passwords… But the problem with calling the stored procedure was solved by specifying the password in upper case.

/Brian