Search code examples
javasqldb2-400jt400

PreparedStatement Update with case

import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?, 
                  DATE2 = (CASE WHEN parmDate <> 0 AND DATE2 = 0    
                                  THEN parmDate
                                  ELSE DATE2 END) 
          WHERE ERF = " + String.valueOf(erfNr).trim();
        
db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate); // <== ???

db2Stmt.executeUpdate();

I want update the field date2 in file.

How I have to handle the case structure?

parmDate is a overgiven parameter.

I got Data truncation by db2Stmt.setInt(8, parmDate ); In the Database file the field is decimal 8,0.

Why a truncation?

        String myQuery = "";
    int erfNr, f3BLin, entryBufferId, parmDate ;
    
    erfNr = 11183594;
    f3BLin = 9;
    entryBufferId = 999;
    parmDate  = 19700323;
            
    // Update-Statement
    try {
        
        myQuery = "UPDATE FILE SET t_BCN = ?, t_BLN = ?, t_BZO = ?, t_BID = ?, t_AND = ?, t_ANT = ?, t_AUS = ?, " +
                  "t_DATE2 = (CASE WHEN ? <> 0 AND t_DATE2 = 0 THEN ? ELSE t_DATE2 END)"    
                + " WHERE t_ERF = ? ";

        SimpleDateFormat sdfTime = new SimpleDateFormat("HHmmss");
        SimpleDateFormat sdfDate = new SimpleDateFormat("yyyyMMdd");
        int t_AnD = Integer.valueOf(sdfDate.format(new Date()));
        int t_AnT = Integer.valueOf(sdfTime.format(new Date()));

        db2Stmt = db2Conn.prepareStatement(myQuery);

        db2Stmt.setString(1, String.format("%017d", erfNr));
        db2Stmt.setInt(2, f3BLin);
        db2Stmt.setString(3, " ");
        db2Stmt.setInt(4, entryBufferId);
        db2Stmt.setInt(5, t_AnD);
        db2Stmt.setInt(6, t_AnT);
        db2Stmt.setString(7, as400CurrentUser.trim() + "§");
        db2Stmt.setInt(8, parmDate );  <== 
        db2Stmt.setInt(9, parmDate );
        db2Stmt.setInt(10, erfNr);
        db2Stmt.executeUpdate();
        // int resultCode = db2Stmt.executeUpdate();
    }

Exception

java.sql.DataTruncation: Data truncation
at com.ibm.as400.access.AS400JDBCPreparedStatement.testDataTruncation(AS400JDBCPreparedStatement.java:3265)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3176)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setInt(AS400JDBCPreparedStatement.java:2507)
at com.hji.service.DamageService.TestUpdateFAHRZPP(DamageService.java:3359)
at com.hji.controller.DamageController.checkRestApiTest(DamageController.java:1244)

This part is working:

"t_DATE2 = (CASE WHEN " + String.valueOf(parmDate) + " <> 0 AND t_DATE2 = 0 THEN "+ String.valueOf(parmDate) +" ELSE t_DATE2 END)"
Solution

  • If you really need the condition as it is right now, you have to set the parameter twice:

    import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?,"
    + " DATE2 = (CASE WHEN ? <> 0 AND DATE2 = 0 THEN ? ELSE DATE2 END)"
    + " WHERE ERF = ?";
        
db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate);
db2Stmt.setInt(5, parmDate);
db2Stmt.setInt(6, erfNr);

db2Stmt.executeUpdate();

    If you can reduce your condition to a simple if DATE2 = 0 you can rewrite your code like this:

    import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?, DATE2 = IF(DATE2 = 0, ?, DATE2) WHERE ERF = ?";
        
db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate);
db2Stmt.setInt(5, erfNr);

db2Stmt.executeUpdate();

    Also see: