Need variable number of query parameters in dynamic query
I have a query string that I am creating programmatically, based on some user inputs. The user might search on 5 fields, or 8 fields, or no fields.
In my sql statment, some of these fields require parameters in the %Execute statement.
For example:
if user picks lastname, sql = "select * from person where lastname = ?"
if user also picks age, sql = "select * from person where lastname=? and age > ?"
I then have these lines of code to create my result set:
set statement = %SQL.Statement
statement.%Prepare
resultset = statement.%Execute(param1, param2)
-- but it might be
resultset = statement.%execute(param1)
How can I pass a variable number of parameters when I don't know how many the user will pick to filter the search? I need some kind of parameter object? Or will indiraction work? Something with $lists? %ListOfDataTypes?
parameterObject.Insert(param1)
parameterObject.Insert(param2)
resultset = statement.%Execute(parameterObject)
Does anything like this exist?
Possible do on-other, namely to write one "universal" query:
select * from person where
(lastname=:lastname or :lastname is null) and
(age > :age or :age is null)
That is:
sql = "select * from person where (lastname=? or ? is null) and (age > ? or ? is null)"
resultset = statement.%Execute(param1, param1, param2, param2)
Please notice, that execution plan for
might be less optimal than
So in some sense it's better to generate different queries based on input
Whoops! Fixed.
The main downside of using "?" instead of :param is that if you have multiple ocurrences from the same column you have to repeat the parameter. That's why I would discard using dynamic queries if the SQL already covers all conditions to display the results and create it as class query (possibly your first example).
ClassMethod TestAritySum()
{
set args = 3
set args(1) = 10
set args(2) = 20
set args(3) = 30
quit $classmethod($this, "Sum", args...)
}
ClassMethod Sum(n... As %Integer)
{
set sum = 0
for i=1:1:n {
set sum = sum + n(i)
}
quit sum
}
Wow, that works in 2014 too! So, there must be something with my $lb parameter, you're right.
Note what happens here, without the ... . What is that?
Can't find it in the documentation.Nevermind, I found it: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...quit $classmethod($this, "Sum", .args)
ClassMethod Sum(n... As %Integer)
zw n
n=1
n(1)=3
n(1,1)=10
n(1,2)=20
n(1,3)=30
But... does it work for a SQL query? It's fine in COS, but what about a %ResultSet.Execute() call? Should I change it to %SQL.Statement?
Yes, you must use ... to inform the method that you're are using the format compatible with variable arity.
When using . instead, the compiler assumes it should index from the subscript that you provided. It's not a bug, but a unexpected behavior caused by your input as the compiler modifies the way it treats the parameter if you put ... on the declaration.
So yep, you should
alwaysuse ... for passing and receiving parameters with variable arity, unless you know exactly which parameters and their passing index, you can express it using a more "literal" format: as you have noticed, right from start I defined a param variable to hold all my values, this is really useful for populating the arity from a loop.But what if already know what to pass? You could also call the method as:
Also, yes, it works for %ResultSet.Execute, by the way here is the method signature:
Method Execute(args...) As %Status [ PublicList = (qHandle, args) ]
Actually, the amount of placeholders inside the SQL is what dictates how many parameters should be read. SQL engines or ORMs that support queries with placeholders usually escape the input value already to prevent attacks like SQL injection. And that is why you should never use _concatenation_ with SQL strings. Because the engine cannot sanitize what is not an explicit value.
As you said the parameter is a %List instead of a multidimensional you don't need to do anything regarding the input, just pass it and read it using %INLIST.
And surely, if you can provide a fixed number of parameters, this should always be your first option.
I didn't know that Caché allowed to use JOINs without specifying FROM. That's really useful to know indeed.
Rubens, your rule #2 says " Argument index starts from 0" but in your example the index starts at 1.
Good point!
Wouldn't it be great if $classmethod and $method functions support variable arity as well? (Today they don't, Cache 2017.1). How to use them with such a methods without this feature?
PS. Sorry for off-topic.
I'll assume that your 'where' variable is already your SQL query.
set params=1
set params(1)=$lb(,1,2,3,4,5,6,7)
Set where(16)=InstancePrefix_"PayerName IN (?)""
"
do statement.%Prepare(.where)
set rows = s.%Execute(params...)
Remember that you still can concatenate SQL parts, because they aren't editable by the end-user, I just don't recommend concatenating values.
Yes, exactly. We are building the sql statement line by line; the where clauses are built based on user input, and the whole mess is concatenated with actual values. hmm. But essentially, it looks like the params array is what I needed. Except that I'm getting a <LIST> error, so I have to go back and look at that. I'm on 2014.1.3.
Thanks!
Ahh, that's because you're using IN instead of %INLIST. IN doesn't expect a list, so throws an exception.
No, I am using %INLIST -- it's all the other where clauses that are using IN, with concatenation. I had read that %INLIST would use the same cached query, or something, so decided %INLIST was better, then ran into this problem where I now have to pass in an Execute parameter, whereas all of the other params are basically hard-coded.
Here's a copy of my query that was built:
SELECT * FROM MyTable
WHERE ((DateTime >= '2017-07-11 00:00:00') AND (DateTime <= '2017-07-18 23:59:59')) AND (Claim->Status->Name IN ('Paid'))
AND (Claim->ClaimNumber = ANY (Select Claim->ClaimNumber from MyTable where Claim->ClaimNumber %INLIST ? SIZE ((10)) ))
The last line is mine. Shoot. It was working when I passed in a $lb like this:
set returnParams = $lb(filter) //$lb($lb(1,2,3), hypothetical param2, ... ,paramN)
set param1 = $lg(returnParams,1) //param1 = $lb(1,2,3)
// ... set paramN = $lg(filterParams,n) ...
Set status = tRS.Execute(param1)
but now, with the returnParams as an arity (that's cool, btw), I'm getting a <LIST>. If necessary, I'll go back to the forced "param1" thing, because this is an urgent fix. I can play around with it later.
Uhmm... what could be causing this issue is that embedded list. Your %INLIST reads one list, but the result is another list.
Or maybe I'm still missing some piece of code to understand better.
Anyway according to the documentation:
I assume it should be:
set params=N
set params(1)=$lb(1,2,3)
set params(2)=hypothetical param2
...
set params(N)=paramN
I can see that your returnParams is a list instead of the format that allows variable arity.
But like I said, I might be missing some piece of code to understand better.
However, since my suggestion is not a surefire tip and you're in a hurry I think you should opt-in for the fatest working solution and investigate the error cause when you have some time, just like you said.
No, it's just how I'm copying code into here... Here is what I am using:
^llctemp("query")="SELECT * FROM MyTable WHERE ((DateTime >= '2017-07-11 00:00:00') AND (DateTime <= '2017-07-18 23:59:59')) AND (Claim->Status->Name IN ('Paid')) AND (Claim->ClaimNumber = ANY (Select Claim->ClaimNumber from MyTable where Claim->ClaimNumber %INLIST ? SIZE ((10)) )) "
"returnParams")=1
^llctemp("returnParams",1)=$lb("2902700","2869840","2870820","")
That's my test data. Then I say:
Turns out I LITERALLY need the ... ( ! )
Now I'm not getting the <LIST> but I'm also not getting data! Sigh. I'll get it. I am rushing a little, so that's messing me up. I think with all this, I can get it to work at some point. Thanks!!
Now I can assume that you merged your "returnParams" subscript into a variable called filterParams.
Doesn't look wrong for me, just wondering about that SIZE. Try removing it and see if it affects your results.
Also check if you modified the display mode. Along with that status variable to see if some error happened.
P.S.: It's becoming hard to type here :x
Yes, maybe we should stop shrinking comments' width on some level )
Well, it all works if I force feed the params list. If I do get it, I'll let you know. Thanks for all the help!
Makes sense, I think it's because OUTER JOIN lifts equivalence constraints when joining.
Oh no, good to know about concatening values instead of using ? Execute params. I should go rewrite the entire query! It has so many optional parameters though, so I'll go look at the Rubens/Timothy solution. Thanks.
So this is not a good way to build the where clause:
" Set where(16)=InstancePrefix_"PayerName IN ("_valuelist_")""
?
JOIN or SELECTOptional FROM Clause?
But it should be noted that for JOIN not all usages are supported, for example:
Supported:
SELECT * FROM
(select 'aaa' Column1,'bbb' Column2 union select 'ccc','ccc' union select 'xxx','yyy' union select 'hhh','zzz') Table1
LEFT OUTER JOIN
(select 'ggg' Column1,'hhh' Column3 union select 'xxx','zzz') Table2
ON Table1.Column1=Table2.Column3
Not supported:
SELECT * FROM
(select 'aaa' Column1,'bbb' Column2 union select 'ccc','ccc' union select 'xxx','yyy' union select 'hhh','zzz') Table1
INNER JOIN
(select 'ggg' Column1,'hhh' Column3 union select 'xxx','zzz') Table2
USING (Column1)
I beside itself doing exactly via the query class.
I think it's a bit safer anyway to know exactly how many parameters to expect (avoid a sql injection attack?); I can see how long the one statement with a known number of parameters takes compared to other statements. So far I have just the one unknown parameter, and all the rest are built into the sql. Thanks.
Oh -- the parameter is a $lb(); the sql is using %INLIST, which, in the example in the documentation, seems to require a parameter passed in the %Execute() rather than built into a value in the sql statement. The user might or might not search on this field. It's more like:
select * from person where ID %INLIST ?
I was thinking that since I need to pass this in to the Execute, I might as well set up a structure in case we add more parameters to the statement in the future; but perhaps it's better to just pass in a known number.
Thanks.
To expand on Rubens' comment about a variable arity of arguments, I often use the following pattern:
Perhaps also generating and appending an ORDER BY similarly.
Regarding other comments about multiple uses of the same parameter value, an approach I've seen used for that is to JOIN to a list of parameters - something like:
This is perhaps a little more readable/maintainable than trying to keep track of what all the ?'s mean when the query expects the same value to be repeated multiple times. I can imagine a similar general pattern for building such queries dynamically (e.g., build an array of parameters by name, then construct the subquery for the join and add values to the tArgs array in the order they appear in that subquery).
%Execute and Execute as well support a variable arity of arguments. This can be reproduced as:
set args=2
set args(1)="first_value"
set args(2)="second_value"
do statement.%Execute(args...)
Notice that the variable itself contains the total of arguments while each subscript is sequential, this defines their position as arguments.
Methods that support a variable arity imply on using three rules:
1 - Argument variable must contain the arity size.
2 - Argument index starts from 1.
3 - Index must be sequential.
Method with a variable arity can also pass through their arguments to the next method like this:
ClassMethod First(params... As %String)
{
do ..Second(params...)
}
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue