This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Functional Reactive Programming and have Stephen Blackheath and Anthony Jones on-line!
See this thread for details.
Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

servlet use of db connection

 
Kelly Dolan
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a servlet that gets called so frequently that it acquires a dbconnection, holds onto it and for ever doGet()/doPost() it processes, it uses this dbconnection. Once I get my dbconnection, I call setAutoCommit(false).

I am experiencing a problem using the jdbc-odbc driver with msaccess. If the processing of a request is correct, I call dbconnection.commit() and everything is great. However, if something fails and I call dbconnection.rollback(), my database changes are not rolled back. It's like rollback() does nothing.

I tried an experiment and learned that if I call dbconnection.close() instead of dbconnection.rollback() (then acquiring a new dbconnection for future use), it appears as if my changes are rolled back.

1. With regard to acquiring a dbconnection and holding onto it...is this an appropriate use within a servlet? Or is there some side effect to using the same database connection within a servlet environment.

2. Has anyone had a problem like this before? Is it a problem with msaccess or the jdbc-odbc driver?

3. Am I missing something?

Thanks!

- Kelly
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. With regard to acquiring a dbconnection and holding onto it...is this an appropriate use within a servlet? Or is there some side effect to using the same database connection within a servlet environment.
Sounds like a poor design, particularly if a database connection (or several) are associated with a single servlet. There is raraely a reason not to use a connection pool.

2. Has anyone had a problem like this before? Is it a problem with msaccess or the jdbc-odbc driver?
Or possibly both. Access is a poor choice of dtabase and there are better free alternatives that will be more consistent and stable. Also, the Jdbc-Odbc bridge is a a bit of a hack and I'm not sure it has ever really been supported by Sun - it was released as a quick and easy starter for JDBC, but none of the initial bugs have ever been fixed. And there are many.

3. Am I missing something?
Hypersonic or MySQL? Possibly not quite the answer you were looking for, but starting with a better DB will save you a world of hurt in the long run. Hypersonic is extremely light and MySQL is missing a couple of features, but both are extremely useable and have good JDBC drivers.

Dave.
 
Jeroen Wenting
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. With regard to acquiring a dbconnection and holding onto it...is this an appropriate use within a servlet? Or is there some side effect to using the same database connection within a servlet environment.
Extremely poor design.
What will happen if there's more than one simultaneous request? It will have to wait for that connection.
And if you create more and more connections and never release them your database will bog down and start refusing connections as it runs out of resources.

2. Has anyone had a problem like this before? Is it a problem with msaccess or the jdbc-odbc driver?
Both Access and the bridge driver are kinda limited. But the main problem here is your design.
ALWAYS close connections when done with them, and preferably use a connection pool for performance reasons and to manage connections.

I am experiencing a problem using the jdbc-odbc driver with msaccess. If the processing of a request is correct, I call dbconnection.commit() and everything is great. However, if something fails and I call dbconnection.rollback(), my database changes are not rolled back. It's like rollback() does nothing.
Have you set autocommit to false on the connection? Many drivers have it set to true by default.
Access may also not support transactions at all, though I think it does.

As David suggests a better database engine should be your first thought here.
I do not agree with his suggestions though, hSQL AFAIK being out of production and mySQL being overly complex to set up for what it can do and not "nice".
My favourite is Firebird, the open derivative of Borland Interbase (http://firebird.sourceforge.net/). Easy to use, low footprint, excellent performance and stability, and very complete.
Essentially the same product as Interbase (which still sells and is used by among others the US DOE).
[ June 16, 2004: Message edited by: Jeroen Wenting ]
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Deviating from the original topic...

I do not agree with his suggestions though, hSQL AFAIK being out of production and mySQL being overly complex to set up for what it can do and not "nice".

Development of the original Hypersonic SQL, does indeed appear to have stopped. Others have created another SourceForge project to continue its development.

I'd be curious to learn more about the problems you've had with MySQL, Jeroen. Any chance you'd post a thread on the subject in the JDBC forum?
 
Tim Holloway
Saloon Keeper
Posts: 18303
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL lacked proper transaction support until recently.

OTOH, MS-Access doesn't even have transaction support. Plus, unless they've updated it, the jdbc-odbc bridge isn't thread-safe. the only thing MS-Access or FoxPro are good for in a J2EE environment is to let you "get your feet wet" while not having to learn a whole new DBMS. In a production multi-user system, the whole works isn't likely to stay up more than 15 minutes and the database will be turned into hamburger.
 
Kelly Dolan
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just wanted to chime in and say thanks all! and respond to a few comments.

1) ...not the greatest design? Ok. BTW - access to the connection object is serialized such that only one thread will use it at one time.

2) ...using another database? I agree MSAccess may not be the greatest "free" choice but in this case, I do not have a choice.

Thanks!!!

Kelly
 
Karthikeyan Dharmarajan
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David O'Meara:
1. With regard to acquiring a dbconnection and holding onto it...is this an appropriate use within a servlet? Or is there some side effect to using the same database connection within a servlet environment.
Sounds like a poor design, particularly if a database connection (or several) are associated with a single servlet. There is raraely a reason not to use a connection pool.

2. Has anyone had a problem like this before? Is it a problem with msaccess or the jdbc-odbc driver?
Or possibly both. Access is a poor choice of dtabase and there are better free alternatives that will be more consistent and stable. Also, the Jdbc-Odbc bridge is a a bit of a hack and I'm not sure it has ever really been supported by Sun - it was released as a quick and easy starter for JDBC, but none of the initial bugs have ever been fixed. And there are many.

3. Am I missing something?
Hypersonic or MySQL? Possibly not quite the answer you were looking for, but starting with a better DB will save you a world of hurt in the long run. Hypersonic is extremely light and MySQL is missing a couple of features, but both are extremely useable and have good JDBC drivers.

Dave.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic