What's wrong in this transaction?

[gbattine]

Hi guys,
i've developed a jsf application.
I do lots of prepared statement in a session,what i want is do all in a
transaction. So i've created an object that collects all the prepared
statement of the session.
When the user click a final submit botton transaction has to b
executed.
My problem is that if a sql exception comes where object is executed
rollback isn't called and transaction is partial executed!!!!!!!
Why this?
Can you help me finding error in my simple code?
Thanks very much

This is my method
public String executeTransaction() throws SQLException {
Connection conn=null;
DataSource dataSource = Singleton.getInstance().getDataSource();
conn = dataSource.getConnection();




Experiment exp = (Experiment) ViewUtils
.eval("#{experiment}");


List results=new ArrayList();
ResultSet rs=null;
Statement stmt=null;
boolean allgood=false;
if (conn != null) {

conn.setAutoCommit(false);



for (int i=0;i<exp.getExperiments().size();i++){
try{

((java.sql.PreparedStatement)
exp.getExperiments().get(i)).executeUpdate();
if(i==(exp.getExperiments().size()-1))
conn.commit();
}
catch (Exception e) {
conn.rollback();
e.printStackTrace();
}

}

}
else System.out.println("connessione non disponibile");



return "submit";
}

please help me

 

[vahan]

I think it will work:

public String executeTransaction() throws java.sql.SQLException {
java.sql.Connection conn = null;
javax.sql.DataSource dataSource =
Singleton.getInstance().getDataSource();
conn = dataSource.getConnection();

Experiment exp = (Experiment) ViewUtils.eval("#{experiment}");

List results = new ArrayList();
ResultSet rs = null;
Statement stmt = null;
boolean allgood = false;
if (conn != null) {

conn.setAutoCommit(false);
//you can change Transaction Isolation level

//conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_READ_COMMITTED);
conn.setSavepoint();//set rollback point

try {
for (int i = 0; i < exp.getExperiments().size(); i++) {

((java.sql.PreparedStatement)
exp.getExperiments().get(i)).executeUpdate();
if (i == (exp.getExperiments().size() - 1)) {
conn.commit();
}

}
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
}

} else {
System.out.println("connessione non disponibile");
}

return "submit";
}

 

[gbattine]

Thanks vahan...
but it doesn't work!!!
I've the same error!
Now i've used,following you
package giu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

public class QueryManager extends BaseBean{
private int key;

public QueryManager(){}

public String executeTransaction() throws java.sql.SQLException {
java.sql.Connection conn = null;
javax.sql.DataSource dataSource =
Singleton.getInstance().getDataSource();
conn = dataSource.getConnection();


Experiment exp = (Experiment) ViewUtils.eval("#{experiment}");


List results = new ArrayList();
ResultSet rs = null;
Statement stmt = null;
boolean allgood = false;
if (conn != null) {


conn.setAutoCommit(false);
//you can change Transaction Isolation level


//conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_READ_COMMITT­ED);

conn.setSavepoint();//set rollback point


try {
for (int i = 0; i < exp.getExperiments().size(); i++) {


((java.sql.PreparedStatement)
exp.getExperiments().get(i)).executeUpdate();
if (i == (exp.getExperiments().size() - 1)) {
conn.commit();
}


}
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
}


} else {
System.out.println("connessione non disponibile");
}


return "submit";
}

}

but never,when sql exception comes rollback doesn't act!
Please help me..should i do other things for support transactions?
I'm using
mysql-connector-java-3.1.13.zip

and innodb table with mysql...what can i do?
Please help me

 

[Oliver Wong]

but never,when sql exception comes rollback doesn't act!
Please help me..should i do other things for support transactions?
I'm using
mysql-connector-java-3.1.13.zip

and innodb table with mysql...what can i do?

Have you read this?
http://dev.mysql.com/doc/refman/5.0/en/commit.html

- Oliver

 

[gbattine]

Oliver Wong ha scritto:

Have you read this?
http://dev.mysql.com/doc/refman/5.0/en/commit.html

yes i've read it but it seems like my code...now i've added

int x=conn.TRANSACTION_NONE;

System.out.println("Support for transactions "+x);

and it gives me 0!!!!!!!
What does mean?
Transactions are not supported?
Please help me...

 

[Rhino]

Oliver Wong ha scritto:


yes i've read it but it seems like my code...now i've added

int x=conn.TRANSACTION_NONE;

System.out.println("Support for transactions "+x);

and it gives me 0!!!!!!!
What does mean?
Transactions are not supported?
Please help me...

All you have done is print the value of a Field (constant) named
Connection.TRANSACTION_NONE and found out that its value is 0. Don't you
understand the difference between a Field and a Method? If not, you
desperately need a basic Java course before you start writing database
code - or any other code, for that matter!

If you are trying to figure out if your database supports transactions, get
a DatabaseMetaData object and then use the supportsTransactions() method. If
the result is a boolean true, your database supports transactions.

 

[Furious George]

All you have done is print the value of a Field (constant) named
Connection.TRANSACTION_NONE and found out that its value is 0. Don't you
understand the difference between a Field and a Method? If not, you
desperately need a basic Java course before you start writing database
code - or any other code, for that matter!

If you are trying to figure out if your database supports transactions, get
a DatabaseMetaData object and then use the supportsTransactions() method. If
the result is a boolean true, your database supports transactions.

OK, you have mastered the basic Java course, but you obviously have no
real working experience in the field. Otherwise you would know that if
the supportsTransactions() method returns true, your database claims
transaction support. You would know the difference between claiming
transaction support and supporting transactions.

 

[gbattine]

Furious George ha scritto:

OK, you have mastered the basic Java course, but you obviously have no
real working experience in the field. Otherwise you would know that if
the supportsTransactions() method returns true, your database claims
transaction support. You would know the difference between claiming
transaction support and supporting transactions.

excuse me,but i don't understood what does mean...
now i've better understood my problem..,it's a strange problem....

I've tried to know if transactions are supported,and i've done this

Connection conn = dataSource.getConnection();
System.out.println(conn.getMetaData().supportsTransactions());
conn.setAutoCommit(false);
System.out.println("We are in autocommit
mode?"+conn.getAutoCommit());

in the console my application prints true to transaction support and
falso to autocommit mode.
That is there is support for transactions and i've disabled autocommit
mode,OK!
The problem is that when i execute the statements in my list it commit
each statement!
Why?
It doesn't wait form my commit or rollback,it commit each statemente!
Please help me,what i miss in my code?

 

[Furious George]

Furious George ha scritto:

OK, you have mastered the basic Java course, but you obviously have no
real working experience in the field. Otherwise you would know that if
the supportsTransactions() method returns true, your database claims
transaction support. You would know the difference between claiming
transaction support and supporting transactions.

excuse me,but i don't understood what does mean...
now i've better understood my problem..,it's a strange problem....

I've tried to know if transactions are supported,and i've done this

Connection conn = dataSource.getConnection();
System.out.println(conn.getMetaData().supportsTransactions());
conn.setAutoCommit(false);
System.out.println("We are in autocommit
mode?"+conn.getAutoCommit());

in the console my application prints true to transaction support and
falso to autocommit mode.
That is there is support for transactions and i've disabled autocommit
mode,OK!
The problem is that when i execute the statements in my list it commit
each statement!
Why?
It doesn't wait form my commit or rollback,it commit each statemente!
Please help me,what i miss in my code?

I am sorry for not being more explicit. I was disagreeing with the
previous poster's assertion that if the supportsTransactions() method
returns true, then the database supports transactions.

I meant that sometimes a database will claim that it supports
transactions when in fact it does not (or does not support them
correctly). If that is the case, then there is something wrong with
the database (not your code). This is sometimes called vaporware.
However, sorry for wasting your time on a technicality, but I do not
think this is your problem.

 

[Rhino]

OK, you have mastered the basic Java course, but you obviously have no
real working experience in the field.

Obviously?? I've been using relational databases - and teaching them
professionally - for over 20 years. Now don't you feel foolish?

Otherwise you would know that if
the supportsTransactions() method returns true, your database claims
transaction support. You would know the difference between claiming
transaction support and supporting transactions.

Actually I _do_ know the difference between claiming support and actually
providing it. I just haven't come across that many instances where vendors
claimed support that wasn't there. I made the assumption that MySQL was
being truthful in its claims. If you know that MySQL is lying (or
exaggerating) about supporting transactions, I will stand corrected. But
just the fact that some vendors occasionally fudge the truth about some
features of their systems does NOT prove that supportsTransactions() is
false in _this_ case.

 

[Rhino]

Furious George ha scritto:

OK, you have mastered the basic Java course, but you obviously have no
real working experience in the field. Otherwise you would know that if
the supportsTransactions() method returns true, your database claims
transaction support. You would know the difference between claiming
transaction support and supporting transactions.

excuse me,but i don't understood what does mean...
now i've better understood my problem..,it's a strange problem....

I've tried to know if transactions are supported,and i've done this

Connection conn = dataSource.getConnection();
System.out.println(conn.getMetaData().supportsTransactions());
conn.setAutoCommit(false);
System.out.println("We are in autocommit
mode?"+conn.getAutoCommit());

in the console my application prints true to transaction support and
falso to autocommit mode.
That is there is support for transactions and i've disabled autocommit
mode,OK!
The problem is that when i execute the statements in my list it commit
each statement!
Why?
It doesn't wait form my commit or rollback,it commit each statemente!
Please help me,what i miss in my code?

I use MySQL regularly and turn off autoCommit most of the time; it only
commits when I want it to commit, not after each statement.

The problem is probably in the code that you don't show.

That is _NOT_ an invitation for you to post a long program (or send it to me
for my personal inspection!); I simply don't have the time for that.

Try stepping through your program with a good debugger and watch the
autoCommit status carefully. Perhaps you are turning autoCommit back on
after you turn it off. Or maybe you are doing things that cause commits that
aren't commit() statements. For example, in some relational databases,
reaching the normal end of a program without crashing causes all
uncommitted work to be committed even if you haven't coded a commit() at
that point.

 

[Furious George]

Obviously?? I've been using relational databases - and teaching them
professionally - for over 20 years. Now don't you feel foolish?

Actually, no. I was just nitpicking one statement. Don't take it
personally.

Actually I _do_ know the difference between claiming support and actually
providing it. I just haven't come across that many instances where vendors
claimed support that wasn't there. I made the assumption that MySQL was
being truthful in its claims. If you know that MySQL is lying (or
exaggerating) about supporting transactions, I will stand corrected. But
just the fact that some vendors occasionally fudge the truth about some
features of their systems does NOT prove that supportsTransactions() is
false in _this_ case.

I believe MySQL is trustworthy in this respect.

Sorry, for any offense given.

 

[Rhino]

Actually, no. I was just nitpicking one statement. Don't take it
personally.

Gee, how could I possibly be offended at you dismissing me as a rank newbie
when I have been working with databases for 20+ years?

I believe MySQL is trustworthy in this respect.

Sorry, for any offense given.

Okay.