servlet use of db connection

[Kelly Dolan]

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]

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]

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]

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]

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]

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]

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.