reserved words as column names

[Matthew Cascio]

My understanding is that using reserved words as column names is
allowable as long as they are quoted. I am trying to create a table
dynamically with columns defined by the first row of a text file I
import. Unfortunately, I have no control over the column names and
the data provider has chosen to use the word "USE" as a column name.
"USE" is a reserved word.

To handle this, I tried quoting the word during the CREATE TABLE
statement, as follows:

CREATE TABLE my_table (
'use' varchar(100)
)

But this is still causing an error. Any suggestions for avoiding the
obvious workaround of checking column names against reserved words and
changing them when there is a conflict? I'd like to use the column
names as defined by the data provider, if possible.

If it matters, I am running mySQL on Win32 and interfacing through
ColdFusion -- yes, rare and strange combination, but that's what the
client ordered :-)

Thanks all!

Matt

[Chris Hope]

Matthew Cascio wrote:

My understanding is that using reserved words as column names is
allowable as long as they are quoted. I am trying to create a table
dynamically with columns defined by the first row of a text file I
import. Unfortunately, I have no control over the column names and
the data provider has chosen to use the word "USE" as a column name.
"USE" is a reserved word.

To handle this, I tried quoting the word during the CREATE TABLE
statement, as follows:

CREATE TABLE my_table (
'use' varchar(100)
)

But this is still causing an error. Any suggestions for avoiding the
obvious workaround of checking column names against reserved words and
changing them when there is a conflict? I'd like to use the column
names as defined by the data provider, if possible.

If it matters, I am running mySQL on Win32 and interfacing through
ColdFusion -- yes, rare and strange combination, but that's what the
client ordered :-)

Use backticks `

CREATE TABLE my_table (
`use` varchar(100)
)

Remember to always use them in your queries as well eg SELECT `use` FROM
my_table

You really shouldn't use reserved words for column names if you can help it
though, but in this case I guess you need to.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Bill Karwin]

Matthew Cascio wrote:

My understanding is that using reserved words as column names is
allowable as long as they are quoted.

MySQL uses backticks (`), not quotes (') for this purpose.

This is nonstandard SQL; IIRC the SQL standard uses double-quotes (")
for delimited identifiers.

Regards,
Bill K.