Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2017
    Posts
    3

    Unanswered: Data truncation error while setting INOUT CHARACTER(2) parameter in Stored Procedure

    I'm calling a stored procedure in JAVA which has INOUT parameters. Database is DB2. Type is CHARACTER(2). I'm getting Data Truncation error while setting the variable. Could you please let me know where I'm going wrong and how I can set the value of the parameter using a variable without causing exception?

    Code:
    String cstmt_str = "CALL " + storedProcName + "(?,?,?)";
     String ett="JD"; String nwReasonCode=" ";Connection conn = null;CallableStatement cstmt = null;
    
        cstmt = conn.prepareCall(cstmt_str);
    
        cstmt.registerOutParameter("5506ETT", Types.CHAR);
        //cstmt.setString("5506ETT","JD"); does not give a problem
        cstmt.setString("5506ETT",ett); //Data truncation Exception occurs here.
    
        cstmt.registerOutParameter("5506NWR", Types.CHAR);
        cstmt.setString("5506NWR", nwReasonCode);//Doesnt give a problem
    
        cstmt.registerOutParameter("5506STS", Types.CHAR);
        cstmt.setString("5506STS", "PND");// If I set the value directly instead of variable, it does not complain. If I use a variable that holds the value, it gives the same exception here too.
        cstmt.execute();
    Stored Procedure Signature:
    Num Mode Name DataType Length
    1 INOUT 5506ETT CHARACTER 2
    2 INOUT 5506NWR CHARACTER 10
    3 INOUT 5506STS CHARACTER 3

    Error Trace:
    Code:
    ERROR TRACE: java.sql.DataTruncation: Data truncation at com.ibm.as400.access.AS400JDBCPreparedStatement.testDataTruncation(AS400JDBCPreparedStatement.java:3450) at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3361) at com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:2999) at com.ibm.as400.access.AS400JDBCCallableStatement.setString(AS400JDBCCallableStatement.java:3082) at org.jboss.jca.adapters.jdbc.WrappedCallableStatement.setString(WrappedCallableStatement.java:1563) at com.ssss.ssjtracdbws.dao.SSJTracDBWSDAO.submitNewRequestJSON(SSJTracDBWSDAO.java:617) at com.ssss.ssjtracdbws.webservices.SSJTracDBService.submitNewRequestJSON(SSJTracDBService.java:154) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:167) at org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:269) at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:227) at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:216) at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:542) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:524) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:126) at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:208) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:50) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:145) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:559) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:340) at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:353) at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:911) at org.apache.tomcat.util.net.NioEndpoint$ChannelProcessor.run(NioEndpoint.java:920) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)
    Last edited by skrrao; 01-05-17 at 12:07.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    characters that need 16-bit or 32-bit encodings wont fit in a CHAR(2), check java-locale(client codepage), database-codeset assumptions etc.

  3. #3
    Join Date
    Jan 2017
    Posts
    3
    Quote Originally Posted by db2mor View Post
    characters that need 16-bit or 32-bit encodings wont fit in a CHAR(2), check java-locale(client codepage), database-codeset assumptions etc.
    How do I know, find database codeset assumptions? I use iSeries Navigator. Is there any way to find out the database codeset assumptions? Please let me know as I'm a newbie.
    Java locale is en, English, United States. I found out that the column is using CCSID 37(EBCDIC). When I looked into IBM documentation, it says, Java uses Unicode. so the JDBC automatically converts EBCDIC to Unicode and viceversa, and that the developers dont need to worry about the conversion. So how can I make this work?:
    cstmt.setString("5506ETT",ett);
    Last edited by skrrao; 01-09-17 at 11:50.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Study IBM's example java code for calling an sproc, in file SpClient.java , which binds parameter-values using parameter number instead of parameter name with setString , and also shows use of setJcc* methods for setting input parameter values.

    See URL http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sample.doc/doc/java_jdbc/s-SpClient-java.html
    Last edited by db2mor; 01-06-17 at 01:44.

  5. #5
    Join Date
    Jan 2017
    Posts
    3
    Hi I looked into spclient. java from the URL above. I got the below exception:
    Code:
    java.lang.ClassCastException: org.jboss.jca.adapters.jdbc.jdk6.WrappedCallableStatementJDK6 cannot be cast to com.ibm.db2.jcc.DB2CallableStatement
    The code I used modified was:
    Code:
    cstmt.registerOutParameter("5506ETT", Types.CHAR); // 1st type
    // ((com.ibm.db2.jcc.DB2CallableStatement)cstmt).registerJccOutParameterAtName("5506ETT",Types.CHAR); // 2nd type
    ((com.ibm.db2.jcc.DB2CallableStatement)cstmt).setJccStringAtName("5506ETT",ett);
    While registering Out the parameter, I tried both, 1st and 2nd types. The error was the same. Could you please guide me if I can modify it correctly?

Tags for this Thread

Posting Permissions

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