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_NAME:DEFERR
ABILITY:
bet::markettype:`markettype_id`:bet::market:`type`:1:1:1:NUL L: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


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: SQLForeignKeys result-set question

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.

-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
Sent: Thursday, 3 November 2005 3:20 AM
To: myodbc@lists.mysql.com
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:NUL L: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 =3D
'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 =20
|
+--------------------+-------------------+-----------------+ ------------
---+----
----------+------------+-------------+------------------+--- ------------
--------
--------+-------------------------+-----------------------+- ------------
--------
---+
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



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: SQLForeignKeys result-set question

On 06-Nov-2005 jbonnett@sola.com.au 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@easysoft.com]
> Sent: Thursday, 3 November 2005 3:20 AM
> To: myodbc@lists.mysql.com
> 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:NUL L: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

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: SQLForeignKeys result-set question

--------------020205010904080902010609
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Martin J. Evans wrote:

>On 06-Nov-2005 jbonnett@sola.com.au 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.
>>
>>
Martin asked me to take a look at this. The attached patch should do the
trick.

The problem as far as I can see is the primary and foreign column
information comes from the line

InnoDB free: 11264 kB; (`type`) REFER
`martin/eventtype`(`eventtype_id`); (`mark

And I think the driver just needs to remove the quotes.

--
Nick Gorham
Easysoft Limited

--------------020205010904080902010609
Content-Type: text/plain;
name="patch.myodbc"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="patch.myodbc"

diff -r -u mysql-connector-odbc-3.51.12/driver/catalog.c mysql-connector-odbc-3.51.12a/driver/catalog.c
--- mysql-connector-odbc-3.51.12/driver/catalog.c 2005-10-09 20:33:40.000000000 +0100
+++ mysql-connector-odbc-3.51.12a/driver/catalog.c 2005-11-08 11:02:38.000000000 +0000
@@ -1978,9 +1978,27 @@
{
/* Multiple columns exists .. parse them to individual rows */
char **prev_data= data;
- data[7]= strdup_root(alloc,ref_token); /* FKTABLE_COLUMN */
+ /*
+ * remove quote chars
+ */
+ if ( ref_token[ 0 ] == '`' )
+ {
+ data[7]= strdup_root(alloc,ref_token+1); /* FKTABLE_COLUMN */
+ data[7][strlen(data[7])-1 ] = '\0';
+ }
+ else {
+ data[7]= strdup_root(alloc,ref_token); /* FKTABLE_COLUMN */
+ }
pktoken= my_next_token(pktoken,&pkcomment,ref_token,' ');
- data[3]= strdup_root(alloc,ref_token); /* PKTABLE_COLUMN */
+ if ( ref_token[ 0 ] == '`' )
+ {
+ data[3]= strdup_root(alloc,ref_token+1); /* FKTABLE_COLUMN */
+ data[3][strlen(data[3])-1 ] = '\0';
+ }
+ else
+ {
+ data[3]= strdup_root(alloc,ref_token); /* PKTABLE_COLUMN */
+ }
sprintf(ref_token,"%d",key_seq++);
data[8]= strdup_root(alloc,ref_token); /* KEY_SEQ */
data+= SQLFORE_KEYS_FIELDS;
@@ -1988,8 +2006,25 @@
for ( fk_length= SQLFORE_KEYS_FIELDS; fk_length--; )
data[fk_length]= prev_data[fk_length];
}
- data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */
- data[3]= strdup_root(alloc,pkcomment); /* PKTABLE_COLUMN */
+ /*
+ * remove quote chars
+ */
+ if ( fkcomment[ 0 ] == '`' )
+ {
+ data[7]= strdup_root(alloc,fkcomment+1); /* FKTABLE_COLUMN */
+ data[7][strlen(data[7])-1 ] = '\0';
+ }
+ else {
+ data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */
+ }
+ if ( pkcomment[ 0 ] == '`' )
+ {
+ data[3]= strdup_root(alloc,pkcomment+1); /* PKTABLE_COLUMN */
+ data[3][strlen(data[3])-1 ] = '\0';
+ }
+ else {
+ data[3]= strdup_root(alloc,pkcomment); /* PKTABLE_COLUMN */
+ }
sprintf(ref_token,"%d",key_seq);
data[8]= strdup_root(alloc,ref_token); /* KEY_SEQ */


--------------020205010904080902010609
Content-Type: text/plain; charset=us-ascii


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
--------------020205010904080902010609--