Re: - Mailing list pgsql-jdbc

Hi all,

I was wondering if there's any chance of this behavior to change in the
future ?
I mean will it be possible to continue a transaction after one of the SQLs
failed, by only rolling back what that query did ?
In many real life applications recovery is very possible after a failed
query, and (the not failed part of) the transaction should be committed.
This is one of the big differences in behavior between Postgres and Oracle,
making life hard for porting...

Cheers,
Csaba.

-----Ursprungliche Nachricht-----
Von: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]Im Auftrag von Karl Goldstein
Gesendet: Dienstag, 5. November 2002 17:15
An: pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC]


Patrice,

Yes, that is exactly what I realized yesterday (in my case, it was a
previous query in the same
transaction that I ported from an Oracle-based app that wasn't parsing right
in postgresql).

David asked me to write a simple test case that demonstrates the problem.
Here it is:

-- BEGIN NoResultTest.java --

import java.io.*;
import java.sql.*;
import java.text.*;

public class NoResultTest {

  Connection db;
  Statement st;

  public void testNoResult(String args[]) throws Exception {

    String url = args[0];
    String usr = args[1];
    String pwd = args[2];

    // Load the driver
    Class.forName("org.postgresql.Driver");

    // Connect to database
    System.err.println("Connecting to Database URL = " + url);
    db = DriverManager.getConnection(url, usr, pwd);

    System.err.println("Connected...Now creating a statement");
    st = db.createStatement();

    // create table outside of transaction to simulate a pre-existing table
    st.executeUpdate("create table empty (empty_id integer)");

    // No results error does not occur unless auto-commit is turned off
    db.setAutoCommit(false);

    try {
      PreparedStatement ps =
        db.prepareStatement("select empty_id emptyID from empty");
      ResultSet rs = ps.executeQuery();
      rs.next();
      rs.close();
    } catch (SQLException e) {
      // should always throw a parse exception
      e.printStackTrace();
      // this fixes the problem
      // db.rollback();
    }

    PreparedStatement ps =
      db.prepareStatement("select empty_id AS emptyID from empty");
    ResultSet rs = ps.executeQuery();
    System.err.println("Has result from well-formed query: " + rs.next());
    rs.close();

    st.executeUpdate("drop table empty");

    // Finally close the database
    System.err.println("Now closing the connection");
    st.close();
    db.close();
  }

  public static void main(String args[]) throws Exception {

    NoResultTest test = new NoResultTest();
    test.testNoResult(args);
  }
}
-- END NoResultTest.java --

Here is the output:

[karl@phoenix karl]$ java -version
java version "1.4.1-rc"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-rc-b19)
Java HotSpot(TM) Client VM (build 1.4.1-rc-b19, mixed mode)
[karl@phoenix karl]$ java -cp .:pgjdbc2.jar NoResultTest
jdbc:postgresql:karl k\
arl karl
Connecting to Database URL = jdbc:postgresql:karl
Connected...Now creating a statement
java.sql.SQLException: ERROR:  parser: parse error at or near "emptyid"

        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
        at org.postgresql.Connection.ExecSQL(Connection.java:398)
        at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
        at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
        at NoResultTest.testNoResult(NoResultTest.java:32)
        at NoResultTest.main(NoResultTest.java:57)
Exception in thread "main" No results were returned by the query.
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
        at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
        at NoResultTest.testNoResult(NoResultTest.java:42)
        at NoResultTest.main(NoResultTest.java:57)

Thanks,

Karl

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster