Subject Re: [IBO] Problem with parameters in query
Author Tomasz Tyrakowski
On 17.09.2017 at 19:09, 'Jason Wharton' supportlist@...
[IBObjects] wrote:
> [...]
> I really do wish Firebird would have accepted named parameters so that this
> wouldn't be such a headache, but I am doing my best to only make assumptions
> that I know I can make.

Maybe just leave it to the programmer to decide what type the parameter
is to be of. I mean, when one writes
aQuery.ParamByName('aParam').AsString := s, what one expects is 'aParam'
to be assigned the provided string value (if one believed it to be an
int, one would use AsInteger). If aParam is used multiple times in a
query, just set it with the same string every time (instead of trying to
check if it really is a string in all occurences). If a param is
assigned a value of an incompatible type (with respect to the context
it's being used in), the query execution will fail and that's it. That
seems to be straightforward and intuitive (like writing a query in isql
- if you compare values of incompatible types, the query fails), unless
libfbclient for some reason forbids it.
My hack does just that: don't check the param type, just believe that
when I say it's a float, a float it is.

have a good one

> -----Original Message-----
> From: []
> Sent: Thursday, August 31, 2017 5:26 AM
> To:
> Subject: Re: [IBO] Problem with parameters in query
> On 31.08.2017 at 09:43, Svein Erling Tysvær setysvar@... [IBObjects]
> wrote:
> > [...]
>> SELECT <whatever>
>> FROM MyTable
>> WHERE Field1 = :MyParam
>> OR Field2 = :MyParam
>> and rather write things like:
>> WITH TMP(MyTmpField) AS
>> <whatever> FROM MyTable M JOIN TMP T ON M.MyTmpField = T.Field1
>> OR M.MyTmpField = T.Field2
>> This change will not help you today (of course), but writing your SQL
>> so that it really only has one direct reference to each parameter
>> would avoid potential future pitfalls like the one you're experiencing
> today.
> I have struggled with "parameters with duplicate names" as well, eventually
> some hacks to IBO source allowed me to get rid of them (at the cost of
> risking uncompatible uses of a parameter to go unnoticed, but that's the
> risk I had to take, being in a situation similar to Helmut's - over a
> million lines of old Delphi code with thousands of embedded SQL queries,
> some of them generated dynamically in code).
> But getting back to the point, I consider code readability a top priority
> (especially in large codebases). The first query clearly states what the
> programmer had in mind writing it, while the second one proves you're very
> fluent in caveats of Firebird's SQL, but it would take quite a while for
> another programmer (especially a junior one) to deduce what you really are
> trying to get from the DB (imagine how a more complicated query joining
> several tables would look like when written this way).
> So, IMHO we should all write clear and readable code and it's up to the
> component layer to cope with the technical limitations of a DB client
> library. And Jason is doing quite well in this matter, so I believe we won't
> have to use many workarounds.
> Sorry if I sound like criticizing - nothing of the sort (especially not you
> SET - I'm a regular firebird-support reader and occasional poster, so I do
> appreciate your knowledge), just wanted to emphasize that we should write
> code that explains itself. The rest is in Jason's hands ;)
> best regards
> Tomasz
> --
> __--==============================--__
> __--== Tomasz Tyrakowski ==--__
> __--== SOL-SYSTEM ==--__
> __--== ==--__
> __--==============================--__
> ------------------------------------
> Posted by: Tomasz Tyrakowski <t.tyrakowski@...>
> ------------------------------------
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> - your IBO community resource for Tech Info papers,
> keyword-searchable FAQ, community code contributions and more !
> - your portal to submit and monitor bug reports
> - your portal to purchase and upgrade
> ------------------------------------
> Yahoo Groups Links

__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== ==--__