Re: JDBC transactions using MySQL / DBCP in Tomcat 5.0.28

2004-11-22 Thread Mark Matthews 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Trond G. Ziarkowski wrote:
 Hi all!
 
 In my webapp I do two db inserts into two different tables. If the 
 second one fails I want to rollback the first one. My code is something 
 like the following:
 
 Connection con = gotten from jndi DataSource
 DBBean db = new DBBean(con);
 try {
 con.setAutoCommit(false);
 
 Object o = db.getSomething();
 Object o2 = db.getSomethingElse();
 
 db.doInsert1(params);
 db.doInsert2(params);
 
 con.commit();
 } catch (SQLException sqle) {
 con.rollback();
 throw sqle;
 } finally {
 if (con != null) {
 con.close();
 }
 }
 
 I thought that this should do the trick, but if the second insert fails 
 the data inserted in the first insert are still there. I am using MySQL 
 4.0.21-7 with InnoDB and have tested that I can use transactions with 
 the MySQL client.
 
 Anyone got any tips?

Trond,

Are your tables in InnoDB storage? (i.e. TYPE=InnoDB used when you
created them)

What does 'SHOW CREAATE TABLE [table-name]' show for both tables?

If it doesn't say 'Type=InnoDB' at the end, transactions won't work :(

-Mark



- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 708 332 0507
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBokmYtvXNTca6JD8RApHwAKCh/E83lwfvqMOCEmJTAIzrUDWbogCfVQxL
04L03RNzQvpUg+wNtgLFkSo=
=9qCZ
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: JDBC transactions using MySQL / DBCP in Tomcat 5.0.28

2004-11-16 Thread David Boyer 
Prior to your first insert, do you need to execute an SQL Start
transaction? Otherwise, it sounds like autocommit will revert to the
default start of true.
 
http://dev.mysql.com/doc/mysql/en/COMMIT.html

 [EMAIL PROTECTED] 11/16/2004 12:14:13 PM 

Hi all!

In my webapp I do two db inserts into two different tables. If the 
second one fails I want to rollback the first one. My code is something

like the following:

Connection con = gotten from jndi DataSource
DBBean db = new DBBean(con);
try {
con.setAutoCommit(false);

Object o = db.getSomething();
Object o2 = db.getSomethingElse();

db.doInsert1(params);
db.doInsert2(params);

con.commit();
} catch (SQLException sqle) {
con.rollback();
throw sqle;
} finally {
if (con != null) {
con.close();
}
}

I thought that this should do the trick, but if the second insert fails

the data inserted in the first insert are still there. I am using MySQL

4.0.21-7 with InnoDB and have tested that I can use transactions with 
the MySQL client.

Anyone got any tips?


Regards

Trond

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: JDBC transactions using MySQL / DBCP in Tomcat 5.0.28

2004-11-16 Thread Phillip Qin 
Shouldn't jdbc 3.0 do the trick?

-Original Message-
From: David Boyer [mailto:[EMAIL PROTECTED] 
Sent: November 16, 2004 3:35 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: JDBC transactions using MySQL / DBCP in Tomcat 5.0.28


Prior to your first insert, do you need to execute an SQL Start
transaction? Otherwise, it sounds like autocommit will revert to the
default start of true.
 
http://dev.mysql.com/doc/mysql/en/COMMIT.html

 [EMAIL PROTECTED] 11/16/2004 12:14:13 PM 

Hi all!

In my webapp I do two db inserts into two different tables. If the 
second one fails I want to rollback the first one. My code is something

like the following:

Connection con = gotten from jndi DataSource
DBBean db = new DBBean(con);
try {
con.setAutoCommit(false);

Object o = db.getSomething();
Object o2 = db.getSomethingElse();

db.doInsert1(params);
db.doInsert2(params);

con.commit();
} catch (SQLException sqle) {
con.rollback();
throw sqle;
} finally {
if (con != null) {
con.close();
}
}

I thought that this should do the trick, but if the second insert fails

the data inserted in the first insert are still there. I am using MySQL

4.0.21-7 with InnoDB and have tested that I can use transactions with 
the MySQL client.

Anyone got any tips?


Regards

Trond

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




!DSPAM:419a64b9202861523128358!