Bug with callable statement and output parameters
Luis Londono
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
Dave Cramer
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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Luis Londono
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)
> $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
Dave Cramer
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
---------------------------(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
Kris Jurka
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
Dave Cramer
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);
}
}
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
Dave Cramer
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>
Kris Jurka
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
Kris Jurka
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)---------------------------
Dave Cramer
Do you remember why we need outParmBeforeFunc. Did changes I made
break this, or has this been broken for a while ?
Dave
Kris Jurka
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)---------------------------
Kris Jurka
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
Kris Jurka
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
---------------------------(end of broadcast)---------------------------