connection/statement becomes unstable following SQLException

From: Rob Prowel <tempest766(at)yahoo(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: connection/statement becomes unstable following SQLException
Date: 2003-08-23 04:36:55
Message-ID: 20030823043655.89390.qmail@web60002.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Tenny 2003-08-23 15:29:55 Re: connection/statement becomes unstable following SQLException
Previous Message Garrick Dasbach 2003-08-22 19:40:20 Driver.acceptsURL(String) not working correctly