Обсуждение: Postgresql JDBC question

Поиск
Список
Период
Сортировка

Postgresql JDBC question

От
"Arun Desai"
Дата:
Hi,
        We are using Postgresql 7.3.3. We are encountering following
problems when JDBC is used to access Postgresql DB from a Java application
(using JDK1.3.1_06).

My test program does the following:
a. get a DB connection from a connection pool. For ex: con

b. Using Connection obtained in a) execute a function that returns a
Refcursor.
    In Java I do the following:

    String str = "begin; select sp_test_select (?, 'funcsursor');"
    PreparedStatement pstmtTmp = con.prepareStatement(str);
    pstmtTmp.setInt(1, 1);
    ResultSet rsTmp = pstmtTmp.executeQuery();
    PreparedStatement pstmt = conn.prepareStatement("FETCH ALL IN funcursor;
CLOSE funcursor;");
    ResultSet rs = pstmt.executeQuery();
    // Use rs
    .
    .
    rs.close();
    pstmt.close();
    rsTmp.close();
    pstmtTmp.close();

    This is my Postgresql Function that returns a refcursor.

    CREATE OR REPLACE FUNCTION sp_test_select (numeric, refcursor) returns
refcursor as '
    DECLARE
        id_no alias for $1;
        refc alias for $2;
        BEGIN
            OPEN refc FOR SELECT * FROM testtable WHERE idno = id_no;
        RETURN refc;
    END;
    ' LANGUAGE 'plpgsql';


c. Using the same Connection object obtained in a) execute a function that
does not return Refcursor but updates some table. This step is carried out
after executing step     b). This update is failing. No exception but update
does not take place. If I skip step b) and carry out only steps a) and c)
update works fine.


Am I doing something wrong in step b).


Also where do I get the latest JDBC driver patch for Postgresql 7.3.3.


Any help will be highly appreciated.


Thanks,
Arun Desai.

Re: Postgresql JDBC question

От
Paul Thomas
Дата:
On 09/07/2003 11:27 Arun Desai wrote:
> Hi,
>         We are using Postgresql 7.3.3. We are encountering following
> problems when JDBC is used to access Postgresql DB from a Java
> application
> (using JDK1.3.1_06).
>
> My test program does the following:
> a. get a DB connection from a connection pool. For ex: con
>
> b. Using Connection obtained in a) execute a function that returns a
> Refcursor.
>     In Java I do the following:
>
>     String str = "begin; select sp_test_select (?, 'funcsursor');"
Why the begin? To use transactions via JDBC you should
setAutoCommit(false) on the connection before recreating the statement.
This is basic JDBC. Refer to the tutorials at java.sun.com.

>     PreparedStatement pstmtTmp = con.prepareStatement(str);
>     pstmtTmp.setInt(1, 1);
>     ResultSet rsTmp = pstmtTmp.executeQuery();
>     PreparedStatement pstmt = conn.prepareStatement("FETCH ALL IN
> funcursor;
> CLOSE funcursor;");
>     ResultSet rs = pstmt.executeQuery();
>     // Use rs
>     .
>     .
>     rs.close();
>     pstmt.close();
>     rsTmp.close();

And here you should be commiting the transaction with pstmtTmp.commit().

>     pstmtTmp.close();
>
>     This is my Postgresql Function that returns a refcursor.
>
>     CREATE OR REPLACE FUNCTION sp_test_select (numeric, refcursor)
> returns
> refcursor as '
>     DECLARE
>         id_no alias for $1;
>         refc alias for $2;
>         BEGIN
>             OPEN refc FOR SELECT * FROM testtable WHERE idno = id_no;
>         RETURN refc;
>     END;
>     ' LANGUAGE 'plpgsql';
>
>
> c. Using the same Connection object obtained in a) execute a function
> that
> does not return Refcursor but updates some table. This step is carried
> out
> after executing step     b). This update is failing. No exception but
> update
> does not take place. If I skip step b) and carry out only steps a) and c)
> update works fine.

Obviously it would.

> Am I doing something wrong in step b).

Yes. You're starting a transaction and not committing it.


HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+