Need variable number of query parameters in dynamic query

SQL, Caché

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?

Answers

Answer by Vitaliy Serdtsev 18 July 2017

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)

+ 2 0

Accepted answer

Comment by Alexander Koblov 18 July 2017

Please notice, that execution plan for

select * from person where (lastname=? or ? is null) and (age > ? or ? is null)

might be less optimal than

select * from person where lastname=?

So in some sense it's better to generate different queries based on input

+ 2

Comment by Vitaliy Serdtsev 18 July 2017

Non-optimal plan does not necessarily mean low speed. I have had cases where the query with the best plan to work longer than a query with worse plan.
Now the optimizer is pretty advanced. Important to periodically make tuning table and add an index on each field involved in the search.
When unknown parameters >5, is easier to do one query than to write a bunch of code.

+ 1

Comment by Rubens Silva 18 July 2017

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).

Comment by Vitaliy Serdtsev 18 July 2017

I beside itself doing exactly via the query class.

+ 1

Answer by Laura Cavanaugh 18 July 2017

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.

0 0

Comment by Rubens Silva 18 July 2017

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.

Comment by Laura Cavanaugh 18 July 2017

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_")""

Comment by Rubens Silva 18 July 2017

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.

Comment by Laura Cavanaugh 18 July 2017

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!

Comment by Rubens Silva 18 July 2017

Ahh, that's because you're using IN instead of %INLIST. IN doesn't expect a list, so throws an exception.

Comment by Laura Cavanaugh 18 July 2017

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 filter = $listfromstring(filterClaims) //build my $lb() for the Execute parameter
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.

Comment by Rubens Silva 18 July 2017

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:

SET states=$LISTBUILD("VT","NH","ME")
SET myquery = "SELECT Name,Home_State FROM Sample.Person WHERE Home_State %INLIST ?"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute(states)

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.

Comment by Laura Cavanaugh 18 July 2017

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:

Set status = tRS.Execute(filterParams...) // < -- look, I need the ... !!

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!!

Comment by Rubens Silva 18 July 2017

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

Comment by Evgeny Shvarov 18 July 2017

Yes, maybe we should stop shrinking comments' width on some level )

+ 2

Comment by Laura Cavanaugh 18 July 2017

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!

+ 1

Answer by Timothy Leavitt 18 July 2017

To expand on Rubens' comment about a variable arity of arguments, I often use the following pattern:

Set tQuery = "select fields from some_table"
Set tWhereList = ""

If (pFilter1 '= "") {
    Set tWhereList = tWhereList_$ListBuild("field1 = ?")
    Set tArgs($i(tArgs)) = pFilter1
}
If (pFilter2 '= "") {
    Set tWhereList = tWhereList_$ListBuild("field2 = ?")
    Set tArgs($i(tArgs)) = pFilter2
}

// and so on...

If (tWhereList '= "") {
    Set tQuery = tQuery_" "_$ListToString(tWhereList," and ")
}

Set tResult = ##class(%SQL.Statement).%ExecDirect(,tQuery,tArgs...)

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:

select t.fields from some_table t join (select ? as field1, ? as field2) params where t.field1 = params.field1 and t.field2 = params.field2 and Some_Stored_Procedure(t.field3,params.field1) > params.field2

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).

+ 3 0

Comment by Rubens Silva 18 July 2017

I didn't know that Caché allowed to use JOINs without specifying FROM. That's really useful to know indeed.

Comment by Vitaliy Serdtsev 18 July 2017

I didn't know that Caché allowed to use JOINs without specifying FROM.

JOIN or SELECT^{Optional 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)

+ 1

Comment by Rubens Silva 18 July 2017

Makes sense, I think it's because OUTER JOIN lifts equivalence constraints when joining.

Comments

Comment by Rubens Silva 18 July 2017

%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...)
}

+ 3

Comment by John Murray 18 July 2017

Rubens, your rule #2 says " Argument index starts from 0" but in your example the index starts at 1.

+ 1

Comment by Rubens Silva 18 July 2017

Whoops! Fixed.

Comment by Alexey Maslov 19 July 2017

Method with a variable arity can also pass through their arguments to the next method

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.

Comment by Rubens Silva 20 July 2017

Are you sure?

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
}

Comment by Laura Cavanaugh 20 July 2017

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?

Comment by Rubens Silva 20 July 2017

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 ~~always~~ use ... 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:

$classmethod($this, "Sum", 1,2,3,4,5,6) // up to 255 parameters.

Also, yes, it works for %ResultSet.Execute, by the way here is the method signature:

Method Execute(args...) As %Status [ PublicList = (qHandle, args) ]