Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unanswered: DB2 EXECUTE Prepare Statement

    Hello,

    I was trying to create table, insert a value into the table and run a select count to get the value and put it into a number.
    Please find below here is the sql statemet. I have change the statement deliminator to @.

    CREATE TABLE test(
    test varchar2(10)
    )@

    INSERT INTO test values ('1')@

    BEGIN
    DECLARE tempvar number;
    DECLARE stmt STATEMENT;
    DECLARE text VARCHAR(100);

    SET stmt = 'SELECT count(*) FROM test';

    PREPARE stmt FROM text;
    EXECUTE stmt INTO tempvar;

    END@


    When I run the above SQL statement, it give me an error as below here:


    An unexpected token "<variable declaration>" was found following "". Expected tokens may include: "<SQL statement>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

    Thank you and appreciated in advance for any help!

  2. #2
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    I use another way to code it.

    CREATE TABLE test(
    test varchar2(10)
    )@

    INSERT INTO test values ('1')@

    BEGIN
    DECLARE tempvar number;

    SELECT count(*) INTO tempvar FROM test';

    END@

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    if you declare a STATEMENT variable, it must follow all other variable declarations. Note, that you don't have to declare STATEMENT variables at all. So, you can comment out the line with DECLARE STATEMENT.
    Moreover, SELECT INTO statement can't be dynamically prepared. You can use SET statement instead as below.

    Code:
    BEGIN
      DECLARE tempvar number;
      DECLARE text VARCHAR(100);
      DECLARE stmt STATEMENT;
    
      SET text = 'set ?=(SELECT count(*) FROM test)';
    
      PREPARE stmt FROM text;
      EXECUTE stmt INTO tempvar;
    
    END@
    Regards,
    Mark.

  4. #4
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thanks and appreciated Mark, it is working fine with your guide.

Posting Permissions

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