Re: JDBC behaviour

På torsdag 18. februar 2016 kl. 10:38:23, skrev Sridhar N Bamandlapally <
sridhar(dot)bn1(at)gmail(dot)com <mailto:sridhar(dot)bn1(at)gmail(dot)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. Youhave 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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>