Transaction rollback not working

[teser3]

I am trying to get Transaction working with JDBC and Oracle database.
In my below example, if I try and fail the second insert by putting in
an invalid table name (called BadTableName), the rollback doesnt work
because the first insert makes it into the database. Please advise how
I can get this to work:

public class Serra
{
.....
public PreparedStatement prep;
...

public int methodOne(City city)
{
int one = 0;
try
{
prep = connection.prepareStatement("insert into States
(fieldOne,fieldTwo) values (?,?)");
prep.setString(1, city.getFieldOne());
prep.setString(2, city.getFieldTwo());
prep.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return one;
}


public int methodTwo(City city)
{
int two = 0;
try
{
prep = connection.prepareStatement("insert into BadTableName
(fieldThree,fieldFour) values (?,?)");
prep.setString(1, city.getFieldThree());
prep.setString(2, city.getFieldFour());
prep.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return two;
}

....

public int mainInsert(City city)
{
try
{
connection.setAutoCommit(false);
methodOne(city);
methodTwo(city);
connection.commit();
}
catch (SQLException ex)
{
try
{
connection.rollback();
}
catch(Exception e)
{
e.printStackTrace();
}
ex.printStackTrace();
}
finally
{
//closing part here for the ResultSet, Statement and
Connection
}

 

[Roedy Green]

try
{
connection.setAutoCommit(false);
methodOne(city);
methodTwo(city);
connection.commit();
}
catch (SQLException ex)
{
try
{
connection.rollback();

Don't you need some sort of "begin transaction" call?

 

[teser3]

Don't you need some sort of "begin transaction" call?

Any example of what the exact "begin transaction" part looks like?

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Don't you need some sort of "begin transaction" call?

No.

Plain JDBC does not use such.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I am trying to get Transaction working with JDBC and Oracle database.
In my below example, if I try and fail the second insert by putting in
an invalid table name (called BadTableName), the rollback doesnt work
because the first insert makes it into the database. Please advise how

public int methodOne(City city)
{
int one = 0;
try
{
prep = connection.prepareStatement("insert into States
(fieldOne,fieldTwo) values (?,?)");
prep.setString(1, city.getFieldOne());
prep.setString(2, city.getFieldTwo());
prep.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return one;
}

public int methodTwo(City city)
{
int two = 0;
try
{
prep = connection.prepareStatement("insert into BadTableName
(fieldThree,fieldFour) values (?,?)");
prep.setString(1, city.getFieldThree());
prep.setString(2, city.getFieldFour());
prep.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return two;
}

public int mainInsert(City city)
{
try
{
connection.setAutoCommit(false);
methodOne(city);
methodTwo(city);
connection.commit();
}
catch (SQLException ex)
{
try
{
connection.rollback();
}
catch(Exception e)
{
e.printStackTrace();
}
ex.printStackTrace();
}
finally
{
//closing part here for the ResultSet, Statement and
Connection
}

Does all 3 methods have the same connection object ?

Arne

 

[Real Gagnon]

I am trying to get Transaction working with JDBC and Oracle database.
In my below example, if I try and fail the second insert by putting in
an invalid table name (called BadTableName), the rollback doesnt work
because the first insert makes it into the database. Please advise how
I can get this to work:

That's because you catch the Exception in methodTwo() so mainCity() is
never notified.

Remove the try/catch and change the signature to

public int methodTwo(City city)throws SQLException

(and do the same for methodOne()!)

Bye.

 

[teser3]

That's because you catch the Exception in methodTwo() so mainCity() is
never notified.

Remove the try/catch and change the signature to

public int methodTwo(City city)throws SQLException

(and do the same for methodOne()!)

Bye.
--
Real Gagnon from Quebec, Canada
* Java, Javascript, VBScript and PowerBuilder code snippets
*http://www.rgagnon.com/howto.html
*http://www.rgagnon.com/bigindex.html

Real Gagnon,

I hope you get this message. It works great and thanks for your input
and solving my problem!