Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unhappy Unanswered: Help With Stored Procedure Error! (SOLVED)

    Hi!

    Could someone please help me with this Stored Procedure?

    I used to work with SQL Server and used procedures all the time, but i'm having a really hard time with DB2 Procedures now...

    On this one, wich is the first i'm trying to make, the select statement is working fine on it's one, but when i try to create the procedure I get the error "NEWAGEIN.MONTA_MENU: 13: Um token inesperado "AS" foi localizado após ".ID_PAI_ENTRADA,'0')". Os tokens esperados podem incluir: "INTO".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.63.108"

    Wich in english would be "A unexpected token "AS" has been found after "ID_PAI_ENTRADA,'0')". The expected tokens may include: "INTO".."
    Please help, I'm stucked with it!

    Here's the procedure:

    Code:
    CREATE PROCEDURE MONTA_MENU (
    	IN @COD_USUARIO_P BIGINT
    )
    	DYNAMIC RESULT SETS 1
    MONTA_MENU: BEGIN
    
    	DECLARE @COD_USUARIO BIGINT;
    	SET @COD_USUARIO = @COD_USUARIO_P;
    	
    	SELECT DISTINCT MENU.DESC_ENTRADA ENTRADA,
    	                MENU.ENDERECO_ENTRADA ENDERECO,
    	                MENU.ORDEM_ENTRADA ORDEM,
    	                COALESCE(MENU.ID_PAI_ENTRADA,'0') AS MENU_PAI
    	
    	FROM    ENTRADA_MENU MENU
    	        INNER JOIN ENTRADA_PERFIL ENTR_PERF ON MENU.COD_ENTRADA = ENTR_PERF.COD_ENTRADA
    	        INNER JOIN PERFIL ON ENTR_PERF.COD_PERFIL = PERFIL.COD_PERFIL
    	        INNER JOIN USUARIO_PERFIL USER_PERF ON ENTR_PERF.COD_PERFIL = USER_PERF.COD_PERFIL
    	
    	WHERE   USER_PERF.COD_USUARIO = @COD_USUARIO
    	
    	ORDER BY  MENU_PAI, MENU.ORDEM_ENTRADA;
    	
    END MONTA_MENU
    (Sorry for any misspells...)
    Last edited by beandrielli; 09-27-12 at 12:24. Reason: Problem Solved

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    In DB2, you use CURSOR to return a result set from a stored procedure. Declare a cursor for the SELECT statement, then OPEN it at the end.

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  3. #3
    Join Date
    Sep 2012
    Posts
    4

    Thank You!

    I didn't know the cursor was mandatory in the procedures in DB2...
    But now i'm getting a new error!

    when it executes the procedure, it references the database before the tables, and I get a error...
    But the tables exist =/

    In the normal select, when I reference the database before the table it returns a error too, but without it it works fine!
    Am i doing something wrong? How do I specify the tables in the procedure?

    Here's how the procedure is now:

    Code:
    CREATE PROCEDURE MONTA_MENU_INTRA (
    	IN @COD_USUARIO_P BIGINT
    )
    	DYNAMIC RESULT SETS 1
    P1: BEGIN
    	-- Declare cursor
    	DECLARE @COD_USUARIO BIGINT;
    	SET @COD_USUARIO = @COD_USUARIO_P;
    	DECLARE cursor1 CURSOR WITH RETURN for
    	-- 
    	SELECT DISTINCT MENU.DESC_ENTRADA ENTRADA,
    	                MENU.ENDERECO_ENTRADA ENDERECO,
    	                MENU.ORDEM_ENTRADA ORDEM,
    	                COALESCE(MENU.ID_PAI_ENTRADA,'0') MENU_PAI
    	
    	FROM    ENTRADA_MENU MENU
    	        INNER JOIN ENTRADA_PERFIL ENTR_PERF ON MENU.COD_ENTRADA = ENTR_PERF.COD_ENTRADA
    	        INNER JOIN PERFIL ON ENTR_PERF.COD_PERFIL = PERFIL.COD_PERFIL
    	        INNER JOIN USUARIO_PERFIL USER_PERF ON ENTR_PERF.COD_PERFIL = USER_PERF.COD_PERFIL
    	
    	WHERE   USER_PERF.COD_USUARIO = @COD_USUARIO
    	
    	ORDER BY  MENU_PAI, MENU.ORDEM_ENTRADA;
    
    	-- Cursor left open for client application
    	OPEN cursor1;
    END P1

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please review the syntax for CREATE PROCEDURE:

    Code:
    CREATE PROCEDURE MONTA_MENU_INTRA (
    	IN @COD_USUARIO_P BIGINT
    )
    	DYNAMIC RESULT SETS 1
    P1: BEGIN
    	-- Declare cursor
    	DECLARE @COD_USUARIO BIGINT;
    	DECLARE cursor1 CURSOR WITH RETURN for
    	-- 
    	SELECT DISTINCT MENU.DESC_ENTRADA ENTRADA,
    	                MENU.ENDERECO_ENTRADA ENDERECO,
    	                MENU.ORDEM_ENTRADA ORDEM,
    	                COALESCE(MENU.ID_PAI_ENTRADA,'0') MENU_PAI
    	
    	FROM    ENTRADA_MENU MENU
    	        INNER JOIN ENTRADA_PERFIL ENTR_PERF ON MENU.COD_ENTRADA = ENTR_PERF.COD_ENTRADA
    	        INNER JOIN PERFIL ON ENTR_PERF.COD_PERFIL = PERFIL.COD_PERFIL
    	        INNER JOIN USUARIO_PERFIL USER_PERF ON ENTR_PERF.COD_PERFIL = USER_PERF.COD_PERFIL
    	
    	WHERE   USER_PERF.COD_USUARIO = @COD_USUARIO
    	
    	ORDER BY  MENU_PAI, MENU.ORDEM_ENTRADA;
    
    	SET @COD_USUARIO = @COD_USUARIO_P;
    
    	-- Cursor left open for client application
    	OPEN cursor1;
    END P1
    Andy

  5. #5
    Join Date
    Sep 2012
    Posts
    4
    I changed to the way you showed me...
    But I still get the same error about the tables...
    Even if I don't put the database name in the procedure, it returns me the exception:
    "NEWAGEIN.ENTRADA_MENU" is a undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.63.108
    My Database name is "NEWAGEIN", and the tables exists...
    I i put the tables names without the database name in the script windows, the select works... With the database name it doesn't... But the IBM Data Studio puts the name by itself when I try to implement the procedure... =/

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to fully qualify the table name in the stored procedure. Please place the correct schema name for each table in the procedure. If you omit the correct schema name, DB2 will supply one for you, and if that table does not exist, you get the error you are getting.

    Andy

  7. #7
    Join Date
    Sep 2012
    Posts
    4

    Talking Thank you!

    Andy, Thank you very much for the help!
    Now I understand procedures better (And feel a little dumb xD)
    The error about the tables was that my schema default had a .(dot) in the name!
    I created a new one and now everything works fine!

    So, thank you =)

Tags for this Thread

Posting Permissions

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