Troubleshooting
Problem
An error message appears when you try to invoke a stored procedure on a Microsoft SQL Server database with WebSphere Adapter for JDBC V6.2.0.3.
Symptom
The following error messages are thrown when you try to invoke a stored procedure on a Microsoft SQL Server database with WebSphere Adapter for JDBC V6.2.0.3:
[6/4/10 11:08:33:562 CDT] 00000071 JDBCRA001 1
com.ibm.j2ca.dbadapter.core.runtime.DBUtils traceConnectionMeteData
Driver Name:Microsoft SQL Server JDBC Driver 2.0
[6/4/10 11:08:34:328 CDT] 00000071 JDBCRA001 1
com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder
composeExecuteStoredProcedureSQL Call Statement SQL is : { ? = call
dbo.user.save;1(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?) }
[6/4/10 11:08:34:328 CDT] 00000071 JDBCRA001 <
com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder
composeExecuteStoredProcedureSQL Exiting method.
:
:
:"transactionNotSupportedActivitySessionNotSupported" on bean
"BeanId(TOBDAL_MediationModuleApp#TOBDAL_MediationModuleEJB.jar#Module,
null)". Exception data: com.ibm.websphere.sca.ServiceRuntimeException:
ResourceException thrown in J2CMethodBindingImpl.invoke()
javax.resource.spi.CommException: javax.resource.ResourceException:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
the keyword 'user'.
at
com.ibm.ws.sca.internal.j2c.J2CMethodBindingImpl.invoke(J2CMethodBinding
Impl.java:476)
Cause
When the column name, table name, stored procedure name and other database object names include a database "reserved keyword", the JDBC Adapter fails to operate on them. For example, when the stored procedure "dbo.user.save" contains a reserved keyword "user" in a Microsoft SQL Server, JDBC Adapter fails to execute stored procedure.
Resolving The Problem
According to the following links:
IBM DB2:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0000720.html
An ordinary identifier should not be a reserved word.
Oracle:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm
Nonquoted identifiers cannot be Oracle Database reserved words. Quoted identifiers can be reserved words, although this is not recommended.
Microsoft SQL Server:
http://msdn.microsoft.com/en-us/library/aa223962%28SQL.80%29.aspx
When used in Transact-SQL statements, identifiers that fail to comply with these rules must be delimited by double quotation marks or brackets.
There is a workaround to solve the above error by enclosing database object name (dbo.user.save) with a reserved keyword like "user" by using square brackets or double quotation marks in MS SQL Server. When DB2 or Oracle is used, you can enclose the database object name with a reserved keyword by using double quotation marks.
For example, there is a business object "Inq2DboUserU46saveU591.xsd" generated in Microsoft SQL Server like this:
<jdbcasi:SPName>dbo.user.save;1</jdbcasi:SPName>
You can change it to:
<jdbcasi:SPName>dbo.[user.save];1</jdbcasi:SPName> or
<jdbcasi:SPName>dbo."user.save";1</jdbcasi:SPName>
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21433726