From: holger.fleischmann at manroland.com (holger.fleischmann at
 manroland.com)
Date: Wed, 4 Jun 2008 13:37:08 +0200
Subject: [Pljava-dev] Using JDBC in PL/Java 1.4.0: Statement.executeQuery()
	automatically closes the statement?
Message-ID: 
 <5D63919D95F87E4D9D34FF7748CE2C2A0127B52F@ARVMAIL1.mra.roland-man.biz>

Dear PL/Java Team

Thank you for PL/Java, it is a great tool!

However, this post is about a problem that occurs since switching from
PL/Java 1.3.0 to 1.4.0 (forced by migrating to a new PostgreSQL
version). 
It would be very nice if somebody could have a look at it. I think it is
a bug in 1.4.0. Thank you:-)

When executing SQL queries inside PL/Java functions it seems that the
java.sql.Statement now gets automatically closed by
Statement.executeQuery(). This leads to a SQLException: "statement is
closed" when calling Statement.getWarnings() afterwards. This behavior
is new in 1.4.0 (tested both on PostgreSQL 8.3.1 and 8.1.11). 
[This is exactly what the Spring class JdbcTemplate - which I am forced
to use in this context - does in a query: executeQuery() and then
getWarnings(). Therefore simply not calling getWarnings() is not an
option for my problem.]

Here is an example function to reproduce the SQLException:

--- SNIP --- test class to create a jar from:

import java.sql.*;
import java.util.logging.*;

public class PlJavaTest
{
    private static void doTest() throws Exception
    {
        Connection connection =
DriverManager.getConnection("jdbc:default:connection");
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM
information_schema.tables");

        try {
            // this will throw a SQLException:
            statement.getWarnings();
        }
        catch (Exception e) {
            Logger.getAnonymousLogger().log(Level.SEVERE, "Caught
exception in getWarnings()", e);
            throw e;
        }
    }
}

--- SNIP --- SQL commands to install the jar and call the test PL/Java
function:

# select sqlj.remove_jar('pljavatest', false);
select sqlj.install_jar('file:///.../pljavatest.jar', 'pljavatest',
false);
select sqlj.set_classpath('public', 'pljavatest');
create or replace function pljavatest() returns void as
'PlJavaTest.doTest' language 'java' volatile;
select pljavatest();

--- SNIP --- output of last SQL command:

pljavatest=# select pljavatest();
ERROR:  04 Jun 08 13:25:26 PlJavaTest Caught exception in getWarnings()
java.sql.SQLException: getWarnings: Statement is closed
        at
org.postgresql.pljava.jdbc.SPIStatement.getWarnings(SPIStatement.java:31
4)
        at PlJavaTest.doTest(PlJavaTest.java:14)

--- SNIP ---

As a workaround I am currently using wrapper classes around the original
Connection and Statement which override the getWarnings() method. This
works but is certainly not a clean solution...

Regards
Holger Fleischmann

Achtung: Neue E-Mail-Adresse! Attention: New e-mail-address! holger.fleischmann at manroland.com

Sollten Sie nicht der vorgesehene Empfanger sein, so bitten wir hoflich um eine Mitteilung und um Loschung der Nachricht.
Vielen Dank fur Ihre Unterstutzung.
If you are not the addressee, please inform us immediately that you have received this e-mail by mistake, and delete it.
We thank you for your support. 

--------------------------------------------------------
manroland AG
Vorsitzender des Aufsichtsrates: Hanno C. Fiedler
Vorstand: Gerd Finkbeiner (Vorsitzender), Dr. Ingo Koch, Dr. Markus Rall, Paul Steidle
Sitz der Gesellschaft: Offenbach am Main, Registergericht: Amtsgericht Offenbach HRB-Nr. 42592
USt-Ident-Nr. DE 250200933