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]

Re: reserved words as column names

Matthew Cascio wrote:
[color=blue]
> 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 :-)[/color]

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]

Re: reserved words as column names

Matthew Cascio wrote:
[color=blue]
> My understanding is that using reserved words as column names is
> allowable as long as they are quoted.[/color]

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.