Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Red face Unanswered: Struggling with db2 (Express C/9.0.5) and ASP application

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    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
    Last edited by sathyaram_s; 06-17-09 at 05:27.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jun 2009
    Posts
    4

    still struggling

    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?

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Try P3 as
    Code:
    Cmd("P3")="2005-01-01"
    or
    Code:
    Cmd("P3")="DATE('2005-01-01')"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2009
    Posts
    4
    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!!!

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jun 2009
    Posts
    4
    Mmmm, actually I try with the same procedure but eliminating the P3 parameter and still have the same error.

    Thoughts?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •