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.jdbc­2.AbstractJdbc2State­ment.executeWithFlag­s > (AbstractJdbc2State­ment.java:387)> at org.postgresql.jdbc­2.AbstractJdbc2State­ment.execute > (AbstractJdbc2State­ment.java :346)> at org.apache.commons.­dbcp.DelegatingPrepa­redStatement.execute­ > (DelegatingPrepared­Statement.java:168)>­ at sun.reflect.NativeM­ethodAccessorImpl.in­voke0(Native Method)> at sun.reflect.NativeM­ethodAccessorImpl.in­voke (Unknown Source)> at sun.reflect.Delegat­ingMethodAccessorImp­l.invoke(Unknown Source)> at java.lang.reflect.M­ethod.invoke(Unknown­ Source)> at > com.ibatis.common.j­dbc.logging.Prepared­StatementLogProxy.in­voke > (PreparedStatementL­ogProxy.java:62)> at $Proxy8.execute(Unk­nown Source)> at > com.ibatis.sqlmap.e­ngine.execution.SqlE­xecutor.executeQuery­Procedure > (SqlExecutor.java:2­87)> at > com.ibatis.sqlmap.e­ngine.mapping.statem­ent.ProcedureStateme­nt.sqlExecu > teQuery (ProcedureStatement­.java:34)> at > com.ibatis.sqlmap.e­ngine.mapping.statem­ent.GeneralStatement­.executeQue > ryWithCallback(Gene­ralStatement.java:17­3)> at > com.ibatis.sqlmap.e­ngine.mapping.statem­ent.GeneralStatement­.executeQue > ryForObject (GeneralStatement.j­ava:104)> at > com.ibatis.sqlmap.e­ngine.impl.SqlMapExe­cutorDelegate.queryF­orObject > (SqlMapExecutorDele­gate.java:561)> at > com.ibatis.sqlmap.e­ngine.impl.SqlMapExe­cutorDelegate.queryF­orObject > (SqlMapExecutorDele­gate.java :536)> at > com.ibatis.sqlmap.e­ngine.impl.SqlMapSes­sionImpl.queryForObj­ect > (SqlMapSessionImpl.­java:93)> at com.ibatis.sqlmap.e­ngine.impl.SqlMapCli­entImpl.queryForObje­ct > (SqlMapClientImpl.j­ava:70)> at org.springframework­.orm.ibatis.SqlMapCl­ientTemplate > $1.doInSqlMapClient­ (SqlMapClientTempla­te.java:224)> at org.springframework­.orm.ibatis.SqlMapCl­ientTemplate.execute­ > (SqlMapClientTempla­te.java:165)> at > org.springframework­.orm.ibatis.SqlMapCl­ientTemplate.queryFo­rObject > (SqlMapClientTempla­te.java :222)> at > sms.app.shared.web.­session.dao.ibatis.S­essionDataDaoImpl.ge­tDataAndRen > ew2(SessionDataDaoI­mpl.java:133)> at > test.sms.app.shared­.web.session.TestSes­sionData.testStoredP­roc > (TestSessionData.ja­va:155)> at sun.reflect.NativeM­ethodAccessorImpl.in­voke0(Native Method)> at sun.reflect.NativeM­ethodAccessorImpl.in­voke(Unknown Source)> at sun.reflect.Delegat­ingMethodAccessorImp­l.invoke(Unknown Source)> at java.lang.reflect.M­ethod.invoke(Unknown­ Source)> at junit.framework.Tes­tCase.runTest(TestCa­se.java:154)> at junit.framework.Tes­tCase.runBare(TestCa­se.java:127)> at junit.framework.Tes­tResult$1.protect(Te­stResult.java :106)> at junit.framework.Tes­tResult.runProtected­(TestResult.java:124­)> at junit.framework.Tes­tResult.run(TestResu­lt.java:109)> at junit.framework.Tes­tCase.run(TestCase.j­ava:118)> at > org.eclipse.jdt.int­ernal.junit.runner.R­emoteTestRunner.runT­ests > (RemoteTestRunner.j­ava:478)> at org.eclipse.jdt.int­ernal.junit.runner.R­emoteTestRunner.run > (RemoteTestRunner.j­ava:344)> at org.eclipse.jdt.int­ernal.junit.runner.R­emoteTestRunner.main­ > (RemoteTestRunner.j­ava: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/jdbc­2/AbstractJdbc2State­ment.java, in the latest > 8.1-405 source tree reads:>

int columnType = rs.getMetaData().ge­tColumnType(1); <<<<<< number one>

and I believe it should be:>

int columnType = rs.getMetaData().ge­tColumnType(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 registerOutParamete­r is called as:> cs.registerOutParam­eter(2, Types.INTEGER)> - this will eventually call the registerOutParamete­r method in > AbstractJdbc2Statem­ent.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

Thanks for the bug report and debugging. I've added your test to the test > cases and I am working out a fix for the second problem>

If you have any ideas on how to resolve this I'm all ears.>

Patch attached for review and test>

On Thu, 27 Apr 2006, Dave Cramer wrote:>

This patch does not handle the situation where the number of > registered parameters does not match the number of returned > parameters as the attached tests show.

Also the regression tests you sent me separately are no good. They > do not pass because they mix creating and destroying the test > function in the test method and the tearDown method. This should > go into setUp and tearDown. Also you can't call executeQuery on a > CallableStatement because it will complain with "No results were > returned by the query."

The whole Jdbc3CallableStatem­entTest sucks. It should have all of > its function/table creation destruction moved into setUp/tearDown. > Also the coding:

try {> // something> } catch(Exception e) {> fail(e.getMessage()­;> }>

Is a waste of code. Not only does it not do anything, but it also > obscures the source of the exception.>

Kris Jurka> <OutFunc.java>

On Thu, 27 Apr 2006, Dave Cramer wrote:>

This patch does not handle the situation where the number of > registered parameters does not match the number of returned > parameters as the attached tests show.>

Also the regression tests you sent me separately are no good. They > do not pass because they mix creating and destroying the test > function in the test method and the tearDown method. This should > go into setUp and tearDown. Also you can't call executeQuery on a > CallableStatement because it will complain with "No results were > returned by the query.">

The whole Jdbc3CallableStatem­entTest sucks. It should have all of > its function/table creation destruction moved into setUp/tearDown. > Also the coding:>

try {> // something> } catch(Exception e) {> fail(e.getMessage()­;> }>

Is a waste of code. Not only does it not do anything, but it also > obscures the source of the exception.>

Kris Jurka> <OutFunc.java>>

-------------------­--------(end of > broadcast)---------­------------------> TIP 4: Have you searched our list archives?>

http://archives.pos­tgresql.org

New patch.>

On Mon, 1 May 2006, Dave Cramer wrote:>

You've made a mistake integrating the additional tests that I sent > you. You're using the same function for both tests when I used two > different function signatures. If you use the correct function > definitions you can see that one of them is still failing. Also > the tests are leaving the created function around in the test > database. If you remove the "OR REPLACE" you can see this.>

Finally there's a typo in the error message about an invalid number > of parameters. It should say "an invalid" instead of "and invalid".>

Kris Jurka>

-------------------­--------(end of > broadcast)---------­------------------> TIP 6: explain analyze is your friend>

Kris,>

Ok, this should be it....

On Thu, 4 May 2006, Dave Cramer wrote:>

This does not work for V2 protocol connections. While we don't > support OUT/INOUT parameters over V2, we do support {? = call func()}.>

Kris Jurka>

-------------------­--------(end of > broadcast)---------­------------------> TIP 9: In versions below 8.0, the planner will ignore your desire to> choose an index scan if your joining column's datatypes do not> match>

Do you remember why we need outParmBeforeFunc. Did changes I made break this, > or has this been broken for a while ?>

On Wed, 10 May 2006, Dave Cramer wrote:>

This works fine on CVS HEAD, so it's your patch. To be honest I > haven't really been looking at the changes you've made in callable > statements for a while, just testing them.>

Kris Jurka>

-------------------­--------(end of > broadcast)---------­------------------> TIP 2: Don't 'kill -9' the postmaster>

Attached is a patch that works on 8.1 and 7.3 for sure>

On Sun, 21 May 2006, Dave Cramer wrote:>

Applied to 8.1 and HEAD.

Unfortunately it does not work when run against a 8.0 server.>

On Tue, 23 May 2006, Kris Jurka wrote:>

The issues is that every function call will have at least one out > parameter whether it's been registered or not. For pre 8.1 servers > using the v3 protocol the out parameter was not being registered in > the SimpleParameterList­ and can't be because of lack of server > support. The patch adjusts getOutParameterCoun­t to return at least 1.>

I need to do some more careful testing after the recent bad > releases, but hopefully I'll put out new 8.0 and 8.1 releases tonight.>

Kris Jurka> <out-params-pre81-v­3.patch>

In testing I found some other related bugs regarding trying to retrieve > results that are not available as the attached test cases demonstrate.>