PreparedStatements with variable number of parameters

[bennett.matthew]

Hello,

Is there a good way to make PreparedStatements with a variable number
of parameters?

My queries look something like this:

SET @rowid := null;
SELECT * FROM (
SELECT
id,
name,
@rowid := COALESCE(@rowid + 1, 1) AS rowid
FROM
mytable
ORDER BY
name
) AS myquery
WHERE rowid IN (5, 7, 12, 20);

And I can have a variable number of IN terms.

At the moment, I'm building the PreparedStatement with a concatenated
String.

I've seen another discussion of this topic online, and the final
implementation used there was to build a cache of PreparedStatements,
so you would (hopefully) only have to concatenate the String once. But
I have so many different combinations it's unlikely the same query will
come up twice in quick succession.

Is this really the best way of doing things. Indeed, is there any
(beyond security) benefit of using PreparedStatements in this case?

Thanks,
Matt.

[Bill Karwin]

be*************@gmail.com wrote:

Is there a good way to make PreparedStatements with a variable number
of parameters?

No, there is no way to put a variable number of values into the IN
clause by using statement parameters. Statement parameters can
substitute only a single value; not a list of values, or any other
syntactic element.

If you can predict a finite limit to the number of values you will want
to compare against, you could do this:

WHERE rowid IN (?, ?, ?, ?, ?, ?, ?, ? ... )

That is, list as many parameters as the greatest number of distinct
values you could list in the IN predicate. When you plug values into
the parameters, you can either supply NULLs for the parameters you don't
need, because rowid IN (1,2,3,NULL) is the same as rowid IN (1,2,3). Or
else you can repeat your list of values, because rowid IN (1,2,3,1,2,3)
is the same as rowid IN (1,2,3).

At the moment, I'm building the PreparedStatement with a concatenated
String.

I recommend that you continue to build the prepared statement with a
concatenated string.

Regards,
Bill K.

[bennett.matthew]

Hello Bill,

Thanks for the advice.

If you can predict a finite limit to the number of values you will want
to compare against, you could do this:

WHERE rowid IN (?, ?, ?, ?, ?, ?, ?, ? ... )

I can predict a maximum number of requests, but it's about 100. What
would be the performance hit of trying to find ~100 results that aren't
there?

Regards,
Matt.

[Bill Karwin]

be*************@gmail.com wrote:

I can predict a maximum number of requests, but it's about 100. What
would be the performance hit of trying to find ~100 results that aren't
there?

I don't know the answer to that for certain, so you should try a couple
of experiments using your database, and time the result to see if
there's any significant difference between IN compared with 3 constant
values vs. 100 constant values. A test using your database would be a
more relevant answer than a supposition about the performance. But my
guess is that the difference will be negligible.

I should alter my recommendation about using NULL parameters, based on
this paragraph I just read:

"To comply with the SQL standard, IN returns NULL not only if the
expression on the left hand side is NULL, but also if no match is found
in the list and one of the expressions in the list is NULL."
http://dev.mysql.com/doc/refman/5.0/...operators.html

So "WHERE rowid IN (1, 2, 3, NULL)" _doesn't_ do the same thing as
"WHERE rowid IN (1,2,3)".

Regards,
Bill K.