This week's book giveaway is in the General Computing forum. We're giving away four copies of Arduino in Action and have Martin Evans, Joshua Noble, and Jordan Hochenbaum on-line! See this thread for details.
I have this JSP file in which i'm updating 3 tables one after another. Now the problem that i'm facing is that if some error occurs while updating the second table, the third table doesn't get updated but the first table has the updated value. How can i implement such an execute Update in which all 3 tables get updated or none gets updated.
You can specify a point using the setSavepoint(). It enables you to Rollback the transaction upto the savepoint if there is an error in the transaction.
I have this JSP file in which i'm updating 3 tables one after another. Now the problem that i'm facing is that if some error occurs while updating the second table, the third table doesn't get updated but the first table has the updated value.
You shouldnt be doing this. Ideally a jsp should be concerned with the view only. Such things should be done from a JavaBean.
How can i implement such an execute Update in which all 3 tables get updated or none gets updated.
By default the commit fetaure of a Connection is set to true. ie the Connection always commits after every execution. You can test this by calling getAutoCommit() immediately after obtaining a Connection object. So what u have to do is to set it to false and manually commit if all three transactions have gone thru.
ram.
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
thank u all for reply....
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
I tried your code ... but it gave me the following error when the 2nd table statement had an error in it.
Please can any one tell me where al i going wrong and what does the error "Some non-transactional changed tables couldn't be rolled back" means and how i can rectify this error.
Thank you
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
and yeh, i checked the database it has rolled back the 1st table that got updated...but then why is this error getting generated...? Please tell me how can i avoid this error...! Please help..!
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
i have used this statement to see my table type SHOW TABLE STATUS LIKE 'table_name'
my table type is MyISAM. Does this mean that the table is non-transactional table. If so, then how do i change it to a transactional table...
i have never come across such an exception in Oracle, which's what i have been using for past 2-3 years. However googling i got this info (do u use mysql), see if it helps
Some non transactional changed tables couldn't be rolled back Error If you get the error/warning: Warning: Some non transactional changed tables couldn't be rolled back when trying to do a ROLLBACK, this means that some of the tables you used in the transaction didn't support transactions. These non transactional tables will not be affected by the ROLLBACK statement. The most typical case when this happens is when you have tried to create a table of a type that is not supported by your mysqld binary. If mysqld doesn't support a table type (or if the table type is disabled by a startup option) , it will instead create the table type with the table type that is most resembles to the one you requested, probably MyISAM. You can check the table type for a table by doing: SHOW TABLE STATUS LIKE 'table_name' You can check the extensions your mysqld binary supports by doing: show variables like 'have_%'
a) Finding out what rule your query is breaking ( eg inserting duplicate records ) and preventing that from happening, or: b) Chaning your table type to one that supports transactions. Note that this will get rid of your error, but not fix the logic c) If you dont care u can ignore the exception which would IMHO be a bad thing to do.
ram.
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
I used this to check the system varaibles ' show variables like 'have_%'
It gave the following output: Variable_name Value
have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO
Does it mean that my database doesn't support innodb type tables... if so how can i rectify this issue too.....
If you decide to work with BDB tables, you can either install a MySQL-Max binary or recompile MySQL from source using the --with-bdb flag. BDB tables are available for most operating systems, including Windows, Linux, and Mac OS X.
And if you don't have Gemini, you can get an installer that includes MySQL, PHP, mod_perl and other goodies from the NuSphere website.
For InnoDB, the same site says
This table type is now included by default in most MySQL installations, so if you get current binary from mysql.com, you'll have InnoDB tables.
For each of these table types you should append type=<innodb/gemini/bdb> after your create statement. or to migrate existing tables, use ALTER table table_name type=<TYPE_NAME>;
PS: This has been good for me, I too learned a lot about MySql :-)
ram.
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
I have found that the tables that i'm working on are of the nature "non-transactional"... i have tried to change the type of the table using ALTER TABLE table_name TYPE = InnoDB. But for some reason the type doesnot change. So please can any one tell me how can i implement some thing similar to ROLL BACK for non-transactional table as ROLL BACK doesnt work for non-transactional table.
Rekha, As far as I know (been reading a lot since your post that things are failing in MySql), you are not able to change the type bcos you do not have InnoDB (as you yourself have said in a prev post). Your table types are all ISAM which's basically non-transactional. Each statement behaves likes its own transaction and is committed. So there's no way IMHO to do what u require. You should consider installing the other table types.(check my prev post). Ofcourse I may be wrong, and people who know a lot better and of whom there are, Iam sure, many can show u the light. cheers, ram.
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
I have checked it out on the net, to maintain integrity in non-transactional table the follwing steps would be okay, 1. lock Table 2. Test Condition 3. Update if everything ok 4. Unlock Table
As somebody pointed out, by default the jdbc connection is set to true. Set the autocommit to false, and commit in the finally block of your code, and if there is an error in any of the update, rollback the updates.
Braj.
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
Hi all,
I treid the above code, and it gives me the following error: "javax.servlet.ServletException: Transactions not supported" Some one told me that i should be implementing a transaction object. and then try a trans.start() and trans.commit().How can i do that...??? Please can any one suggest me any ideas. Please.
Thanxs
Rekha Pande
Ranch Hand
Joined: Jan 29, 2004
Posts: 145
posted
0
Hi all.. my problem is solved. The problem was not with the code but with the database log status. It was in 'No Buffer' mode. Now after i have changed its log status to buffered, the database now supports transactions.
0
