Field names conflicting with SQL reserved words

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

Field names conflicting with SQL reserved words

Post by jeremyw » 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).

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Fri 30 Apr 2010 08:03

Why don't the SDAC components do the same thing behind the scenes just for this very reason?
I guess, because you are working with tmsdataset.options.quotenames = false and so explicitly telling SDAC, it should NOT do that :)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 30 Apr 2010 10:15

To solve the problem set the TMSQuery.Options.QuoteNames property to True.

jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

Post by jeremyw » Mon 03 May 2010 16:54

Awesome thanks! I was hoping there would be a simple property like this that I was overlooking. I'll have to take a closer look at all the possible options now.

On a side note, does this quote field names only, or will it also quote table names too? I ask because I have a table named Case (another reserved word) which has given me some problems, and it would be annoying having to find and replace all the SQL commands querying this table.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 05 May 2010 07:27

When TMSQuery.Options.QuoteNames is True, then field names and table names are quoted.

Post Reply