Re: connection/statement becomes unstable following SQLException

I guess the most obvious question is do you call Connection.rollback() after
the failed statement? It would be helpful to see the transaction
context code.

Rob Prowel wrote:

>consider the following:
>
>table lookup_industry(industry varchar(20) not null
>unique);
>
>and the following code:
>
>------------------------------------------------------------
>public static void main(String [] arg) {
>
> lookupIndustry conn=new lookupIndustry();
> try {
> conn=new lookupIndustry((new
>jobDBconnection()).conn); }
> catch(Exception e) {
> System.exit(1); }
> // connect to the db
>
> System.out.println("---------------------");
> for (int i=0; i<arg.length; i++) {
> try {
> System.out.println("adding
>industry("+arg[i]+")");
> conn.addIndustryList(arg[i]);
>}
> // attempt to add a new record
> catch (Exception e) {
>
>System.out.println(arg[i]+":"+Integer.toString(i)+
> " "+e.toString()); }
> finally {
>System.out.println("try>"+Integer.toString(i)); }
>
>System.out.println("loop>"+Integer.toString(i));
> }
> // should add new records and print
>alerts for each record that
> // already exists
>
> System.out.println("---------------------");
> System.exit(0);
> }
>
>--------------------------------------------------------------------
>
>And the following output:
>
>$ jdbcrun the geese fly high
>---------------------
>adding industry(the)
>the:0 java.sql.SQLException: ERROR: Cannot insert a
>duplicate key into unique index
>lookup_industry_industry_key
>
>try>0
>loop>0
>adding industry(geese)
>insert rv=1
>try>1
>loop>1
>adding industry(fly)
>fly:2 java.sql.SQLException: ERROR: Cannot insert a
>duplicate key into unique index
>lookup_industry_industry_key
>
>try>2
>loop>2
>adding industry(high)
>insert rv=1
>try>3
>loop>3
>---------------------
>
>----------------------------------------------------
>
>notice that the only two iterations that generate an
>exception are the first and thrid ones...even though
>(the,geese,fly,high) are already in the database and
>the values are constrained to be unique in the table
>definition.
>
>It seems that there is a bug in the Connection or
>Statement logic in the JDBC driver that isn't
>resetting things immediately after an exception
>occurs.
>
>I have one Connection open and addIndustryList()
>creates a new Statement object for every insert that
>it does to the table. If the entry already exists in
>the table then addIndustryList() throws an
>SQLException object that I SHOULD be able to catch and
>ignore, just moving on to add the next record. Only
>other thing I should mention is that I turn autocommit
>off and commit each transaction as it is entered.
>
>I believe I've uncovered a bug and would like someone
>who owns or is very familiar with the code to reply.
>I can then send them more information such as my
>complete classes and the psql script to create the
>table in question.
>
>Finally, here's my platform information:
>
>i586 Linux 2.4.21
>PostgreSQL 7.2.1
>pgjdbc3.jar
>J2SE 1.4.2
>
>Thanks.
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
>http://calendar.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
>