ASP and DB2 Stored Procedures

[cruza]

I am having problems returning values from a db2 stored procedure to my ASP page. Unfortunately, I am stuck using the Microsoft ODBC driver and DB2 stored procedure. I know there are many limitations when combining these, but from what I have read, I should be able to set an output parameter in my stored procedure and access it in my ASP page.

Here is the code for the stored procedure:

CREATE PROCEDURE CIS.CISEU004
(OUT ROW_COUNT INT)
FENCED
RESULT SET 1
LANGUAGE SQL
EXTERNAL NAME CISEU004
NOT DETERMINISTIC
NO DBINFO
COLLID CISSP
WLM ENVIRONMENT DSNDWMD0
ASUTIME LIMIT 52700
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
MODIFIES SQL DATA
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.29.136.146:*)'
------------------------------------------------------------------------
-- SQL STORED PROCEDURE ------------------------------------------------------------------------
P1: BEGIN
DECLARE ROWCOUNT INT;
SELECT COUNT(*) INTO ROWCOUNT FROM SYSIBM.SYSTABLES;
SET ROW_COUNT = ROWCOUNT;
END P1

My command object in my ASP page is set up as folows:
set cmd = (our command object)
with cmd
.NamedParameters = true
.CommandType = adCmdText
.CommandText = "CALL CIS.CISEU004 (?)"
end with
cmd.Parameters.Append cmd.CreateParameter("RC", adInteger, adParamOutput)

Then I execute the stored procedure:

cmd.Execute

We get an "Unspecified Error" at this point at the execute line.

Any helpful knowledge on this subject would be appreciated.

[Webforumz Staff]

I would try on of the two following changes:

1) change to .CommandType = adCmdStoredProc and .CommandText = "CISEU004"

or

2) change to .CommandText = "{?=CALL CIS.CISEU004 (?)}"

Also before you append the parameter I would put:
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
adParamReturnValue)
and change adParamOutput to adParamInput on the append you've got.
Then you can get the return value of the query by using cmd("RetVal") after it's executed.

I assume you also have a line like:
cmd.Parameters("RC") = somenumber
to set the value of the parameter before executing the query.

[cruza]

Thanks for the reply.

When I frist set this up, I did as you sugggested in 1). I get a "catastrophic error" in this case. Microsoft has documentation on the .commandtype stating that you cannot use adCmdStoredProc and must use adCmdText.

I was told when I was setting this up that I had to set the parameters the way I stated instead of as you suggest, but I will gladly try what you suggest and see what happens.

I haven't seen the syntax you show in 2) "{?=CALL CIS.CISEU004 (?)}".
What are the "?" for?

Thanks for your help

[Webforumz Staff]

The first ?= is optional i think and i believe it represents the return value.

The second represents the passed parameter. The syntax you used is fine I think, other than missing the { }.

There are a few other ways to call a stored procedure from ASP, so if this method still causes you problems I can point you to one of them.

[cruza]

Still having trouble, but I made a bit of progress if that's what you want to call it. According to everything I've found out up to this point, the problem lies with using the Microsoft ODBC driver with DB2 stored procedures. There are a lot of limitations.

To get the Stored Procedure to run without the "Unspecified Error", I created a useless select statement in the Sp. In my ASP, instead of just issuing a cmd.Execute, I did the following:

Set myRS = cmd.Execute

This returned a record set that I was able to get values from, but I also set the ROW_COUNT as before, and still can not get a value from it.

The new Stored Proc is as follows:

CREATE PROCEDURE CIS.CISEU004
(OUT ROW_COUNT INT)
FENCED
RESULT SET 1
LANGUAGE SQL
EXTERNAL NAME CISEU004
NOT DETERMINISTIC
NO DBINFO
COLLID CISSP
WLM ENVIRONMENT DSNDWMD0
ASUTIME LIMIT 52700
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
MODIFIES SQL DATA
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.29.136.146:*)'
------------------------------------------------------------------------
-- SQL STORED PROCEDURE ------------------------------------------------------------------------
P1: BEGIN
DECLARE ROWCOUNT INT;
DECLARE C1 CURSOR WITH RETURN FOR
SELECT NAME
FROM SYSIBM.SYSTABLES;
OPEN C1;
SELECT COUNT(*) INTO ROWCOUNT FROM SYSIBM.SYSTABLES;
SET ROW_COUNT = ROWCOUNT;
END P1