1

I am using Delphi 2010 and UIB to access a Firebird database.

I am trying to run the Query

    SELECT
        RECID
    FROM TABLE
    WHERE ((:DX = '') OR (DX=:DX))
        AND ((:POSTCODE='') OR (POSTCODE=:POSTCODE))

Note, that I am using each named parameter twice in the statement.

When I prepare it in a TUIBQuery I get ParamCount=2 and two parameters named DX. When I trace the code I see TSQLParams.Parse passing over the my SQL and calling AddFieldA four times. I can see that duplicates are recognized and added to the end of FXSQLDA, but FParamCount is not incremented.

As a temporary dumb fix I have added such an increment and I now get (DX, DX, POSTCODE, POSTCODE) as parameter-list. That sort of works.

Questions:

  • Is the sort of parameter-reuse I am attempting supported by Firebird?
  • By UIB?
  • Is my fix viable?
  • Are there better fixes?
Marian Aldenhövel
  • 677
  • 1
  • 6
  • 24
  • Why do you think there is error there? There are two named parameters in the query but four parameters overall. – Andrej Kirejeŭ Feb 23 '14 at 19:14
  • In the original code, without my heavy-handed fix, I get two parameters named DX. That is: Params.FieldName[0] = 'DX' and Params.FieldName[1] = 'DX'. So there is no way for me to access the parameter POSTCODE. – Marian Aldenhövel Feb 23 '14 at 19:29
  • ParamByName('postcode').As... = ... doesn't work? – Andrej Kirejeŭ Feb 23 '14 at 19:57
  • It does! Marks Answer and your hint made me just try it. So to summarize: It works fine. It is just the Params-Collection that does not look right when evaluated. I found out about it when I wrote logging-statements that dump a prepared query and its parameter-values out before executing it. For that special case there is no apparent way of accessing POSTCODE because Paramcount (wrongly) suggests there are only two. Given the I explanation can live with that. – Marian Aldenhövel Feb 24 '14 at 08:38

1 Answers1

1

Firebird itself does not support named parameters in dynamic SQL (it does in procedural SQL). Firebird only supports positional parameters.

So it is your driver that translates named parameters to positional.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197