• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Multiple updates

 
Ranch Hand
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Ranch Hand
Posts: 489
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank u all for reply....
 
Rekha Pande
Ranch Hand
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 489
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 489
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 489
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 489
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 67753
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving to the JDBC forum.
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
A day job? In an office? My worst nightmare! Comfort me tiny ad!
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic