Get query error

ricci · #1 (**permalink**) 09-27-11, 12:54

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.

n_i · #2 (**permalink**) 09-27-11, 13:45

Quote:

Originally Posted by ricci

I've looked for it en SQLCA but I haven't foun it.

Look again. You need sqlerrmc.

ricci · #3 (**permalink**) 09-27-11, 18:21

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.

n_i · #4 (**permalink**) 09-27-11, 22:24

I understand some of the words, but not the entire sentence. Could you please rephrase?

ricci · #5 (**permalink**) 09-28-11, 11:20

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.

ARWinner · #6 (**permalink**) 09-28-11, 13:13

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

ricci · #7 (**permalink**) 09-29-11, 11:28

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

ARWinner · #8 (**permalink**) 09-29-11, 11:32

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

ricci · #9 (**permalink**) 09-29-11, 13:14

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech