Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Angry Unanswered: DB2 dynamic cursor on prepare statement

    Hi there,

    I do have an issue with a cursor with dynamically creating the SQL statement.
    The below works okay.

    CREATE PROCEDURE TEST_SQL
    (IN P_ACCNUM VARCHAR(6),
    IN P_CMPNAME VARCHAR(25),
    IN P_BOID VARCHAR(3))
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN


    -- Declare cursor
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
    SELECT * FROM CLIENT_FINDER.CLI
    WHERE CLIENT_FINDER.CLI.ACCNUM Like P_ACCNUM || '%';

    -- Cursor left open for client application
    OPEN CURSOR1;
    END P1


    The following SP fails with message
    A database manager error occurred.SQLCODE: -401, SQLSTATE: 42818 - The data types of the operands for the operation "=" are not compatible.. SQLCODE=-401, SQLSTATE=42818, DRIVER=3.57.86

    CREATE PROCEDURE BASECLIDATA_SQL ( IN P_ACCNUM VARCHAR(6),
    IN P_ACCOFFICENUM VARCHAR(3),
    IN P_CMPNAME VARCHAR(25) )
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- p_AccNum
    -- p_AccOfficeNum
    -- p_CMPName
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE V_TABLE_COUNT VARCHAR(200);
    DECLARE V_SQL VARCHAR(200);
    DECLARE V_STMT STATEMENT;

    -- Declare cursor
    DECLARE CURSOR1 CURSOR WITH RETURN FOR V_STMT;
    SET V_SQL = 'SELECT * FROM CLIENT_FINDER.CLI
    WHERE CLIENT_FINDER.CLI.ACCNUM = ' || P_ACCNUM;
    PREPARE V_STMT FROM V_SQL;
    OPEN CURSOR1;
    --FETCH CURSOR1 INTO V_TABLE_COUNT;
    -- Cursor left open for client application
    END P1

    Can anyone help?
    Thanks in advance

  2. #2
    Join Date
    Jul 2009
    Posts
    8

    Another try

    SET V_SQL = 'SELECT * FROM CLIENT_FINDER.CLI
    WHERE CLIENT_FINDER.CLI.ACCNUM Like ' || P_ACCNUM;

    I am using the IBM data studio 2.2 FYI

    Error returned:
    A database manager error occurred.SQLCODE: -440, SQLSTATE: 42884 - No authorized routine named "LIKE" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.57.86

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    P_ACCNUM has the VARCHAR datatype. Character literals should be enclosed in single quotes.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This must not be specific to SQL.
    It is programming basics of string handling and construction of executable code dynamicaly.

    Set V_sql = 'select * From Client_finder.cli
    Where Client_finder.cli.accnum = ''' || P_accnum || '''';

  5. #5
    Join Date
    Jul 2009
    Posts
    8

    Accepted

    Thanks guys have figured out now

  6. #6
    Join Date
    Jul 2010
    Posts
    1

    Angry prepare stmt dynamic cursor - like clause 'P%'

    Hi,

    I prepared an sql statement with a like clause. I put the information in a temp table to check the sql.

    My test sql string is

    delete from test1 where test_id in (select test_id from test2 where test_name like 'P%')

    When I send this stmt to prepare and execute to insert in my temp table it work.

    However when I ask to prepare and execute this statement ...I get an SQL0007N error.

    I would like to know the answer ...to your question as you say it is now resolved.

    Any help is well...newbie to DB2.

Posting Permissions

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