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

Struggling with db2 and ASP app -- 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:

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
  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 for a faster answer!
Share on Google+

Post your reply

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