Results 1 to 9 of 9

Thread: Get query error

  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Red face Unanswered: Get query error

    Hi.

    I wonder if anybody knows if is any variable in db2 where catche the query that is excecuting, I mean for exemple:
    If I execute this:

    SELEC * FROM schema.table;

    In data studio in SQL Reults send me this:

    selec * from schema.table
    An unexpected token "selec" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.11.69


    The word "selec" has to come from any part and I want to get it just like I can get SQLCODE. I've looked for it en SQLCA but I haven't foun it. Please help.

    Regards.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ricci View Post
    I've looked for it en SQLCA but I haven't foun it.
    Look again. You need sqlerrmc.

  3. #3
    Join Date
    Sep 2011
    Posts
    57

    Thanks

    Thanks a lot for for your help, but i wonder if I can get here the error and insert into 'x' table, i'm working on CML. According what I see it's for java, isn't it?.

    Regards.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I understand some of the words, but not the entire sentence. Could you please rephrase?

  5. #5
    Join Date
    Sep 2011
    Posts
    57
    I've tried to use it, I mean I wanna catch error when ocurrs. till this momment I cath the SQLCODE somethig like this:

    BEGIN ATOMIC
    DECLARE UNDO HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    set v_ErrorCode = SQLCODE;

    set v_TransactionDate = CURRENT TIMESTAMP;

    INSERT INTO NOM_ESQ.BOOKS(ID_BOOK, BOOK_TITLE, BOOK_PAGES) VALUES (p_IdBook, p_BookTitle, p_BookPages);

    INSERT INT NOM_ESQ.BOOKS(BOOK_TITLE, BOOK_PAGES) VALUES ('LA CONSPIRACION', 588);

    set v_ErrorCode = SQLCODE;
    END;

    When I execute this I get an error from the sentence INT instead or INTO, till this moment I catch the SQLCODE of the and insert into a bitacora table. But I gotta catch also the sentence which makes the error.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
           BEGIN
              GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
              SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
                  FROM SYSIBM.SYSDUMMY1;
           END;
    ANdy

  7. #7
    Join Date
    Sep 2011
    Posts
    57

    Question

    First of all, thank for your support but I still got a troble. I've tried to use it in this way, but doesn't insert the description of the error, maybe I'm not settingo something, this is the code.

    Code:
    BEGIN ATOMIC		
         DECLARE UNDO HANDLER FOR SQLEXCEPTION
         set v_codError = SQLCODE;
         set v_fechaProceso = CURRENT TIMESTAMP;
         GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
    END;	
    
    -- Sentences to execute
        INSERT INTO NOM_ESQ.BOOKS(ID_BOOK, BOOK_TITLE, BOOK_PAGES) VALUES (p_IdBook, p_BookTitle, p_BookPages);
    
    INSERT INT NOM_ESQ.BOOKS(BOOK_TITLE, BOOK_PAGES) VALUES ('LA CONSPIRACION', 588);
    -- End sentences
    		
    B:			
         Insert into NOM_ESQ.EXCEPCION_FELIZ (fecha_excepcion, codigo_error,   mensaje_error, sentencia)
         values (v_fechaProceso, v_codError, SYSPROC.SQLERRM(v_codError), MESSAGE_TEXT_OUT);
    
    END

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you read the documentation on GET DIAGNOSTICS, it states it gets the info from the previous SQL statement. For this reason it has to be the first statement in the handler.

    Andy

  9. #9
    Join Date
    Sep 2011
    Posts
    57
    You´re rigth actually the issue was that I make the set of v_coderror, finally I get what I want.

    Thanks a lot for your help.
    Regards.

Posting Permissions

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