FireDAC / Delphi XE7 / Firebird 2.5 + Dialect 3: Key fields turn upper case [Edit]

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
-1
Dirk
1/22/2015 9:48:38 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

4 Replies
1976 Views

Similar Articles

[PageSpeed] 29

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
>
-1
Mike
1/22/2015 11:10:13 PM
> {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
1
Dirk
1/23/2015 10:24:26 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
>
0
Mike
1/23/2015 11:30:40 PM
> {quote:title=Mike Hickman wrote:}{quote}
> 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; "

Yes, I tried that (again), but "normalizing" here means the equivalent to all upper case. Regarding your link, my case is basically line 2 in the table that explains dialect 3 (CREATE PROCEDURE "Test"). I created table and field names with double quotes and upper/lowe case characters, and the only way to use it would be to use quoted field names (see column "will work"). However FireDAC 11 retrieves the primary key fields without the quotes, albeit in upper/lower case, and is later converted in all upper 
case. Which is exactly the third example of line 2, column "will fail".

I am still curious to learn what I am missing here. Or should I file a bug, if nobody knows, since the (AnyDAC) documentation states it should work as I expect? At least for me it seems that FireDAC is not usable for Firebird dialect 3 currently.

Anyway, thank you again Mike for taking the time to respond.

Dirk
0
Dirk
1/25/2015 11:04:38 AM
Reply:

Similar Artilces:

FireDAC / Delphi XE7 / Firebird 2.5 + Dialect 3: Key fields turn upper case [Edit] #2
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 singl...

FireDAC / Delphi XE7 / Firebird 2.5 + Dialect 3: Key fields turn upper case
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. 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 copyri...

Converting Delphi 2007 Indy 10.2.3 to Delphi 2009 Indy 10.5.5 [Edit]
Hello, I am currently attempting to port over a Delphi 2007 project that uses Indy 10.2.3 (very successfully) to Delphi 2009 and Indy 10.5.5 (I just got the latest development build this morning). I think I am running into an encoding issue, but am not sure. Specifically, IDHTTP with SSL calls an old CGI and the CGI returns a .zip file and I then save it to the disk. In 2007 and before this worked perfectly. In 2009, it is not. Here is the examples of the 2 different results (though cut way short in the post) I am getting back: 2007: 'PK'#3#4#$14#0#0#0#8#0'rLQ9žrPb€'#0...

FireDAC mobile app error as sqlite as delphi XE7 [Edit] #2
i im trying of connect my app as FireDAC but i have some errors. I follow all steps of Mobile_Tutotils_en_pdf file for delphi XE7. First i add one FireDAConnection compoent, after i in FireDAConnection Edit i set DriverID as Sqlite, in Database parameter i set this existing sqlite database "shoplist.s3db , and i set lockingmode to normal as this manual tell. I After i follow steps 1,2,3,4 and in the step 5 of the page 265 when i try of to set Connected property to true, i get this error: 1. [FireDac][Comp][Clnt]-340. Driver ID is not defined. Set TFDConnection.DriverName or Add D...

FireDac (XE7) apparently not using Foreign Key indexes in Firebird 2.5
Migrating an application from DBX to FireDac we have found the following: Two Tables: TABLE1: ID1 and ID2 Primary Key TABLE2: ID1, ID2 and ID3 Primary Key , and ID1 and ID2 as Foreign key (FK_TABLE1) pointing to same fields on TABLE1. No more indexes defined. (Firebird creates by default an index with each primary and foreign key) A simple query (SELECT * from TABLE1, TABLE2 WHERE T1.ID1 = T2.ID1 and T1.ID2=T2.ID2) that only retrieves a couple of records took more tan 6 seconds after migrating to FireDac, while with DBX was under 1 sec. Afer investigating we saw that if ...

SEPA components for Delphi with Source Code (Delphi 5
Hi all, in the european union change next year the Bankingformat to the SEPA Format. All peoples and companies must change the bankingssoftware and the costumer data form acountnummers in the new IBAN and BIC numbers. See: http://www.arma-it.de/shop/artikelueber.php?wgruppeid=211&wgruppe_offen=211 Functions: - generate SEPA XML'S - Calc IBAN - BIC Database (DE,AT and CH) Questions: vertrieb@arma-it.de PS: Bankinssoftware for Develpoers (Germany only) http://www.arma-it.de/shop/artikelueber.php?wgruppeid=212&wgruppe_offen=212 El 26/10/13 21:38, A...

FireDAC mobile app error as sqlite as delphi XE7 [Edit]
i im trying of connect my app as FireDAC but i have some errors. I follor all steps of Mobile_Tutotils_en_pdf file for delphi XE7. First i add one FireDAConnection compoent, after i in FireDAConnection Edito i set DriverID as Sqlite, in Database parameter i set this existing sqlite database "shoplist.s3db i create this database and table as other tool, and i can to add this to DBExpres in data exploer and i can to use it in one mobile delphi project", and i set lockingmode to normal as this manual tell. After as tell in page 269 of this manual when i click over test button, i ...

Caution error in FireDac 8.0.1 to Delphi Xe2 and earlier [Edit] #2
Please note that FireDac 8.0.1 have a bug, Embarcadero refuses to correct an error. In December 2014 I bought FireDac to Delphi XE2, 8.0.1 a few days later it turned out that the program has a bug StrsEmpty2Null not support parameter which makes the component is not useful for me. Although embecedro 8.0.5 patch is available and I bought a legal component, has not made me any corrections. Help addicted from buying Delphi Xe 7.0 Information received from worker embecedro Poland?. Does this attitude is fair. Can someone please help me make a package 8.0.5 or help to improve the error. E...

FireDAC and FetchBlobs with MySQL 5.5 and Delphi XE5
I try to obtain a stream of blob field stored in MySQL Database. With Oracle and MSSQL, it works great but with MYSQL, it doesn't work. I proceed like it's indicated in the documentation: ms-help://embarcadero.rs_xe5/libraries/FireDAC.Comp.DataSet.TFDDataSet.FetchBlobs.html {code} FDQuery1.FetchOptions.Items := FDQuery1.FetchOptions.Items - [fiBlobs]; FDQuery1.Open; ..... FDQuery1.FetchBlobs; oStr := FDQuery1.CreateBlobStream(FDQuery1.FieldByName('image'), bmRead); try // process image finally oStr.Free; end; {code} Does somebody succeed loading blob in ...

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

Migrating from Delphi 6 to Delphi XE 3! [Edit]
All, I am a Delphi developer working in an windows form application developed using Delphi 6. Now, we are planning to upgrade the development tool. Can anyone provide me information related to major roadblocks that we can face while migrating from Delphi 6 to Delphi XE 3? Should we migrate to Delphi XE 3 or any other preferred version of Delphi based on the fact that our target users will be using Windows 7 or Windows 8? Do we have any tools or utilities to migrate the source code from Delphi 6 to higher version of Delphi? Also, any suggestions related to best practices are welcome....

Text.Write speed
Hi ! I have speed troubles for using Text.Write method in Delphi 2006, particularly with distant files (local network). For example, with the following code ("F" is a text file ; "Line" is an about 200-character string): ----- AssignFile(F, FileName); try Rewrite(F); for l := 1 to 100 do begin for c := 1 to Length(Line) do Write(F, Line[c]); Writeln(F); end; finally CloseFile(F); end; ----- On a distant file: Delphi 5 -> 0.08 seconds Delphi 2006 -> close to 8 seconds Is there a speci...

ASP 2.0/3.5: How to get Visual Studio Web Developer 2008 Express Edition to use ASP.NET 2.0 rather than 3.5?
Hello, my ISP only supports ASP.NET 2.0. I am using VS Web Dev 2008 Express Edition. I have read somewhere that one can set the IDE to 'target' asp.net 2.0 or 3.5. I can't find this setting anywhere on the menu's. How does one do that?The code I am writing works fine so far. I am worried that I may use a 3.5 feature that is not supported. Thus I want to make sure my code is 2.0 compliant before I upload it.RegardsKhalidLondon  You can do this by right click on the project which you want to run as 2.0, and select properties, In properties you can find Target Frame...

Web resources about - FireDAC / Delphi XE7 / Firebird 2.5 + Dialect 3: Key fields turn upper case [Edit] - embarcadero.delphi.firedac

Firebird.com - Creative Services and Copywriting for Advertising, Design, PR and Music Video Productions ...
Firebird is a Creative Service Collectives providing Design Concepts, Copy and Script Writing, Song Writing, Firebird Music Production and Video ...

Gibson Firebird - Wikipedia, the free encyclopedia
The Gibson Guitar Corporation released several new styles during the 1950s to compete with Fender 's solid-body instruments, such as the Telecaster ...

Firebird: The true open source database for Windows, Linux, Mac OS X and more
Firebird SQL: The true open-source relational database

Queensland Firebirds (@TheFirebirds) on Twitter
Log in Sign up You are on Twitter Mobile because you are using an old version of Internet Explorer. Learn more here Queensland Firebirds @ TheFirebirds ...

Firebirds captain Laura Geitz says her side wants to be the best
Queensland Firebirds captain Laura Geitz wants this Firebirds team to be the best ever.

Firebirds pip Swifts in grand final thriller (01:29)
Queensland lead for just 15 seconds but it was enough to win the decider after NSW were unable to hold off the fast-finishing combination of ...

Caitlyn Nevins seeking happy ending with Queensland Firebirds after 'tough decision' to leave Melbourne ...
For a woman whose teammates insist she looks uncannily like Snow White, Caitlyn Nevins is almost living her own fairytale.

Live 2015 ANZ Championship Grand Final: Queensland Firebirds vs. NSW Swifts - The Courier-Mail
... the ANZ Championship. The Swifts led the entire game at the Brisbane Entertainment Centre, but with less than 30 seconds on the clock the Firebirds ...

Thunderbirds go down to Firebirds by two goals in Brisbane
THE Adelaide Thunderbirds narrowly failed to pull off a Houdini escape act in Brisbane when beaten by the Queensland Firebirds in another frustrating ...

Live 2015 ANZ Championship Grand Final: Queensland Firebirds vs. NSW Swifts - Other Sports - Fox Sports ...
... the ANZ Championship. The Swifts led the entire game at the Brisbane Entertainment Centre, but with less than 30 seconds on the clock the Firebirds ...

Resources last updated: 11/21/2015 11:34:28 AM