Hi,

When working wih a SQL dialect 3 database and field names in upper/lower case, I used to reference those fields in double quotes on my old Delphi 2010 with IBX, which worked well enough. After migrating to XE7 with the latest FireDAC, I set IndexFieldNames to "Mmm" including the double quotes. "Mmm" is also my only primary key field. When opening the table, FireDAC executes an SQL statement that ends with {code}ORDER BY A."Mmm" ASC, A.MMM ASC{code} which of course delivers an exception because there is no
 field MMM.

I hope it is ok if I post some single lines of code here to explain the issue - no copyright infringement intended. @Mods: Please delete if this is not acceptable.

Some hours of FireDAC source code debugging, these are my findings: IndexFieldNames are accepted perfectly, bute then FireDAC.Comp.Client line 11082 reads {code}FPrimaryKeyFields := oView.Rows.GetValuesList('COLUMN_NAME', ';', '');{code} which returns Mmm without the double quotes. Later, FireDAC.Phys.SQLGenerator line 1860 executes {code}sIndexFields := FDMergeFieldNames(ATableParams.FIndexFields, ATableParams.FPrimaryKeyFields);{code} with the parameters being "Mmm" and Mmm (i.e. with and without the qu
otes), which turns sIndexFields to '"Mmm":Mmm'. These are then rightfully treated as different fields, so that FireDAC.Phys.SQLGenerator line 1916 {code}AddOrderBy(sSQLField, lAsc);{code} adds both fields separately which results in the ORDER BY clause I cited initially.

Please tell me that I am overlooking something - I cannot image that FireDAC would have such an obvious mistake with SQL dialect 3 databases. Is there any good documentation where I can learn to circumvent such issues? ( I have tried to remove fiMeta from TFDConnection.Cache, but then I run into other issues which refer to object Mmm not existing...)

Thanks in advance for any hint!

Dirk

Edited by: Dirk Henckels on Jan 22, 2015 1:45 PM

Have you tried dropping the quotes ?
Per 
http://www.da-soft.com/anydac/docu/frames.html?frmname=topic&frmfile=Object_Names.html

"without quotes and AnyDAC will normalize name; "




On 24.01.2015 8:24 AM, Dirk Henckels wrote:
>> {quote:title=Mike Hickman wrote:}{quote}
>> No doubt Dimitry will be more specific but I notice the table property
>> formatOptions.QuoteIdentifiers which defaults to false.
>
> Thanks Mike, but unfortunately QuoteIdentifiers did not change anything. I believe that fetching the primary key fields needs something that is similar to the PostgreSQL special treatment in function NormName of FireDAC.Comp.Client line 11736. I am just not sure if one of the zillions of settings does the job or some code is missing for Firebird dialect 3.
>
> Just to be complete, these are connection and table that I am using:
> {code}
>    object Database: TFDConnection
>      Params.Strings = (
>        'User_Name=dirk'
>        'lc_ctype=ISO8859_1'
>        'password=xxxxxxxxx'
>        'DriverID=FB'
>        'CharacterSet=ISO8859_1'
>        'Protocol=NetBEUI'
>        'Database=\MyDB.fdb'
>        'OpenMode=Open'
>        'MonitorBy=Remote'
>        'Server=myserver')
>      FetchOptions.AssignedValues = [evItems]
>      LoginPrompt = False
>      Transaction = Transaction
>      Left = 256
>      Top = 64
>    end
>    object Table: TFDTable
>      IndexFieldNames = '"Mmm"'
>      Connection = Database
>      Transaction = Transaction
>      FormatOptions.AssignedValues = [fvQuoteIdentifiers]
>      FormatOptions.QuoteIdentifiers = True
>      UpdateOptions.UpdateTableName = '"MyTable"'
>      TableName = '"MyTable"'
>      Left = 416
>      Top = 112
>      object TableMmm: TLargeintField
>        FieldName = '"Mmm"'
>        Required = True
>      end
>      object TableCode: TStringField
>        FieldName = '"Code"'
>        Size = 100
>      end
>    end
> {code}
>
> Thanks again,
>
> Dirk
>

> {quote:title=Mike Hickman wrote:}{quote}
> No doubt Dimitry will be more specific but I notice the table property
> formatOptions.QuoteIdentifiers which defaults to false.

Thanks Mike, but unfortunately QuoteIdentifiers did not change anything. I believe that fetching the primary key fields needs something that is similar to the PostgreSQL special treatment in function NormName of FireDAC.Comp.Client line 11736. I am just not sure if one of the zillions of settings does the job or some code is missing for Firebird dialect 3.

Just to be complete, these are connection and table that I am using:
{code}
  object Database: TFDConnection
    Params.Strings = (
      'User_Name=dirk'
      'lc_ctype=ISO8859_1'
      'password=xxxxxxxxx'
      'DriverID=FB'
      'CharacterSet=ISO8859_1'
      'Protocol=NetBEUI'
      'Database=\MyDB.fdb'
      'OpenMode=Open'
      'MonitorBy=Remote'
      'Server=myserver')
    FetchOptions.AssignedValues = [evItems]
    LoginPrompt = False
    Transaction = Transaction
    Left = 256
    Top = 64
  end
  object Table: TFDTable
    IndexFieldNames = '"Mmm"'
    Connection = Database
    Transaction = Transaction
    FormatOptions.AssignedValues = [fvQuoteIdentifiers]
    FormatOptions.QuoteIdentifiers = True
    UpdateOptions.UpdateTableName = '"MyTable"'
    TableName = '"MyTable"'
    Left = 416
    Top = 112
    object TableMmm: TLargeintField
      FieldName = '"Mmm"'
      Required = True
    end
    object TableCode: TStringField
      FieldName = '"Code"'
      Size = 100
    end
  end
{code}

Thanks again,

Dirk

No doubt Dimitry will be more specific but I notice the table property
formatOptions.QuoteIdentifiers which defaults to false.

http://docwiki.embarcadero.com/RADStudio/XE6/en/Browsing_Tables_%28FireDAC%29


On 23.01.2015 7:48 AM, Dirk Henckels wrote:
> Hi,
>
> When working wih a SQL dialect 3 database and field names in upper/lower case, I used to reference those fields in double quotes on my old Delphi 2010 with IBX, which worked well enough. After migrating to XE7 with the latest FireDAC, I set IndexFieldNames to "Mmm" including the double quotes. "Mmm" is also my only primary key field. When opening the table, FireDAC executes an SQL statement that ends with {code}ORDER BY A."Mmm" ASC, A.MMM ASC{code} which of course delivers an exception because there is 
no
>   field MMM.
>
> I hope it is ok if I post some single lines of code here to explain the issue - no copyright infringement intended. @Mods: Please delete if this is not acceptable.
>
> Some hours of FireDAC source code debugging, these are my findings: IndexFieldNames are accepted perfectly, bute then FireDAC.Comp.Client line 11082 reads {code}FPrimaryKeyFields := oView.Rows.GetValuesList('COLUMN_NAME', ';', '');{code} which returns Mmm without the double quotes. Later, FireDAC.Phys.SQLGenerator line 1860 executes {code}sIndexFields := FDMergeFieldNames(ATableParams.FIndexFields, ATableParams.FPrimaryKeyFields);{code} with the parameters being "Mmm" and Mmm (i.e. with and without the 
qu
> otes), which turns sIndexFields to '"Mmm":Mmm'. These are then rightfully treated as different fields, so that FireDAC.Phys.SQLGenerator line 1916 {code}AddOrderBy(sSQLField, lAsc);{code} adds both fields separately which results in the ORDER BY clause I cited initially.
>
> Please tell me that I am overlooking something - I cannot image that FireDAC would have such an obvious mistake with SQL dialect 3 databases. Is there any good documentation where I can learn to circumvent such issues? ( I have tried to remove fiMeta from TFDConnection.Cache, but then I run into other issues which refer to object Mmm not existing...)
>
> Thanks in advance for any hint!
>
> Dirk
>
> Edited by: Dirk Henckels on Jan 22, 2015 1:45 PM
>