Charles, To my knowledge there is no JDBC Type II, III or IV driver for Access. You can go the sun web site and view the list of third party JDBC drivers that are available for the different database's used today, and the features that the drivers support. You will note that the Sun web site does not list an available Type II, III, or IV third party JDBC driver for Access.
You may want to do a search on google.com and see if someone out there is making a Type II, III or IV JDBC driver for Access, but I suspect it is highly unlikely. Access does have a size limitation compared to other database's (I seem to remember a size/performance limitation of 10MB, but my memory may be faulty), and does not follow the ANSI SQL standard for database's as closely as Oracle or even SQLServer. But the performance hit of using the JDBC-ODBC bridge, especially with complex queries, along with no built in-support for the extended JDBC API in the ODBC-JDBC bridge, are some of the main reason why most Java/JSP developers do not use Access for even prototype development. Just my two cents worth on the subject. Celeste -----Original Message----- From: charles arehart [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 5:58 PM To: JRun-Talk Subject: RE: RE: RE: access a MS Access DB from Jrun Beyond the debate about Access, there are a couple of points that still seem worth making. First, with regard to the debate over Access, we're already way off topic here. This is just the kind of debate I feared would come up. As to the problem you encountered, ok, if everything was absolutely unchanged, then it would seem the DBMS was at fault, but I'd have been very interested to find the root cause. Still, I realize you were compelled to simply move off the product, and no one can blame you for that. As to why anyone would still use it, let's just say different strokes for different folks. But the problems with the driver have been illuminating, and that goes to the core of Tamas' original question. I'd still be curious if anyone's learned of an alternate driver. I do feel compelled though to respond to the accusation that the "relationship thing" is an Access-specific feature. It's really not. While Access offers a GUI way to define relationships, it's really just a visual way to define foreign keys, which could just as easily be done in DDL (data definition language) with a CONSTRAINT keyword in either CREATE TABLE or ALTER TABLE. You're right though, Travis, that many people do get locked into only using the visual tools for database structure manipulation. Indeed, I wrote an article to help them make the bridge to using DDL such as that above, in the article "Reconfiguring Remote Databases via SQL " at http://www.sys-con.com/coldfusion/article.cfm?id=131. Though written for the ColdFusion Dev Journal and focused on Access users, it could be just as useful for JRun developers using any DBMS, when the database is on a remote server that they can't manage via any visual means. /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 5:23 PM To: JRun-Talk Subject: RE: RE: RE: access a MS Access DB from Jrun When we moved to SQL Server it was identical, no code changed, no queries changed, in fact i think we did a direct import from access to sql server. As for it being a relationship thing, I don't use any of accesses features like that, I make my own relationships using keys and ids, so it's not that either. In fact, I rarely use any db specific features in case i want to move dbs (hence the crossdb project www.crossdb.com). I don't like having to rely on any one specific application or platform, etc. And the features you talk about in Access are probably mostly of visual features which don't help us people who don't touch the db gui's, other than for some things like checking if values got inserted properly etc. I write scripts for most things in case i need to replicate it. And to tell you the truth, Acess just isn't even in the cards anymore. I don't like the fact that you can't move to a linux/unix box either when using Access. So in conclusion, the big question is, Why use Access? Especially when there are better alternatives that don't cost anything? Travis ---- Original Message ---- From: charles arehart <[EMAIL PROTECTED]> Sent: 2001-11-20 12:40:13.0 To: JRun-Talk <[EMAIL PROTECTED]> Subject: RE: RE: access a MS Access DB from Jrun Well, this was the kind of debate that I was trying to avoid, but I do feel I need to make at least one defense against the assertion that Access was to blame. In the bigger picture, I'm offering a reply to suggest that things aren't always what they seem. Those not interested in the discussion, please just move along. I know for many it's a tired debate. First, of course, you're right that there are alternatives like MySQL, but it's not a perfect replacement either (lacks some features Access has). As for your "loss of data", I really doubt that it was Access per se that was at fault. Of course, the fact that you made a port of the same app/structure and the problem went away would certainly be convincing enough--but others have used it successfully with no such loss. Were you able to ever isolate the source of the loss? I ask this because (as is so often the case) the root cause may not have been what it appeared to be. For instance, and I know this may not have applied to your application, what if the problem was merely a mis-formed relationship definition among tables? If someone had turned on the "cascade delete" feature, then deleting one record would lead to deleting other records in the related table. If that's used in anything other than a master-detail situation, that would cause data to unexpectedly disappear. Also, even in some master-detail situations, it's not what one wants, but it's very easy to setup that way without realizing the cost. Also, there may have been a situation where the column names were at fault. You were using CF, right? A little known problem can occur with CFINSERT or CFUPDATE if your column names end in _date or _range, for instance. Data meant for those columns coming from a form will simply be dropped by CFINSERT and CFUPDATE. (For those not familiar with it--and I know we're getting a little off topic, but there's a reason--these two tags work much like the JSP mechanism of automatically populating the fields of a JavaBean with form post parameters. Instead, they take all fields on a form and matches them to column names of the same name in the database--then will insert/update the table.) Unfortunately, these tags can suffer due by conflicting with a form validation feature built into in CF. I won't go into the details, but the bottom line is if you have a database column named "birth_date", then CFINSERT/CFUPDATE would mistakenly ignore that formfield thinking it was there for validation. It's just not smart enough to recognize that there is no "birth" field to be validated and that it must really be a column of that name. It's an unfortunate design decision on the part of ColdFusion, but there it is, and it could cause unexpected loss of data. If it's possible that in the move to SQL Server someone changed the column names (lots of SQL Server DBA's would prefer BirthDate over Birth_Date as many Access developers seem to naturally use), then the "fix" was that renaming. It might have seemed innocuous at the time. Just one of many "little things" besides the DBMS switch that could have been the root cause of stopping the "loss of data". Again, I don't want to start any war over CF versus Java or Access versus other DBMS's. I just do wonder if the problems that some experience are always attributable to the database. Now, do I really want to be an apologist for Access? No, not really. I said in my original post on this thread that one should avoid it for all but the most simple, low-volume apps. But as this thread is showing, for someone trying to use it with the JDBC-ODBC driver, they might conclude Access simply can't work at all with web apps. For some of us, it has worked well for quite some time. If there's a problem with the driver, that's a different problem and definitely one worth knowing about. My real reason for carrying on in reply to Travis point is really to put on the table that sometimes things aren't as obvious as they seem. It could be that the switch from Access to SQL Server alone wasn't the real "fix" for that data loss problem. It sure may seem it, but just today on another mailing list, a user complained about a similar problem with their database. For some reason, "suddenly" the system was slowing to a halt. They wondered if it was the DBMS, or the app server, or the driver. They were sure something was broken and someone needed to fix it. Then, ultimately, it was discovered to be a data problem, totally in his control but just not obvious in the fury to identify and fix the problem. So I'm just saying I'm always cautious before passing judgment against any one component in these web apps that we all know and love. Besides the app server, DBMS, and driver, there are also the OS, web server, and possibly other components, as well as designs of both the apps and db that can all have influence, not to mention patches, volume issues, and lots more. But, hey, that's what keeps us all busy and constantly learning! :-) /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 12:40 PM To: JRun-Talk Subject: RE: RE: access a MS Access DB from Jrun You are right in saying it's the driver. It doesn't support many features, has multiple connection issues, is slow, etc. In most cases, I wouldn't even using it just for testing or proof of production. When I started using it a while back, i had problems that were unsolvable, that magically were fixed when i used a new driver. As for access, we've used access in cold fusion apps before too, but quickly stopped that after data started going missing. And this wasn't even a heavy hit server, it was an internal app for a company that completely relied on the database, maybe 10 users tops. The minute a database starts losing data, is the minute you get rid of it and never use it again. We moved the exact same app with the exact same db structure to MS SQL Server and voila, problem fixed. That is just one reason why you should not use access for anything that is important. That doesn't even take into account that it's slow, etc... And why use a database that is... well junk, that you have to pay for when you can use a reliable database like mySQL for free? Travis ---- Original Message ---- From: charles arehart <[EMAIL PROTECTED]> Sent: 2001-11-20 10:26:35.0 To: JRun-Talk <[EMAIL PROTECTED]> Subject: RE: access a MS Access DB from Jrun Right, we're in agreement. I too have apps built years ago with ColdFusion and Access that still run with no problem having hundreds if not thousands of hits per day, but there will be others who'll want to jump in and declare Access null and void "for production" right off the bat. I was just hoping to temper that oft-raised debate. They may very well still wade in responding to Tamas' original note before reading ours. :-) But I'm intrigued by your assertion that "the problems with the JDBC-ODBC Bridge driver far outweighs Access's limitations". Are you saying that you found that you couldn't even do the equivalent application in Java using the JDBC-ODBC driver? Or in other words, that all things being equal (regarding volume and nature of access to the database) an app using the JDBC driver would not perform as well as it might because of the driver itself? (Of course, I'm again not trying to open the debate of ASP or CF vs JSP/servlets: I'm referring just to any anecdotal experience to suggest that the JDBC-ODBC driver itself would be specifically at fault for poorer performance than an equivalent app written in another platform accessing the same Access DB.) If that's the case, then it would seem the conclusion would be to use Access only for proofs of concept, extremely light load apps, etc, and move to another DBMS, as you did, for production. I suppose that was the meaning in the Sun's recommendation to not use it for "production". It's just that blanch at such overarching statements because "production" means so many things to so many people. Indeed, the same argument is made against Access, but as we've discussed that's not always a fair accusation. But it sounds like the argument against the driver isn't the same as the argument against Access. Thanks for clarifying that. I wonder, given that some would still want to try to use Access in the right circumstances, if anyone or any company has come out with an alternative JDBC driver for Access? /charlie -----Original Message----- From: Fishwick, Randall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 11:57 AM To: JRun-Talk Subject: RE: access a MS Access DB from Jrun Charlie, Agreed in principle. But our company used an Access database with ODBC and Active Server Pages for several years with no serious problems. This was a web based telephone number lookup that was accessed hundreds if not thousands of times a day. The point being that the problems with the JDBC-ODBC Bridge driver far outweighs Access's limitations. We're now using both Oracle and SQL Server for our JSP/Servlet based web applications. Randy -----Original Message----- From: charles arehart [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 8:35 AM To: JRun-Talk Subject: RE: access a MS Access DB from Jrun Of course, keep in mind too that Access itself is "not very good for heavy duty projects". For small volume apps, or in the very early stages of an app, it can suffice, but there are indeed many benefits of moving to a better DBMS platform. I don't think we need to open any can of worms about this. Sometimes people want to demonstrate a proof of concept with something as simple as Access. Beyond that, they really should consider something more substantial, if anything more than a few people will hit the DB at any one time. Of course, that can still translate to hundreds of users per day. Just be careful. /charlie -----Original Message----- From: Mark Phelps [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 10:40 AM To: JRun-Talk Subject: RE: access a MS Access DB from Jrun You just need to obtain a JDBC driver for Access. Sun provides a driver called the JDBC-ODBC bridge which would work. You can download it from www.javasoft.com. I have heard from others on this list, however, that this driver is not very good for heavy duty projects. Perhaps somebody on the list knows of a better driver out there somewhere. -----Original Message----- From: Tamas Vertse [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 20, 2001 8:24 AM To: JRun-Talk Subject: access a MS Access DB from Jrun Hey, I have a Jrun server 3.0 and I would like to access an mdb file from a JSP file under Jrun. How can I do it? (My Jrun is standalone. I haven't linked any external webserver to it.) I wait your respones. Thanks, Tamas ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/jrun-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
