Delphi Firedac - FieldName issue

Question

I migrate my application (Delphi 10.3) with MS Access base from ADODB to Firedac. I have a problem with some TField FieldName when it has a complex name : Simple example: CREATE TABLE TEST ([SE_NAME] CHAR(3))

On a query like SELECT * FROM TEST T1 LEFT JOIN TEST T2 ON T1.SE_NAME=T2.SE_NAME

I expect FieldNames :'T1.SE_NAME' and 'T2.SE_NAME' (like in Access 2013).

With a ADODB TADOQuery :

  var f: TField;
  var s: string := '';
  for f in Query.Fields do
    s := s +  f.FieldName + ' ;' ;

s is 'T1.SE_NAME ;T2.SE_NAME ;' OK.

With a Firedac TFDQuery :

  var f: TField;
  var s: string := '';
  for f in Query.Fields do
    s := s +  f.FieldName + ' ;' ;

s is 'SE_NAME ;SE_NAME_1 ;' : Not OK : FireDac changes the columns names.

How to keep, (with Firedac options ?) , the real columns names, without changing the SQL query (for compatibily needs) ?

In MSAccess (2013) the result of SELECT * FROM TEST T1 LEFT JOIN TEST T2 ON T1.SE_NAME=T2.SE_NAME is :

Note that :

I know how to use queries, aliases... but I need to keep the queries for compatibility.
The queries are just examples (not the real queries).

In situations like this, I use aliases: `SELECT T1.SE_NAME SE_NAME1, T2.SE_NAME SE_NAME2 FROM TEST T1 LEFT JOIN TEST T2 ON ...`. — Olivier, Jul 23 '21 at 10:00
The [full syntax of aliases](https://en.wikipedia.org/wiki/Alias_(SQL)) is: `SELECT whatever AS alias, other AS newname... FROM table AS abbreviation, ...` just like you already do in the short form with `FROM test (AS) T1`. — AmigoJack, Jul 23 '21 at 10:08
An alias can also be `"T1.SE_NAME"` - note the double quotation marks. — AmigoJack, Jul 23 '21 at 14:00
@AmigoJack Access uses [brackets](https://support.microsoft.com/en-us/office/access-sql-select-clause-12d169e7-0348-407d-9c67-180ff32540ac#bm3): `[T1.SE_NAME]`. — Olivier, Jul 23 '21 at 14:38
I'm no SQL expect, but I do know what a JOIN is, but if you are SELECT'ing data only from T1, why would you expect any fields from T2 to appear in the result set, and thus in the `Fields` list? — Remy Lebeau, Jul 23 '21 at 17:56
@RemyLebeau A `SELECT *` will fetch all the fields from all the tables involved in the `FROM` part of the query. — Olivier, Jul 23 '21 at 19:20
@RemyLebeau You can go [here](https://www.db-fiddle.com/f/hb6UBMfpHqzAm7JVXPeasY/0), click the Run button and you will see that the query returns 2 columns (one from `T1` and one from `T2`). — Olivier, Jul 23 '21 at 19:27
Do you have that many queries to change that you're ready so spend 500 points instead of just fixing them? Unfortunately what you're asking for is probably not possible. But you could directly look at FireDAC source code to be sure. — Olivier, Jul 26 '21 at 08:20
@Olivier Not too many but in fact, the queries are not the real problem. If FieldName changes, we need to change the code using it and some external parts. Just for example : we use FastReport for reporting where fields are used by fieldName, so we have to change the reports too. Just note that our customers can create their own querie, and the doc is made with the 'Access' syntax. — philnext, Jul 26 '21 at 12:48
If you change your queries as @AmigoJack proposed, setting the alias to T1.SE_NAME, then the rest of your code won't need to be changed. FastReport will still find the same field names that it was expecting. — Marc Guillot, Jul 27 '21 at 13:44
@Marc, short story :MS Access doesn't allow T1.SE_NAME as alias. — philnext, Jul 27 '21 at 14:05
@philnext [Punctuation is not allowed](https://stackoverflow.com/a/3601221/4299358) - a detail from the error message that you could have included for us instead of letting everyone else figure it out. This restriction was new to me - another reason to not rely on DBMS dialects but instead stick to Ansi SQL. — AmigoJack, Jul 27 '21 at 14:41
@AmigoJack I clearly indicated that I needed to keep the original syntax of the queries. Yes the puncutation is not allowed by Access in aliases but I have other reasons to keep the syntax, I can explain them but it is not the subject of the question. The subject is not the efficiency of the query or the interest of joining a table against itself. — philnext, Jul 27 '21 at 21:43
Then maybe you should stay with ADO. Why did you need to switch to FireDAC? — Olivier, Jul 28 '21 at 07:30
@Olivier, Firedac has more support in Delphi and has a lot of features not available in ADO, also, we yet use FireDac to interface other databases (MySQL, MSSQL...) for some fuctions and it seemed coherent to use Firedac for our main database. I'll may look the source of Firedac to see the origin of the problem. — philnext, Jul 28 '21 at 15:50

AmigoJack · Answer 1 · 2021-07-27T14:52:32.637

This is known as technical dept: you made decisions in the past which you considered fine, and now it proves to cause more damage than if you would have chosen differently (and at lower costs) in the past.

SELECT * FROM test JOIN test immediately raises questions:

Why is the same table joined against itself? This rarily makes sense.
Are really all columns needed? This is rarily efficient.

You should overcome your design decisions and fix it:

Select only those columns you actually need. No matter how many columns the table has. This also helps avoiding ambiguity when tracking down issues (while helping performance in general).
Use aliases to exactly get the results you want:
- If you want t1.column as a column name in the results then define that thru SELECT t1.column AS "t1.column" (Ansi SQL, most DBMSs support this).
- MS Access needs square brackets and does not allow dots in aliases (the error message says "punctuation" but i.e. comma and colon are fine), so you have to come up with your own logic, i.e. underscores: SELECT t1.column AS [t1_column]. Or just use a distinctive alias right away.
If you leave it undefined you cannot expect there are no surprises (FireDAC surely wants to help you with the underscored suffix, as otherwise it would just overwrite existing matches). Just try out SELECT 1+ 1* 2 FROM table in different DBMSs and see which result column name that generates - I wonder how you prepare accessing that without using a column alias.
You can even select the same column from the same table multiple times: SELECT column AS c1, column+ 2021 AS c2 FROM test, yet you expect that to magically work out without using aliases? Just use them - don't let it go undefined. In doubt do SELECT name AS name FROM test AS t (and even then it's not entirely sure if the resulting column name becomes name or NAME - that's why you should use quotation marks around your alias).
If you want to mix SELECT * FROM with Query.Fields.Fieldname to get a list of column names one table could have then do it per table instead of throwing multiple tables into the query. And even this approach is not good - rather consult the DBMS information schema - in FireDAC there are Get*Names() methods for that.

Cross linking FireDAC adding underscore and SQL select join: is it possible to prefix all columns as 'prefix.*'?

Thx for the detailled post, but note that I just want an answer to my problem not an explaination about wrong or good software design. As you have questions about 'SELECT * FROM test JOIN test' some answers : 1/ Why is the same table joined against itself : it is a hierachicall organisation where items are parents (and/or) children of other items. 2/ Are really all columns needed? : Sure not ! It is just an example which need to be concise. — philnext, Jul 26 '21 at 17:42
@philnext If you prefer to "just" get an answer without further explanation on why it is a dead end and how much was tried and analyzed I could edit all my text to reduce it to the words "**It's impossible**", but I cannot imagine this will make you happier. — AmigoJack, Jul 29 '21 at 13:56

Delphi Firedac - FieldName issue

1 Answers1