Re: Bug with callable statement and output parameters

I am attaching a java file that goes against the following functions:

CREATE OR REPLACE FUNCTION test_somein_someout(
pa IN int4,
pb OUT varchar,
pc OUT int8)
AS
$BODY$
begin
pb := 'pb out';
pc := pa + 1;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION test_allinout(
pa INOUT int4,
pb INOUT varchar,
pc INOUT int8)
AS
$BODY$
begin
pa := pa + 1;
pb := 'pb out';
pc := pa + 1;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

I used postgresql-8.1-405.jdbc3.jar against 8.1.0 version of the db. Here
are the results:

org.postgresql.util.PSQLException: A CallableStatement function was executed
and the return was of type java.sql.Types=12 however type
java.sql.Types=0was registered.
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
AbstractJdbc2Statement.java:387)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
AbstractJdbc2Statement.java:257)
at TestDriver.testSomeInSomeOut(TestDriver.java:20)
at TestDriver.main(TestDriver.java:54)
org.postgresql.util.PSQLException: A CallableStatement function was executed
and the return was of type java.sql.Types=4 however type
java.sql.Types=12was registered.
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
AbstractJdbc2Statement.java:387)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
AbstractJdbc2Statement.java:257)
at TestDriver.testAllInOut(TestDriver.java:40)
at TestDriver.main(TestDriver.java:60)

On 4/20/06, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> 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
>
>