aspose file tools*
The moose likes JDBC and the fly likes Multiple updates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple updates" Watch "Multiple updates" New topic
Author

Multiple updates

Rekha Pande
Ranch Hand

Joined: Jan 29, 2004
Posts: 145
Hi all...

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.

Please help.
Thanxs in advance
Ram Velagapudi
Greenhorn

Joined: Oct 01, 2004
Posts: 1
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.



Hope this helps you!
ramprasad madathil
Ranch Hand

Joined: Jan 24, 2005
Posts: 489


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
thank u all for reply....
Rekha Pande
Ranch Hand

Joined: Jan 29, 2004
Posts: 145
I tried your code ... but it gave me the following error when the 2nd table statement had an error in it.



My code
try
{
if(conditon==true)
{
uprecords.executeUpdate("Update Table 1");
if(condition==true)
{
up_benef.executeUpdate("Update Table 2");
}
conn.commit();
}
}
catch(Exception ie)
{
conn.rollback();
}


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
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
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...

Thank you
ramprasad madathil
Ranch Hand

Joined: Jan 24, 2005
Posts: 489

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
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.....
ramprasad madathil
Ranch Hand

Joined: Jan 24, 2005
Posts: 489

You can learn about transactional and nontransaction tables in MYSql (and how to switch from one to another) in folln rsources.

1. http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html
2. http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html
3. http://www.developer.com/db/article.php/2235521

ram.
ramprasad madathil
Ranch Hand

Joined: Jan 24, 2005
Posts: 489


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...


Yes, u either ought to have bdb, gemini or innodb type tables.

The folln is lifted from web monkey


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
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.

Please help.

Thank you.
ramprasad madathil
Ranch Hand

Joined: Jan 24, 2005
Posts: 489

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
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

So.. now how would i be locking a table.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61434
    
  67

Moving to the JDBC forum.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Braj Prasad
Greenhorn

Joined: Apr 08, 2002
Posts: 16
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
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
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.

thank u all for all ur replies.

Take Care.
 
 
subject: Multiple updates