the database column is not found even though it exists (firedac)

Question

There is this listbox which gets populated with the name of the columns of a database in Delphi (Radstudio, Firedac). Depending on the listboxitem selected by the user, the data of the relevant column should then be loaded into another listbox.

To read that specific column I use the following code:

SQL.Add ('SELECT '  
     + QuotedStr(Trim(ListBoxName.Selected.Text))
     + ' from TABLE_NAME');

open;

so far so good. However, when I try to access the data of the column, an error is raised saying the 'Field ... not found'.

ListBoxItem.Text := VarToStr(FieldValues[ListBoxName.Selected.Text]); ==> Field 'field_name' not found!

I guess the problem is with the qoutedstr() in the first line. But not using it causes errors with the name of the columns with numbers and . in them.

So, what am I doing wrong?

I dont know, what is your DB, but try to use '"' + Trim(ListBoxName.Selected.Text) + '"' — da-soft, May 04 '22 at 13:07
@da-soft ``QuotedStr`` should do the same job. But it would be much better to use a parameterized query! — Delphi Coder, May 04 '22 at 16:06
@hsh_Ar you should clear the ``SQL`` property before adding a new query. Or use ``SQL.Text :=`` instead. — Delphi Coder, May 04 '22 at 16:08
@DelphiCoder I'd like to know more about parameterized query in my case. Could you give me a hint? — hsh_Ar, May 04 '22 at 16:15
https://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_Parameters_in_Queries — Delphi Coder, May 04 '22 at 16:27
@DelphiCoder Most databases do not allow query parameters to be used in a `SELECT` field list, though. But there are other ways to address this issue, see [How to quote field names in FireDAC in Delphi](https://stackoverflow.com/questions/47875203/) — Remy Lebeau, May 04 '22 at 20:48

Remy Lebeau · Answer 1 · 2022-05-04T20:42:54.853

You are executing the SQL query SELECT 'field_name' from TABLE_NAME, in other words you are returning a string literal, not the contents of a named table field. That is why FieldValues['field_name'] is not able to find such a field in the result set - it really doesn't exist.

Since your selected text is the name of a table field, you need to drop the QuotedStr():

SQL.Text := 'SELECT ' + Trim(ListBoxName.Selected.Text) + ' from TABLE_NAME';
Open;

You will then be executing the SQL query SELECT field_name from TABLE_NAME, and then FieldValues['field_name'] will work.

If you still get errors on the field_name, then the field's name likely contains reserved characters that need to be escaped, but using '...' is not the right way to escape them. Depending on your database, you may need to use `...` or "..." or [...] instead.

Or, you can use FireDAC's "identifier substitution escape sequences" or "substitution variables" features instead, let FireAC handle the database-specific escaping for you. Have a look at How to quote field names in FireDAC in Delphi for examples.

when I drop the `QuotedStr()`, error occurs in case there is decimal point in the field name. but the identifier substitution worked. — hsh_Ar, May 06 '22 at 16:20
@hsh_Ar "*error occurs in case there is decimal point in the field name*" - that would fall under the "field's name likely contains reserved characters that need to be escaped" statement in my answer. — Remy Lebeau, May 06 '22 at 22:25

the database column is not found even though it exists (firedac)

1 Answers1