Professional Web Applications Themes

C stored procedure SQL error. - IBM DB2

I have a very basic C stored procedure written. It was working fine with some bogus/kludge SQL (just to get a DBRM created). I have now added the following to the SP, although it does nothing yet: int getInputCount (char* timeStamp) { /************************ Local Variables */ EXEC SQL BEGIN DECLARE SECTION; long int count = 0; EXEC SQL END DECLARE SECTION; /************************* Function Start */ sprintf (varChar.stmt, "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s", timeStamp); varChar.len = strlen (varChar.stmt); EXEC SQL PREPARE countStmt FROM :varChar; /* Commented out more SQL statements here ... */ return count; } This compiles, ...

  1. #1

    Default C stored procedure SQL error.

    I have a very basic C stored procedure written. It was working fine
    with some bogus/kludge SQL (just to get a DBRM created).

    I have now added the following to the SP, although it does nothing
    yet:

    int getInputCount (char* timeStamp)
    {
    /************************ Local Variables */
    EXEC SQL BEGIN DECLARE SECTION;
    long int count = 0;
    EXEC SQL END DECLARE SECTION;

    /************************* Function Start */
    sprintf (varChar.stmt,
    "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
    timeStamp);
    varChar.len = strlen (varChar.stmt);

    EXEC SQL PREPARE countStmt FROM :varChar;

    /* Commented out more SQL statements here ... */

    return count;
    }


    This compiles, prelinks, link-edits, binds fine (I think). But when
    run thru a driver program I am getting the following SQL error from
    the stored proc. I am capturing data from the sqlca struct
    immediately after the EXEC SQL PREPARE statement.

    I get:
    ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
    SQL CODE = -805 which should say (expanded):
    DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
    PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
    part empty?

    Any ideas on what is wrong? I have the stored proc bound into a DB2
    package, I have the stored proc (loadlib output) bound into a DB2
    plan. I run with a command that looks like:
    RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
    the CVTUV001 DB2 plan.

    What is that PREPARE statement doing to cause this error?
    Craig Guest

  2. #2

    Default Re: C stored procedure SQL error.

    You're probably running with the old versoin of the library
    (cached)...either build the new copy to a differently named lib, and use
    alter procedure to change the external name, or bounce the instance.

    Craig wrote:
    > I have a very basic C stored procedure written. It was working fine
    > with some bogus/kludge SQL (just to get a DBRM created).
    >
    > I have now added the following to the SP, although it does nothing
    > yet:
    >
    > int getInputCount (char* timeStamp)
    > {
    > /************************ Local Variables */
    > EXEC SQL BEGIN DECLARE SECTION;
    > long int count = 0;
    > EXEC SQL END DECLARE SECTION;
    >
    > /************************* Function Start */
    > sprintf (varChar.stmt,
    > "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
    > timeStamp);
    > varChar.len = strlen (varChar.stmt);
    >
    > EXEC SQL PREPARE countStmt FROM :varChar;
    >
    > /* Commented out more SQL statements here ... */
    >
    > return count;
    > }
    >
    >
    > This compiles, prelinks, link-edits, binds fine (I think). But when
    > run thru a driver program I am getting the following SQL error from
    > the stored proc. I am capturing data from the sqlca struct
    > immediately after the EXEC SQL PREPARE statement.
    >
    > I get:
    > ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
    > SQL CODE = -805 which should say (expanded):
    > DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
    > PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
    > part empty?
    >
    > Any ideas on what is wrong? I have the stored proc bound into a DB2
    > package, I have the stored proc (loadlib output) bound into a DB2
    > plan. I run with a command that looks like:
    > RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
    > the CVTUV001 DB2 plan.
    >
    > What is that PREPARE statement doing to cause this error?
    Sean McKeough Guest

  3. #3

    Default Re: C stored procedure SQL error.

    Sean:

    Thank you - bouncing DB2 did indeed work. We now think just executing
    a STOP/START PROCEDURE in the future will work, or in the worst case,
    bouncing the stored procedure address space (SPAS) will alleviate that
    problem.


    Sean McKeough <mckeoughnospam.ca.ibm.com> wrote in message news:<bekf6h$nae$2hanover.torolab.ibm.com>...
    > You're probably running with the old versoin of the library
    > (cached)...either build the new copy to a differently named lib, and use
    > alter procedure to change the external name, or bounce the instance.
    >
    > Craig wrote:
    >
    > > I have a very basic C stored procedure written. It was working fine
    > > with some bogus/kludge SQL (just to get a DBRM created).
    > >
    > > I have now added the following to the SP, although it does nothing
    > > yet:
    > >
    > > int getInputCount (char* timeStamp)
    > > {
    > > /************************ Local Variables */
    > > EXEC SQL BEGIN DECLARE SECTION;
    > > long int count = 0;
    > > EXEC SQL END DECLARE SECTION;
    > >
    > > /************************* Function Start */
    > > sprintf (varChar.stmt,
    > > "SELECT COUNT(*) FROM BCAD.GLTT0 WHERE TT_TS = %s",
    > > timeStamp);
    > > varChar.len = strlen (varChar.stmt);
    > >
    > > EXEC SQL PREPARE countStmt FROM :varChar;
    > >
    > > /* Commented out more SQL statements here ... */
    > >
    > > return count;
    > > }
    > >
    > >
    > > This compiles, prelinks, link-edits, binds fine (I think). But when
    > > run thru a driver program I am getting the following SQL error from
    > > the stored proc. I am capturing data from the sqlca struct
    > > immediately after the EXEC SQL PREPARE statement.
    > >
    > > I get:
    > > ERR MSG = BCADDB2..CWFUTT01.1736263E13F97AC8 CVTUV001 01
    > > SQL CODE = -805 which should say (expanded):
    > > DBRM OR PACKAGE NAME BCADDB2..CWFUTT01.1736263E13F97AC8 NOT FOUND IN
    > > PLAN CVTUV001. REASON 01. If this is true, why is the collection-id
    > > part empty?
    > >
    > > Any ideas on what is wrong? I have the stored proc bound into a DB2
    > > package, I have the stored proc (loadlib output) bound into a DB2
    > > plan. I run with a command that looks like:
    > > RUN PROG(DRIVERVT) PLAN(CVTUV001) where my stored proc is bound into
    > > the CVTUV001 DB2 plan.
    > >
    > > What is that PREPARE statement doing to cause this error?
    Craig Guest

Similar Threads

  1. Error while executing stored procedure
    By singhpk in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 14th, 04:03 PM
  2. Simple Stored Procedure Error
    By ism in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 29th, 11:16 PM
  3. Stored procedure error
    By HariMaki in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: December 3rd, 12:59 PM
  4. description of error in stored procedure
    By Ola Fjelddahl in forum IBM DB2
    Replies: 2
    Last Post: September 4th, 03:46 PM
  5. Stored Procedure Servers Error
    By Adam Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 02:03 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139