>> I say "probably" because I'd be nervous
>> if most of your params are of the same datatype and you
>> haven't been validating things at the API.
>>
>> Helen

> Um, I don't quite follow this last bit. What specifically
> do you mean by "haven't been validating things at the API"?

> Helen, I get nervous when you say you'd be nervous!!

First Issue...

Until Firebird v1.5 (I dont know if IB 7 fixes the "problem"),
the order of parameters in the array was not easy to predict.

Simple statements like:
SELECT ... FROM ... WHERE A=:P1 AND B=:P2
are easy enough. These are returned as
Params[0] == ParamByName('P1');
Params[1] == ParamByName('P2');

Whereas: SELECT FIRST :P1 SKIP :P2 ...,
(SELECT ... FROM T WHERE T.X=:P3 AND T.Y=:P4)
FROM ... WHERE A=:P5 AND B=:P6
returns
Params[0] == ParamByName('P1');
Params[0] == ParamByName('P2');
Params[0] == ParamByName('P5');
Params[0] == ParamByName('P6');
Params[0] == ParamByName('P3');
Params[0] == ParamByName('P4');

And it gets a lot hairier the more complicated you get. For
example Unions and Order By have very interesting effects.
(I have quite a list of examples from when I studied this for
a C project if you want them.)

IBO was built to be smart enough to handle the most common
situations (FIRST/SKIP gave problems for a while), but it
is possible to create statements that fooled even IBO.

Then along came FB 1.5 that "fixed" the problem by ensuring
that parameters are returned in the order they appear in the
statement. Which is great, but...

FB 1.5 allows the parameter ordering "fix" to be disabled
with a configuration option - but does not give the client
any direct way of discovering what option is enabled (other
than testing a known problem statement - such as the one
that I posted the other day).

This means that IBO is currently looking to try and match
the old style unpredictable ordering, which may or may not
be the case against an FB 1.5 server. I imagine Jason will
build in some detection eventually.


Second Issue...

The API does not actually accept parameter names, these are
a client feature. IBO searches for the parameter names and
replaces them with '?' before sending to a statement to the
server. When the statement is prepared IBO matches the names
back to the items returned by the server (using the extra
smarts to try and match the server parameter order).

This means that a statement like:
SELECT ... FROM ...
WHERE A=:PA AND B=:PA

is actually using two parameters, not one! IBO generally
resolves this for you automatically.


So I imagine that what Helen was referring to by "haven't been
validating things at the API" was the idea of either checking
what parameter ordering is active and/or by checking that
the datatypes and/or parameter name of Params[n] is what you
expect it to be.

--
Geoff Worboys
Telesis Computing