Problem catching multiple values

From: hiuguis(at)iteso(dot)mx
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Problem catching multiple values
Date: 2005-05-24 21:10:45
Message-ID: 1116969045.42939855b95f0@iteso.mx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi to all.

Im having an problem when I try to catch multiple values from an SETOF
float8 pgsql function invoked from java. This is the message that I get:

"org.postgresql.util.PSQLException: A CallableStatement function was executed
and the return was of type java.sql.Types=8 however type java.sql.Types=1111
was registered"

I had read that Types=8 is the DOUBLE type and I am using
cs.registerOutParameter(1, Types.OTHER); for catch the resultset because I had
read also that this is the way you can retrieve multiple values from an SETOF
Stored Procedure so, my question is, There is another way to retrieve multiple
values from java? Where can I find more documentation about this?

Here is my code:

---------------------------------------------
CREATE OR REPLACE FUNCTION obtnresumenventa("varchar", "varchar")
RETURNS SETOF float8 AS
'DECLARE
obtnvtasbruto cursor for SELECT sum(total) FROM ventas;
obtnvtasbrutof cursor for SELECT sum(total) FROM ventas WHERE fechahora > $1
AND fechahora < $2;
obtnvtasprodsiva cursor for SELECT sum(subtotal) FROM ventas;
obtnvtasprodsivaf cursor for SELECT sum(subtotal) FROM ventas WHERE
fechahora > $1 AND fechahora < $2;
obtniva cursor for SELECT sum(iva) FROM ventas;
obtnivaf cursor for SELECT sum(iva) FROM ventas WHERE fechahora > $1 AND
fechahora < $2;
obtnenvio cursor for SELECT sum(envio) FROM ventas;
obtnenviof cursor for SELECT sum(envio) FROM ventas WHERE fechahora > $1 AND
fechahora < $2;
obtnventatarjeta cursor for SELECT sum(total) FROM ventas WHERE formapago =
''T'';
obtnventatarjetaf cursor for SELECT sum(total) FROM ventas WHERE formapago =
''T'' AND fechahora > $1 AND fechahora < $2;
obtnventadep cursor for SELECT sum(total) FROM ventas WHERE formapago =
''D'';
obtnventadepf cursor for SELECT sum(total) FROM ventas WHERE formapago =
''D'' AND fechahora > $1 AND fechahora < $2;
obtnventapedenv cursor for SELECT sum(total) FROM ventas where estatus = 2;
obtnventapedenvf cursor for SELECT sum(total) FROM ventas WHERE estatus = 2
AND fechahora > $1 AND fechahora < $2;
obtnventaprocped cursor for SELECT sum(total) FROM ventas WHERE estatus = 1;
obtnventaprocpedf cursor for SELECT sum(total) FROM ventas WHERE estatus = 1
AND fechahora > $1 AND fechahora < $2;
obtnventacanc cursor for SELECT sum(total) FROM ventas WHERE estatus = 3;
obtnventacancf cursor for SELECT sum(total) FROM ventas WHERE estatus = 3
AND fechahora > $1 AND fechahora < $2;
obtnventadev cursor for SELECT sum(total) FROM ventas WHERE estatus = 4;
obtnventadevf cursor for SELECT sum(total) FROM ventas WHERE estatus = 4 AND
fechahora > $1 AND fechahora < $2;

vtasbruto float8;
vtasprodsiva float8;
iva float8;
envio float8;
ventatarjeta float8;
ventadep float8;
ventapedenv float8;
ventaprocped float8;
ventacanc float8;
ventadev float8;

vtasbrutof float8;
vtasprodsivaf float8;
ivaf float8;
enviof float8;
ventatarjetaf float8;
ventadepf float8;
ventapedenvf float8;
ventaprocpedf float8;
ventacancf float8;
ventadevf float8;

BEGIN
IF $1 = '''' THEN
OPEN obtnvtasbruto;
FETCH obtnvtasbruto INTO vtasbruto;
CLOSE obtnvtasbruto;
OPEN obtnvtasprodsiva;
FETCH obtnvtasprodsiva INTO vtasprodsiva;
CLOSE obtnvtasprodsiva;
OPEN obtniva;
FETCH obtniva INTO iva;
CLOSE obtniva;
OPEN obtnenvio;
FETCH obtnenvio INTO envio;
CLOSE obtnenvio;
OPEN obtnventatarjeta;
FETCH obtnventatarjeta INTO ventatarjeta;
CLOSE obtnventatarjeta;
OPEN obtnventadep;
FETCH obtnventadep INTO ventadep;
CLOSE obtnventadep;
OPEN obtnventapedenv;
FETCH obtnventapedenv INTO ventapedenv;
CLOSE obtnventapedenv;
OPEN obtnventaprocped;
FETCH obtnventaprocped INTO ventaprocped;
CLOSE obtnventaprocped;
OPEN obtnventacanc;
FETCH obtnventacanc INTO ventacanc;
CLOSE obtnventacanc;
OPEN obtnventadev;
FETCH obtnventadev INTO ventadev;
CLOSE obtnventadev;

RETURN NEXT vtasbruto;
RETURN NEXT vtasprodsiva;
RETURN NEXT iva;
RETURN NEXT envio;
RETURN NEXT ventatarjeta;
RETURN NEXT ventadep;
RETURN NEXT ventapedenv;
RETURN NEXT ventaprocped;
RETURN NEXT ventacanc;
RETURN NEXT ventadev;
ELSE
OPEN obtnvtasbrutof;
FETCH obtnvtasbrutof INTO vtasbrutof;
CLOSE obtnvtasbrutof;
OPEN obtnvtasprodsivaf;
FETCH obtnvtasprodsivaf INTO vtasprodsivaf;
CLOSE obtnvtasprodsivaf;
OPEN obtnivaf;
FETCH obtnivaf INTO ivaf;
CLOSE obtnivaf;
OPEN obtnenviof;
FETCH obtnenviof INTO enviof;
CLOSE obtnenviof;
OPEN obtnventatarjetaf;
FETCH obtnventatarjetaf INTO ventatarjetaf;
CLOSE obtnventatarjetaf;
OPEN obtnventadepf;
FETCH obtnventadepf INTO ventadepf;
CLOSE obtnventadepf;
OPEN obtnventapedenvf;
FETCH obtnventapedenvf INTO ventapedenvf;
CLOSE obtnventapedenvf;
OPEN obtnventaprocpedf;
FETCH obtnventaprocpedf INTO ventaprocpedf;
CLOSE obtnventaprocpedf;
OPEN obtnventacancf;
FETCH obtnventacancf INTO ventacancf;
CLOSE obtnventacancf;
OPEN obtnventadevf;
FETCH obtnventadevf INTO ventadevf;
CLOSE obtnventadevf;

RETURN NEXT vtasbrutof;
RETURN NEXT vtasprodsivaf;
RETURN NEXT ivaf;
RETURN NEXT enviof;
RETURN NEXT ventatarjetaf;
RETURN NEXT ventadepf;
RETURN NEXT ventapedenvf;
RETURN NEXT ventaprocpedf;
RETURN NEXT ventacancf;
RETURN NEXT ventadevf;
END IF;
RETURN;
END;'
LANGUAGE 'plpgsql' VOLATILE;
-----------------------------------

This is the way Im using for retrieve the data:

-----------------------------------
try
{
Connection conexion = poolDeConexion.getConnection();
try
{
conexion.setAutoCommit(false);
CallableStatement cs = conexion.prepareCall("{? = call
obtnresumenventa(?,?)}");
cs.registerOutParameter(1, Types.OTHER);
cs.setString(2, F1);
cs.setString(3, F2);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
while(rs.next())
{
VtasBrutoF = rs.getDouble(1);
VtasProdSIvaF = rs.getDouble(2);
IVAF = rs.getDouble(3);
EnvioF = rs.getDouble(4);
VentaTarjetaF = rs.getDouble(5);
VentaDepF = rs.getDouble(6);
VentaPedEnvF = rs.getDouble(7);
VentaProcPedF = rs.getDouble(8);
VentaCancF = rs.getDouble(9);
VentaDevF = rs.getDouble(10);
}
rs.close();
cs.close();
poolDeConexion.free(conexion);
}
catch(SQLException e)
{
System.out.println("(ADMFYS) Excepcion en procedimiento almacenado:
ObtnResumenVenta, fallo en: " + e.getErrorCode());
e.printStackTrace();
}
}
catch(Exception e)
{
System.err.println("(ADMFYS) Error en la asignacion a Connection
(ObtnResumenVenta): " + e );
}
-----------------------------------

Thanks in advance.

Hugo Maldonado.

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Josh Berkus 2005-05-24 23:17:32 Re: IN/OUT parameters
Previous Message Tom Lane 2005-05-24 20:49:48 Re: IN/OUT parameters