New post
Question
· Jul 17, 2017

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?  

Discussion (28)1
Log in or sign up to continue
Vitaliy Serdtsev · Jul 18, 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)

3 0
Laura Cavanaugh · Jul 20, 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?

0 0
Rubens Silva · Jul 20, 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) ]

0 0
Rubens Silva · Jul 18, 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.

0 0
Vitaliy Serdtsev · Jul 18, 2017
  1. 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.
  2. Now the optimizer is pretty advanced. Important to periodically make tuning table and add an index on each field involved in the search.
  3. When unknown parameters >5, is easier to do one query than to write a bunch of code.
2 0
Rubens Silva · Jul 18, 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.

0 0
Laura Cavanaugh · Jul 18, 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.

0 0
Rubens Silva · Jul 18, 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.

0 0
Laura Cavanaugh · Jul 18, 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!!
  

0 0
Vitaliy Serdtsev · Jul 18, 2017

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

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)

1 0
Laura Cavanaugh · Jul 18, 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
Timothy Leavitt · Jul 18, 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
Rubens Silva · Jul 18, 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 0