Hi Tom/Gordon
In case of Postgresql, when doing an insert into a field that
corresponds to a Byte type, we get an exception thrown from the jdbc
driver. I have attached the complete stack trace to this email.
The issue was because we were doing a setObject(index, parameterValue)
and Postgresql jdbc driver complains that you need to use the
setObject(index,parameterValue, Types)
These are the changes made to address this problem :
src/java/oracle/toplink/essentials/internal/databaseaccess/DatabasePlatform.setParameterValueInDatabaseCall()
refactored code and introduced 2 methods :
setComplexParameterValue() & setPrimitiveParameterValue()
Other than the refactoring of code there is no code changes made to
this file.
src/java/oracle/toplink/essentials/platform/database/PostgreSQLPlatform.setPrimitiveParameterValue()
this method is overridden to use reflection on the parameter value
and delegate the call to the appropriate setter method of the prepared
statement. This code is identical to the code being used in the exiting
cmp code.
We think that this code should ideally be in the DatabasePlatform,
as there might be other drivers that have the same issue. What do you
guys think about this ?
I have attached the exception stack, diffs and code to this email. If
you guys want to discuss this over a concall we are free tomorrow after
10:00AM PST.
Thanks
Pramod & Mitesh
#|2006-03-21T14:54:05.906-0800|WARNING|sun-appserver-pe9.0|oracle.toplink.essentials.file:/C:/Sun/AppServer/domains/domain1/applications/j2ee-apps/ejb-cmp-datatypesApp/entity.jar-manager|_ThreadID=35;_ThreadName=p: thread-pool-1; w: 29;_RequestID=1a171ec3-562b-4a63-a17b-5809d8475c47;|
Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.3 (Build 060319)): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.lang.Byte. Use setObject() with an explicit Types value to specify the type to use.Error Code: 0
Call:INSERT INTO DATATYPES (ID, BOOLEANDATA, CHARACTERDATA, SHORTDATA, UTILDATEDATA, INTEGERDATA, SQLDATEDATA, LONGDATA, FLOATDATA, BYTEDATA, TIMESTAMPDATA) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
bind => [1, false, a, 32767, 1976-09-15, 2147483647, 1976-09-15, 12345, 3.4028235E38, 127, 1980-03-09 15:52:00.000123]
Query:InsertObjectQuery(ejb30.persistence.datatypes.ejb.DataTypes_at_15c2e5c)
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.executeCall(UnitOfWorkImpl.java:1339)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:331)
at oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:176)
at oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:192)
at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:457)
at oracle.toplink.essentials.queryframework.InsertObjectQuery.executeCommit(InsertObjectQuery.java:74)
at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedWrite(DatabaseQueryMechanism.java:635)
at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedInsert(DatabaseQueryMechanism.java:599)
at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:495)
at oracle.toplink.essentials.queryframework.WriteObjectQuery.executeCommitWithChangeSet(WriteObjectQuery.java:130)
at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:283)
at oracle.toplink.essentials.queryframework.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:67)
at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
at oracle.toplink.essentials.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:536)
at oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:123)
at oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:95)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2194)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:894)
at oracle.toplink.essentials.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:254)
at oracle.toplink.essentials.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:175)
at oracle.toplink.essentials.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:2625)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1007)
at oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:353)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1089)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:2406)
at oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:177)
at oracle.toplink.essentials.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:116)
at oracle.toplink.essentials.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:76)
at com.sun.ejb.containers.ContainerSynchronization.beforeCompletion(ContainerSynchronization.java:154)
at com.sun.enterprise.distributedtx.J2EETransaction.commit(J2EETransaction.java:395)
at com.sun.enterprise.distributedtx.J2EETransactionManagerOpt.commit(J2EETransactionManagerOpt.java:357)
at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3653)
at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:197)
at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:110)
at $Proxy55.create(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:121)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:650)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:193)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1705)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1565)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:947)
at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:178)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:717)
at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.dispatch(SocketOrChannelConnectionImpl.java:473)
at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.doWork(SocketOrChannelConnectionImpl.java:1270)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:440)
Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.lang.Byte. Use setObject() with an explicit Types value to specify the type to use.
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1703)
at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1371)
at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1396)
at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1384)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:621)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:470)
... 56 more
|#]
Index: src/java/oracle/toplink/essentials/internal/databaseaccess/DatabasePlatform.java
===================================================================
RCS file: /cvs/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/databaseaccess/DatabasePlatform.java,v
retrieving revision 1.11
diff -r1.11 DatabasePlatform.java
1353a1354,1373
> if (! setComplexParameterValue(session, statement, index, parameter)) {
> setPrimitiveParameterValue(statement, index, parameter);
> }
> }
>
> /**
> * Set a primitive parameter.
> * Database platforms that need customised behavior would override this method
> */
> protected void setPrimitiveParameterValue(final PreparedStatement statement, final int index,
> final Object parameter) throws SQLException {
> statement.setObject(index, parameter);
> }
>
> /**
> * Set a complex parameter.
> * @return true if parameter was successfully set by this method, false otherwise.
> */
> private boolean setComplexParameterValue(final AbstractSession session, final PreparedStatement statement, final int index, final Object parameter) throws SQLException {
> boolean parameterBound = true;
1371c1391
< statement.setObject(index, parameter);
---
> parameterBound = false;
1372a1393
> return parameterBound;
1891c1912,1913
< }
---
> }
>
Index: src/java/oracle/toplink/essentials/platform/database/PostgreSQLPlatform.java
===================================================================
RCS file: /cvs/glassfish/entity-persistence/src/java/oracle/toplink/essentials/platform/database/PostgreSQLPlatform.java,v
retrieving revision 1.6
diff -r1.6 PostgreSQLPlatform.java
25a26,29
> import java.math.BigDecimal;
> import java.math.BigInteger;
> import java.sql.PreparedStatement;
> import java.sql.SQLException;
326,327c330
< }
<
---
> }
334c337
< }
---
> }
335a339,374
> /**
> * Set a primitive parameter.
> * Postgres jdbc client driver give problem when doing a setObject() for wrapper types.
> * Ideally this code should be in the DatabasePlatform so that all platforms can use
> * this.
> */
> protected void setPrimitiveParameterValue(final PreparedStatement statement, final int index,
> final Object parameter) throws SQLException {
> if (parameter instanceof Number) {
> Number number = (Number) parameter;
> if (number instanceof Integer) {
> statement.setInt(index, number.intValue());
> } else if (number instanceof Long) {
> statement.setLong(index, number.longValue());
> } else if (number instanceof Short) {
> statement.setShort(index, number.shortValue());
> } else if (number instanceof Byte) {
> statement.setByte(index, number.byteValue());
> } else if (number instanceof Double) {
> statement.setDouble(index, number.doubleValue());
> } else if (number instanceof Float) {
> statement.setFloat(index, number.floatValue());
> } else if (number instanceof BigDecimal) {
> statement.setBigDecimal(index, (BigDecimal) number);
> } else if (number instanceof BigInteger) {
> statement.setBigDecimal(index, new BigDecimal((BigInteger) number));
> }
> } else if (parameter instanceof String) {
> statement.setString(index, (String)parameter);
> } else if (parameter instanceof Boolean) {
> statement.setBoolean(index, ((Boolean) parameter).booleanValue());
> } else {
> statement.setObject(index, parameter);
> }
> }
>