JDBC function call (getting resultset)

From: marcelospbr(at)globo(dot)com
To: pgsql-jdbc(at)postgresql(dot)org
Subject: JDBC function call (getting resultset)
Date: 2003-12-10 15:15:16
Message-ID: 3FA790190004DB39@riosf06.globoi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I have a function that return a refcursor (step 1).
When I execute from the DBManager, I get three lines as resulted (step 2).
When I run the java code, I don't have success (step 3). Throws the error:
No results were returned by the query.
Somebody knows what's happening?

Pgsql version 7.3.2 and JDBC2.

1. Function

CREATE OR replace function fn_cad_servico_listar(
cad_servico.num_ano_exercicio%TYPE
, cad_servico.num_mes_exercicio%TYPE
, cad_servico.cod_atividade%TYPE
, cad_servico.cod_servico%TYPE
, cad_servico.dsc_servico%TYPE
)
returns refcursor AS '
DECLARE
p_num_ano_exercicio alias FOR $1;
p_num_mes_exercicio alias FOR $2;
p_cod_atividade alias FOR $3;
p_cod_servico alias FOR $4;
p_dsc_servico alias FOR $5;
p_retorno refcursor;
BEGIN

OPEN p_retorno FOR

SELECT
srv.num_ano_exercicio
, srv.num_mes_exercicio
, srv.cod_atividade
, atv.dsc_atividade
, srv.cod_servico
, srv.dsc_servico
, srv.num_posicao
, srv.cod_unidade_valor
, mda.dsc_sigla
, srv.vlr_repasse
FROM
cad_servico srv
, cad_atividade atv
, gen_unidade_valor mda
WHERE
atv.num_ano_exercicio = srv.num_ano_exercicio
AND atv.num_mes_exercicio = srv.num_mes_exercicio
AND atv.cod_atividade = srv.cod_atividade
AND mda.cod_unidade_valor = srv.cod_unidade_valor
AND (srv.num_ano_exercicio = p_num_ano_exercicio
OR p_num_ano_exercicio IS NULL)
AND (srv.num_mes_exercicio = p_num_mes_exercicio
OR p_num_mes_exercicio IS NULL)
AND (srv.cod_atividade = p_cod_atividade
OR p_cod_atividade IS NULL)
AND (srv.cod_servico = p_cod_servico
OR p_cod_servico IS NULL)
AND (srv.dsc_servico LIKE p_dsc_servico
OR p_dsc_servico IS NULL)
ORDER BY
srv.num_ano_exercicio DESC
, srv.num_mes_exercicio DESC
, atv.num_posicao ASC
, srv.num_posicao ASC
;

return p_retorno;

END;

' LANGUAGE 'plpgsql';

2. Executing from DBManager

BEGIN;
SELECT fn_cad_servico_listar(NULL, NULL, NULL, NULL, NULL);
FETCH ALL IN "<unnamed cursor 1>";

resultly:
num_ano_exercicio num_mes_exercicio cod_atividade dsc_atividade cod_servico
dsc_servico num_posicao cod_unidade_valor dsc_sigla vlr_repasse
2004 1 1 Registro 1 Nascimento 1 1 R$ 50
2004 1 1 Registro 2 Obito 2 1 R$ 40
2004 1 1 Registro 3 Natimorto 3 2 UFESP 30

3) My Java code.
...

String sql = "select fn_cad_servico_listar(null, null, null, null, null)";

PreparedStatement ps = null;
ResultSet rsCall = null;
Statement st = null;
ResultSet rs = null;

try {
ps = this.conn.prepareStatement(sql);
rsCall = ps.executeQuery();

if(rsCall != null && rsCall.next()) {
sql = "fetch all in \"" + rsCall.getString(1) + "\"";

st = this.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = st.executeQuery(sql);

while(rs != null && rs.next()) {
System.out.println("Year: " + rs.getInt("num_ano_exercicio").toString());
System.out.println("Month: " + rs.getInt("num_mes_exercicio").toString());
System.out.println("Activity: " + rs.getInt("cod_atividade").toString());
System.out.println("Service: " + rs.getInt("cod_servico").toString());
}
}
}
catch(Exception ex) {
System.out.println(ex.getMessage());
}

...

Thanks.

Marcelo

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Mofeed Shahin 2003-12-10 22:31:00 Re: Multi column foreign keys.
Previous Message John Sidney-Woollett 2003-12-10 14:49:41 JDBC function call: PS vs CS]