Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

Next:From: Josh BerkusDate: 2005-05-24 23:17:32
Subject: Re: IN/OUT parameters
Previous:From: Tom LaneDate: 2005-05-24 20:49:48
Subject: Re: IN/OUT parameters

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group