Your better (variadic arguments) solution can be improved. It has the following disadvantages:
You have to repeat many times
set args($increment(args)) =
You have to add a space at the end of every part (except of the last one). For example:
"and Product = ? "
As an inspiration you can look at this class. We use it in our product.
Class L3.SQL.QueryBuilder Extends%RegisteredObject
{
Property STATEMENT [ MultiDimensional, Private ];Property PARAMS [ MultiDimensional, Private ];
Method Add(line As%String, params... As%String)
{
If$Get(line)=""Set$Ecode="Parameter 'line' is empty."Set i=$Increment(..STATEMENT)
Set..STATEMENT(i)=line
If '$Data(params) ReturnForj=1:1:params {
Setk=$Increment(..PARAMS)
Set..PARAMS(k)=params(j)
}
}
Method Execute(Output statementResult As%SQL.StatementResult) As%Status
{
#dim statement As%SQL.Statement#dim sc As%StatusKill statementResult
If +$Get(..STATEMENT)=0Set$Ecode="Empty statement."; Set SelectMode to 0. Set statement=##class(%SQL.Statement).%New(0)
Merge STATEMENT=..STATEMENTSet sc=statement.%Prepare(.STATEMENT)
If$$$ISERR(sc) Return sc
Merge PARAMS=..PARAMSSet statementResult=statement.%Execute(PARAMS...)
If (statementResult.%SQLCODE'=0) && (statementResult.%SQLCODE'=100) {
Return$System.Error.FromSQLCode(statementResult.%SQLCODE,statementResult.%Message).Status
}
Return$$$OK
}
}
Now I am able to rewrite you example code like this:
set qb=##class(L3.SQL.QueryBuilder).%New()
do qb.Add("select Product->Name, Outlet->City, AmountOfSale, UnitsSold")
do qb.Add("from HoleFoods.SalesTransaction where Actual = 1")
if (product '= "") {
do qb.Add("and Product = ?", product)
}
if (channel '= "") {
do qb.Add("and Channel %INLIST ?", channel)
}
if (minProductPrice '= "") {
do qb.Add("and Product->Price >= ?", minProductPrice)
}
if (soldOnOrAfter '= "") {
do qb.Add("and DateOfSale >= ?", soldOnOrAfter)
}
set sc=qb.Execute(.rset)
You can use many question marks in one line:
do qb.Add("and DateOfSale between ? and ?", soldAfter, soldBefore)
Hello Timothy.
Thanks for your article.
Your better (variadic arguments) solution can be improved. It has the following disadvantages:
set args($increment(args)) =
"and Product = ? "
As an inspiration you can look at this class. We use it in our product.
Class L3.SQL.QueryBuilder Extends %RegisteredObject { Property STATEMENT [ MultiDimensional, Private ]; Property PARAMS [ MultiDimensional, Private ]; Method Add(line As %String, params... As %String) { If $Get(line)="" Set $Ecode="Parameter 'line' is empty." Set i=$Increment(..STATEMENT) Set ..STATEMENT(i)=line If '$Data(params) Return For j=1:1:params { Set k=$Increment(..PARAMS) Set ..PARAMS(k)=params(j) } } Method Execute(Output statementResult As %SQL.StatementResult) As %Status { #dim statement As %SQL.Statement #dim sc As %Status Kill statementResult If +$Get(..STATEMENT)=0 Set $Ecode="Empty statement." ; Set SelectMode to 0. Set statement=##class(%SQL.Statement).%New(0) Merge STATEMENT=..STATEMENT Set sc=statement.%Prepare(.STATEMENT) If $$$ISERR(sc) Return sc Merge PARAMS=..PARAMS Set statementResult=statement.%Execute(PARAMS...) If (statementResult.%SQLCODE'=0) && (statementResult.%SQLCODE'=100) { Return $System.Error.FromSQLCode(statementResult.%SQLCODE,statementResult.%Message).Status } Return $$$OK } }
Now I am able to rewrite you example code like this:
set qb=##class(L3.SQL.QueryBuilder).%New() do qb.Add("select Product->Name, Outlet->City, AmountOfSale, UnitsSold") do qb.Add("from HoleFoods.SalesTransaction where Actual = 1") if (product '= "") { do qb.Add("and Product = ?", product) } if (channel '= "") { do qb.Add("and Channel %INLIST ?", channel) } if (minProductPrice '= "") { do qb.Add("and Product->Price >= ?", minProductPrice) } if (soldOnOrAfter '= "") { do qb.Add("and DateOfSale >= ?", soldOnOrAfter) } set sc=qb.Execute(.rset)
You can use many question marks in one line:
do qb.Add("and DateOfSale between ? and ?", soldAfter, soldBefore)
Best regards.