Very Computer

during a single transaction I wanted to store a cuple of
rows in a database. If the key already exists the program
says "failed" otherwise it says "ok". At the end the transaction
is commited.

I expected that if there would by a single "failed" then none
of the "ok"'s would have been stored in the database. But that
was not the case!

In Sun's Java Tutorial it is said that you should use "rollback"
but this behavior isn't right, isn't it?

From the programmers view the transaction is only partly commited.
When I was trying this from the SQL console the database behaves
like expected. Is this an error in the JDBC driver? Does it start
a new transaction after an exception?

In the example I use Postgresql with one simple table in which
onw row already exists:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class dbtest {
    public static void main(String[] args) {
        Connection con;
        Statement stm;

        // suppose we have this:
        // create table a (a int primary key);
        // insert into a values (1);

        try {
            Class.forName("org.postgresql.Driver");

            con=DriverManager.getConnection("jdbc:postgresql://host/db", "user", "password");

            con.setAutoCommit(false);
            System.out.println("autocommit: "+con.getAutoCommit());

            stm = con.createStatement();

            for (int i=1; i<=2; i++) {
                try {

                    System.out.print("insert "+i+"...");
                    stm.executeUpdate("insert into a values("+i+")");
                    System.out.println("ok");

                } catch (SQLException e) {
                    System.out.println( "failed" );
                    System.out.println( e.getMessage() );
                }
            }

            System.out.print("commit...");
            con.commit();
            System.out.println("ok");

        }
        catch (SQLException e) {
            System.out.println("failed");
            System.out.println( e.getMessage() );
        }
        catch (ClassNotFoundException e) {
            System.out.println( e.getMessage() );
        }
    }

Quote:}

Java is right. Don't commit anything you don't want committed. The
application still has control after receiving an exception. It is up to
the application to decide whether the exception means that the
application's transaction is still good or bad. This is usually the
same in the DBMS. Some DBMSes have an optional configuration
where any failure during a transaction means that it should roll back,
but this is not the default, nor is it frequently used.
   Catch exceptions and do a rollback in the catrch block. Do the
commit() as the last thing in the try block (as you did).

Joe Weinstein at BEA

Quote:

> From the programmers view the transaction is only partly commited.
> When I was trying this from the SQL console the database behaves
> like expected. Is this an error in the JDBC driver? Does it start
> a new transaction after an exception?

> In the example I use Postgresql with one simple table in which
> onw row already exists:

> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.Statement;
> import java.sql.SQLException;

> public class dbtest {
>     public static void main(String[] args) {
>         Connection con;
>         Statement stm;

>         // suppose we have this:
>         // create table a (a int primary key);
>         // insert into a values (1);

>         try {
>             Class.forName("org.postgresql.Driver");

>             con=DriverManager.getConnection("jdbc:postgresql://host/db", "user", "password");

>             con.setAutoCommit(false);
>             System.out.println("autocommit: "+con.getAutoCommit());

>             stm = con.createStatement();

>             for (int i=1; i<=2; i++) {
>                 try {

>                     System.out.print("insert "+i+"...");
>                     stm.executeUpdate("insert into a values("+i+")");
>                     System.out.println("ok");

>                 } catch (SQLException e) {
>                     System.out.println( "failed" );
>                     System.out.println( e.getMessage() );
>                 }
>             }

>             System.out.print("commit...");
>             con.commit();
>             System.out.println("ok");

>         }
>         catch (SQLException e) {
>             System.out.println("failed");
>             System.out.println( e.getMessage() );
>         }
>         catch (ClassNotFoundException e) {
>             System.out.println( e.getMessage() );
>         }
>     }
> }