Select problem

I am converting my Access and MS SQL databases to MySQL and have a few
problems:

The following works in Access and MS SQL but not MySQL:

SELECT * FROM [CFG] WHERE [Id] = "XXX"
SELECT * FROM [USER] WHERE [Name] = "XXX"
SELECT * FROM [PRODUCT] WHERE [1] = "XXX"

In the last SELECT, the fields are numbered 1 to 10 in the PRODUCT table.

The reason I enclosed all by table names in [ and ] is because USER is a
special name in MS SQL.

The following works in MySQL:

SELECT * FROM CFG WHERE Id = "XXX"
SELECT * FROM 'USER' WHERE Name = "XXX"

But I cannot work around the last one where the field number is a number.

It is possible to configure MySQL so that it can recognise the [ and ]
brackets?

How can I include in the WHERE clause where the field name is a number?

Any help greatly appreciated.

Tks
John

Re: Select problem

zMisc wrote:

> The following works in Access and MS SQL but not MySQL:
>
> SELECT * FROM [CFG] WHERE [Id] = "XXX"
> SELECT * FROM [USER] WHERE [Name] = "XXX"
> SELECT * FROM [PRODUCT] WHERE [1] = "XXX"

You can't use [], you should use a pair of `` instead. Here's an example:


mysql> create table number(`user` varchar(255), `1` text );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into number values( 'test', 'hello' );
Query OK, 1 row affected (0.01 sec)

mysql> select * from number where `1` = 'hello';
+------+-------+
| user | 1 |
+------+-------+
| test | hello |
+------+-------+
1 row in set (0.00 sec)

Re: Select problem

"zMisc" wrote in message
news:20ZDf.232768$V7.45499@news-server.bigpond.net.au...
> SELECT * FROM [PRODUCT] WHERE [1] = "XXX"
> In the last SELECT, the fields are numbered 1 to 10 in the PRODUCT table.

You can use any name you like if you enclose the field names in delimiters.
MySQL uses backticks (``) by default, and recent versions of MySQL can be
configured to use double-quotes ("") in compliance with the SQL standard.

Apparently MS Access uses square brackets for the purpose of delimiting
special identifers, though this is definitely nonstandard syntax and is not
likely to be compatible with other RDBMS implementations.

May I suggest that it is unusual -- and confusing -- to use an integer as an
identifier? I can't think of any programming language (C, Java, etc.) in
which this is permitted.

Regards,
Bill K.