JDBC database independance?

HI.

I have done some quick experiments with MySQL and have found something that makes me wonder about how database independance can be acheived when using JDBC.

I created a table in MySQL using the following DDL:

create table TestTable (
anID varchar(20) not null primary key,
aBoolean bool,
);

Since MySQL doesn't have a boolean type it uses tinyint instead (a byte).

I would have expected the following JDBC statement to work:

statement.executeUpdate("insert into TestTable (anID, aBoolean) values ('XY12345', true);");

But it gives an exception:

java.sql.SQLException: Column not found, message from server: "Unknown column 'true' in 'field list'"

Thinking that maybe it needs the literal quoted I tried putting putting single quotes around it, which doesn't produce a runtime error, but always produces the same value (0) in the aBoolean field no matter what I enter ('true', 'false', 'wibble', whatever).

Trying with a prepared statement, and using the setBoolean method, all works as expected - 1 gets entered into the field for true, and 0 gets entered for false.

The getBoolean method also correctly produces the correct boolean values for the values entered into the table.

I realise this is a pretty small experiment to base anything on, but it got me wondering just how database independant the standard statement.execute("SQL statement") method of doing things is.

Does the JDBC driver parse the "SQL statement" and mangle it to suit the eccentricities of the target DBMS, or does it just pass it straight to the database?

In my MySQL example it doesn't look like passing a literal boolean value is handled by the JDBC driver, although I'm sure I've used boolean literals for other DBMSs. So either the MySQL JDBC driver is not doing something that it should be doing (parsing the SQL statement, and translating the boolean literals into bytes (tinyints), or the execute statemenets are not database independant, whereas using prepared statments is more database indpendant.

I have had a little look on the web, and in the (admittedly ancient) JDBC textbooks that I have access to, and can't find anything that clears this up for me.

Are the execute/executeUpdate/executeQuery methods not that database independant (they just pass the SQL statements on to the database without examining/translating the statement), or is the MySQL JDBC driver lacking boolean translation for the execute statements?

TIA

Mark

edit[wrote compiler error, when I meant runtime error]

I will attempt to answer from my own personal knowledge. The execute statement is sent to the underlying database unparsed. When MySQL receives the value, true it attempts to find a column named true. Thus your column error. When the database recieves the literal 'true', it knows that the column is defined as int and strings are not permitted. The database will put the default value of 0 into the field.
...
Prepared statements do exactly that. They prepare a statement and each database driver will `message` the data for you. This is one of the reasons that using prepared statements is preferred in the community. Another reason being security.
...
This is not a definitive answer yet a personal reflection.

Thank you. That would explain the behaviour I have noticed.

I'm still a bit concerned about the whole execute methods "passing the statements to the database untouched" situation. One of JDBC's claims to fame is that it is supposedly database independant, yet this behaviour is definitely not that - the database independance would have to come from both the developer and the database sticking to ANSI standards, which is probably what largely happens, but in no way does this involve JDBC.

Most reference books and tutorials for JDBC use the execute approach, which gave me the impression that that was probably an accepted way of doing things, but is apparently not the best way.

Does JDBC not attempt at all to give the JDBC driver a chance to translate the statement, or is it just that the MySQL driver is crap?

I just tried playing with the Connection.nativeSQL method to see if the MySQL driver will do something useful with the boolean literals - it didn't, the resulting SQL statement was untouched, and still contained the boolean literals. I'm starting to think that the MySQL driver is lacking.

My guess (I have no proof to back this up, but it makes the most sense to me at the moment) as to how this all works is: JDBC requires an ANSI SQL compliant database, or at least a JDBC driver that pretends to be ANSI compliant. The driver is given a chance to tweak things if it needs to (which the MySQL driver is not doing for the boolean stuff at least).

I will have a poke around in the source code to figure this out for myself sometime, but if anyone who knows the answer could clarify this for me it would be great.

Thanks.

Mark