Collecting error fields during insert

anirbanpal · #1 (**permalink**) 12-01-09, 05:14

Hi,

I am using a db2 " insert into ... values () " command within a script and is getting data compability mismatch due to some decimal values being trying to be inserted into INT fields. But the message that I am seeing is not explicitly specifying the field(s) which are encountering this issue. C
Can someone suggest me how to get the field names in this situation?

Thanks,
Anirban.

sathyaram_s · #2 (**permalink**) 12-01-09, 05:28

what do you get??

i get

Quote:

C:\>db2 "create table t1(i int,j int) "
DB20000I The SQL command completed successfully.

C:\>db2 "insert into t1 values(1,'1')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "J". SQLSTATE=42821

anirbanpal · #3 (**permalink**) 12-01-09, 06:50

I am sorry, this error was reported in another project and they were discussing this with me. They are basically executing a stored proc and within that they are storing certain values in variables. Then they are issuing the the insert statement within the proc with values of these variables. Now it just gives them the error code. I am getting the code and will post in here.

I want to know is there any special way to trap errors within stored procs ?

Thanks
Anirban.

stolze · #4 (**permalink**) 12-01-09, 07:20

You can use exception handlers to react on errors in stored procs. Or you don't do anything special, then an exception in a stored proc results in termination of the procedure and the error of the nested statement is reported to the caller.

sathyaram_s · #5 (**permalink**) 12-01-09, 08:55

Code:

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
I                              SYSIBM    INTEGER                   4     0 Yes
J                              SYSIBM    INTEGER                   4     0 Yes

Code:

create procedure p2(out txt1 char(254))
language sql
begin p1:

declare s1 char(254) ;
declare s2 char(254);
declare exit handler for sqlexception
begin
get diagnostics exception 1 txt1=  message_text ;
--set txt1='dd' ;
end ;

set s1='insert into  t1 values(1,''d'')' ;
execute immediate s1  ;
set txt1='10' ;


end @

Code:

  Value of output parameters
  --------------------------
  Parameter Name  : TXT1
  Parameter Value : SQL0408N  A value is not compatible with the data type of its assignment target.  Target name is "J".  SQLSTATE=42821                                                                                                                 

  Return Status = 0

or if you want just the column name, you can use DB2_TOKEN_STRING instead of MESSAGE_TEXT.

Also, you can write different exit handlers for different exceptions. Check the manual for more info

Internet Services

Web Development

Digital Marketing

Consumer Tech