Columns named with reserved words

Hello,

I'm using MySQL 5.5.3 and have a table with a column named "Key". When I attempt to insert a record, MySQL barfs with an error similiar to:

Exception: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Key,
LockVersion, UpdatedOn, Value )

From the SQL that is being generated, it seems that column names are not escaped. It looks like this:

INSERT INTO MyTable
(
  Id,
  CreatedOn,
  DeletedOn,
  IdentityId,
  Key,
  LockVersion,
  UpdatedOn
)
VALUES
(
  26,
  '4/6/2012 22:52:05',
  NULL,
  25,
  'SomeKey',
  0,
  '4/6/2012 22:52:05'
);

When I use the MySQL tools, the statement looks as follows:

INSERT INTO MyTable
(
  `Id`,
  `CreatedOn`,
  `DeletedOn`,
  `IdentityId`,
  `Key`,
  `LockVersion`,
  `UpdatedOn`
)
VALUES
(
  26,
  '4/6/2012 22:52:05',
  NULL,
  25,
  'SomeKey',
  0,
  '4/6/2012 22:52:05'
);

Any chance you can ensure that column names are escaped such that column names that matches reserved words do not cause failures?

Thanks,

Werner

From experience, best to pick a different name.

But you can set it here. Docs mention it may cause problems on databases other than SQL server.

http://www.mindscapehq.com/Help/LightSpeed/html/P_Mindscape_LightSpeed_LightSpeedContext_QuoteIdentifiers.htm

To add to chadw's comment, the potential problem (the reason we don't set it by default) is that some databases treat quoted identifiers as case sensitive. I believe it is safe to set QuoteIdentifiers on MySQL unless the MySQL server is running on a Unix OS -- that is, I believe MySQL on Windows does not treat quoted identifiers as case-sensitive.

If your MySQL server is running on Unix or Linux, you should still be fine if the casings match, but if not you may have to set up explicit mappings to the correctly cased names.