JR40894: CMVC 214863 - RESOLVE GETPREPAREDSTATEMENT CAUSES SQLSYNTAXERROREXCEPTION SQLCOD -228.

7.0.0-WS-WCServer-FP009
WebSphere Commerce Version 7.0.0.6 Fix Pack
WebSphere Commerce Version 7.0.0.7 Fix Pack
WebSphere Commerce Version 7.0.0.8 Fix Pack
WebSphere Commerce Version 7.0.0.9 Fix Pack

APAR status

• Closed as program error.

Error description

• WebSphere Commerce Fixpack 7.0.0.3 broke backward compatibility
  of the method:
  
  JDBCQueryHelper.getPreparedStatement(Connection aConnection,
  String astrComponentId, String astrSQLQuery,
     List paramValues)
  
  Specifically, in order to support pagination we changed the
  result type from ResultSet.TYPE_FORWARD_ONLY (the default) to
  ResultSet.TYPE_SCROLL_INSENSITIVE
  
       // In order to enable cursor pagination, here we set
  ResultSet.TYPE_SCROLL_INSENSITIVE and
       // ResultSet.CONCUR_READ_ONLY for PreparedStatement.
       stmt = conn.prepareStatement(astrSQLQuery,
  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  
  However, the INSENSITIVE SCROLL type of the result set can not
  be used with a SELECT statement that contains a FOR UPDATE
  clause.
  
  That is why any custom code that uses a 'SELECT FOR UPDATE' SQL
  query results in error SQLCODE=-228 after installing WebSphere
  Commerce 7.0.0.3.
  The following error occurred during processing: "{0}".
  com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL
  Error: SQLCODE=-228, SQLSTATE=42620, SQLERRMC=SQL_CURSH200C1,
  DRIVER=4.9.78
        at com.ibm.db2.jcc.am.fd.a(fd.java:676)
        at com.ibm.db2.jcc.am.fd.a(fd.java:60)
        at com.ibm.db2.jcc.am.fd.a(fd.java:127)
        at com.ibm.db2.jcc.am.en.c(en.java:2553)
        at com.ibm.db2.jcc.am.en.d(en.java:2541)
        at com.ibm.db2.jcc.am.en.a(en.java:2017)
        at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
        at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
        at com.ibm.db2.jcc.t4.q.a(q.java:32)
        at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
        at com.ibm.db2.jcc.am.en.ib(en.java:1988)
        at com.ibm.db2.jcc.am.fn.rc(fn.java:3016)
        at com.ibm.db2.jcc.am.fn.xc(fn.java:3146)
        at com.ibm.db2.jcc.am.fn.setObject(fn.java:1853)
        at
  com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.setObject(WSJd
  bcPreparedStatement.java:2435)
        at
  com.ibm.commerce.foundation.internal.server.services.dataaccess.
  workspace.util.JDBCQueryServiceWorkspaceHelper.getPreparedStatem
  ent(JDBCQueryServiceWorkspaceHelper.java:2783)
        at
  com.ibm.commerce.foundation.server.services.dataaccess.JDBCQuery
  Service.executeQuery(JDBCQueryService.java:841)
  

Local fix

• In case the problem occurs after upgrading to FP3 in the
  development environment, customer may remove the 'FOR UPDATE'
  clause as a temporary workaround.
  

Problem summary

• USERS AFFECTED:
  Customers that have customized the code that uses a 'SELECT FOR
  UPDATE' SQL query in JDBCQueryServiceWorkspaceHelper after
  installing WebSphere Commerce 7.0.0.3.
  
  PROBLEM ABSTRACT:
  Custom code that uses a 'SELECT FOR UPDATE' SQL query with
  JDBCQueryHelper results in error SQLCODE=-228 after installing
  WebSphere Commerce 7.0.0.3.
  
  BUSINESS IMPACT:
  Custom function may be broken after installing WebSphere
  Commerce 7.0.0.3 fixpack.
  
  RECOMMENDATION:
  

Problem conclusion

• In order to resolve the issue code has been modified to restore
  backward compatibility and introduce a new method to support the
  prepared statement with the TYPE_SCROLL_INSENSITIVE  type of the
  result set.
  
  -------------------------------------------------------------
  The latest available maintenance information can be obtained
  from the Recommended Fixes for WebSphere Commerce technote:
  http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WC BUS EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP