Re: JDBC behaviour - Mailing list pgsql-jdbc

From Andreas Joseph Krogh
Subject Re: JDBC behaviour
Date
Msg-id VisenaEmail.100.3a3abd9c7ae96d92.152f3c1e851@tc7-visena
Whole thread Raw
In response to Re: JDBC behaviour  (Sridhar N Bamandlapally <sridhar.bn1@gmail.com>)
List pgsql-jdbc
På torsdag 18. februar 2016 kl. 10:38:23, skrev Sridhar N Bamandlapally <sridhar.bn1@gmail.com>:
let me put this way
 
table employee ( id PrimaryKey, name )
 
In Java ( just little pseudo-code way )
 
try {
conn.setAutoCommit(false);
     try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); } catch ...
     try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); } catch ...
     try { executeUpdate("insert into employee(id,name) values(1, 'K2')"); } catch ...
conn.commit();
} catch ...
 
throws error 
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of transaction block
 
 
In PL/SQL ( similar error thrown when used BEGIN-END )
 
postgres=# begin;
BEGIN
postgres=# insert into employee values (1,'aa');
INSERT 0 1
postgres=# insert into employee values (2,'bb');
INSERT 0 1
postgres=# insert into employee values (3,'cc');
INSERT 0 1
postgres=# insert into employee values (1,'aa');
ERROR:  duplicate key value violates unique constraint "employee_pkey"
DETAIL:  Key (eid)=(1) already exists.
postgres=# insert into employee values (4,'dd');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
 
 
my question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END
 
You are free to ignore what we tell you about the transaction being invalid after an exception, and try all you want to issue new statements. But it will all result in errors like the above. You have to issue a ROLLBACK to proceed, and PG knows this so it refuses to do anything until you do.
 
I other words; There is no way to issue a statement without getting an error in a transaction marked as invalid. This is what transactions are for, guaranteeing consistent results.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-jdbc by date:

Previous
From: Sridhar N Bamandlapally
Date:
Subject: Re: JDBC behaviour
Next
From: John R Pierce
Date:
Subject: Re: JDBC behaviour