icrosoft's recent release of the SQL Server 2000 Driver for Java Database Connectivity (JDBC)
reminded me of my first big Java project. I worked on a basic two-tiered system in which one component wrote file information to a database and another component displayed the file information to the user.
A major constraint of the project was that we needed to support Microsoft's SQL Server DBMS. Our clients used SQL Server so we couldn't very well debate whether it was better or worse than Oracle, MySQL, Cloudscape, or a plain text file. The customer was king, and if they had SQL Server, that is what we had to use.
The project started in 1997, when JDK 1.1 had just been released. Very few JDBC drivers were available for SQL Server then, and particularly unfortunate was the lack of Type 4 drivers. To understand why the Type 4 driver is preferable to the others available, examine the different types of driver Sun Microsystems lists:
- Type 1: Uses a bridging technology to access the database (e.g., Sun's JDBC-ODBC Bridge driver)
- Type 2: Uses native API drivers; requires software on the client machine
- Type 3: Translates JDBC calls into a database-independent network protocol, which a server then translates into a database protocol
- Type 4: Uses the network protocols built into the database engine
Type 1 and 2 drivers require native code to be installed on the client's machine, so they are suitable for a corporate network or an application server in a three-tier architecture. They are not suitable for machines connecting to the database via the Internet. Type 3 drivers do not require native drivers on the client's machine, but they require additional security to work over the Internet. In general, Type 4 drivers are preferable because they use the network protocol of the database without needing any special native code on the client machine.
Conventional wisdom says that you should not use the Type 1 JDBC-ODBC Bridge for any commercial application. Why? Because not only do you have to set up an ODBC source on the client machine, the bridge is slower than a Type 4 driver would be.
Of course, how much the speed of the bridge matters depends on your application. If you have an application that is using the database continuously, you need to have as little overhead in your driver as possible. On the other hand, if your database access is not a bottleneck, it may not really matter. Isn't it far more important that the database driver executes your requests correctly without leaking memory? For example, each time you call the
ResultSet.getTimestamp() method on the bridge, you leak 16 bytes of native memory.