Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: problem creating Stored procedure from command center

    Hi,
    I am trying to create a stored procedure from Command center. But getting the following error :

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

    Explanation:

    A syntax error in the SQL statement was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement that preceded the token
    that is not valid. "

    The procedure I am using is iterate.db located in sqlproc directory under samples folder.

    However the same procedure can be created using the procedure builder tool.

    Looking forward to your suggestions.

    Regards
    Sandip

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Have you tried setting the statement termination character to something other than ';' in tools >> tools settings?

  3. #3
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    I tried with string termination character ;
    It didn't work.
    Then Itried with backslash /, then dot .
    No success

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is it possible to try @ ...

    Cheers

    Sathyaram

    Originally posted by sandips
    I tried with string termination character ;
    It didn't work.
    Then Itried with backslash /, then dot .
    No success

  5. #5
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    The body of the stored proc looks exactly like as follows


    CREATE PROCEDURE iterator()
    LANGUAGE SQL
    BEGIN
    DECLARE SQLSTATE CHAR(5);
    DECLARE v_dept CHAR(3);
    DECLARE v_deptname VARCHAR(29);
    DECLARE v_admdept CHAR(3);
    DECLARE at_end INT DEFAULT 0;

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE c1 CURSOR FOR
    SELECT deptno, deptname, admrdept
    FROM department
    ORDER BY deptno;
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    OPEN c1;
    ins_loop:
    LOOP
    FETCH c1 INTO v_dept, v_deptname, v_admdept;
    IF at_end = 1 THEN
    LEAVE ins_loop;
    ELSEIF v_dept = 'D11' THEN
    ITERATE ins_loop;
    END IF;
    INSERT INTO department (deptno, deptname, admrdept)
    VALUES ('NEW', v_deptname, v_admdept);
    END LOOP;
    CLOSE c1;
    END @


    What's wrong with this sample in Command center? Maybe I am missing some configuration parameters. I set the compiler path properly to vc++ compiler.

    However, it's working perfectly in command window.

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    What error message are you getting? (I assume it isn't the same as before).

  7. #7
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    SQL0104N An unexpected token "END-OF-STATEMENT" was found
    following "END P1 ". Expected tokens may include: "JOIN
    <joined_table> ".

    Explanation:

    A syntax error in the SQL statement was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement that preceded the token
    that is not valid.

    As an aid to the programmer, 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


    Thanks in advance for your suggestions.

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    It doesn't look like the error message you have posted came from the SP you posted. You didn't label your SP BEGIN-END block as P1 in the code you posted.

    It still looks to me as though you haven't set your statement termination character to an '@'.

    Do you want to just try something straightforward, to reduce the likelihood of a simple typo being the cause of your problem.

    e.g.

    create procedure sp_tmp ( in p_in varchar(10), out p_out varchar(10) )
    language sql
    begin
    set p_out = p_in;
    end@

  9. #9
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    Dear Sathyaram,
    wow! after lot of deliberations on this topic, you hinted the clue right.
    Yes, I tried with statement termination character evrything except a @.
    Thanks a ton for highlighting the problem so accurately.

    Warm regards
    Sandip

Posting Permissions

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