Struggling with db2 (Express C/9.0.5) and ASP application

mgsmario · #1 (**permalink**) 06-17-09, 00:43

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:

<%@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

Marcus_A · #2 (**permalink**) 06-17-09, 02:24

First, you should probably upgrade to 9.5.2 since you are on Fixpack 0. You only have a few more days to download the code, because it will replaced with 9.7.0 very soon. IBM does not post old versions of DB2 Express-C. GA releases (fixpack 0) tend to have a lot of bugs.

Second, I would test the SP from the Command Line processor:

db2 call XXX.Y ('P4',200,'2009-06-17',?) where xxx is the schema name

I have had problems using the current schema calling SP's (for some reason I never resolved), so note that I have fully qualified the procedure with the schema.

Notice also that I used the correct input format of a DB2 DATE column. I can't tell if you inputs are correct because I don't know ASP. The ? is used for the OUT parm.

sathyaram_s · #3 (**permalink**) 06-17-09, 04:24

Quote:

I have had problems using the current schema calling SP's (for some reason I never resolved), so note that I have fully qualified the procedure with the schema.

Use the fully qualified procedure name

or

use the CURRENT FUNCTION PATH

In other words, for routines (UDFs and stored procs), DB2 uses CURRENT FUNCTION PATH to resolve the name , similar to CURRENT SCHEMA for table(like) objects ...

HTH

Sathyaram

mgsmario · #4 (**permalink**) 06-17-09, 12:46

Hi Guys,

Thanks Marcus & Sathyaram

I did the upgrade and still have problems. Now, I have 9.5.2 version
I ran the proc in the CLP and works fine, it returns me the output value
I used owner.procedure to call my procedure and still have the same problem.

Thoughts?

sathyaram_s · #5 (**permalink**) 06-17-09, 13:03

Try P3 as

Code:

Cmd("P3")="2005-01-01"

or

Code:

Cmd("P3")="DATE('2005-01-01')"

mgsmario · #6 (**permalink**) 06-18-09, 03:44

Hi Sathyaram

Here are the results:

With Cmd("P3")="2005-01-01" same results

With Cmd("P3")="DATE('2005-01-01')" I've got the following error:

Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/db2/test31.asp, line 25 ----(Cmd("P3")="DATE('2005-01-01')"

More information about my environment:

IIS 5.1
Database server = DB2/NT 9.5.2
IBM ODBC Driver: 9.05.200.315
IDE: Macromedia Dreamweaver 8
OS: Windows XP Pro SP3

Thoughts??, this error is giving me a hard time and I cannot move forward with the rest of the migration!!!

sathyaram_s · #7 (**permalink**) 06-18-09, 04:19

My feeling is that for the third input parameter, DB2 doesn't recognize that the input given is a DATE.

So address that issue and I'm sure DB2 will be able to call the SP

HTH

Sathyaram

mgsmario · #8 (**permalink**) 06-18-09, 04:49

Mmmm, actually I try with the same procedure but eliminating the P3 parameter and still have the same error.

Thoughts?

tonkuma · #9 (**permalink**) 06-18-09, 07:05

"OUT P4 CHAR" is a fixed length character.
Please enshure the parameter corresponds to P4 is not a variable length character field.
Or change to "OUT P4 VARCHAR", if this was an issue.

Internet Services

Web Development

Digital Marketing

Consumer Tech