473,699 Members | 2,292 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 6073
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
7810
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 same as f1's) void f1(char a ,...)
10
5019
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
1256
by: piotrek | last post by:
Hi I'd like to ask: How to create function with variable number of parameters? PK
10
9838
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
1800
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
2543
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
6672
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
1917
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 to pass some undefined number of pairs of arguments. I will loop through the N arguments to do the...
2
2276
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
8700
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9184
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9048
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8931
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7767
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6539
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4386
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.