3 messages in com.mysql.lists.javaRe: Selecting data with a PreparedSta...| From | Sent On | Attachments |
|---|---|---|
| David Griffiths | 11 Jun 2004 15:26 | |
| Mark Matthews | 13 Jun 2004 09:16 | |
| David Griffiths | 13 Jun 2004 14:56 |
| Subject: | Re: Selecting data with a PreparedStatement and setNull() |
|---|---|
| From: | David Griffiths (dgri...@boats.com) |
| Date: | 06/13/2004 02:56:10 PM |
| List: | com.mysql.lists.java |
Mark,
Thanks for the reply. I ended up using the null-safe "equals" - <=> and it works fine (is there a performance hit for using that?).
You are 100% right about no other databases doing it. I seemed to remember Oracle would do it (haven't used it in a few years), but I did some searching, and found you're right - you have to code your SQL to be (column = ? OR column IS NULL). The null-safe equals is a much better approach. I guess it's outside the JDBC specs to correct bad SQL.
David
Mark Matthews wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
I've done a search through the archives, and on Google, but have't found much insight into this.
I am doing a database comparison between a MySQL database and a non-MySQL database.
I've written some code that will find a row in the non-mysql database, and using the primary key, look that row up in MySQL. The select clauses (and primary keys) are created using meta-data from the non-MySQL database.
One issue I ran into was a table that had no primary key. My solution was to select the row out from "not-MySQL" and then build a where-clause using every column in the table.
Most of the columns are nullable on this primary-keyless table, and when trying to find the matching row in MySQL, it can't be found unless all the parameters in the where-clause are not-null.
If a column is null, it uses the java.sql.Types object to set the correct type (VARCHAR, NUMERIC, INTEGER, and so forth) in the setNull().
Is the PreparedStatement correctly changing "=" to "IS NULL" if setNull is used, or is it going to "= null"? I've tried 3.0.8, 3.0.11, and the latest (3.0.14) jar files.
I've dumped the sql and the parameters out, and run the queries by hand, and they always return the data, as I am using "IS NULL" where appropriate (or the null-safe equals, <=>).
I guess this all boils down to, does the Connector/J properly handle setNull, converting the SQL to "IS NULL" or " <=> NULL" rather than "= NULL"?
No, Connector/J does _not_ do this (and I am not aware of other major databases that do either). The SQL standard itself says you have to use IS NULL even for prepared statements or callable statements AFAIK, and this appears to be the case, at least for Oracle and SQL Server.
I'd be curious as to which 'not-MYSQL' database does allow this, in any case.
-Mark
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAzH4FtvXNTca6JD8RAu8oAJ0aMXIq1nBf5AqoY7fD6v8pae9XWgCePRPR xLmO2TT9CePnQCpCP0U1HE0= =2YLu -----END PGP SIGNATURE-----