Field names conflicting with SQL reserved words
Posted: Thu 29 Apr 2010 17:46
We're in the process of converting from our old Paradox database to SQL Server (both Express and Compact), and as part of that process we've purchased the SDAC components to interface with our new databases. However, I've run into an issue when trying to post changes to editable queries when the underlying table contain certain field names.
Initially the error messages were completely baffling, but on a hunch I thought it might have to do with the field names found in the tables in question. Sure enough, the issue is tied to the field names themselves, one of which is "Order", just as an example. It appears when using the SDAC components to post data, SQL statements are being generated automatically behind the scenes to carry out the insertions or updates. This generated code is apparently just sticking the associated field names in, which would understandably cause some SQL parsing problems if those field names are reserved words (like Order).
Now obviously it was a bad idea to structure the original database with these field names in the first place, but changing them at this point would necessitate a lot of annoying code changes as well. Is there any other alternative? When writing my own SQL to access these problematic fields, all I've needed to do is wrap the field (or table) name in brackets, such as "SELECT [Order] FROM [TableName]". Why don't the SDAC components do the same thing behind the scenes just for this very reason?
For reference, we are using C++ Builder 2010 and the SDAC VCL components (version 4.80).
Initially the error messages were completely baffling, but on a hunch I thought it might have to do with the field names found in the tables in question. Sure enough, the issue is tied to the field names themselves, one of which is "Order", just as an example. It appears when using the SDAC components to post data, SQL statements are being generated automatically behind the scenes to carry out the insertions or updates. This generated code is apparently just sticking the associated field names in, which would understandably cause some SQL parsing problems if those field names are reserved words (like Order).
Now obviously it was a bad idea to structure the original database with these field names in the first place, but changing them at this point would necessitate a lot of annoying code changes as well. Is there any other alternative? When writing my own SQL to access these problematic fields, all I've needed to do is wrap the field (or table) name in brackets, such as "SELECT [Order] FROM [TableName]". Why don't the SDAC components do the same thing behind the scenes just for this very reason?
For reference, we are using C++ Builder 2010 and the SDAC VCL components (version 4.80).
