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
3  3722 
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dw |
last post by:
Hello all, I'm having a dickens of a time calling a stored procedure on a
connection. Every time I do, it generates an error "Arguments are of the
wrong type, are out of acceptable range, or are in...
|
by: Paul |
last post by:
Hi,
In SQL Books Online in the section on @@Error it gives the following
example:
-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city,...
|
by: Lauren Quantrell |
last post by:
I'm using an Access2K front end on a SQL Server2K backend.
I use Scope_Identity() in a lot of stored procedures to pass the
newwly inserted record's unique ID to the next select statement in the...
|
by: Anthony Robinson |
last post by:
I'm executing a stored procedure and getting the
SQL0440N No authorized routine named
"AMROBI2.CREATEAIMCONNECTION" of type "PROCEDURE
" having compatible arguments was found.
The schema...
|
by: Saso Zagoranski |
last post by:
hi,
this is not actually a C# problem but since this is the only newsgroup I
follow I decided
to post my question here (please tell me where to post this next time if you
think this post...
|
by: Woody Splawn |
last post by:
I am using SQL Server 2000 as the back-end. I have created a stored
procedure in SQL server called usp_AddContract. This Stored procedure
inserts a new contract into a contracts table. I have...
|
by: singlal |
last post by:
Hi, my question was not getting any attention because it moved to 2nd
page; so posting it again. Sorry for any inconvenience but I need to
get it resolved fast. Need your help!
...
|
by: Soniad |
last post by:
Hello,
I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable...
|
by: mgsmario |
last post by:
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
