Problem solved. It turns out (not suprisingly I guess) to have
nothing to do with InnoDB or the FOREIGN key constratints. After some
checking I realized that the PreparedStatement.setObject() method will
NOT do an implicit conversion to String and hence the comparision to
the VARCHAR column was never satisifed - however, whenver I would
print out the value being set, the System.out.println() was doing the
implicit conversion to String and hence yielding the necessary and
aparently correct value.
So, the fix was to make sure I add the .toString() call inside of the
PreparedStatement.setObject() call and all works as desired.
skidvd@abilsoft.com (skidvd) wrote in message news:<fd0e0d40.0404241908.301cbca9@posting.google. com>...[color=blue]
> Hello:
>
> I have just recently converted to using the InnoDB table type so that
> I can enforce FOREIGN key constraints. I have been using MyISAM
> tables (accessed via JDBC) successfully for some time. However, I
> have just come across a problem with the new configuration that
> boggles my mind....
>
> First some configuration data:
>
> Server version: 4.1.1-alpha-standard
> Protocol version: 10
> mysql-connector-java-3.1.1-alpha
>
> I have created the following table:CREATE TABLE PlatformType
> (
> platformType VARCHAR(255) NOT NULL,
> manufacturer VARCHAR(255) NOT NULL,
> PRIMARY KEY( platformType ),
> INDEX platformType_index ( platformType )
> )
> TYPE=InnoDB;
>
> mysql> desc PlatformType;
> +--------------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------------+--------------+------+-----+---------+-------+
> | platformType | varchar(255) | | PRI | | |
> | manufacturer | varchar(255) | | | | |
> +--------------+--------------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> I then proceed to build a simple test driver that (via JDBC) inserts a
> test record, reads the test record to verify insertion, updates it,
> and finally deletes it. The insert seems to work fine. However, when
> I attempt to select the record with the following PreparedStatement:
>
> select platformType F1, manufacturer F2 from PlatformType where
> platformType = ?
> (the '?' has been appropriately populated with the exact column value
> used at insertion time - verified multiple times).
>
> The query returns no rows! Also no errors, but definately no rows.
> Yet, when I perform the exact same query in the interactive mysql
> interpreter, I do see the expected row.
>
> Additonally, if I modify the query to look like this:
>
> select platformType F1, manufacturer F2 from PlatformType where
> platformType LIKE ? (with the '?' populated with the exact column
> insertion value plus the '%' wildcard)
>
> the select operation then does return the expected row as well.
>
> Please note that there are no other rows in the table.
>
> Finally, please note that the exact same test driver works
> successfully (WITHOUT the need for the LIKE clause) on the following
> very similar table:
>
> CREATE TABLE OSECRegistration
> (
> id VARCHAR(255) NOT NULL,
> hostName VARCHAR(255) NOT NULL,
> platformType VARCHAR(255) NOT NULL,
> registrationTime BIGINT NOT NULL,
> createdBy VARCHAR(255) NOT NULL,
> dateCreated BIGINT NOT NULL,
> modifiedBy VARCHAR(255) NOT NULL,
> dateModified BIGINT NOT NULL,
> PRIMARY KEY( id ),
> INDEX id_index ( id )
> )
> TYPE=InnoDB;
>
> Again, the test for this table is the exact same code base (they are
> both generated in exactly the same fashion) and works fine without the
> need to change '= ?' to 'LIKE %' in order to get the results. Also, I
> have triple verified that the values being sent into the ps.set calls
> for the '?' arguments are correct and have no leading and/or trailing
> garbage, etc. This has also been verified withing the mysql
> interpreter - I use the concat operation to place a '[' char around
> the beginning and end of the column in question (platformType) to
> ensure that no leading/trailing garbage made it in - and it did not.
>
> I'd appreciate any an all assistance that you can provide of
> decipering this mystery as it really has me perplexed (and stressed as
> I need to resolve this fairly quickly). I cannot see MATERIAL
> difference in the table construction that could even remotely explain
> this behavior. Additionally, since the test code works perfectly on
> the other table, I cannot explain why it does not for the PlatformType
> table (which is really quite similar - just fewer columns).
>
> Thanks again for any and all thoughts, ideas, suggestions![/color]
