Re: Bug with callable statement and output parameters - Mailing list pgsql-jdbc

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