Re: Bug with callable statement and output parameters

Luis,

Can you send us a test case that fails. There are a number of test
cases in the driver source code that do pass.

Dave
On 20-Apr-06, at 6:02 PM, Luis Londono wrote:

> I kept getting the following error when using a callable statement
> with multiple output parameters:
>
> org.postgresql.util.PSQLException: A CallableStatement function was
> executed and the return was of type java.sql.Types=12 however type
> java.sql.Types=4 was registered.
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
> (AbstractJdbc2Statement.java:387)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
> (AbstractJdbc2Statement.java :346)
> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute
> (DelegatingPreparedStatement.java:168)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke (Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at
> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke
> (PreparedStatementLogProxy.java:62)
> at $Proxy8.execute(Unknown Source)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure
> (SqlExecutor.java:287)
> at
> com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecu
> teQuery (ProcedureStatement.java:34)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQue
> ryWithCallback(GeneralStatement.java:173)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQue
> ryForObject (GeneralStatement.java:104)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject
> (SqlMapExecutorDelegate.java:561)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject
> (SqlMapExecutorDelegate.java :536)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject
> (SqlMapSessionImpl.java:93)
> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject
> (SqlMapClientImpl.java:70)
> at org.springframework.orm.ibatis.SqlMapClientTemplate
> $1.doInSqlMapClient (SqlMapClientTemplate.java:224)
> at org.springframework.orm.ibatis.SqlMapClientTemplate.execute
> (SqlMapClientTemplate.java:165)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject
> (SqlMapClientTemplate.java :222)
> at
> sms.app.shared.web.session.dao.ibatis.SessionDataDaoImpl.getDataAndRen
> ew2(SessionDataDaoImpl.java:133)
> at
> test.sms.app.shared.web.session.TestSessionData.testStoredProc
> (TestSessionData.java:155)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java :106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests
> (RemoteTestRunner.java:478)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
> (RemoteTestRunner.java:344)
> at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main
> (RemoteTestRunner.java:196)
>
>
> after much hair pulling, I am certain there are a couple of bugs
> with the jdbc driver. The first bug is relatively clear. Line 375
> of org/postgresql/jdbc2/AbstractJdbc2Statement.java, in the latest
> 8.1-405 source tree reads:
>
> int columnType = rs.getMetaData().getColumnType(1); <<<<<< number one
>
> and I believe it should be:
>
> int columnType = rs.getMetaData().getColumnType(i+1); <<<<<
> letter i plus one
>
> There is a second problem with output parameter checking, but it is
> harder to explain:
> - call a function such as: myfunc(p1 IN varchar, p2 OUT int4, p3
> OUT timestamp)
> - The registerOutParameter is called as:
> cs.registerOutParameter(2, Types.INTEGER)
> - this will eventually call the registerOutParameter method in
> AbstractJdbc2Statement.java
> - this will store in functionReturnType[1] the value of
> Types.INTEGER
> - the call is made to the db and the resultset containing the
> output parameters will be retrieved
> - in the same place as the previous error, the types are checked,
> - The problem arises now. The first output parameter is column 0
> of the result set, but index 1 in the functionReturnType array.
> - The types do not match and an error is thrown.
>
> I am not sure what the right fix is for this latter error.
>
> -Luis