Re: Interactive query works, JDBC fails, related to correlation names

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Stuart Friedberg wrote:
> Windows 2000 w/SP3, using Connector/J 3.0.6-stable against
> mySQL 3.23.55.  Also tried
>   mysql-connector-java-3.0-nightly-20030311-bin.jar
>   mysql-connector-java-3.1-nightly-20030311-bin.jar
> to no avail.
> 
> Found a fairly simple query that works interactively but not
> through JDBC.  Seems to be an issue with correlation names,
> but I don't see why JDBC would do anything but pass the
> query through.
> 
> mysql> describe AgentName;
> +------------+-------------+------+-----+---------+-------+
> | Field      | Type        | Null | Key | Default | Extra |
> +------------+-------------+------+-----+---------+-------+
> | ID         | int(11)     |      | PRI | 0       |       |
> | PrintsAs   | varchar(64) |      | MUL |         |       |
> | AgentID    | int(11)     |      | MUL | 0       |       |
> | Family     | varchar(32) |      |     |         |       |
> | Given      | varchar(32) |      |     |         |       |
> | Generation | varchar(16) |      |     |         |       |
> | Title      | varchar(16) |      |     |         |       |
> | SortsAs    | varchar(32) |      |     |         |       |
> +------------+-------------+------+-----+---------+-------+
> 
> mysql> describe Collaboration;
> +--------------+---------+------+-----+---------+-------+
> | Field        | Type    | Null | Key | Default | Extra |
> +--------------+---------+------+-----+---------+-------+
> | AgentID      | int(11) |      | PRI | 0       |       |
> | MemberID     | int(11) |      |     | 0       |       |
> | MemberSeq    | int(11) |      | PRI | 0       |       |
> | MemberNameID | int(11) |      |     | 0       |       |
> +--------------+---------+------+-----+---------+-------+
> 
> The following works fine using the mySQL Windows client:
>   mysql> SELECT AN.AgentID, AN.ID, PrintsAs, Family,
>       ->        Given, Generation, Title, SortsAs
>       ->   FROM AgentName AS AN, Collaboration AS CO
>       ->   WHERE CO.AgentID = 1
>       ->     AND CO.MemberNameID = AN.ID
>       ->   ORDER BY CO.MemberSeq;
> Using JDBC, I get
>   General error,  message from server: "Table 'db.AN' doesn't exist"
> Changing the correlation name from AN to XX changed the message, but
> not the problem.
> 
> Modifying the JDBC query by replacing the correlation name in the
> WHERE clause with the actual table name:
>   SELECT AN.AgentID, AN.ID, PrintsAs, Family,
>          Given, Generation, Title, SortsAs
>     FROM AgentName AS AN, Collaboration AS CO
>     WHERE CO.AgentID = 1
>       AND CO.MemberNameID = AgentName.ID
>     ORDER BY CO.MemberSeq
> yields
>   General error,  message from server: "Unknown table 'AgentName' in where clause"
> which is possibly a clue as to what's going on, as the table definitely
> exists.
> 
> I was able to get the JDBC query working with
>   SELECT AgentName.AgentID, AgentName.ID, PrintsAs, Family
>          Given, Generation, Title, SortsAs
>     FROM AgentName, Collaboration AS CO
>     WHERE CO.AgentID = 1
>       AND CO.MemberNameID = AgentName.ID
>     ORDER BY CO.MemberSeq
> That seems to point at a problem with correlation names.  However,
> I have several similar queries using the same tables and correlation
> names that don't have problems.
> 
> I tried another combo and got a different error message
>   SELECT AgentName.AgentID, AgentName.ID, PrintsAs, Family,
>          Given, Generation, Title, SortsAs
>     FROM AgentName AS AN, Collaboration AS CO
>     WHERE CO.AgentID = 0
>       AND CO.MemberNameID = AN.ID
>     ORDER BY CO.MemberSeq
> yields
>   General error,  message from server: "Unknown table 'AgentName' in field list"
> 
> Suggestions welcomed for pinning this down further.


Are you asking for updatable result sets? If so, the driver is getting 
confused while trying to build the SQL queries to make that possible. 
Either don't ask for Updatable result sets (pass the appropriate flags 
to createStatement(), or add 'strictUpdates=false' to your JDBC URL to 
prevent the driver from using the stricter (but sometimes confusable 
code) method of generating these statements.

The real fix is in the works, but it will require MySQL 4.1, as only 
MySQL-4.1 returns the original column and table names as part of the 
field-level metadata.

	-Mark


- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
  /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
         <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+drEKtvXNTca6JD8RAhbKAJ9S0Q7eR73m8ukSaDozejbevI6KnwCfVJ+Q
hcDnAsQ328rlUsezfC1m8y4=
=42Bx
-----END PGP SIGNATURE-----