From: Martin J. Evans
Date: November 7 2005 2:11pm
Subject: RE: SQLForeignKeys result-set question
List-Archive: http://lists.mysql.com/myodbc/10436
Message-Id: <XFMail.20051107141104.martin.evans@easysoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit

On 06-Nov-2005 jbonnett@stripped wrote:
> I would think the back-ticks (not single quotes) are there because '_'
> is a wild card character in SQL and 'type' is a keyword.
> 
> I guess the back-ticks are not there in your later example because it is
> just the output of a query.
> 
> John B.

John,

Thanks for the response but:

I don't think it matters whether type is a reserved word or _ is a wildcard.
The ODBC spec says the columns in the SQLForeignkeys result-set should be their
name - not some quoted form of it. In other words, I should be able to take a
column from the result-set and quote it but I can't with myodbc.

Also, I think _ is only a special character in "like" and type is not a column
called type but part of a column name.

Try this with Oracle, MS SQL Server and you see they don't quote the columns -
it just works.

Now that I think of it I seem to remember that bugzilla had a similar problem
with MySQL and the tables call which was quoting some table names.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development


> -----Original Message-----
> From: Martin J. Evans [mailto:martin.evans@stripped] 
> Sent: Thursday, 3 November 2005 3:20 AM
> To: myodbc@stripped
> Subject: SQLForeignKeys result-set question
> 
> Hi,
> 
> I'm using mysql-connector-odbc-3.51.12. I have two tables "market" and
> "markettypes" and market has a foreign key on column type which is
> column
> markettype_id in the table marketypes. When I run SQLForeignKeys I get:
> 
> PKTABLE_CAT:PKTABLE_SCHEM:PKTABLE_NAME:PKCOLUMN_NAME:FKTABLE_CAT:FKTABLE
> _SCHEM:F
> KTABLE_NAME:FKCOLUMN_NAME:KEY_SEQ:UPDATE_RULE:DELETE_RULE:FK_NAME:PK_NAM
> E:DEFERR
> ABILITY:
> bet::markettype:`markettype_id`:bet::market:`type`:1:1:1:NULL:NULL:7:
> 
> The colon is the column separator here. My question is, why is the
> PKCOLUMN_NAME and FKCOLUMN_NAME contents created with single quotes
> around the
> name.
> 
> If I do:
> 
> mysql> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
> table_name =
> 'market' and referenced_table_name is not null;
> +--------------------+-------------------+-----------------+------------
> ---+----
> ----------+------------+-------------+------------------+---------------
> --------
> --------+-------------------------+-----------------------+-------------
> --------
> ---+
>| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME |
> TABLE_CATALOG |
> TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
> POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA |
> REFERENCED_TABLE_NAME
>| REFERENCED_COLUMN_NAME |
> +--------------------+-------------------+-----------------+------------
> ---+----
> ----------+------------+-------------+------------------+---------------
> --------
> --------+-------------------------+-----------------------+-------------
> --------
> ---+
>| NULL               | bet               | market_ibfk_1   | NULL
>|
> bet          | market     | type        |                1 |
> 
>          1 | bet                     | markettype            |
> markettype_id   
>       |
> +--------------------+-------------------+-----------------+------------
> ---+----
> ----------+------------+-------------+------------------+---------------
> --------
> --------+-------------------------+-----------------------+-------------
> --------
> ---+
> 1 row in set (0.00 sec)
> 
> The column names are without the single quotes.
> 
> I had a quick look in myodbc odbc driver code and could not spot
> anything doing
> this.
> 
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development