FDQuery parametres in Delphi 10 seattle

Question

I have this query :

SELECT * FROM Vente WHERE 
(DatePart ("d", Vente.DateDebut)=01) and
(DatePart ("m", Vente.DateDebut)=06) and
(DatePart ("yyyy", Vente.DateDebut)=2017);

This query is working fine in MS Access 2010 , but when I try to excute it from TFDQuery component , it give me an error :

[FireDAC][Phys][ODBC][Microsoft][Pilote ODBC Microsoft Access] Too few parameters. 3 Expected ...

What's the problem? How can I fix that?

Update:

It seems like the problem in the DatePart () function , because I try also this:

SELECT DatePart ("d", Vente.DateDebut) FROM Vente

And it give me the same error , just with "3 Expected ..." becomes "1 Expected ...".

I don't see any problem. Is it the physical driver who throws that exception? Btw. to filter by a constant date value better use expression [like this](https://stackoverflow.com/a/44577464/8041231), you can use that in `WHERE` clause as well. I mean, something like `WHERE DateValue(Vente.DateDebut) = {d 2017-06-01}`. Or better yet for all supported DBMSs, `WHERE {CONVERT(Vente.DateDebut, DATE)} = {d 2017-06-01}` — Victoria, Jun 30 '17 at 13:47
Could you please check what returns the `Text` property of your query object? It's the command that is being send to the DBMS. I guess you'll get the same command. If so, maybe the driver doesn's accept `"` escaping and expects `'`. — Victoria, Jun 30 '17 at 14:37
@Victoria I try yours , but it gives a synthax error , and the command is in the question when I write `showmessage(FDVente.SQL.Text);` — Ilyes, Jun 30 '17 at 14:44
I meant `FDVente.Text`. And for the proposed clause you must have enabled macro expansion properties (which is by default). Of course such query is not executable in DBMS management tool. But it's a cost of unification. — Victoria, Jun 30 '17 at 15:16
Did you make any progress with this topic? I'm still wondering what could be the problem. The query seems to be fine, so the only thing I can think of is the escape `"` char for the used driver. Also, I'm wondering why those macros fail to work. — Victoria, Jul 01 '17 at 19:35
@Victoria I don't know what's the problem , that why I ask this question , the query work fine in MS Access 2010 — Ilyes, Jul 01 '17 at 20:18
I see. Though there is a [similar question](https://stackoverflow.com/q/25354844/8041231) where's not mentioned driver but it might be the same problem. The `"` escape char used instead of `'`. Still, I would prefer using macros here. Where that Pilote ODBC driver comes from? — Victoria, Jul 02 '17 at 17:52

score 0 · Answer 1 · answered Jul 16 '17 at 23:19

My best guess is, that the driver rejects your query because of " (quotation mark) string escapes that you used. For example FireDAC MS Access metadata class internally uses ' (apostrophe) to escape string values. If I'm right, you'll be having trouble to write SQL queries with string constants executable in a FireDAC application as well as in MS Access, because for FireDAC you would write query like this (apostrophe must be escaped in Delphi code):

SELECT DatePart(''d'', Vente.DateDebut) FROM Vente

Which is something that MS Access won't understand. It needs quotation marks, which are in turn not acceptable for FireDAC:

SELECT DatePart("d", Vente.DateDebut) FROM Vente

I'm not aware of a way that would allow you to write commands with string constants for FireDAC as well as MS Access. This should work in FireDAC (won't in MS Access):

SELECT * FROM Vente WHERE
({EXTRACT(DAY, Vente.DateDebut)} = :Day) AND
({EXTRACT(MONTH, Vente.DateDebut)} = :Month) AND
({EXTRACT(YEAR, Vente.DateDebut)} = :Year)

After preprocessing for MS Access it should produce command like this (notice the apostrophe):

SELECT * FROM Vente WHERE
(DATEPART('d', Vente.DateDebut) = :Day) AND
(DATEPART('m', Vente.DateDebut) = :Month) AND
(DATEPART('yyyy', Vente.DateDebut) = :Year)

FDQuery parametres in Delphi 10 seattle

1 Answers1