Tim Gahnström wrote:

>
>
> You could pass arrays to a stored procedure (I don't know how) and
> build the query string inside your SP and use the EXECUTE STATEMENT
> as Ann suggested. It will give you the flexibility you are looking
> for but not any speed increase.
>
> The reason SPs are faster is because they are compiled and optimized
> in advance and this is not possible using the EXECUTE STATEMENT.
>

The alternatives (that occur to me) are building the statement from
inputs and invoking it with EXECUTE STATEMENT or having a huge if then
else in the procedure representing the cross product of all possible
field comparison expressions. I'm not sure that the big procedure would
actually be faster in practice if there were more than a dozen fields
that could be involved...

> This is a drawback but it also means that you can just as well build
> your query string on the client side (in php) and pass it as a string
> to the SP. This way you don't have to pass in and parse an array and
> you can keep most of the logic in PHP.
>

What I intended to suggest is that the whole WHERE clause be built on
the client side, passed as a string, and concatenated in the procedure
with the rest of the select statement. There's no reason to parse the
input on the procedure side. There _is_ a reason not to create the
entire statement on the client side, and that has to do with privileges.

There is a slow not very coherent discussion going on in devel about
privileges and EXECUTE STATEMENT. For reasons having to do partly with
implementation, an EXECUTE STATEMENT currently runs with the privileges
of the client, not the privileges of the procedure.

Sophisticated applications give very few privileges to users and more to
procedures, so you could, for example, have a table of employees and
salaries that allows no one to query salaries directly, but allows all
users to see their own salaries, and all managers to update the salaries
of their direct reports. Such a procedure might include a dynamic
statement that allowed managers to specify which direct reports should
be updated - based on dynamic criteria, but still restricted to the
manager's own direct reports.

At the moment, that can't work because all dynamic statements in
procedures use the clients privileges. In a future version, it will be
possible to change that rule - exactly how is under discussion.
However, procedures that blindly execute full statements passed on from
the client should not execute those statements with special privileges -
for obvious reasons.


Regards,


Ann