Cannot deploy J2EE app to NW 7.1 EHP1 due to SAP Open SQL bug

[Former Member]

We have a custom J2EE app which we ported to NW 6.40 a couple of years ago. We now want to deploy the same app to NW 7.1 CE EHP1, but we cannot deploy due to the following exception:

 #3#letsys.com/eRespondSuite*HealthMonitor#letsys.com/eRespondSuite#com.sap.sql.log.OpenSQLException: Failed to set the parameter 2 of the statement >>delete from JPL_APP_ACTN where JPL_APP_PC_ID = ? and JPL_NAME in (?)<<: Cannot assign a >>java.lang.String<< object of length 12,866 to a parameter with JDBC type >>VARCHAR(256)<<.
        at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:104)
        at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:143)
        at com.sap.sql.jdbc.common.CommonPreparedStatement.handleSetterException(CommonPreparedStatement.java:416)
        at com.sap.sql.jdbc.common.CommonPreparedStatement.setString(CommonPreparedStatement.java:593)
        at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.setString(PreparedStatementWrapper.java:380)
        at com.sap.security.core.role.persistence.PolicyConfigurationLinkingHandler.deleteDuplicateAppActions(PolicyConfigurationLinkingHandler.java:194)
        at com.sap.security.core.role.persistence.PolicyConfigurationLinkingHandler.moveAppActions(PolicyConfigurationLinkingHandler.java:145)
        at com.sap.security.core.role.persistence.PolicyConfigurationLinkingHandler.moveModPolicyConfigurations(PolicyConfigurationLinkingHandler.java:119)
        at com.sap.security.core.role.persistence.PolicyConfigurationLinkingHandler.linkPolicyConfigurations(PolicyConfigurationLinkingHandler.java:105)
        at com.sap.security.core.role.persistence.JACCPersistenceManager.linkPolicyConfigurations(JACCPersistenceManager.java:387)
        at com.sap.security.core.role.jacc.JACCPolicyConfiguration.linkConfiguration(JACCPolicyConfiguration.java:147)
        at com.sap.engine.services.servlets_jsp.server.deploy.util.SecurityUtils.getPolicyConfiguration(SecurityUtils.java:893)
        at com.sap.engine.services.servlets_jsp.server.deploy.util.SecurityUtils.createSecurityResources(SecurityUtils.java:112)
        at com.sap.engine.services.servlets_jsp.server.deploy.DeployAction.initXmls(DeployAction.java:778)
        at com.sap.engine.services.servlets_jsp.server.deploy.DeployAction.deploy(DeployAction.java:301)
        at com.sap.engine.services.servlets_jsp.server.deploy.WebContainer.deploy(WebContainer.java:214)
        at com.sap.engine.services.deploy.server.utils.container.ContainerWrapper.deploy(ContainerWrapper.java:195)
        at com.sap.engine.services.deploy.server.application.DeploymentTransaction.makeComponents(DeploymentTransaction.java:526)
        at com.sap.engine.services.deploy.server.application.DeployUtilTransaction.commonBegin(DeployUtilTransaction.java:249)
        at com.sap.engine.services.deploy.server.application.DeploymentTransaction.begin(DeploymentTransaction.java:188)
        at com.sap.engine.services.deploy.server.application.ApplicationTransaction.makeAllPhasesOnOneServer(ApplicationTransaction.java:493)
        at com.sap.engine.services.deploy.server.application.ApplicationTransaction.makeAllPhases(ApplicationTransaction.java:544)
        at com.sap.engine.services.deploy.server.DeployServiceImpl.makeGlobalTransaction(DeployServiceImpl.java:2536)
        at com.sap.engine.services.deploy.server.DeployServiceImpl.deploy(DeployServiceImpl.java:378)
        at com.sap.engine.services.deploy.server.DeployServiceImpl.deploy(DeployServiceImpl.java:323)

The crux of the problem is that the Open SQL layer is adding validation to ensure that the length of the bind variable value does not exceed the size of the database column that it is being mapped to. First, a closer look at the SQL, note that this statement is executed internally by NetWeaver during the deployment process and these tables are in the NW internal database and have nothing to do with our app.

delete from JPL_APP_ACTN where JPL_APP_PC_ID = ? and JPL_NAME in (?)

The JPL_NAME is defined as NVARCHAR2(256) and bind varaible 2 has a length of 12866.

It makes no sense to limit the length of the second bind variable to 256. This is equivalent to the following hypothetical example:

Table: animal (id NUMBER, species VARCHAR(10))

DELETE FROM animal WHERE species in ('DOG','ELEPHANT,'COW');

In this situation the same SQL would fail, but there is no reason to reject this.

I will also log a support ticket but I'm hoping I can find a faster solution here as last time it took 3 weeks for SAP support to respond.

[Former Member]

Resolved by upgrading NetWeaver 7.1 CE EHP1 to SP2.