Quoted fields names (XE2 & SQL Server 2008 R2)

wynn · Post by **wynn** » Mon 09 Apr 2012 20:46

I am upgrading a project from BDS2006 & a previous version of the DevArt dbExpress driver for SQL Server (dbexpsda30.dll) to XE2 & the latest driver (dbexpsda40.dll).
The issue I'm running into is the field names are not being quoted. The UseQuoteChar property is set to true for the TDataSetProvider, and the UseQuoteChar parameter on the TCRSQLConnection is also set to true.
I have also dumped the TCRSQLConnection.MetaData.QuoteChar value, and it is blank. My educated guess is the quoting is actually happening but it's using a blank string.
Any ideas?

AndreyZ · Post by **AndreyZ** » Tue 10 Apr 2012 11:38

Hello,

To make dbExpress driver for SQL Server quote field names, you should set the UseQuoteChar parameter in the TCRSQLConnection.Params property to True. dbExpress driver for SQL Server uses double quotes for quoting field names. You can use Microsoft SQL Server Profiler to check whether dbExpress driver for SQL Server quotes field names or not. If you encountered some particular problem with quoting, please describe the problem in more details.

wynn · Post by **wynn** » Tue 10 Apr 2012 13:46

I have set the UseQuoteChar parameter in the TCRSQLConnection.Params property to True. (In fact, the legacy code was doing this already.)

Using a SQLMonitor component, I can see the resulting SQL statements and the field names are not quoted.

As I mentioned, the TCRSQLConnection.MetaData.QuoteChar appears to be an empty string.

I believe that dbExpress is quoting the field names, but with a blank character.

AndreyZ · Post by **AndreyZ** » Wed 11 Apr 2012 08:57

I cannot reproduce this problem. The TCRSQLConnection.MetaData.QuoteChar property contains double quotes. I have checked this question using Delphi XE2 with Update 4 and dbExpress driver for SQL Server 5.0.3 . Please try using the same versions of Delphi and dbExpress driver for SQL Server, and check if the problem persists. If it does, please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com .

wynn · Post by **wynn** » Wed 11 Apr 2012 12:37

Okay. Thanks for looking into it so far.

AndreyZ · Post by **AndreyZ** » Thu 12 Apr 2012 08:10

We are looking forward to hearing from you.

wynn · Post by **wynn** » Mon 16 Apr 2012 20:52

This issue was finally resolved by explicitly opening the TCRSqlConnection (Connected := True).

More background...
This was a legacy project (BDS2006) being upgraded to XE2 (update 3) and the latest DevArt driver. The server is a SOAP server implemented as an ISAPI dll. The database settings are read from a configuration file when the server data module is created, but the connection was allowed to automatically connect when the TSQLDataSet(s) were opened.

My wild guess is this had more to do with moving to the "new" dbExpress framework (4.0) and reading the metadata from the server. It wasn't happening in a timely manner unless the connection was established before using the datasets. Or it is some other reason entirely.

AndreyZ · Post by **AndreyZ** » Tue 17 Apr 2012 11:58

The TCRSQLConnection component can be used to work with different database servers that have different quote characters. That's why the TCRSQLConnection.MetaData.QuoteChar property shows the correct quote character only after opening a connection. Please specify the exact code you used to obtain the quote character without opening a connection.