Can I get a resultset back from a stored procedure using Callable Statement?

Поиск
Список
Период
Сортировка
От Arun Jacob
Тема Can I get a resultset back from a stored procedure using Callable Statement?
Дата
Msg-id A9601C3D40FBD3119E8000508BA55436037BA210@f5-exchange1.win.net
обсуждение исходный текст
Ответы Re: Can I get a resultset back from a stored procedure using  (Dave Cramer <Dave@micro-automation.net>)
Список pgsql-jdbc

Right now the only way that I know of to retrieve a resultset back from a stored procedure is a two step process:

(1) have the stored procedure return a cursor.
(2) execute "select [stored procedure]([any parameters])
(3) get the name of the returned cursor.
(4) execute a FETCH FROM [cursor name].

my stored procedure looks like:

    CREATE FUNCTION select_all_devices(INTEGER) RETURNS REFCURSOR AS '
    DECLARE
        rc1 REFCURSOR;
    BEGIN

        open rc1 for SELECT goid FROM Device where goid = $1;
        return rc1;

    END
    ' LANGUAGE 'plpgsql';

The java that retrieves a resultset back is:

            ResultSet setCursor;
        ResultSet setReal;
       
        // assume we've got the connection already.

        Statement stmtCursor = conn.createStatement();
       
        // get the cursor
            setCursor = stmtCursor.executeQuery(sql);
            setCursor.next();
            String cursor_name = setCursor.getString(1);

        // get what the cursor is pointing at

            PreparedStatement stmtReal = conn.prepareStatement("fetch all from \"" + cursor_name + "\"");
            setReal = stmtReal.execute();

This two step process is cumbersome and seems to invalidate using stored procedures to retrieve data. We would like to use stored procs to retrieve data so that the procs completely encapsulate the schema.

Now that the JDBC driver has CallableStatement support, I was wondering if anyone knows how to get a ResultSet mapping to the contents of the cursor-- not ResultSet containing the cursor --  back from a CallableStatement. The ResultSet returned right now contains the cursor, which we still have to fetch from into another ResultSet. Am I missing something fundamental, or is this the only way to get a ResultSet back that maps to the original Select statement in the stored procedure above?

Any help would be greatly appreciated.

Thanks,

- Arun

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: tony
Дата:
Сообщение: Re: Netscape 4.7 browser requests cause different behavior
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Can I get a resultset back from a stored procedure using