Re: - Mailing list pgsql-jdbc

I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
the definition of a transaction?

"A transaction is an atomic unit of processing; it is eigher performed
in its entirety or not at all"

My understanding of this is that if one statement failed, all of the
following statements should fail.

On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
[]'s
Daniel Serodio