[Pljava-dev] Sample Code ResultSetHandle interface

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

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

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

On 08/19/15 21:47, Chapman Flack wrote:

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

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