FireDAC + TableNames with '$'

[Squamis 0]

Hello everyone!

I have some problem with TDFQuery (Postgres DB). It can't recognize params in SQL-query because one table has name with double '$'.

If I use macroses - it works fine, but it is a very simple query to use macroses.

May be someone can help me how to fix this problem without using additional mechanisms (views, macroses, ...).

May be I should add some "echo"-chars before '$' or ...

 

P.S. It is a real project, so I can't rename tables :(

with tjWLL   as (select
                      wrl.fldidxwarerule as fldidxwarerule,
                      json_agg(wll.*)    as "WARESRULESLINKS"
                  from
                      vdata."wares$rules"                 wrl
                      inner join vdata."wares$rules$link" wll on (wrl.fldidxwarerule = wll.fldidxwarerule)
                  where
                          (wll.fldidxwarerule = any(:fldidxwarerule) or (case when :fldidxwarerule = '{}' then True else False end))
                      and :IsWLL
                  group by
                      wrl.fldidxwarerule
                 ),
tjWRL        as (select
                      wrl.*,
                      wll."WARESRULESLINKS"
                  from
                      vdata."wares$rules" wrl
                      left  join tjWLL    wll on (wrl.fldidxwarerule = wll.fldidxwarerule)
                  where
                         wrl.fldidxwarerule = any(:fldidxwarerule)
                      or case when :fldidxwarerule = '{}' then True else False end
                 )
select
    json_build_object('WARESRULES', to_json(tjWRL)) as "WARESRULES"
from
    tjWRL

 

[Lars Fosdal 1732]

Quick and dirty: What if you add the table names as aliases too? 

 

tableWRules

tableWLinks 

 

with tjWLL   as (select
                      wrl.fldidxwarerule as fldidxwarerule,
                      json_agg(wll.*)    as "WARESRULESLINKS"
                  from
                      vdata.":tableWRules"                 wrl
                      inner join vdata.":tableWLinks" wll on (wrl.fldidxwarerule = wll.fldidxwarerule)
                  where

 

And add the actual table names like you would do with any other parameter?

[Fr0sT.Brutal 897]

Just guessing - is there a property like "macro char" which is "$" by default?

[Squamis 0]

50 minutes ago, Fr0sT.Brutal said:

Just guessing - is there a property like "macro char" which is "$" by default? 

Good question

[Lars Fosdal 1732]

Hard coded. 

FireDAC.Phys.SQLPreprocessor.pas, line 1028 (D11.1)
    '$':

      if not FInStr1 and not FInStr2 and

         (ConnMetadata <> nil) and (ConnMetadata.Kind = TFDRDBMSKinds.PostgreSQL) then

        ProcessQuoteTag;

 

Although that seems to be an exception for PG.

 

I guess Alphanum + Underscore is the only safe bet for DB entity names.

[Squamis 0]

6 minutes ago, Lars Fosdal said:

Hard coded. 

Thanks for your time for me! Good day!

[Fr0sT.Brutal 897]

$ is pretty common in Firebird as well (for system tables). Maybe \$ or $$ allow to bypass macro substitution?

[Squamis 0]

3 minutes ago, Fr0sT.Brutal said:

$ is pretty common in Firebird as well (for system tables). Maybe \$ or $$ allow to bypass macro substitution?

In both way TFDQuery starts recognizing params. But on try executing query:

Error:

can't find table vdata."wares$$rules$$link"

and

can't find table vdata."wares\$rules\$link"

[Lars Fosdal 1732]

@Dmitry Arefiev - Is there a way to deal with $ in table names for PostgreSQL?

[Fr0sT.Brutal 897]

Won't this help https://docwiki.embarcadero.com/RADStudio/Sydney/en/Preprocessing_Command_Text_(FireDAC)#Escape_Sequences ?

[Lars Fosdal 1732]

$ is not mentioned on that page, and $$ did not help.

[Fr0sT.Brutal 897]

57 minutes ago, Lars Fosdal said:

$ is not mentioned on that page, and $$ did not help.

My advice was to try

{id <identifier name>}

Expands to DBMS-specific quoted identifier syntax. For example: {id Order Details} -> “Order Details” on Oracle.

[Squamis 0]

2 hours ago, Fr0sT.Brutal said:

My advice was to try

Also without results :(

[Fr0sT.Brutal 897]

14 minutes ago, Squamis said:

Also without results :(

Then I'd consider it a bug. DB object names could be any as far as they're quoted. It's annoying to be unable to turn off the macro stuff.

If you can't wait for a fix, the only option left is to use macros. You can even automate substitution and pack it into a single procedure to keep clean code.

Edited January 11, 2023 by Fr0sT.Brutal

[Squamis 0]

1 hour ago, Fr0sT.Brutal said:

You can even automate substitution and pack it into a single procedure to keep clean code

This is the way that I select yesturday, after this topic.

 

P.S. If it is a bug, how Embarcadero will know about it?

[skyzoframe[hun] 4]

var
	Q : TFDQuery;
    .
    .
    . 
Q.SQL.Text := ('select * from TEST$TEST_;');
Q.SQL.Text := ('select * from TEST$$TEST;');

 

Hmm... strange, because it is working for me. Also in command line.

 

https://dbfiddle.uk/eDOCApeX

[Squamis 0]

Just now, skyzoframe[hun] said:

mm... strange, because it is working for me. Also in command line.

Postgres?

[Squamis 0]

 

1 minute ago, skyzoframe[hun] said:

Hmm... strange, because it is working for me. Also in command line. 

Try TableName like t1$t2$t3

[skyzoframe[hun] 4]

1 minute ago, Squamis said:

 

Try TableName like t1$t2$t3 

 

Yes it is working... https://dbfiddle.uk/OrzyibpI

[skyzoframe[hun] 4]

Try to handle everything over stored procedures in database side.
In code  call the procedure.

 

https://www.postgresql.org/docs/current/sql-createprocedure.html

Edited January 11, 2023 by skyzoframe[hun]

[Squamis 0]

Yes, I can use stored procedures, macroses, .... This topic just a question for understanding - it is a bug or we can do something!

Just why a simple query can't be executed with standart functionality.

 

[skyzoframe[hun] 4]

3 minutes ago, Squamis said:

Yes, I can use stored procedures, macroses, .... This topic just a question for understanding - it is a bug or we can do something! 

Just why a simple query can't be executed with standart functionality.

 

I have no clue.

[Lajos Juhász 239]

You can fill a QP ticket - https://quality.embarcadero.com/secure/Dashboard.jspa. This is the only possible way to get response from Embarcadero.

[Fr0sT.Brutal 897]

2 hours ago, skyzoframe[hun] said:

Hmm... strange, because it is working for me. Also in command line.

The subject is flaw in Delphi's FireDAC, online SQL tests are irrelevant

[weirdo12 16]

I am not seeing that problem with PostgreSQL 14.5 using FireDAC.

 

 

 

This works fine too:

 

SELECT "$$1$$".row_id, "$$1$$".description, :dummy_string_param, :dummy_int_param FROM t$1$$$$$$$$$ "$$1$$"
LEFT OUTER JOIN "t1$t2$t3" ON "$$1$$".row_id = "t1$t2$t3".row_id

 

Edit:

 

This gives me a syntax error - only when parameters are included (which makes some sense):

 

SELECT "$$1$$".row_id, "t1$t2$t3".row_id, "$$1$$".description, :dummy_int_param FROM t$1$$$$$$$$$ "$$1$$"
LEFT OUTER JOIN "t1$t2$t3" ON "$$1$$".row_id = "t1$t2$t3".row_id

 

[FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"

 

This works:

 

SELECT "$$1$$".row_id, t1.row_id, "$$1$$".description, :dummy_int_param FROM t$1$$$$$$$$$ "$$1$$"
LEFT OUTER JOIN "t1$t2$t3" t1 ON "$$1$$".row_id = t1.row_id

Edited January 11, 2023 by weirdo12