CODEWITHSUNDEEP
javajdbcdb2zos
Bassant
Bassant

Reputation: 21

Java Exception doesn't have the same exception message returned from db2 console?

I want to insert multiple rows in a table from a console/tool (e.g.: Data studio) I get the following error message

THE INSERT OR UPDATE VALUE OF FOREIGN KEY FK$MAR$S IS INVALID. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.13.111

This means I have some trouble with a FOREIGN KEY variable, but I solved that later and it works well.

My problem is that when I'm running the same query from a Java application using PreparedStatement.executeBatch() (batch because it could insert more than one row at a time), I get a different error message:

com.ibm.db2.jcc.am.wn: [jcc][t4][102][10040][3.57.82] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4228, SQLSTATE=null

When I used getNextException(), I get the following:

com.ibm.db2.jcc.am.co: A NON-ATOMIC INSERT STATEMENT ATTEMPTED TO PROCESS MULTIPLE ROWS OF DATA, BUT ERRORS OCCURRED

And the error code is -4228.

Why this difference? I want the java application return the same error details as the console tool, so I can handle those exceptions in my java code.

For example, if the returned error code=-803 which means duplicate exception, I would handle my code to make update instead of insert, or if the returned message contains some words like " FOREIGN KEY ", I'll tell user to make sure about lookup tables and so on

I use DB2 version 10.5.3 on z/OS and the DB2 driver version is : 3.65.92

Upvotes: 2

Views: 3058

Answers (3)

Clover
Clover

Reputation: 559

Though this is an old thread. I will share the code which worked for me

 try{
preparedStatement.batchUpdate( new ClassName{
//code with setting values and batch size});
}catch (Exception e) {
            if (e.getCause() instanceof BatchUpdateException) {
                BatchUpdateException be = (BatchUpdateException) e.getCause();
                SQLException current = be.getNextException();
                do {
                    current.printStackTrace();
                } while ((current = current.getNextException()) != null);
            }
        }

In here I'm trying to get the exception based on BatchUpdateException instance.

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

    } catch (SQLException ex) {
        while (ex != null) {
            if (ex instanceof com.ibm.db2.jcc.DB2Diagnosable) {
                com.ibm.db2.jcc.DB2Diagnosable db2ex = (com.ibm.db2.jcc.DB2Diagnosable) ex;
                com.ibm.db2.jcc.DB2Sqlca sqlca = db2ex.getSqlca();
                if (sqlca != null) {
                  System.out.println("SQLCODE: " + sqlca.getSqlCode());
                  System.out.println("MESSAGE: " + sqlca.getMessage());
                } else {
                  System.out.println("Error code: " + ex.getErrorCode());
                  System.out.println("Error msg : " + ex.getMessage());
                }
            } else {
              System.out.println("Error code (non-db2): " + ex.getErrorCode());
              System.out.println("Error msg  (non-db2): " + ex.getMessage());
            }
            ex = ex.getNextException();
        }
        ...
    }

Above is an example of handling db2 exceptions. The example of output when there are 2 violations simultaneously: unique key on the table MYSCHEMA.MYTABLE where batch inserts come, and a foreign key on a parent table. I split it intentionally into 2 parts:

Before getNextException():

Error code: -4229

Error msg : [jcc][t4][102][10040][4.19.66] ... getNextException(). ERRORCODE=-4229, SQLSTATE=null

After getNextException():

SQLCODE: -803

MESSAGE: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MYSCHEMA.MYTABLE" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=4.19.66

SQLCODE: -530

MESSAGE: The insert or update value of the FOREIGN KEY "MYSCHEMA.MYTABLE.MYTABLE_FK" is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.19.66

Upvotes: 2

mustaccio
mustaccio

Reputation: 18980

I think the batch exception message is pretty clear. Consider that different statements in a batch might fail or issue warnings for different reasons. The batch level error message is therefore generic and instructs you to use "getNextException() to retrieve the exceptions for specific" statements in the batch.

Upvotes: 1

Related Questions