Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Question Unanswered: DB2 Stored Procedure Newbie question

    I am using DB2 Express Edition V9, trying to run a very simple stored procedure as shown below. I am executing it from the Control Center. The Statement Termination Character was ; , even tried without it but still get the same error message. What am I missing here? Thanks in advance.

    create procedure my_proc1 ()
    LANGUAGE SQL
    BEGIN
    INSERT into t1 values (1, 'FIRST');
    END

    I get the following error:

    ------------------------------ Commands Entered ------------------------------
    create procedure my_proc1 ()
    LANGUAGE SQL
    BEGIN
    INSERT into t1 values (1, 'FIRST');
    END;
    ----------------------------------------------------------------
    create procedure my_proc1 ()
    LANGUAGE SQL
    BEGIN
    INSERT into t1 values (1, 'FIRST')
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "values
    (1, 'FIRST')". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=4. SQLSTATE=42601

    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Change your Statement Termination character to something other than the semicolon. Most people use the @.

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    7

    Question

    Thanks Andy. I changed the Statement Termination Character to be '@' and that seems to have solved the problem other than the fact that I don't see the insert happening in the table

    ------------------------------ Commands Entered ------------------------------
    create procedure my_proc1 ()
    LANGUAGE SQL
    BEGIN
    INSERT into T1 values (10, 'ABCDE');
    END@
    ------------------------------------------------------------------------------
    create procedure my_proc1 ()
    LANGUAGE SQL
    BEGIN
    INSERT into T1 values (10, 'ABCDE');
    END
    DB20000I The SQL command completed successfully.

    Now when I do a select * from T1 - it returns nothing

    Is this something to do with the '@' as statement termination character? I tried putting '@' at the end of the insert statement - no luck. Or it is something else like Auto Commit?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    All you did was create the stored procedure. All this does is define the process in the database. If you want the stored procedure to actually be run, you have to CALL it.

    Andy

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    ofcourse! can't believe I asked that dumb question. Thanks Andy.

Posting Permissions

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