JDBC Connector Troubleshooting Data Truncation

moss_snake_01 · January 22, 2024, 8:49pm

Hello! I am trying to troubleshoot a JDBC connector that is invoking a single SPROC for both CREATE and MODIFY operations (only two we’re focused on at the moment).

I’ve pasted both the SPROC and the Java for reference.

I keep receiving [“java.sql.DataTruncation: Data truncation”] for the sync attributes operation.

I’ve turned on ccg debugging for the VAs, and noticed there was extra whitespace in the attribute values from the result for the provisioning plan, so I added a trim(); while that trimmed it, I still receive the same [“java.sql.DataTruncation: Data truncation”] error.

I’ve been able to call this procedure through the client with no issues; however when invoked through the provisioning rule, I have the issue.

When I use either a straight SQL DML statement such as update, rather than the sproc, it works as expected.

The only thing I can think of is that create requires 18 parameters, while modify requires 17. However, I am passing a static string to the 18th parameter and the [“java.sql.DataTruncation: Data truncation”] is not what I’d expect if it was simply just a attribute not being declared for the sproc.

Stored Procedure:

CREATE PROCEDURE EX850SP                          
       (IN inp_vcusts varchar(1) DEFAULT NULL,    
        IN inp_vcuetyp varchar(1) DEFAULT NULL,   
        IN inp_vculocn varchar(10) DEFAULT NULL,  
        IN inp_vcuemad varchar(100) DEFAULT NULL, 
        IN inp_vcuempl varchar(3) DEFAULT NULL,   
        IN inp_vcufnam varchar(25) DEFAULT NULL,  
        IN inp_vculnam varchar(25) DEFAULT NULL,      
        IN inp_vcusprv varchar(10) DEFAULT NULL,      
        IN inp_vcunetu varchar(10) DEFAULT NULL,      
        IN inp_vcudpds varchar(30) DEFAULT NULL,      
        IN inp_vcujbds varchar(30) DEFAULT NULL,      
        IN inp_vculead varchar(1) DEFAULT NULL,       
        IN inp_vcuodcd varchar(10) DEFAULT NULL,     
        IN inp_vcuexdt numeric(8, 0),                
        IN inp_vculudt varchar(26) DEFAULT NULL,     
        IN inp_vculuby varchar(10) DEFAULT NULL,     
        IN inp_vcueeid varchar(20) DEFAULT NULL,     
        IN inp_vcurole varchar(20) DEFAULT NULL)     
                                                     
                                                     
LANGUAGE SQL                                         

SET OPTION COMMIT = *CHG                                      
                                                              
BEGIN                                                         
                                                              
   IF EXISTS (SELECT *                                        
            FROM   CUEMSID                                    
            WHERE    CUEEID    =   inp_vcueeid  ) THEN        
                ----Update statement for all input parameters.
                    UPDATE  CUEMSID                           
                     SET                                                                    
                        CUSTS = NVL(UPPER(inp_vcusts), CUSTS),     
                        CUETYP = NVL(UPPER(inp_vcuetyp), CUETYP),  
                        CULOCN = NVL(inp_vculocn, CULOCN),         
                        CUEMAD = NVL(UPPER(inp_vcuemad), CUEMAD),  
                        CUEMPL = NVL(UPPER(inp_vcuempl), CUEMPL),  
                        CUFNAM = NVL(UPPER(inp_vcufnam), CUFNAM),  
                        CULNAM = NVL(UPPER(inp_vculnam), CULNAM),  
                        CUSPRV = NVL(UPPER(inp_vcusprv), CUSPRV),    
                        CUNETU = NVL(UPPER(inp_vcunetu), CUNETU),       
                        CUDPDS = NVL(UPPER(inp_vcudpds), CUDPDS),    
                        CUJBDS = NVL(UPPER(inp_vcujbds), CUJBDS),    
                        CULEAD = NVL(UPPER(inp_vculead), CULEAD),    
                        CUODCD = NVL(inp_vcuodcd, CUODCD),           
                        CUEXDT = inp_vcuexdt,                        
                        CULUDT = NVL(inp_vculudt, CULUDT),      
                        CULUBY = NVL(inp_vculuby, CULUBY),                                     
                        CUEEID = NVL(inp_vcueeid, CUEEID)                                      
                      WHERE CUEEID = inp_vcueeid ;                                              
                                                                                                
             ELSE                                                                               
                     --- If record doesn't exist insert it                                      
                     INSERT INTO                                                                
        CUEMSID (CUSTS, CUETYP, CULOCN, CUEMAD, CUEMPL, CUFNAM, CULNAM,          
         CUSPRV, CUNETU,CUDPDS, CUJBDS, CULEAD, CUODCD, CUEXDT, CULUDT,
         CULUBY, CUEEID, CUROLE)     
        values(                                
         NVL(UPPER(inp_vcusts), CUSTS),            
         NVL(UPPER(inp_vcuetyp), CUETYP),          
         NVL(inp_vculocn, CULOCN),                 
         NVL(UPPER(inp_vcuemad), CUEMAD),          
         NVL(UPPER(inp_vcuempl), CUEMPL),          
         NVL(UPPER(inp_vcufnam), CUFNAM),          
         NVL(UPPER(inp_vculnam), CULNAM),          
         NVL(UPPER(inp_vcusprv), CUSPRV),  
         NVL(UPPER(inp_vcunetu), CUNETU),         
         NVL(UPPER(inp_vcudpds), CUDPDS),  
         NVL(UPPER(inp_vcujbds), CUJBDS),  
         NVL(UPPER(inp_vculead), CULEAD),  
         NVL(inp_vcuodcd, CUODCD),         
         inp_vcuexdt,                      
         NVL(inp_vculudt, CULUDT),    
         NVL(inp_vculuby, CULUBY),    
         NVL(inp_vcueeid, CUEEID),    
         NVL(inp_vcurole, CUROLE));   
END IF;                               
END;

Java:

   import java.sql.Types;
    import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.Date;
    import java.sql.Decimal;

    import sailpoint.object.ProvisioningPlan;
    import sailpoint.object.ProvisioningPlan.AccountRequest;
    import sailpoint.object.ProvisioningPlan.AttributeRequest;
    import sailpoint.object.ProvisioningResult;

    import sailpoint.tools.Util;

    // Get a string value from an attribute request object
    public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
      String val = null;
      if ( acctReq != null ) {
        AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
        if ( attrReq != null && attrReq.getValue() instanceof String ) {
          val = attrReq.getValue();
        }
      }
      log.error(attribute + \" = \" + val);
      return val;
    }

    // Start of iSeries Provision rule
    log.error(\"Enter iSeries JDBC Provisioning\");

    ProvisioningResult result = new ProvisioningResult();
    CallableStatement cStatement = null;
    String roleiSeries = \"ROLEALLUSERS\";

    // Check if plan is null
    if ( plan instanceof ProvisioningPlan ) {
      // Get all account requests from plan
      List accounts = plan.getAccountRequests();
      // If the plan contains one or more account requests, we'll iterate through them
      if ((accounts != null) && (accounts.size() > 0)) {
        for (AccountRequest account : accounts) {
          String nativeId = (String) account.getNativeIdentity();
          log.error(\"native identity: \" + nativeId);
          log.error(\"application: \" + account.getApplicationName());
          log.error(\"operation: \" + account.getOperation());

          try {
            // CREATE procedure
            if ( AccountRequest.Operation.Create == account.getOperation() ) {
              log.error(\"Entering Create Operation\");

              cStatement = connection.prepareCall(\"CALL OBJECT.EX850SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\");

              // Add attributes from account create profile
              log.error(\"setting value for index 1\");
              cStatement.setString(1, getAttributeRequestValue(account, \"CUSTS\")); //employment status code
              log.error(\"setting value for index 2\");
              cStatement.setString(2, getAttributeRequestValue(account, \"CUETYP\")); //employee type code
              log.error(\"setting value for index 3\");
              cStatement.setString(3, getAttributeRequestValue(account, \"CULOCN\")); //location code
              log.error(\"setting value for index 4\");
              cStatement.setString(4, getAttributeRequestValue(account, \"CUEMAD\")); //work email
              log.error(\"setting value for index 5\");
              cStatement.setString(5, getAttributeRequestValue(account, \"CUEMPL\")); //company code
              log.error(\"setting value for index 6\");
              cStatement.setString(6, getAttributeRequestValue(account, \"CUFNAM\")); //legal first name
              log.error(\"setting value for index 7\");
              cStatement.setString(7, getAttributeRequestValue(account, \"CULNAM\")); //last name
              log.error(\"setting value for index 8\");
              cStatement.setString(8, getAttributeRequestValue(account, \"CUSPRV\")); //manager name
              log.error(\"setting value for index 9\");
              cStatement.setString(9, nativeId); //search sam upper (CUNETU)
              log.error(\"setting value for index 10\");
              cStatement.setString(10, getAttributeRequestValue(account, \"CUDPDS\")); //department description
              log.error(\"setting value for index 11\");
              cStatement.setString(11, getAttributeRequestValue(account, \"CUJBDS\")); //job title
              log.error(\"setting value for index 12\");
              cStatement.setString(12, getAttributeRequestValue(account, \"CULEAD\")); //employee management level
              log.error(\"setting value for index 13\");
              cStatement.setString(13, getAttributeRequestValue(account, \"CUODCD\")); //org level 1 code
              log.error(\"setting value for index 14\");
              if ( getAttributeRequestValue(account, \"CUEXDT\") != null ) {
                log.error(\"setting CUEXDT to \" + Util.atoi(getAttributeRequestValue(account, \"CUEXDT\")));
                cStatement.setInt(14, Util.atoi(getAttributeRequestValue(account, \"CUEXDT\"))); //iSeries term date
              } else {
                log.error(\"setting CUEXDT to 0\");
                cStatement.setInt(14, 0);
              }
              log.error(\"setting value for index 15\");
              cStatement.setString(15, getAttributeRequestValue(account, \"CULUDT\")); //iSeries update date
              log.error(\"setting value for index 16\");
              cStatement.setString(16, getAttributeRequestValue(account, \"CULUBY\")); //service user dirxml
              log.error(\"setting value for index 17\");
              cStatement.setString(17, (String) plan.getNativeIdentity()); //employee id
              //Statement.setString(17, getAttributeRequestValue(account, \"CUEEID\")); //employee id
              log.error(\"setting value for index 18\");
              cStatement.setString(18, getAttributeRequestValue(account, \"CUROLE\")); //role
              log.error(\"EX850SP full statement: \" + cStatement.toString());

              cStatement.executeUpdate();

              // Successful Create, so mark result as COMMITTED
              log.error(\"Create Account Execution successfully completed\");
              result.setStatus(ProvisioningResult.STATUS_COMMITTED);

              log.error(\"Exiting Create Operation\");
            } else if ( AccountRequest.Operation.Modify == account.getOperation() ) {
              log.error(\"Entering Modify Operation\");

              cStatement = connection.prepareCall(\"CALL OBJECT.EX850SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\");
              //cStatement = connection.prepareCall(\"UPDATE DATAEMS.CUEMSID SET CUSTS = NVL(UPPER(?), CUSTS), CUETYP = NVL(UPPER(?), CUETYP), CULOCN = NVL(?, CULOCN), CUEMAD = NVL(UPPER(?), CUEMAD), CUEMPL = NVL(UPPER(?), CUEMPL), CUFNAM = NVL(UPPER(?), CUFNAM), CULNAM = NVL(UPPER(?), CULNAM), CUSPRV = NVL(UPPER(?), CUSPRV), CUNETU = NVL(UPPER(?), CUNETU), CUDPDS = NVL(UPPER(?), CUDPDS), CUJBDS = NVL(UPPER(?), CUJBDS), CULEAD = NVL(UPPER(?), CULEAD), CUODCD = NVL(?, CUODCD), CUEXDT = ?, CULUDT = NVL(?, CULUDT), CULUBY = NVL(?, CULUBY) WHERE CUEEID = ?\");

              // Add attributes from account modify profile
              log.error(\"setting value for index 1\");
              cStatement.setString(1, getAttributeRequestValue(account, \"CUSTS\")); //employment status code
              log.error(\"setting value for index 2\");
              cStatement.setString(2, getAttributeRequestValue(account, \"CUETYP\")); //employee type code
              log.error(\"setting value for index 3\");
              cStatement.setString(3, getAttributeRequestValue(account, \"CULOCN\")); //location code
              log.error(\"setting value for index 4\");
              cStatement.setString(4, getAttributeRequestValue(account, \"CUEMAD\").trim()); //work email
              log.error(\"setting value for index 5\");
              cStatement.setString(5, getAttributeRequestValue(account, \"CUEMPL\")); //company code
              log.error(\"setting value for index 6\");
              cStatement.setString(6, getAttributeRequestValue(account, \"CUFNAM\")); //legal first name
              log.error(\"setting value for index 7\");
              cStatement.setString(7, getAttributeRequestValue(account, \"CULNAM\")); //last name
              log.error(\"setting value for index 8\");
              cStatement.setString(8, getAttributeRequestValue(account, \"CUSPRV\")); //manager name
              log.error(\"setting value for index 9\");
              cStatement.setString(9, nativeId); //search sam upper (CUNETU)
              log.error(\"setting value for index 10\");
              cStatement.setString(10, getAttributeRequestValue(account, \"CUDPDS\").trim()); //department description
              log.error(\"setting value for index 11\");
              cStatement.setString(11, getAttributeRequestValue(account, \"CUJBDS\")); //job title
              log.error(\"setting value for index 12\");
              cStatement.setString(12, getAttributeRequestValue(account, \"CULEAD\")); //employee management level
              log.error(\"setting value for index 13\");
              cStatement.setString(13, getAttributeRequestValue(account, \"CUODCD\")); //org level 1 code
              log.error(\"setting value for index 14\");
              if ( getAttributeRequestValue(account, \"CUEXDT\") != null ) {
                log.error(\"setting CUEXDT to \" + Util.atoi(getAttributeRequestValue(account, \"CUEXDT\")));
                cStatement.setInt(14, Util.atoi(getAttributeRequestValue(account, \"CUEXDT\"))); //iSeries term date
              } else {
                log.error(\"setting CUEXDT to 0\");
                cStatement.setInt(14, 0);
              }
              log.error(\"setting value for index 15\");
              cStatement.setString(15, getAttributeRequestValue(account, \"CULUDT\")); //iSeries update date
              log.error(\"setting value for index 16\");
              cStatement.setString(16, getAttributeRequestValue(account, \"CULUBY\")); //service user dirxml
              log.error(\"setting value for index 17\");
              cStatement.setString(17, (String) plan.getNativeIdentity()); //employee id
              log.error(\"setting value for index 18\");
              cStatement.setString(18, roleiSeries); //role
              log.error(\"EX850SP full statement: \" + cStatement.toString());

              cStatement.executeUpdate();

              // Successful Modify, so mark result as COMMITTED
              log.error(\"Modify Account Execution successfully completed\");
              result.setStatus(ProvisioningResult.STATUS_COMMITTED);

              log.error(\"Exiting Modify Operation\");
            }
          } catch(Exception e) {
            result.setStatus(ProvisioningResult.STATUS_FAILED);
            result.addError(e);
            log.error(\"iSeries JDBC Provisioning SQL Error: \" + e.getMessage());
          } finally {
            if(cStatement != null) {
              cStatement.close();
            }
          }
        }
      }
    }

    log.error(\"Exit iSeries JDBC Provisioning\");
    return result;

Also, the log.error messages are not making it to the ccg.log; previously I tried log.debug, log.warn and found : What is the correct way of debugging connector rules - #18 by chirag_patel – but no luck – I wasn’t sure if I needed to import any logger libraries as well. I’ve been using another established provisioning rule as my start point and it’s been a while since I touched Java.

sauvee · January 23, 2024, 8:10am

Does the ccg.log at least tell you that the rule was invoked?

officialamitguptaa · January 23, 2024, 8:24am

In general, The error message “java.sql.DataTruncation: Data truncation” indicates that there is an attempt to insert or update data in a database, and the data being provided is too large to fit into the specified column in the database table.

Here are some common causes for this error:

Column Size Mismatch: The size of the data being inserted or updated exceeds the maximum size allowed for the corresponding column in the database table. Check the column definitions in the database schema and ensure that the data being passed is within the allowed size limits.
Mismatched Data Types: Ensure that the data type of the Java object being passed for insertion or update matches the data type of the corresponding column in the database. If there is a mismatch, data truncation may occur.
String Length Exceeds Column Size: If the error is related to string data, make sure that the length of the string being inserted or updated is within the allowed length of the column.
Character Encoding Issues: If dealing with character data, ensure that the character encoding of the data being passed matches the character encoding expected by the database. Mismatched character encodings can lead to data truncation.

To resolve the issue, you may need to review the code that is performing the database operation and ensure that the data being passed aligns with the database schema. Check the sizes and types of the columns, and make any necessary adjustments to ensure that the data being inserted or updated conforms to the database constraints. If necessary, you may need to increase the size of the columns in the database or modify the data being passed to fit within the specified constraints.

moss_snake_01 · January 23, 2024, 5:08pm

Thank you both for your suggestions!

Yeah, the ccg.log was picking up the invocation of the rule, I was scratching my head trying to find the logger output from the java code though, as I suspected that would be captured there.

The data truncation error was due to a miscommunication with the developer, and I was provided the wrong sproc – everything is good now with the appropriate number of parms being requested. Appreciate the replies!

officialamitguptaa · January 23, 2024, 5:15pm

Perfect!!
Please mark the suggestion as solution.

system · March 23, 2024, 5:15pm

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
JDBC Connector Error ISC Discussion and Questions	3	2484	July 19, 2023
JDBC provisioning - two required attributes ISC Discussion and Questions identity-security-cloud , provisioning , jdbc-connector	7	316	May 26, 2024
IDN/ISC JDBC Connector NPE for Stored Procedure ISC Discussion and Questions identity-security-cloud , connectors , jdbc-connector	5	108	August 30, 2024
JDBC Connector: Intermittent error with Oracle DB: java.sql.SQLRecoverableException: IO Error: Connection reset by peer ISC Discussion and Questions identity-security-cloud , provisioning , connectors , jdbc-connector	8	592	September 15, 2024
JDBC Store Procedure provisoning issue ISC Discussion and Questions rules , identity-security-cloud , provisioning	7	88	December 10, 2024

JDBC Connector Troubleshooting Data Truncation

Related topics