JDBC CallableStatement bug on functions with return parameter

From: John LH <johnlh(at)aquafold(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: JDBC CallableStatement bug on functions with return parameter
Date: 2011-02-04 22:12:41
Message-ID: 4D4C79D9.1040908@aquafold.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,

I believe there is a bug in the PostgreSQL JDBC driver. I have a
function "sales_tax (in subtotal money) RETURNS money" with DDL below.
I am trying to execute the function with a CallableStatement. I use the
SQL of :

{ ? = CALL "public"."sales_tax"(?) }

I register the parameters and then execute the statement, but it fails
with the error message :

ERROR: function public.sales_tax(double precision) does not exist

I trace the code down to the
org.postgresql.jdbc2.AbstractJdbc2Statement.modifyJdbcCall(String
p_sql). At the end of this function on line 2384-2410 (Pasted below) it
makes a conversion. It seems that the CallableStatement converts the
original SQL above to :

select * from "public"."sales_tax"(?, ?) as result

If you execute this statement with substituted variable a terminal
window into PG, the statement will fail. The syntax seems to be
incorrect. But the SQL will execute correctly if you execute :

select * from "public"."sales_tax"(?) as result

I am not sure what is the correct behavior, but this doesn't seem to be
correct as I can't execute a function with a return value.

Does anyone know if this is a bug or a known limitation? ... or if I am
doing something wrong?

thanks
-John

== PROCEDURE DDL ==============================================

CREATE OR REPLACE FUNCTION public.sales_tax (in subtotal money) RETURNS
money AS
$BODY$
BEGIN
RETURN subtotal * 0.06;
END;
$BODY$
LANGUAGE 'plpgsql'
GO

================================================================
== AbstractJdbc2Statement.modifyJdbcCall(String p_sql) ===============
if (connection.haveMinimumServerVersion("8.1") &&
((AbstractJdbc2Connection)connection).getProtocolVersion() == 3)
{
String s = p_sql.substring(startIndex, endIndex );
StringBuffer sb = new StringBuffer(s);
if ( outParmBeforeFunc )
{
// move the single out parameter into the function call
// so that it can be treated like all other parameters
boolean needComma=false;

// have to use String.indexOf for java 2
int opening = s.indexOf('(')+1;
int closing = s.indexOf(')');
for ( int j=opening; j< closing;j++ )
{
if ( !Character.isWhitespace(sb.charAt(j)) )
{
needComma = true;
break;
}
}
if ( needComma )
{
sb.insert(opening, "?,");
}
else
{
sb.insert(opening, "?");
}

}
return "select * from " + sb.toString() + " as result";
}
================================================================

Browse pgsql-jdbc by date

  From Date Subject
Next Message zhong ming wu 2011-02-05 01:48:14 ssl connection strangely stops working
Previous Message Kevin Grittner 2011-02-03 17:36:12 Re: Timing of notice delivery