Thread: JDBC function call (getting resultset)

JDBC function call (getting resultset)

From
marcelospbr@globo.com
Date:
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





Re: JDBC function call (getting resultset)

From
Kris Jurka
Date:

On Wed, 10 Dec 2003 marcelospbr@globo.com wrote:

> 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?

Perhaps take a look at this:

http://developer.postgresql.org/docs/postgres/jdbc-callproc.html

I believe you will also need to use the 7.4 jdbc driver even though you
are running against a 7.3 database.


Kris Jurka