Alternately you may wish to unpack it under /usr/local/mysql and add /usr/local/mysql to your CLASSPATH variable.

deinstallation

If for some reason you wish to remove this package, all that is necessary is to erase its files. Go to the directory where you initially installed it and rm -R twz1/*, then rmdir twz1. You may also wish check that you don't have the zip file in your CLASSPATH.

features

autoreconnect

If the TCP connection between the driver and MySQL drops tjFM will attempt to re-establish communication using the same parameters as initially used to make the connection. This normally comes into play when used in servelets or servers which may be idle longer than MySQL's idle disconnect time. Please note that this feature presents a (very) small risk of allowing the application to corrupt the data by double action in the rare case of mysqld crashing or going through a forced shutdown after a client request for an update action has completed but not yet been reported to tjFM; this risk is on the same order and mostly in the same circumstances which could cause corruption in the DB itself. But because of this risk the autoReX property is available; if set to false autoreconnect will be disabled for data manipulation (delete/insert/update/...) commands.

caching

The MySQL connection protocol is half duplex, non-interruptible, deterministic set of well defined packets. What this means to an application is that once a command is issued on a connection from a client that client must then receive all, only, and exactly what the server will send in response and cannot issue another command until the response is exhausted. At the intersection with the JDBC API this means that EITHER only one Statement and ResultSet can be in use on a Connnection at one time, OR the response packets to a query must be cached for presentation in response to ResultSet methods.

tjFM attempts to have it more than both ways. If the property "multipleQuery" is set to false then tjFM acts as a trivial (well, not really) conduit between the application and the TCPIP connection to mysqld; any attempt to execute a second query on a connection while a first is still active will be met with an SQLException. The good thing about this is that it comsumes a minimal amount of client side resources. If multipleQuery is set to true (the default) the response to a query will be cached. More you have the option of caching to memory (default) or to disk by setting the "cacheMode" property to "disk". Of course disk caching is not available in applets unless you override the SecurityManager's restriction on write. The cache file will by default be in the current directory under the name of <number>.cache where "number" is internally generated. If you wish this file written to another directory you can use the "cachePath" property to set the cache directory. This file will be erased when the ResultSet is properly closed.

Application initialization - the properties file

twz1jdbcForMysql is designed to allow numerous options. Applications, as opposed to applets, are configured by way of properties files, a native feature of java. In tjFM configuration options are also referred to generically as properties.

Properties are set by the file .jdbcMysql.properties from the current directory or the home directory of the current user as determined from system properties (not the user specified in the URL) which may be subsequently overridden by other properties files specified by the property "moreProperties" up to an arbitrary depth of 16 fileis. These become the "default properties" for the driver and subsequently become the default properties for a connection. At the connection level the driver defaults may be overridden by properties specified on the getConnection() method or ultimately overridden by the parameter variable specified in the url.

I stress again that properties and property files are a feature available only in applications and not applets. A future release may provide means of specifying properties by a default URL, but for now the default properties specified below can only be overridden by the properties or URL specified on the getConnection() method or a call to jdbcMysqlBase.setDefaultProperty().

Supported properties in .jdbcMysql.properties
property name	default value	notes
autoReX	true	true, false, yes, no. This property controls use of the automatic reconnect for a data update (delete/insert/update) command. The default is true, but there exists a very small window of risk of data corruption by double action if autoReX is true and the backend db goes down after it has executed the command but before reporting it to tjFM then sucessfully restarts. Autoreconnect is always used in a query.
cacheMode	memory	Options are "memory" or "disk". This will control whether query results are cached to memory or disk for subsequent retrieval from ResultSet methods. The standard tradeoffs apply: memory is generally faster but is relatively exhaustible. Note that this is meaningless if "multipleQuery" is false for this connection.
cachePath	.	The directory where cache files [named <numeric-request-id>.cache] will be written if "cacheMode" is set to "disk".
connectionTimeout	120	The time, in seconds that a thread blocked waiting for access to the connection will wait before receiving an SQLException.
db	mysql	This is the "database", in MySQL argot, to which the driver makes available upon connection. In reality it is a directory name. In tjFM a MySQL database is equated to a "catalog" in the JDBC API.
dbmdDB	<connection>	The database (catalog) to use in a DatabaseMetaData connection.
dbmdMaxRows	65536	The maximum rows returned in a DatabaseMetaData query.
dbmdPassword	<connection>	The password to use making a DatabaseMetaData connection.
dbmdUser	<connection>	The user ID to use makeing a DatabaseMetaData connection.
dbmdXcept	false	Should an exception be thrown on unsupported methods? The default is false, implying that an empty table will be returned.
debugFile	[none]	Tells tjFM to enable debugging and the specified name is the file to which debug data will be written. Note that debugging may have a significantly adverse effect on performance. At present debugging can only be activated at initialization. Unavailable in applets.
debugRead	false	Options are true or false (or yes or no). This option is effective only if debugging is turned on by specifying a "debugFile" at initialization. If set true, data read from the MySQL server will be dumped in hex format to the debug file; this file will become very large very rapidly and performance of the driver and its application will be vastly degraded.
debugWrite	false	Options are true or false (or yes or no). This option is effective only if debugging is turned on by specifying a "debugFile" at initialization. If set true, data written to the MySQL server will be dumped in hex format to the debug file; performance of the driver and its application will be degraded.
host	localhost	This is the host machine to which tjFM attempts to connect for access to the mysql backend.
maxField	65535	The maximum size in bytes of a field (column in a row) returned to the application. As per the API data larger than this will be "silently truncated". At the upper bound this is also limited by the maximum size of the buffer that mysqld will allocate - which is controllable in the startup script for mysqld. Note that there is a method in Statement to change this.
maxRows	Integer.MAX_VALUE	The maximum number of rows which will be retrieved by the jdbc driver before the rest are "silently discarded" (as per API). This is settable by methods in Statement.
moreProperties	[none]	Tells the driver at initialization time to look for more properties in the specified file. The maximum possible depth of these files has been arbitrarily set to 16 to avoid passible loops. Properties found in later files override previous settings.
multipleQuery	true	True/false/yes/no. If true data returned from a query will be cached (see "cacheMode" and "cachePath"). Setting this to false will cause any connection to be made in "singleQuery" mode which will prevent more than one Statement or ResultSet from being open on one connection at one time.
password	[none]	The password by which MySQL validates the user connection. Note that "user" and "password" are artifacts of MySQL and need not reflect a user name or password from the host or client system.
port	3306	The port on "host" by which we attempt to reach the MySQL engine.
privateSQL	true	True/false/yes/no. Controls whether tjFM dynamic configuration can occur through "private SQL" statements in execute(). (e. g. execute("jdbc: cacheMode disk") to set the cacheMode of this statement to disk for future operatons. Note: dynamic configuration is not yet implemented.
rsmdXcept	false	Should an exception be thrown on ResultSetMetaData requests which are inconsistant with the column type? Default is no.
rsDateXcept	false	If true, throw an exception if date is prior to 1900 on a getDate, otherwise return a null. This inconvenience is a java.sql.Date artifact.
socketTimeout	[none]	The time in seconds that a socket will block before throwing an IOException. Zero means permanent. Not specifying this will leave the socket as created by your java implementation, usually permanent.
user	[none]	The name with which the user is connected to MySQL.
RSLock	false	Enable locking of ResultSet queries against the Statement. Generally unnecessary unless the application shares Statements and ResultSets across multiple threads.
SO_LINGER	[none]	The number of seconds to linger waiting for socket close handshaking to complete. Not specifying this leaves the socket state as created by your java implementation.
TCP_NODELAY	[none]	True/false/yes/no. A socket control option. This will turn on or off Nagle's algorithm for buffer flushing. Not specifying it leaves the socket as java implements it.

Invoking tjFM in an application

Before writing or using an application with tjFM you want to write a .jdbcMysql.properties file, described above, or at least understand what the defaults are. tjFM will look for this file first in the current directory and if it doesn't find it there, in the home directory of the userid invoking the program. Other than that tjFM works like most other JDBC drivers.

twz1jdbcForMysql obeys standard methods of initialization. The driver registers itself statically when loaded. The package name is twz1.jdbc.mysql and the driver class is jdbcMysqlDriver. Hence you initialize tjFM by:

Class.forName("twz1.jdbc.mysql.jdbcMysqlDriver");

(It's always a good idea to catch exceptions, though!) At this point all the methods of DriverManager are available. The jdbcMysqlBase class is also instantiated upon load; the properties files are read and the tables built. The static jdbcMysqlBase methods are now available, and if debugging is enabled it is in effect.

tjFM accepts and considers its own URL's with the prefix "jdbc:z1MySQL:". DriverManager will pass its static requests for any URL with that prefix to tjFM. To establish a connection with MySQL you use one of the variants of DriverManager.getConnection(). At minimum you specify a URL; other forms of getConnection will accept name, password, and a properties list; these are passed to tjFM as a URL and a properties list (specifying the name and password in getConnection() results in DriverManager building a properties list if you haven;t already). Values in a passed properties list supercede any already in effect. Properties in the URL will supercede even those.

Using square brackets to indicate optional values, the URL will be parsed as follows:

jdbc:z1MySQL:[//host[:port]][/db[?property=value][&property=value]...]

Later properties in the URL supercede earlier ones. As of now, no URL character decoding is done but that should not pose a problem as it is unlikely that the desired value of any property value would not be representable in a String.

Perhaps an example -- from jdbctest1.java:

    header("Loading Driver");

    try {
        Class.forName("twz1.jdbc.mysql.jdbcMysqlDriver");
        }
    catch(Exception e){System.out.println(e);}

...

    header("The following connex will succeed");

    cx = null;
    url = "jdbc:z1MySQL:";
    try {
        cx = DriverManager.getConnection(url);
        }
    catch(SQLException se){System.out.println(se);} 
....

In this example nothing was specified in the URL - everything of importance came from the properties file: that's probably good practice because it is easier to edit the properties than it is to recompile code.

The test program

A test program, jdbctest1.java is supplied with tjFM which illustrates the above. To actually run it, you will need to set the "user", "password", and "db" parameters in the .jdbcMysql.properties file. You will also have to create a "jdbctest" database in MySQL for which the user you specified is authorized to create and drop tables as well as write to them.

There is now also a jdbctest2.java supplied which continues the testing of features in tjFM.

At this point follow the API and MySQL is yours to program with!

Using tjFM in an applet

My experience has been at writing applications, not applets, but, on the other hand, I never intentionally coded anything in any way which could not be used in applets. Now, there are many features of tjFM which will not work in applets due to the sandbox restrictions: properties initialization fails (the SecurityException is caught internally), debug files, and most importantly, disk caching will not work. But the rest of the features and function, in theory, should work.

Well, there was "documentation" at the top of my TODO list, and while glumly glancing through it I noticed a "try it in an applet" down near the bottom. Guess what I did.

The test applet, source and class, and a sample html page are in the ./twz1/jdbc/mysql/htdocs directory all named appropriate extensions of jdbcTestApplet1. There is also a "twz1jdbcForMysql.zip" file which is in fact merely an archive of the class files from the ./twz/jdbc/mysql directory. To use this test code move the lot to a directory accessible under your webserver. Change the paths in the html file to reflect the paths under the webserver root correctly.

To actually run the applet, you need a browser with java 1.1 support. Because of security restrictions the IP address for your MySQL host must be the same as the webserver. Unlike the application test, you will have to modify the URL specified in jdbcTestApplet1.java and recompile it. Then specify the URL of the html file to your webserver and watch it run.

Programatically, invocation of the driver in an applet does not differ from in an application.

Good luck!

It has been brought to my attention that IE4 has a problem with DriverManager. In this case it may be necessary to invoke tjFM as just another class rather than through the static invocation of "Class.forName"

The work around is to code:
• Driver driver = (Driver) jdbcMysqlDriver();
• Connection cx = driver.connect(<url>, null);

and subsequently use cx as you would otherwise.

Notes on API implementation

As stated in the introduction, I have tried to follow the API as far as practical. For the most part behavior of the methods should be a fair reflection of what the API docs say. Here, however are some notes on my misgivings where various methods may not be in full compliance with the API and/or I dislike the API.

CallableStatement

Not implemented. MySQL doesn't support a callable statement and so far as I am aware the developers have no plans to do so. In the far future I may implement this with methods to throw appropriate exceptions just to maximize API compliancy. OTOH there is the even more remote possibility of providing this function solely through this driver.

Connection
All methods except the constructor will test that the connection is open and throw an exception if it is not.
setCatalog()	A catalog refers to what the MySQL documentation and community call a "database".
getCatalog()	Returns the current "database" in MySQL argot.
clearWarnings()	Currently does nothing.
commit()	Docs, (in dbmd) say commit should be a no-op. Previously threw.
getWarnings()	Currently returns null. In the future will make the error stack from the most recent error available. Note that the API is potentially flawed in re threading as there is no inherent connection between this request and the one which caused the warnings.
getTransactionIsolation()	Returns TRANSACTION_NONE
setTransactionIsolation()	Accepts TRANSACTION_NONE, else throws an exception.
rollback()	throws an exception.
setAutoCommit()	Accepts true else throws exception.
getAutoCommit()	Returns true.
setReadOnly()	MySQL doesn't support read only connections. tjFM throws an exception here. If there is interest this option could be implemented in the driver in the future.
isReadOnly	returns false.
getDataBaseMetaData()	Now supported if not thoroughly tested.
nativeSQL()	Echos back the parameter.
prepareCall()	MySQL doesn't support called statements. Throws an exception.
prepareStatement()	PreparedStatement is implemented wholly within tjFM; MySQL does not natively do them. The PS methods exist as a means of sending binary data to MySQL, which would otherwise be impossible because of the String representation of data in the execute methods.

DataBaseMetaData
Now implemented but only lightly tested. Fixed value methods return, probably mostly right. Most ResultSet methods will throw an SQLException. getCatalogs(), getTables(), and getColumns() now work as per the API, but schema specifications must be null. getTableTypes() will return only "TABLE". Pattern matching closely matches the API but includes the escape sequences in MySQL's LIKE. Note that DBMD methods are not as efficient as calling the MySQL "show" methods as that is how they are generated with the additional overhead of translation into the DBMD specified ResultSets.
getBestRowIdentifier()	Will scan the key definitions for a unique key of a single auto-incremented column and return info on that, failing this it will return info on the primary key, otherwise returning an empty ResultSet.
getCatalogs()	implemented.
getColumns()	implemented.
getIndexInfo()	Returns information about all or just the unique keys depending on the "unique" parameter. No statistics are returned, only the list of keys and the data discernable through "show keys" The "approximate" parameter is ignored.
getPrimaryKeys()	implemented.
getTables	implemented.

Driver

Fairly consistent with the API.

PreparedStatement
PreparedStatement is implemented as a means of sending binary data to MySQL. The parameter string in the prepareStatement of Connection uses question marks as the parameter substitution character. Against the need to use a question mark in the instantiation string, the question mark is self escaping. Note that upon submission to the database all the parameters (except NULLs) will be apostrophe encapsulated [MySQL doesn't care, even for numeric data].
setXXX()	Many of the API setXXX methods have parameters to specify a java.sql.type. Since the communication between the driver and MySQL happens as octet strings and MySQL does all necessary type conversion internally, these type parameters are universally ignored in tjFM.
setAsciiStream() setBinaryStream() setUnicodeStream()	These are all treated the same. The use of the length parameter in the API is not clearly documented. tjFM uses it as a maximum length. The stream is NOT closed so that if the stream is otherwise unmolested subsequent calls would read subsequent data until the stream is exhausted [testable by ready()]. It must be noted that data is read AT THE TIME OF EXECUTE, not when the set method is called.
setObject()	This method will insert the string representation of the object into the parameter unless an sqltype is specified as java.sql.Types.OTHER in which case the object will be stored as a serialized object.

ResultSet
wasNull()	Adheres to the API, but this is not well thought out on the part of SUN: should different threads access the RS wasNull() could easily give bogus results. See similar concerns about Warnings.
getWarnings()	Currently returns null. In the future will make the error stack from the most recent error available. Note that the API is potentially flawed in re threading as there is no inherent connection between this request and the one which caused the warnings.
clearWarnings()	Does nothing at present.
getCursorName() setCursorName()	MySQL doesn't do cursors. Puke. ... In a later release I may support this within the driver.
getBoolean()	The current release of MySQL doesn't support booleans though this will change shortly. For the present if the db returns some variant of "true", "false", "yes", "no", 0 or 1 this will return the appropriate boolean value.
getAsciiStream() getBinaryStream()	Because MySQL does not distinguish between binary and text data these are treated the same, returning a stream representation of the bytes returned from MySQL.
getUnicodeStream()	This assumes that java does it right in converting bytes to unicode in String, then back into bytes via getBytes (then back to unicode in InputStream [ possibly I could skip the intermediate steps] ).
getObject()	This will return an object of a type matching the column type as closely as possible. All text types (no binary flag - see the MySQL Reference) will be returned as String. Numeric types are fairly intuitive, but decimals will be returned as doubles. All blobs will be returned as byte arrays. Note that Object deserialization has been completely disabled within this method (MySQL has no way of flagging such an entity in the metadata); Object deserialization can still be accomplished by instantiating an ObjectInputStream with the result of a getBinaryStream. (See jdbctest2.java in ./twz1/jdbc/mysql/test for an example).

ResultSetMetaData
isAutoIncrement	A very recent version of MySQL has code to return this from the db but I haven't yet upgraded. Will support this shortly. Currently always returns false.
isCurrency()	Always false.
isSearchable()	Always true.
getColumnDisplaySize()	Because all variable size elements are stored as BLOB's in MySQL this will be very large for all variable text and binary objects regardless of the actual table definition.
getPrecision()	Returns the size of the column if it is numeric. No accounting for the decimal point, sign or mantissa. Sorry. Suggestions welcome. Throws up if type is not numeric.
getScale()	Returns the number of digits on the right of the decimal point for a numeric type. Pukes if not numeric.
getColumnType() getColumnTypeName()	Some confusion here because of the way MySQL represents variable strings (and my failure to investigate properly). Currently all variable-defined strings return as VARBINARY and VARCHAR where they are defined as fixed. Needs fixed.
getCatalogName()	Oops. Forgot this. Will be there soon.
isReadOnly()	Always false.
isWritable() isDefinitelyWritable()	Always true.

Statement
close()	The API doc doesn't say whether a closed statement can be subsequently reused. I render it unusable. This can easily be changed or made configurable if there is sufficient reason.
getMaxFieldSize() setMaxFieldSize	There are dependencies on the communication buffer size which are not reflected in the API. This value has a default of 65k which can be overridden at instantiation by the "maxField" property. A future release may have warnings when data is truncated, but the API says data is "silently truncated".
getMaxRows() setMaxRows()	Default at instantiation is 200, overridable by the "maxRows" property.
setEscapeProcessing()	As MySQL's escape processing is implicit and invariant this throws an exception. In a future release I may implement means of local escape processing.
getQueryTimeout() setQueryTimeout()	Varies from the API meaning because MySQL's connections require that a processing query complete before further processing continues. Hence, Statement, ResultSets and ResultSetMetaData requests lock at the statement level before continuing. These methods set/query the time until a lock expires (and an exception is thrown to the invoking process). I understand a future version of MySQL will have the ability to cancel a processing query; once that is so, this may be changed to more closely model the evident intent of the API.
cancel()	Because of statement locks (see get/setQueryTimeout()) this will only invalidate any currently valid results. When MySQL supports query cancellation, this may be changed to properly reflect the API.
clearWarnings()	Currently does nothing.
getWarnings()	Currently returns null. In the future will make the error stack from the most recent error available. Note that the API is potentially flawed in re threading as there is no inherent connection between this request and the one which caused the warnings.
setCursorName()	MySQL doesn't curse, but you may as this throws an exception.

Extra API methods and "private" SQL statements

There are things one may wish to do with a database which are not part of the API. Some things may be specific to the db in question. Others may have to do with configuration of the driver. Still others may be oversights in the API.

twz1jdbcForMysql was designed to be highly configurable. However tjFM is still brand new and I have concentrated more on getting it running and usable than on implementing the "bells and whistles", hence there is a great deal of potential which is as yet unrealized.

Access to tjFM will be by two means, calls to static methods in jdbcMysqlBase or by a "private" - that is beginning with "jdbc:" - SQL statement passed to the execute() method of "Statement". It should be noted that using either of these will result in code which is not portable to other databases. The latter means will compile cleanly and may run cleanly if the exceptions are properly caught and dealt with.

As of this writing private SQL is not implemented at all, and only a few of the jdbcMysqlBase methods are available. You can look at the twz1.jdbc.mysql.jdbcMysqlBase.html file generated by javadoc.

Update: long jdbcMysqlBase.getLastInsertID(Statement) now works if the driver supports it. A return of -1 means that the last statement was not an insert, the driver doesn't support the automatic return of lastInsertID or the lastInsertID was -1.

To use the jdbcMysqlBase methods you call them statically as

<value> = jdbcMysqlBase.<method>(<parameters>);

jdbcMysqlBase.setMultipleQuery((jdbcMysqlConnex)books, false);

While not yet available the eqivalent of the above example for a private sql statement, would be, if there existed a Statement created under "books" called "shelve", would be:

shelve.execute("jdbc: setMultipleQuery false");

Currently there are static methods to retrieve the names of the driver default properties (those actually set, not those at their default values), the value of a named driver default property, to query or set MultipleQuery on a connection, and to turn on or off Debug dumps of read/write packets across the communication with the MySQL daemon. Many more are planned.

Thanks

Many people have been of valuable assistance while I have written this. The following list is not exhaustive - I lost my HD some months back and lost some correspondence (please don't be upset if you're not on the list but should be), but among others I would like to thank:

• Monty, aka Michael Widenius, not just for writing a great DB but also for his patience with my many questions about some of the details of the communications protocol and similar issues.
• Steve Baker
• Micheal Conner
• Tim Endres
• Costas Giannacoulis
• David Gutierrez
• Jay 'Whip' Grizzard
• Chuck Huber
• Inprise, nee Borland, for their contribution to this project.
• Zsolt Koppany
• Ambrose C. Li
• Christian Mack
• Tauren Mills
• Mark Matthews for the whip of competition.
• Cris Perdue
• Bojan Pisler one of the first testers who helped shape tjFM in its earliest days.
• Alex Rice
• Benoit Rigaut
• Neil Ryden
• Uwe Schuerkamp
• Paolo Summaruga
• Richard Swift
• Staffan Ulfberg
• Seth White
• Wenjun WU
• Dr. Kelvin ZHU
• Ray Zimmerman

Thank you, for your suggestions, requests, bug reports, cooperation, assistance and persistance. Even when directed only towards your own problems your efforts have helped me make tjFM better for everybody, and when directed toward the common good there is nothing left to say except again, Thanks!

jdk 1.2 addendem

Sun has now released jdk1.2 and JDBC 2.0. tjFM does not yet have all the corresponding methods for 2.0 to match the abstract classes and hence cannot be compiled under a jdk1.2 system.

Of course I will be upgrading tjFM for 1.2 compliancy, but as I run only Linux at home that will have to wait until the Linux-java porting project release it and then some time for me to write in and test the additional methods.

As most of the additional methods in JDBC 2.0 concern features which are irrelevant to MySQL (of course a few will be quite useful..) this should not prevent anyone from using tjFM as is -- compiled under 1.1 -- while running under 2.0 so long as they use only the backward compatible features.

Unfortunately, there's a snag - or rather a bug: 4172371 (Thanks Neil! [for tracking it down]) - in the new version of the jvm which produces a verify error for some classes compiled with the -O option under 1.1.

To circumvent this I have created a new directory under twz1, twz1/noopt/twz1/jdbc/mysql which contains a non-optimized version of the classes. To use this in preference to the optimized classes you point your classpath at twz1/noopt rather than at the directory above (the first) twz1.

The future

"... hasn't been written yet. It's whatever you make it to be, so make it a good one!"

With that thought, considering how much there is to do, I'm going to go start on it right now.

The more retrospective folks can gander at the CHANGES file. Similarly the entire javadoc generated file should be available wherever you're reading this under packages.html.

Please let me know of problems you find in this package and or changes you think would be useful. I can be reached at terrence.w.zellers@pobox.com

-- TWZ

-- TWZ 980928