473,554 Members | 3,091 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PreparedStateme nts with variable number of parameters

Hello,

Is there a good way to make PreparedStateme nts 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 PreparedStateme nt 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 PreparedStateme nts,
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 PreparedStateme nts in this case?

Thanks,
Matt.

Jul 17 '06 #1
3 6064
be************* @gmail.com wrote:
Is there a good way to make PreparedStateme nts 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 PreparedStateme nt with a concatenated
String.
I recommend that you continue to build the prepared statement with a
concatenated string.

Regards,
Bill K.
Jul 17 '06 #2
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.

Jul 17 '06 #3
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.
Jul 18 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7806
by: Michael Cohen | last post by:
Hi, I have to write a function that gets variable number of parameters and pass them to another function (existing). The problem is that the prototype of the function I call has ",..." so I can't just pass it parameters in the format of va_list. The following code does not work since f2 doesn't take arg_list (the prototype of f2 is the...
10
5006
by: The Directive | last post by:
I read the C FAQ question on passing a variable number of arguments, but it didn't help. The example assumes all arguments are of the same type. I want to create a function "trace" that can be used like this: trace( "Err", errtType, lineNum, NULL) /* where errType is char* and lineNum is an int */
3
1253
by: piotrek | last post by:
Hi I'd like to ask: How to create function with variable number of parameters? PK
10
9824
by: Praveen.Kumar.SP | last post by:
Hi Could anyone solve the problem for the code below The Code: #include "stdio.h" #include "iostream.h" void Temp( int a, char* str,...)
4
1796
by: Bit Byte | last post by:
Is it possible to pass variable length parameters to a C function exported in a DLL (i.e. using varargs.h without resorting to say passing a delimited string to be parsed in the function)? i.e. I want something like this: #include <varargs.h> CCONV foo( char* fmt, va_list args ) ;
11
2528
by: Fan Yang | last post by:
I'm reading Modern C++ Design, and it is saying "Variable template parameters simply don't exist." But I find VC7.1 & VC8 support this feature.Who can tell me that which is right -_-b Many thanks.
14
6660
oll3i
by: oll3i | last post by:
i want to write a bank account programme and use a command pattern where execute method has variable number of parameters. public interface Command { public abstract void execute (String ... s); } how do i get the parameters passed to that method? when is the execute method called?
2
1906
by: Alan | last post by:
I have a couple of questions about using a variable number of arguments in a function call (...). The context is that I have some mathematical functions I created. I currently pass them a pair of numbers (doubles), and the functions only work in one dimension. I would ideally like to extend them to work in N dimensions, which means I want...
2
2272
by: rameshbabupeddapalli | last post by:
Hi, I want to create my own variable length parameters function it should work like printf() function. When i write the program as given below it's working fine #include<stdio.h> void main() { void fun(int, ...);
0
7584
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7512
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7783
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8023
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7547
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6129
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5426
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
828
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.