Struggling with db2 and my asp application -- Calling a stored procedure

mgsmario

Hi Guys

I have an ASP application that connects to an Oracle database, right now I'm trying to connect this same ASP application to a DB2 database (Express-C, version 9.5.0).

I'm in the process of trying to translate all the programing withing asp pages, so, can interact smoothly with db2.

Now, I have problems trying to use a stored procedure (db2) and call it from my asp application.

Here's part of my procedure:

Expand|Select|Wrap|Line Numbers

 
CREATE PROCEDURE Y

    (IN P1 VARCHAR(10), IN P2 SMALLINT, IN P3 DATE, OUT P4 CHAR)

Here's my asp page:

Expand|Select|Wrap|Line Numbers

 <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%

'Establish the connection

'strConn = "DSN=mgs;UID=db2admin;PASSWORD=xxx"

strConn = "Provider=IBMDADB2.DB2COPY1; DSN=mgs; UID=MARIO GONZALEZ; PWD=marito"

Set Conn=Server.CreateObject("ADODB.Connection")

Conn.Open strConn
 
'Define the command object

Set Cmd=Server.CreateObject("ADODB.Command")

Cmd.ActiveConnection = Conn

Cmd.CommandText = "CALL Y"

Cmd.CommandType = 1

Cmd.Parameters.Refresh

Cmd.Properties.Refresh
 
'Create and append the parameters

Cmd.Parameters.Append Cmd.CreateParameter("P1",200,1,10)

Cmd.Parameters.Append Cmd.CreateParameter("P2",2,1,0)

Cmd.Parameters.Append Cmd.CreateParameter("P3",133,1,0)
 
'Set the parameter values

Cmd("P1")="A"

Cmd("P2")=1

Cmd("P3")="01/01/2005"
 
Cmd.Parameters.Append Cmd.CreateParameter("P4",200,2,1)
 
'Execute the "CALL" statement for the procedure. ADO 'constructs the CALL statement

set rs1=Cmd.Execute
 
'Free the recordset

set rs1=nothing
 
'Display the results

response.write "<HTML>"

response.write cmd(1)

response.write "<BR>"

response.write cmd(2)

response.write "</HTML>"
 
'Close the connection

Conn.Close

set Conn=nothing

%>

And here's the error on IE:

Error Type:
IBM OLE DB Provider for DB2 (0x80004005)
[DB2/NT] SQL0440N No authorized routine named "Y" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884
/db2/test3.asp, line 30 (set rs1=Cmd.Execute)

Any thoughts?

Thanks

jhardman

Welcome to Bytes.

I've never used db2, but your code looks good to me. Here are some basic troubleshooting questions I would try to answer if I was in your place:
Can you execute the stored proc from a different location, like a DB manager or query editor? do you get the expected results? Can you execute a static query ("SELECT max(lastUpdate) from tbl_currentOrders") using this connection that definitely goes to the right db?

If you answer yes to all of these, then you are probably listing the data types wrong. If you pull up a record set with the data fields you are interested in, you can list their data types as rs1("p1").dataType (I think that's right, but I'm typing from memory).

Let me know if this helps.

Jared

mgsmario

Hi Jared, thanks for replying.

I answered your questions below:

1) Can you execute the stored proc from a different location, like a DB manager or query editor? do you get the expected results?

A> Yes it runs smothly on Command Line Processor

2. Can you execute a static query ("SELECT max(lastUpdate) from tbl_currentOrders") using this connection that definitely goes to the right db?

A> Yes and also I can display the results on my asp page.

3. Datatypes

A> I tried with this property: Recordset1.Fields.Item("p1").Type, and these were the results:

PARAMETER NAME DATA-TYPE DIRECTION DATA-SIZE
P1 200 1 10
P2 2 1 0
P3 133 1 0
P4 200 2 1

Any thoyghts??

Mgsmario

jhardman

I had never used parameters.refresh, but I just looked it up. Check out this article, if you use the parameters.refresh, you don't need to use parameters.add. I think you have twice as many parameters as you need.

Let me know if this helps.

Jared

Struggling with db2 and my asp application -- Calling a stored procedure

re: Struggling with db2 and my asp application -- Calling a stored procedure

re: Struggling with db2 and my asp application -- Calling a stored procedure

re: Struggling with db2 and my asp application -- Calling a stored procedure

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about