Subject: [Pljava-dev] Sample Code ResultSetHandle interface

From: Thomas.K.Hill at t-online.de (Thomas Hill)
Date: Sat, 15 Aug 2015 11:08:30 +0200

Hi,

I have installed pl/Java and am struggling to understand how the code in 
the calling Java program needs to look like if I want to use the 
resultsethandle interface and just call the sample function listsupers.
Calling listsupers from pgAdmin (select rte.listsupers() in my 
environment) works fine and returns two rows.

I tried
         CallableStatement cstmt = null;
         try {
             cstmt = conn.prepareCall("{ ? = CALL 
\"rte\".\"listsupers\"() }");
             cstmt.executeUpdate();

             ResultSet rs = cstmt.getResultSet();
             while (rs.next()){
                         rs.getString(1);
             }

but this is throwning an error when the executeUpdate() is called.

Can someone please help?

Thanks

---

From: Thomas.K.Hill at t-online.de (Thomas Hill)
Date: Sun, 16 Aug 2015 12:51:52 +0200

Am 15.08.2015 um 11:08 schrieb Thomas Hill:
> Hi,
>
> I have installed pl/Java and am struggling to understand how the code 
> in the calling Java program needs to look like if I want to use the 
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at lists.pgfoundry.org
> http://lists.pgfoundry.org/mailman/listinfo/pljava-dev
Note: The sample code getNames for the resultsetprovider interface works 
fine. The error thrown when trying to run the above listsupers sample is 
"Malformed function or procedure escape syntax at offset 2." but 
listsupers dowsn't have any in out our parameters!?

---

From: chap at anastigmatix.net (Chapman Flack)
Date: Wed, 19 Aug 2015 21:47:56 -0400

Hello,

Just to be sure I understand correctly....

1. Using PL/Java you have implemented the sample listSupers method,
   and you have registered this as a PostgreSQL stored procedure,

2. It works, as shown by directly sending SELECT rte.listSupers()
   to PostgreSQL,

3. You have a standalone Java program that makes a normal connection
   to PostgreSQL (using the PostgreSQL JDBC driver?) and you want this
   program to use the JDBC CallableStatement interface to call the
   procedure you defined earlier.

It seems to me that (1) and (2) indicate you have done the PL/Java and
SQL parts correctly, and the question concerns the behavior of
CallableStatement as implemented in the PostgreSQL JDBC driver.

I am afraid the usefulness of my answer may end here, as I have no
experience using the CallableStatement JDBC interface myself. It seems
for most purposes you could equivalently (modulo some possible optimization)
use an ordinary Statement and the same SELECT statement that you confirmed
working in (2). Out of curiosity, does that work for you?

Have you had any better response raising the question on pgsql-jdbc ?

Regards,
-Chap

---

From: chap at anastigmatix.net (Chapman Flack)
Date: Sun, 23 Aug 2015 12:25:55 -0400

> CallableStatement as implemented in the PostgreSQL JDBC driver.

Just to close the loop, Herr Hill found the answer in this section
of the pgsql-jdbc project documentation:

https://jdbc.postgresql.org/documentation/94/callproc.html#callproc-resultset-setof

It seems the PostgreSQL JDBC driver hasn't implemented CallableStatement
to work with a procedure returning a setof type, and the workaround is to
use a regular Statement with 'select * from theprocedure()'.

This is independent of PL/Java, but I suppose the pgsql-jdbc devs would
accept a patch removing that limitation.

-Chap