By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,784 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,784 IT Pros & Developers. It's quick & easy.

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

P: 3
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
  1. CREATE PROCEDURE Y
  2.     (IN P1 VARCHAR(10), IN P2 SMALLINT, IN P3 DATE, OUT P4 CHAR)
  3.  
Here's my asp page:

Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
  2. <%
  3. 'Establish the connection
  4. 'strConn = "DSN=mgs;UID=db2admin;PASSWORD=xxx"
  5. strConn = "Provider=IBMDADB2.DB2COPY1; DSN=mgs; UID=MARIO GONZALEZ; PWD=marito"
  6. Set Conn=Server.CreateObject("ADODB.Connection")
  7. Conn.Open strConn
  8.  
  9. 'Define the command object
  10. Set Cmd=Server.CreateObject("ADODB.Command")
  11. Cmd.ActiveConnection = Conn
  12. Cmd.CommandText = "CALL Y"
  13. Cmd.CommandType = 1
  14. Cmd.Parameters.Refresh
  15. Cmd.Properties.Refresh
  16.  
  17. 'Create and append the parameters
  18. Cmd.Parameters.Append Cmd.CreateParameter("P1",200,1,10)
  19. Cmd.Parameters.Append Cmd.CreateParameter("P2",2,1,0)
  20. Cmd.Parameters.Append Cmd.CreateParameter("P3",133,1,0)
  21.  
  22. 'Set the parameter values
  23. Cmd("P1")="A"
  24. Cmd("P2")=1
  25. Cmd("P3")="01/01/2005"
  26.  
  27. Cmd.Parameters.Append Cmd.CreateParameter("P4",200,2,1)
  28.  
  29. 'Execute the "CALL" statement for the procedure. ADO 'constructs the CALL statement
  30. set rs1=Cmd.Execute
  31.  
  32. 'Free the recordset
  33. set rs1=nothing
  34.  
  35. 'Display the results
  36. response.write "<HTML>"
  37. response.write cmd(1)
  38. response.write "<BR>"
  39. response.write cmd(2)
  40. response.write "</HTML>"
  41.  
  42. 'Close the connection
  43. Conn.Close
  44. set Conn=nothing
  45. %>
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
Jun 17 '09 #1
Share this Question
Share on Google+
3 Replies


jhardman
Expert 2.5K+
P: 3,405
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
Jun 17 '09 #2

P: 3
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
Jun 18 '09 #3

jhardman
Expert 2.5K+
P: 3,405
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
Jun 19 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.