Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Collecting error fields during insert

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what do you get??

    i get
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 12-01-09 at 09:57. Reason: additional info added
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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