Announcement
· Mar 27, 2020
Open Exchange

Give it a try on SQLBuilder tool

#Contest #Docker #ObjectScript #InterSystems IRIS #InterSystems IRIS for Health #Open Exchange

SQLBuilder is a flexible and powerful SQL query string builder for InterSystems IRIS,

With SQLBuilder you have nice and clean object oriented methods, instead of having to use concatenation and substituition to generate dynamic queries.

A Dynamic SQL without SQLBuilder

A Dynamic SQL with SQLBuilder

 

If you like it, don't forget to vote in the IRIS Programming Contest

Check the related application on InterSystems Open Exchange
Discussion (18)1
Log in or sign up to continue
Eduard Lebedyuk · Mar 27, 2020

My preferred approach is using a Query class element.

Here's how it can look like:

Class Sample.Person Extends %Persistent
{

Property Name As %String;

Query ByName(name As %String = "") As %SQLQuery
{
SELECT ID, Name
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}

ClassMethod Try(name)
{
  set rset = ..ByNameFunc(name)
  do rset.%Display()
}

}

Short and concise.

1 0
Henry Pereira · Mar 27, 2020

Hi @Eduard Lebedyuk 
Yes, you are totally right. Using query class element will be concise and faster.
The point to use dynamic query are when the user choose the parameters to you do the query. 
Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.
In my $0.02 maybe will create an ByName and a ByAge methods, and another to combine both.
Like I said, it's a simple example that can solved by an OR, but in a complex report with the user need to choose by parameters use dynamic query could be an alternative
 

0 0
Vitaliy Serdtsev · Mar 27, 2020

Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.

Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' or Age >= :Age)
}

Run Examples:
select * from Sample.SP_Sample_Filter_By(,47)
select * from Sample.SP_Sample_Filter_By('',47)

select * from Sample.SP_Sample_Filter_By('s',47)

select * from Sample.SP_Sample_Filter_By('s')
select * from Sample.SP_Sample_Filter_By('s','')
0 0
Paul Waterman · Oct 23, 2020

But will it use a name or age index? Age index bad example but hey. Sqlquery is a class so you can extend/replace it. We came up with a syntax where the sql would generate based on the parameters.  So you could do

SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 1=1
--If Name'=""
  And Name %STARTSWITH :Name
--endif 
--if Age'=""
  AND Age >= :Age
--endif
0 0
Rodrigo Werneck · Jul 17, 2023

What about this dynamic discarding of SQL criteria based on empty parameters extending %SQLQuery?
 

Class gen.SmartSQLQuery Extends %Library.SQLQuery
{

ClassMethod Func() As %SQL.StatementResult [ CodeMode = generator, ProcedureBlock = 1, ServerOnly = 1 ]
{
    set %code=0
        // don't generate any code if it not for a query
    if %mode="method" quit $$$OK
        // %mode is "propertymethod" for a valid query. We don't have any way currently to detect a misuse of a query class
    if '$$$comMemberDefined(%class,$$$cCLASSquery,%property) quit $$$OK
        // Reset the formal list to the query declaration:
    $$$comSubMemberKeyGetLvar(formal,%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed)
    $$$comMemberKeyGetLvar(qformal,%class,$$$cCLASSquery,%property,$$$cQUERYformalspecparsed)
    $$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed,formal_qformal)
    Set glbArgList = formal_qformal
    Set publicList = ""
    For i=1:1:$ListLength(glbArgList) {
        Set $Piece(publicList,",",i) = $List($List(glbArgList,i),1)
    }
    Set publicList = publicList _ "," _ "tStatement" _ "," _ "tResult"
    $$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHpubliclist,publicList)
    set sc=$$SetOneQueryMeth^%occQuery(%class,%property,%method) quit:$$$ISERR(sc) sc

    $$$comMemberKeyGetLvar(origin,%class,$$$cCLASSquery,%property,$$$cXXXXorigin)
    $$$comMemberKeyGetLvar(query,%class,$$$cCLASSquery,%property,$$$cQUERYsqlquery)
        // preparse the query to construct the actual argument list. If more than the supported number of arguments then revert to
        // the non-dynamic option
    set query = $zstrip(query,"<W")
    set tLines = 0 for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= "" { set tLines = tLines + 1, tLines(tLines) = tLine } }
    set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) QUIT:$$$ISERR(sc) sc
    set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)
    
    $$$GENERATE($Char(9)_"try {")
    $$$GENERATE($Char(9,9)_"Set query = """_$replace(query,$$$NL,"""_$C(13,10)_""")_"""")
    $$$GENERATE($Char(9,9)_"For i=1:1:$Length(query,$$$NL) {")
    $$$GENERATE($Char(9,9,9)_"Set line=$Piece(query,$$$NL,i)")
    $$$GENERATE($Char(9,9,9)_"If line?.E1"":""1.AN {")
    $$$GENERATE($Char(9,9,9,9)_"Set var=$Piece($Piece(line,"":"",2),"" "",1)")
    $$$GENERATE($Char(9,9,9,9)_"if @var="""" {")
    $$$GENERATE($Char(9,9,9,9,9)_"Set $Piece(query,$$$NL,i) = ""-- ""_line")
    $$$GENERATE($Char(9,9,9,9)_"}")
    $$$GENERATE($Char(9,9,9)_"}")
    $$$GENERATE($Char(9,9)_"}")
    $$$GENERATE($Char(9,9)_"set tLines = 0 for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= """" { set tLines = tLines + 1, tLines(tLines) = tLine } }")
    // $$$GENERATE($Char(9,9)_"set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) Throw:$$$ISERR(sc) ##class(%Exception.StatusException).ThrowIfInterrupt(sc)")
    $$$GENERATE($Char(9,9)_"set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)")
    $$$GENERATE($Char(9,9)_"//")
    $$$GENERATE($Char(9,9)_"set tSelectMode = """_$Case($$$ucase(%parameter("SELECTMODE")), "RUNTIME": "", "ODBC": 1, "DISPLAY": 2, "LOGICAL": 0, : "")_"""")
    $$$GENERATE($Char(9,9)_"if SQLCODE=0 && ($Listlength(tStatementArgs) < 361) && ($Length(tStatementPreparsed) < 40000) {")
    $$$GENERATE($Char(9,9,9)_"set tExecuteArgs = """" for tPtr=1:2:$ListLength(tStatementArgs) { set tArg = $Case($List(tStatementArgs,tPtr),""?"":""$g(%parm(""_$Increment(qcount)_""))"",""c"":$$quoter^%qaqpreparser($List(tStatementArgs,tPtr+1)),""v"":""$g(""_$List(tStatementArgs,tPtr+1)_"")"",:"""") Set tExecuteArgs = tExecuteArgs _ "","" _ tArg }")
        $$$GENERATE($Char(9,9,9)_"set tSchemaPath = ##class(%SQL.Statement).%ClassPath($classname())")
        $$$GENERATE($Char(9,9,9)_"set tStatement = ##class(%SQL.Statement).%New(tSelectMode,tSchemaPath)")
        $$$GENERATE($Char(9,9,9)_"do tStatement.prepare(tStatementPreparsed)")
        $$$GENERATE($Char(9,9,9)_"Xecute ""set tResult = tStatement.%Execute(""_$Extract(tExecuteArgs,2,*)_"")""")
    $$$GENERATE($Char(9,9)_"}")
    $$$GENERATE($Char(9)_"}")
    $$$GENERATE($Char(9)_"catch tException { if '$Isobject($Get(tResult)) { set tResult = ##class(%SQL.StatementResult).%New() } set tResult.%SQLCODE=tException.AsSQLCODE(),tResult.%Message=tException.AsSQLMessage() }")
    $$$GENERATE($Char(9)_"Quit tResult")
    QUIT $$$OK
}

}
0 0
Rodrigo Werneck · Jul 18, 2023 
Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' or Age >= :Age)
}

This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.
That´s why I followed Paul´s idea and came up with %SQLQuery´s subclass SmartSQLQuery found above which dynamically comments out each criteria which is not applicable.

0 0
Vitaliy Serdtsev · Jul 19, 2023 
Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}
This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.

What is your conclusion based on?
If you check the plans of this query for different combinations of parameters, then the corresponding indexes are used (it is assumed that the table was previously configured via TuneTable).

select * from Sample.SP_Sample_Filter_By('s')


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(NULL IS NULL or Age >= NULL)
select * from Sample.SP_Sample_Filter_By(,47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(NULL IS NULL or Name %STARTSWITH NULL)
AND
(47 IS NULL or Age >= 47)
select * from Sample.SP_Sample_Filter_By('s',47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(47 IS NULL or Age >= 47)
0 0