Thread: Bug with callable statement and output parameters
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.sqlExecuteQuery (ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject (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.getDataAndRenew2(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
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.sqlExecuteQuery (ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject (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.getDataAndRenew2(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
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
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=0 was 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=12 was 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)
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=0 was 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=12 was 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@fastcrypt.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
Attachment
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 Dave
On Fri, 21 Apr 2006, Dave Cramer wrote: > 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 > What's the status of this? I'd like to put out a new release this week and this would be good to have in it. Kris Jurka
On Wed, 26 Apr 2006, Dave Cramer wrote: > I'll try to fix it tonight. No rush, I've got some other things I'm still looking at. > If you have any ideas on how to resolve this I'm all ears. > 1) Require that the number of columns returned is equal to the number of parameters that the user has called registerOutParameter for. 2) Iterate over the number of parameters ignoring those that haven't been registered as out paramaters when comparing with the ResultSet. This will require adding an extra boolean hasBeenRegistered[] array or inventing an int value that doesn't overlap with any java.sql.Types value so you can tell what has/has not been registered. Also I don't know if it is a problem to require a user to register an out parameter he has no intention of calling a getXXX method on, but that seems like the only workable solution short of trying to poke into the system catalogs to find the called function. Kris Jurka
Patch attached for review and test Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java =================================================================== RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/ AbstractJdbc2Statement.java,v retrieving revision 1.88 diff -c -r1.88 AbstractJdbc2Statement.java *** org/postgresql/jdbc2/AbstractJdbc2Statement.java 1 Feb 2006 18:52:13 -0000 1.88 --- org/postgresql/jdbc2/AbstractJdbc2Statement.java 27 Apr 2006 16:00:50 -0000 *************** *** 366,392 **** // figure out how many columns int cols = rs.getMetaData().getColumnCount(); ! callResult = new Object[cols]; // move them into the result set ! for ( int i=0; i < cols; i++) { ! callResult[i] = rs.getObject(i+1); ! int columnType = rs.getMetaData().getColumnType(1); ! if (columnType != functionReturnType[i]) { // this is here for the sole purpose of passing the cts ! if ( columnType == Types.DOUBLE && functionReturnType[i] == Types.REAL ) { // return it as a float ! if ( callResult[i] != null) ! callResult[i] = new Float(((Double) callResult[i]).floatValue()); } else { ! throw new PSQLException (GT.tr("A CallableStatement function was executed and the return was of type {0} however type {1} was registered.", ! new Object[]{ ! "java.sql.Types=" + columnType, "java.sql.Types=" + functionReturnType[i] }), PSQLState.DATA_TYPE_MISMATCH); } } --- 366,401 ---- // figure out how many columns int cols = rs.getMetaData().getColumnCount(); ! ! // allocate enough space for all possible parameters without regard to in/out ! callResult = new Object [preparedParameters.getParameterCount()+1]; // move them into the result set ! for ( int i=0,j=0; i < cols; i++,j++) { ! // find the next out parameter, the assumption is that the functionReturnType ! // array will be initialized with 0 and only out parameters will have values ! // other than 0. 0 is the value for java.sql.Types.NULL, which should not ! // conflict ! while( j< functionReturnType.length && functionReturnType[j]==0) j++; ! ! callResult[j] = rs.getObject(i+1); ! int columnType = rs.getMetaData().getColumnType(i+1); ! ! if (columnType != functionReturnType[j]) { // this is here for the sole purpose of passing the cts ! if ( columnType == Types.DOUBLE && functionReturnType[j] == Types.REAL ) { // return it as a float ! if ( callResult[j] != null) ! callResult[j] = new Float(((Double) callResult[j]).floatValue()); } else { ! throw new PSQLException (GT.tr("A CallableStatement function was executed and the out parameter {0} was of type {1} however type {2} was registered.", ! new Object[]{""+i+1, ! "java.sql.Types=" + columnType, "java.sql.Types=" + functionReturnType[j] }), PSQLState.DATA_TYPE_MISMATCH); } }
On Thu, 27 Apr 2006, Dave Cramer wrote: > Patch attached for review and test > 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 Jdbc3CallableStatementTest 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
Attachment
On 27-Apr-06, at 10:15 PM, Kris Jurka wrote: > > > On Thu, 27 Apr 2006, Dave Cramer wrote: > >> Patch attached for review and test >> > > 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. Thanks, I'll add this test. > > 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." Sorry, I originally had them there but setUp and tearDown get called for every test, so I moved them, and didn't remove them in teardown. > > The whole Jdbc3CallableStatementTest sucks. It should have all of > its function/table creation destruction moved into setUp/tearDown. > Also the coding: So we're OK with adding, and removing the functions for every test ? > > 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>
New patch. On 27-Apr-06, at 10:15 PM, Kris Jurka wrote: > > > On Thu, 27 Apr 2006, Dave Cramer wrote: > >> Patch attached for review and test >> > > 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 Jdbc3CallableStatementTest 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.postgresql.org
Attachment
On Mon, 1 May 2006, Dave Cramer wrote: > New patch. > 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
Kris, Ok, this should be it.... On 4-May-06, at 1:02 AM, Kris Jurka wrote: > > > On Mon, 1 May 2006, Dave Cramer wrote: > >> New patch. >> > > 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 >
Attachment
On Thu, 4 May 2006, Dave Cramer wrote: > Kris, > > Ok, this should be it.... 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
Kris, Do you remember why we need outParmBeforeFunc. Did changes I made break this, or has this been broken for a while ? Dave On 10-May-06, at 8:52 PM, Kris Jurka wrote: > > > On Thu, 4 May 2006, Dave Cramer wrote: > >> Kris, >> >> Ok, this should be it.... > > 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 >
On Wed, 10 May 2006, Dave Cramer wrote: > Do you remember why we need outParmBeforeFunc. Did changes I made break this, > or has this been broken for a while ? > 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
Kris, Attached is a patch that works on 8.1 and 7.3 for sure On 10-May-06, at 11:11 PM, Kris Jurka wrote: > > > On Wed, 10 May 2006, Dave Cramer wrote: > >> Do you remember why we need outParmBeforeFunc. Did changes I made >> break this, or has this been broken for a while ? >> > > 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 >
Attachment
On Sun, 21 May 2006, Dave Cramer wrote: > Attached is a patch that works on 8.1 and 7.3 for sure > Applied to 8.1 and HEAD. In the future please take more care that your editor does not make superfluous changes to the code. There were a number of whitespace, bracket position, and other changes that make a diff harder to read. In testing I found some other related bugs regarding trying to retrieve results that are not available as the attached test cases demonstrate. Kris Jurka
Attachment
On Mon, 22 May 2006, Kris Jurka wrote: > On Sun, 21 May 2006, Dave Cramer wrote: > >> Attached is a patch that works on 8.1 and 7.3 for sure >> > > Applied to 8.1 and HEAD. Unfortunately it does not work when run against a 8.0 server. Kris Jurka
On Tue, 23 May 2006, Kris Jurka wrote: >> On Sun, 21 May 2006, Dave Cramer wrote: >> >>> Attached is a patch that works on 8.1 and 7.3 for sure >>> > > Unfortunately it does not work when run against a 8.0 server. > 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 getOutParameterCount 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
Attachment
Yeah, The whole out parameters thing is a bit screwy since we don't support real parameters. This makes sense as it's pretty much the same as what I did for the 7.3 server. Dave On 23-May-06, at 4:30 PM, Kris Jurka wrote: > > > On Tue, 23 May 2006, Kris Jurka wrote: > >>> On Sun, 21 May 2006, Dave Cramer wrote: >>>> Attached is a patch that works on 8.1 and 7.3 for sure >> >> Unfortunately it does not work when run against a 8.0 server. >> > > 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 getOutParameterCount 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-v3.patch>
On Mon, 22 May 2006, Kris Jurka wrote: > In testing I found some other related bugs regarding trying to retrieve > results that are not available as the attached test cases demonstrate. > I've fixed the following three problem cases for CallableStatements in CVS HEAD. 1) Calling wasNull before fetching anything threw an ArrayIndexOutOfBoundsException. 2) Fetching data before executing the statement threw a NullPointerException. 3) Fetching data when nothing was registered to be returned threw a NullPointerException. Kris Jurka