mSQL-JDBC 2.0b5 Imaginary JDBC Driver for mSQL Copyright © 1996-1999 George Reese CONTENTS * License * System Requirements * Changes * Introduction * Features * Installation * Usage * Known Bugs * Support LICENSE This program is distributed under the terms of the Artistic License. See the included file "Artistic" for details. SYSTEM REQUIREMENTS * Any Java VM supporting JDBC 2.0 or later (JDK 1.2 beta 4 or later) For support for JDBC 2.0 standard extension features: * JDBC 2.0 standard extension classes For JNDI support: * JDBC 2.0 standard extension classes * JNDI 1.1.1 classes * A JNDI service provider. The provided example uses the file system one. The JDBC 2.0 Standard Extension API is available at http://java.sun.com/products/jdbc/jdbcse2.html. The JNDI 1.1.1 API and several service providers are avabilable at http://java.sun.com/products/jndi. For a variety of reasons, mostly related to the fact that mSQL is not ANSI, a complete JDBC driver is not possible for mSQL. Nevertheless, mSQL goes far enough that you can use the Java implementation to fudge things (like support for a wide range of data types). CHANGES * Added a fix to a bug with setting date parameters in prepared statements reported by Sam Nelson (samkn@ix.netcom.com). * Fixed a bug in unicode parsing introduced in mSQL-JDBC 2.0b1. * Fixed once and for all the bug that caused things to go nuts on large deletes. * Made it easier to make non-standard result sets (and thus make it easier to write code for the DatabaseMetaData methods). * Introduced all of the most important DatabaseMetaData methods. The rest should be easy to add. * Made JDBC catalog == mSQL database. Not particularly useful or meaningful, but what the hell. INTRODUCTION This driver is a beta release. This means that while there are no known bugs, the features of this driver are still young enough to present some level of risk. You should only use this driver if you can accept the risk inherent with a beta product. mSQL-JDBC is a database access API for the mSQL database engine that conforms are much as possible to the Java JDBC API. JDBC enables you to write database applications in Java without worrying about the underlying details of the database supporting your application. For a full discussion on the JDBC API, please see my book "Database Programming with JDBC and Java" (http://www.ora.com/catalog/javadata). FEATURES In addition to supporting the basic SQL syntax, mSQL-JDBC adds support for stored procedures, multi-byte character sets, and JNDI data sources. INSTALLATION Copy msql-jdbc-2-0b5.jar anyplace you like and add it to your CLASSPATH. NOTE: I recommend making a link to msql-jdbc-2-0b5.jar called msql-jdbc.jar and adding the link to your classpath. Thus, when new releases come out, you only need to relink the msql-jdbc.jar file. USAGE You do not need to compile the source code. It is provided for your convenience. If you should desire to do so, however, issue the command: javac -d DIR *.java where DIR is some directory in your CLASSPATH The mSQL-JDBC driver reads data from the database in separate threads. When you make a database query, the query will return immediately with an empty ResultSet object. Another thread will populate that ResultSet with data while allowing you to do any other processing you need. If you ask for a row that has not yet been read, then that call will block until that row gets read (but only until that row is read). * Whenever possible, retrieve columns using the column number. mSQL does not provide column names until after all rows have been retrieved. This means that for a large query, you will have to wait until the last row is read from mSQL before the first row may be retrieved from the result set if you query by name. * mSQL-JDBC performs as little synchronization as is possible to keep it internall consistent in a multi-threaded environment. It relies on you to provide any extra synchronization where it is needed. The synchronization it does is designed simply to make sure that there is no corruption in the mSQL wire protocol between your application and the mSQL database. Making sure you do not call execute() before setting a parameter in the PreparedStatement object, on the other hand, is up to you (as it should be). * There is no performance hit for accessing result set meta-data once all rows have been read from mSQL. This means it is best not to read it *until* you really need it. If you call getMetaData() before the driver is done reading the rows from mSQL, your processing will hold until the rows have been read. * Multi-byte encoding issues: While mSQL itself only supports the Latin character set, it is 8-bit clean. This means that external tools such as this driver can store data in it using other encodings, so long as the encoding is single byte. In other words, the mSQL-JDBC driver allows you to specify the encoding for storing data in the database. The default encoding is 8859_1. You should keep in mind, however, that mSQL cannot possibly support multi-byte character sets. The only way to support multi-byte character sets (such as Unicode) is to use a single byte encoding scheme. Unicode, for example, supports UTF8, which is a single byte encoding of Unicode. Thus, mSQL-JDBC supports UTF8 but NOT UTF16. Finally, if you choose to use an encoding other than 8859_1, remember that tools that are not aware of encoding issues (like the msql command line utility) will represent your data wrong. * Date, Time, and Timestamp support: mSQL 1.0 has no support for these concepts. mSQL 2.0 supports Date and Time, but not Timestamp. mSQL-JDBC, however, supports all three concepts for all versions of mSQL. To use these, keep the following in mind: Timestamp: For all versions of mSQL, a Timestamp field should be a mSQL CHAR(21). That field is the return value of date_object.getTime() stored as a String (getTime() returns a long, but mSQL has no support for 64 bit numbers). In queries, you simply call result.getTimestamp() to get the column as a Timestamp. Time: mSQL 1.0: The database field should be either a CHAR(9) or a CHAR(21) depending on how you want the data stored. A CHAR(9) means you are storing it in the database in a format consistent with mSQL 2.0, as 'HH:mm:ss'. That makes it more friendly to external applications, but limits its applicability in that it only represents an hour, minute, second (not an actual point in time). The CHAR(21) version is actually a Timestamp as described above. mSQL 2.0: You can do what is listed for mSQL 1.0 above (useful for portability of data between 1.0 and 2.0 databases) or, more properly, you can use the built in mSQL TIME data type. Date: mSQL 1.0: The database field should be either a CHAR(12) or a CHAR(21). The CHAR(12) should be represented as 'dd-MMM-yyyy'. The CHAR(21) is a Timestamp as described above. Again, the driver does not care how you choose to store it in the database, it will simply figure it out on its own. mSQL 2.0: As with the Time support, you can choose either the 1.0 version or the proper 2.0 native Date support. KNOWN BUGS * Result sets do not know the difference between being before the first row and after the last row. * Some obscure DatabaseMetaData methods still need implementing. * Watch out for binary support. I may have broken it in this release. SUPPORT For support and discussion on this product, subscribe to the mSQL-JDBC mailing list by visiting: http://list.imaginary.com/mailman/listinfo/msql-jdbc Please address any questions or comments to that mailing list.