DEV Community

InterSystems profile image InterSystems Developer
InterSystems Developer for InterSystems

Posted on • Originally published at community.intersystems.com

Tutorial for Middle/Senior Level Developer: General Query Solution

#tutorial #programming #sql

Brief

What is Query

Query is a method for finding data that meets the conditions and presenting the results as a data set.

Type of Query

  • SQL Query，Using %SQLQuery and SQL SELECT.
  • Custom Query，Using Class %Query and custom logic to generate result.

Note: Before we talk about the general Query solution, let's first understand the basics of Query to help understand the implementation principles. If you already know the basic use of Query, please skip this section and go straight to "Challenges".

Query Basics

SQL Query Basics 

java
Query QueryPersonByName(name As %String = "") As %SQLQuery(COMPILEMODE = "IMMEDIATE", CONTAINID = 1, ROWSPEC = "id:%Integer:ID,MT_Name:%String:name,age:%String,no:%String", SELECTMODE = "RUNTIME") [ SqlName = QueryPersonByName, SqlProc ]
{
    SELECT top 10 ID, MT_Age, MT_Name, MT_No
    FROM M_T.Person
    WHERE (MT_Name %STARTSWITH :name)
    ORDER BY id
}
Enter fullscreen mode Exit fullscreen mode

Description：

  • Query - Declares the Query method keyword.

  • QueryPersonByName - Declares the Query method.

  • name As %String = "" - Declares the Query method keyword.

  • %SQLQuery - The Query type is %SQLQuery.

    • %SQLQuery is a subclass of %Query and uses a simple form of Query that allows writing Select SQL statements directly within the method body.

  • COMPILEMODE - parameter for %SQLQuery indicating the compilation method.

    • IMMEDIATE - compiles immediately, when checking if the current SQL statement is correct.
    • DYNAMIC - compile dynamically, when the SQL statement is compiled at runtime.

  • CONTAINID - To set the number of the column placed as the returned ID.

    • 1 - Returns the ID column.
    • 0 - Does not return.

  • SELECTMODE - Indicates the display method.

    • RUNTIME - None
    • ODBC - Displays the data as ODBC.
    • DISPLAY - Displays the data as a display.
    • LOGICAL - Displays the data in a logical manner.

  • ROWSPEC - Provides the data column name, data type, and description. A list of variable names and data types separated by quotation marks and commas. The format is as follows.

    ROWSPEC = "id:%Integer:ID,age:%String,MT_Name:%String:name,no:%String"
Enter fullscreen mode Exit fullscreen mode
  • id - Indicates the data column name.
  • %Integer - Indicates the data type.

  • ID - The data description.

    • SqlProc - Indicates that the method can be called as a stored procedure.
    • SqlName - The name of the procedure to be called.

  • Undeclared call method - call M.Query_QueryPersonByName()

  • Undeclared call method - call M.QueryPersonByName()

Image description

  • Undeclared call method - d ##class(%ResultSet).RunQuery(className, queryName, arg...) 
USER>d ##class(%ResultSet).RunQuery("M.Query", "QueryPersonByName")

ID:age:name:no:
1:21:yaoxin:314629:
2:29:yx:685381:
3:18:Umansky,Josephine Q.:419268:
4:27:Pape,Ted F.:241661:
5:25:Russell,Howard T.:873214:
6:30:Xenia,Ashley U.:420471:
7:24:Rotterman,Martin O.:578867:
8:18:Drabek,Hannah X.:662167:
9:19:Eno,Mark U.:913628:
11:18:Tsatsulin,Dan Z.:920134:
Enter fullscreen mode Exit fullscreen mode

Basics of Custom Query

When using a custom Query, a fixed template is generally followed. The following class methods are defined in the same class.

  • QueryName - Specify %Query in the Query method type.
  • QueryNameExecute— This method mainly writes the business logic to fetch the data and get the data set.
  • QueryNameFetch — This method iterates through the dataset.
  • QueryNameClose — This method deletes temporary data or objects.

*Note: The following example shows a custom Query template that is one of the most commonly used cases, not a fixed one. *

QueryName Definition

Query QueryPersonByAge(pAge As %String = "", count As %Integer = "10") As %Query(ROWSPEC = "id:%Integer:ID,MT_Name:%String:name,age:%String,no:%String")
{
}
Enter fullscreen mode Exit fullscreen mode

Define the Query type named QueryPersonByAge specified as %Query. and leave the body of the query definition blank.

QueryNameExecute Definition

ClassMethod QueryPersonByAgeExecute(ByRef qHandle As %Binary, pAge As %String = "", count As %Integer = "10") As %Status
{
    s pid = $i(^IRISTemp) // comment1
    s qHandle = $lb(0, pid, 0) // comment2
    s index = 1 // comment 3

    /* Business Logic comment4 */ 
    s id = ""
    for {
        s id = $o(^M.T.PersonD(id))
        q:(id = "")
        q:(id > count)
        s data = ^M.T.PersonD(id)
        s i = 1
        s name = $lg(data, $i(i))
        s age = $lg(data, $i(i))
        continue:(age < pAge)
        s no = $lg(data, $i(i))
        d output
    }   

    q $$$OK

output
    s ^IRISTemp(pid, index) = $lb(id, age, name, no) // comment 6 
    s index = index + 1 // comment 7
}
Enter fullscreen mode Exit fullscreen mode

The QueryNameExecute() method provides all the required business logic. The name of the method have be QueryNameExecute(), where QueryName is the name of the defined Query.

其中：

  • qHandle - Used to communicate with other methods that implement this query. qHandle can be of any type. The default is %Binary.
  • pAge As %String = "", count As %Integer = "10" are the incoming parameters for the Query, which can be used as conditions for the business logic.
  • Comment 1，s pid = $i(^IRISTemp) - get pid.
  • Comment 2，s qHandle = $lb(0, pid, 0) - The first element 0 in the array indicates the start of the loop, the second element pid is used to get the ^IRISTemp data, and the third element 0 is used to traverse the ^IRISTemp starting node.
  • Business logic code - is the main implementation logic for fetching the dataset.
  • Comment 3 and comment 7，add index nodes to ^IRISTemp.
  • Comment 6，s ^IRISTemp(pid, index) = $lb(id, name, age, no) - assigning values to ^IRISTemp for subsequent traversal.
    • Here the data format is in the form of %Library.List, so that the Fetch method does not need to convert the type, otherwise the Fetch method would still need to convert the data to the internal list format.

QueryNameFetch Definition

ClassMethod QueryPersonByAgeFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = QueryPersonByAgeExecute ]
{
    s end = $li(qHandle, 1) // comment1
    s pid = $li(qHandle, 2)
    s index = $li(qHandle, 3)
    s index = $o(^IRISTemp(pid, index)) // comment2
    if index = "" {  // comment3
        s end = 1
        s row = ""
    } else { 
        s row = ^IRISTemp(pid, index)
    }
    s qHandle = $lb(end, pid, index) // comment4
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode

The QueryNameFetch() method must return a single row of data in %Library. The name of the method must be QueryNameFetch, where QueryName is the name of the defined Query.

In Which：

  • qHandle - Used to communicate with other methods that implement this query. Its value should be the value defined by Execute.
  • row - indicates the value of the row of data to be returned of type %Library.List, or the empty string if no data is returned.
  • end - end must be 1 when the last row of data is reached. if 1 is not specified, it will loop indefinitely.
  • PlaceAfter - The PlaceAfter method keyword controls the order of this method in the generated code. Here it means that the QueryPersonByAgeFetch method is generated after the method QueryPersonByAgeExecute is generated.
  • Comment 1， lines 1~3, parses the qHandle array values to get end, pid, index.
  • Comment 2，s index = $o(^IRISTemp(pid, index)) start traversing according to the parsed pid, index.
  • Comment 3， the traversal of ^IRISTemp(pid, index) each line belongs to the value of row, if index is empty, then must be assigned to 1 end.
  • Comment 4，s qHandle = $lb(end, pid, index)will fetch the end, index re-copy to qHandle for the next line of data to prepare.

Note: The Fetch method is executed multiple times, and it iterates through as many rows of data as possible; the Execute and Close methods are executed once.

QueryNameClose Definition

ClassMethod QueryPersonByAgeClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QueryPersonByAgeExecute ]
{
    s pid = $li(qHandle, 2) // comment1
    k ^IRISTemp(pid) // comment2
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode

The QueryNameClose() method closes after the data retrieval is complete by removing and cleaning up temporary data or objects. The name of the method must be QueryNameClose(), where QueryName is the name of the defined Query.

  • qHandle - Used to communicate with other methods that implement this query.
  • Comment 1，to get the pid saved by qHandle.
  • Comment 2，to clear the temporarily generated ^IRISTemp.

Call a custom Query

USER> d ##class(%ResultSet).RunQuery("M.Query", "QueryPersonByAge","20")

ID:name:age:no:
1:yaoxin:21:314629:
2:yx:29:685381:
4:Pape,Ted F.:27:241661:
5:Russell,Howard T.:25:873214:
6:Xenia,Ashley U.:30:420471:
7:Rotterman,Martin O.:24:578867:
Enter fullscreen mode Exit fullscreen mode
  • The query here is for all people who are older than 20 years old and whose id is less than 10.

Challenges

The above 2 basic examples of Query are probably the two most common ways used right now.

However, developers who often write queries or reports may face several problems as follows:

  1. Each time you write a Query, you need to define the column header ROWSPEC is very troublesome, can you specify the column header ROWSPEC yourself?
  2. Now many methods return JSON values, how to quickly convert JSON methods into Query?
  3. Is it possible to write a generic Query that only needs to write the main logic of Execute?
  4. Is it possible to optimize the current template, such as replacing ^IRISTemp with ^||IRISTemp?

There are solutions for all the above questions, please continue reading the following article section.

Solution

If you want to implement a general Query you also need to know a callback method QueryNameGetInfo.

ClassMethod Json2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status
{
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode

In above：

  • colinfo - This parameter is most critical for defining the ROWSPEC column header section. It contains a list element for each column declared in the ROWSPEC. The form is name:exttype:caption.
    • name - the column header name.
    • exttype - the data type.
    • caption - the description.
  • The colinfo type must be %Library.List, and the type of the defined column header is also %Library.List.

Example：

ClassMethod QueryPersonByAgeGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef %qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status
{
    s colinfo = $lb($lb("id", "%Integer", "ID"), $lb("age", "%String", ""), $lb("MT_Name", "%String", "name"), $lb("no", "%String", ""))
    s parminfo = ""
    s idinfo = ""
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode

Description: Query execution order Execute -> GetInfo -> Fetch(n) -> Close.

The following solutions are described separately：

  • Dynamically generating a Query from Json data or methods
  • Dynamically generating a Query through a Select Sql statement
  • Dynamic Query generation via Query
  • Support legacy Query and generate Query columns by means of parameters
  • Define a generic Query and only need to implement the Excecute method

Dynamically generate Query by Json data or method

Defining Json Methods

  • Json method can be defined arbitrarily, this example is only for testing purposes. The following method: query the current computer disk drive to Json results output. 
ClassMethod QueryDrives(fullyQualified = 1, type = "D")
{
    s array = []
    s rs = ##class(%ResultSet).%New()
    s rs.ClassName = "%File"
    s rs.QueryName = "DriveList"
    d rs.Execute(fullyQualified)
    while (rs.Next()) {
        s drive = rs.Get("Drive")
        s drive = $zcvt(drive, "U")
        s obj = {}
        s obj.type = "D"
        continue:(type '= "D")
        s obj.drive = drive
        d array.%Push(obj)
    }
    q array
}
Enter fullscreen mode Exit fullscreen mode

Run：

USER> w ##class(M.Query).QueryDrives().%ToJSON()
[{"type":"D","drive":"C:\\"},{"type":"D","drive":"D:\\"},{"type":"D","drive":"E:\\"},{"type":"D","drive":"F:\\"},{"type":"D","drive":"G:\\"}]
Enter fullscreen mode Exit fullscreen mode

Define QueryName

Query Json2Query(className As %String, methodName As %String, arg...) As %Query
{
}
Enter fullscreen mode Exit fullscreen mode

其中：

  • className - The class name.
  • methodName - The name of the Json method to be executed.
  • arg... - The parameterss of the method to be executed.

Define QueryNameExecute

ClassMethod Json2QueryExecute(ByRef qHandle As %Binary, className As %String, methodName As %String, arg...) As %Status
{
    s array = $classmethod(className, methodName, arg...) // comment1
    if ('$isobject(array)) { // comment2
        s array = [].%FromJSON(array)
    }
    q:('array.%IsA("%Library.DynamicArray")) $$$ERROR($$$GeneralError, "Not a Array Object") // comment3
    q:(array.%Size() = 0) $$$ERROR($$$GeneralError, "No data") // comment4
    s qHandle = array // comment5
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，using reflection mechanism to call the target method and get the return value.
  • Comment 2，determine if the returned string is converted to Json object.
  • Comment 3，determine if the returned string is converted to Json object.
  • Comment 4，Json array length is 0 throws an error message.
  • Comment 5，get the array object.

Define QueryNameGetInfo

ClassMethod Json2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status
{
    s colinfo = $lb() // comment1
    s count = 1 
    s obj = qHandle.%GetIterator()
    if obj.%GetNext(.key, .value) { 
        s obj = value.%GetIterator() 
        while obj.%GetNext(.objKey, .objValue) { // comment2
            s $li(colinfo, count) = $lb(objKey) 
            s count = $i(count) 
        }
    }   
    s parminfo = "" // comment3
    s idinfo = "" // comment4
    s qHandle = qHandle.%GetIterator() // comment5
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，initialize the colinfo array, assign obj to qHandle.%GetIterator() iterator object.
  • Comment 2，iterate through the Json object to get the Key and assign a value to colinfo by $li.
  • Comment 3，initialize parminfo, otherwise error is reported.
  • Comment 4，initialize idinfo, otherwise error is reported.
  • Comment 5，get the iterator object

Define QueryNameFetch

ClassMethod Json2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Json2QueryExecute ]
{
    s iter = qHandle
    q:($g(iter) = "") $$$OK
    if iter.%GetNext(.key, .value) { // comment1
        s row = ""
        s obj = value.%GetIterator()
        while obj.%GetNext(.objKey, .objValue) { // comment2
            if ( $g(row) = "" ) {
                s row = $lb(objValue)
            } else {
                s row = row _ $lb(objValue)
            }
        }
        s end = 0
    } else {
        s row = "" 
        s end = 1 // comment3
    }
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，get the current iterator Json data row.
  • Comment 2，iterate through the current Json object and concatenate the value with row for $lb.
  • Comment 3，if there is no data set end to 1 to indicate the end of the traversal.

Define QueryNameClose

ClassMethod Json2QueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = Json2QueryFetch ]
{
    s qHandle = "" // comment1
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，Empty the object qHandle.

Note:Actually, M has a related recycling mechanism, and in fact the Close method can be used without declaring it.

Calling the Json2Query method

USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","0","D")

type:drive:
D:D::
D:E::
D:F::
D:G::
Enter fullscreen mode Exit fullscreen mode 
USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","1","D")

type:drive:
D:D:\:
D:E:\:
D:F:\:
D:G:\:
Enter fullscreen mode Exit fullscreen mode

Dynamically generate Query by Select Sql statement

Define QueryName

Query Sql2Query(sql As %String, mode As %String = 1) As %Query
{
}
Enter fullscreen mode Exit fullscreen mode
  • sql - The variable that represents the SQL statement to be written.
  • mode - Displays the data format type.
    • 0 - Logical format
    • 1 - Logical format
    • 2 - Logical format

Define QueryNameExecute

ClassMethod Sql2QueryExecute(ByRef qHandle As %Binary, sql As %String, mode As %String = 1) As %Status
{
    s sqlStatement = ##class(%SQL.Statement).%New()
    s sqlStatement.%SelectMode = mode // comment1
    s sqlStatus = sqlStatement.%Prepare(.sql) // comment2
    q:$$$ISERR(sqlStatus) sqlStatus
    s sqlResult = sqlStatement.%Execute() 
    s stateType = sqlStatement.%Metadata.statementType
    q:('stateType = 1 ) $$$ERROR($$$GeneralError, "Not a select statement") // comment3
    s qHandle = {}
    s qHandle.sqlResult = sqlResult // comment4
    s qHandle.sqlStatement = sqlStatement 
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，set the data display format of SQL.
  • Comment 2，pass in SQL statement to get sqlStatement and sqlResult object.
  • Comment 3，pass in the SQL non-Select statement, throw an error message.
  • Comment 4，the qHandle passed into the two objects are sqlResult, sqlStatement.
    • sqlResultis used to traverse the data used.
    • sqlStatementis used to get the data column header information.

Define QueryNameGetInfo

ClassMethod Sql2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status
{
    s colinfo = $lb()
    s sqlStatement = qHandle.sqlStatement // comment1
    s count = 1
    s column = ""
    for {
        s column = $o(sqlStatement.%Metadata.columnIndex(column)) 
        q:(column = "")
        s data = sqlStatement.%Metadata.columnIndex(column)
        s $li(colinfo, count) = $lb($lg(data, 2)) // comment2
        s count = $i(count)
    }
    s parminfo = ""
    s idinfo = ""
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，get sqlStatement object by qHandle.
  • Comment 2，give colinfo list for cyclic assignment of column header information.

Define QueryNameFetch

ClassMethod Sql2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Sql2QueryExecute ]
{
    s sqlStatement = qHandle.sqlStatement // comment1
    s sqlResult =  qHandle.sqlResult 
    s colCount = sqlResult.%ResultColumnCount // comment2
    if (sqlResult.%Next()) {
        for i = 1 : 1 : colCount{
            s val = sqlResult.%GetData(i)
            if ( $g(row) = "" ) { // comment3
                s row = $lb(val)
            } else {
                s row = row _ $lb(val)
            }
        }
        s end = 0 
    } else {
       s row = ""
       s end = 1
    }
    s qHandle.sqlResult = sqlResult // comment4
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Comment 1，get sqlStatement, sqlResult object by qHandle.
  • Comment 2，get the number of columns, equivalent to get a row of data how many items.
  • Comment 3，iterate through the data to assign a value to row.
  • Comment 4，qHandle.sqlResult object, assign a value to the current object of the loop.

Define QueryNameClose

We can pass this.

Note:In fact, M has a related recycling mechanism, and it is ok not to delcare the Close method.

Call the Sql2Query method

USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select * from M_T.Person", 1)

id:MT_Age:MT_Name:MT_No:
1:21:yaoxin:314629:
2:29:yx:685381:
3:18:Umansky,Josephine Q.:419268:
4:27:Pape,Ted F.:241661:
5:25:Russell,Howard T.:873214:
6:30:Xenia,Ashley U.:420471:
7:24:Rotterman,Martin O.:578867:
8:18:Drabek,Hannah X.:662167:
9:19:Eno,Mark U.:913628:
...
100:24:Nathanson,Jocelyn A.:147578:
Enter fullscreen mode Exit fullscreen mode 
USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select ID,MT_Name from M_T.Person")

id:MT_Name:
1:yaoxin:
2:yx:
3:Umansky,Josephine Q.:
4:Pape,Ted F.:
5:Russell,Howard T.:
6:Xenia,Ashley U.:
7:Rotterman,Martin O.:
...
100:Nathanson,Jocelyn A.:
Enter fullscreen mode Exit fullscreen mode 
USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select top 10 ID as id from M_T.Person")

id:
1:
2:
3:
4:
5:
6:
7:
8:
9:
11:
Enter fullscreen mode Exit fullscreen mode

Call the Sql2Query method

Define QueryName

Query Query2Query(className As %String, queryName As %String, arg...) As %Query
{
}
Enter fullscreen mode Exit fullscreen mode
  • className - The class name.
  • queryName - The name of the Query method to be executed.
  • arg... - The parameters of the Query method to be executed.

Define QueryNameExecute

ClassMethod Query2QueryExecute(ByRef qHandle As %Binary, className As %String, queryName As %String, arg...) As %Status
{
    s sqlStatement = ##class(%SQL.Statement).%New()
    s sqlStatus = sqlStatement.%PrepareClassQuery(className, queryName)
    q:$$$ISERR(sqlStatus) sqlStatus
    s sqlResult = sqlStatement.%Execute() 
    s qHandle = {}
    s qHandle.sqlResult = sqlResult
    s qHandle.sqlStatement = sqlStatement
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Similar with Sql2Query

Define QueryNameGetInfo

ClassMethod Query2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status
{
    s colinfo = $lb()
    s sqlStatement = qHandle.sqlStatement
    s count = 1
    s column = ""
    for {
        s column = $o(sqlStatement.%Metadata.columnIndex(column)) 
        q:(column = "")
        s data = sqlStatement.%Metadata.columnIndex(column)
        s $li(colinfo, count) = $lb($lg(data, 2))
        s count = $i(count)
    }
    s parminfo = ""
    s idinfo = ""
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Similar with Sql2Query

Define QueryNameFetch

ClassMethod Query2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Query2QueryExecute ]
{
    s sqlStatement = qHandle.sqlStatement
    s sqlResult =  qHandle.sqlResult
    s colCount = sqlResult.%ResultColumnCount
    if (sqlResult.%Next()) {
        for i = 1 : 1 : colCount{
            s val = sqlResult.%GetData(i)
            if ( $g(row) = "" ) {
                s row = $lb(val)
            } else {
                s row = row _ $lb(val)
            }
        }
        s end = 0 
    } else {
       s row = ""
       s end = 1
    }
    s qHandle.sqlResult = sqlResult
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode
  • Similar with Sql2Query

Call Query2Query

USER>d ##class(%ResultSet).RunQuery("M.Query","Query2Query","M.Query","QueryPersonByName")

age:id:MT_Name:no:
1:21:yaoxin:314629:
2:29:yx:685381:
3:18:Umansky,Josephine Q.:419268:
4:27:Pape,Ted F.:241661:
5:25:Russell,Howard T.:873214:
6:30:Xenia,Ashley U.:420471:
7:24:Rotterman,Martin O.:578867:
8:18:Drabek,Hannah X.:662167:
9:19:Eno,Mark U.:913628:
11:18:Tsatsulin,Dan Z.:920134:
Enter fullscreen mode Exit fullscreen mode

Support traditional Query and generate Query columns by parameter

  • Support traditional Query and generate Query columns by parameter
  • Support defining columns by parameter form without specifying ROWSPEC parameters.
  • Optimization to make ^IRISTemp as ^||IRISTemp.

Define M.CommonQuery

Class M.CommonQuery Extends %Query
{

ClassMethod Close(ByRef qHandle As %Binary) As %Status [ CodeMode = generator, PlaceAfter = Execute, ProcedureBlock = 1, ServerOnly = 1 ]
{
    s %code($i(%code))= (" s pid = $li(qHandle, 2)")
    s %code($i(%code))= (" k ^||GlobalTemp(pid)")
    s %code($i(%code))= (" q $$$OK")
    q $$$OK
}

ClassMethod Fetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ CodeMode = generator, PlaceAfter = Execute, ProcedureBlock = 1, ServerOnly = 1 ]
{
    s %code($i(%code))= (" s end = $li(qHandle, 1)")
    s %code($i(%code))= (" s pid = $li(qHandle, 2)")
    s %code($i(%code))= (" s ind = $li(qHandle, 3)")
    s %code($i(%code))= (" s ind = $o(^||GlobalTemp(pid, ind))")
    s %code($i(%code))= (" if (ind = """") { ")
    s %code($i(%code))= ("  s end = 1")
    s %code($i(%code))= ("  s row = """"")
    s %code($i(%code))= (" } else { ")
    s %code($i(%code))= ("  s row = ^||GlobalTemp(pid, ind)")
    s %code($i(%code))= (" }")
    s %code($i(%code))= (" s qHandle = $lb(end, pid, ind)")
    s %code($i(%code))= (" q $$$OK")
    q $$$OK
}

ClassMethod GetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) As %Status [ CodeMode = generator, ServerOnly = 1 ]
{
    s %code($i(%code))= (" s colinfo = $lb()")
    s %code($i(%code))= (" s column = $lg(qHandle, 4)")
    s %code($i(%code))= (" if ($lv(column)) {")
    s %code($i(%code))= ("  for i = 1 : 1 : $ll(column) {")
    s %code($i(%code))= ("      s $li(colinfo, i) = $lb(""Column"" _ i )")
    s %code($i(%code))= ("  }   ")
    s %code($i(%code))= (" } else {")
    s %code($i(%code))= ("  s len = $l(column, "","")")
    s %code($i(%code))= ("  for i = 1 : 1 : len {")
    s %code($i(%code))= ("      s $li(colinfo, i) = $lb($p(column, "","", i))")
    s %code($i(%code))= ("  }")
    s %code($i(%code))= (" }")
    s %code($i(%code))= (" s parminfo = """"")
    s %code($i(%code))= (" s idinfo = """"")
    s %code($i(%code))= (" q $$$OK")
    q $$$OK
}

}
Enter fullscreen mode Exit fullscreen mode

Define QueryName

Query CustomColumnQuery(column As %List) As M.CommonQuery
{
}
Enter fullscreen mode Exit fullscreen mode
  • column - variable indicating the column of the parameter to be customized.
  • M.CommonQuery - custom Query type, no need to write GetInfo, Fetch, Close methods.

Define QueryNameExecute

QueryNameExecute supports three ways of defining column headers:

  1. The column header is passed in via the column parameter and is implemented as follows. 
ClassMethod CustomColumnQueryExecute(ByRef qHandle As %Binary, column As %List) As %Status
{
    s pid = $i(^||GlobalTemp)
    s qHandle = $lb(0, pid, 0)
    s $li(qHandle, 4) = column // Mode 1 This location is required
    s ind = 1

    s id = ""
    for {
        s id = $o(^M.T.PersonD(id))
        q:(id = "")
        s data = ^M.T.PersonD(id)
        s i = 1
        s name = $lg(data, $i(i))
        s age = $lg(data, $i(i))
        s no = $lg(data, $i(i))
        d output
    }   

    q $$$OK

output
    s data = $lb(id, name)
    s ^||GlobalTemp(pid, ind)=data  
    s ind = ind + 1
}
Enter fullscreen mode Exit fullscreen mode 
USER> d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery","ID,Name")

ID:Name:
1:yaoxin:
2:yx:
3:Umansky,Josephine Q.:
4:Pape,Ted F.:
5:Russell,Howard T.:
Enter fullscreen mode Exit fullscreen mode
  1. Without passing in the column parameter, the column headers are automatically generated based on the number of list data, implemented as follows. 
ClassMethod CustomColumnQueryExecute(ByRef qHandle As %Binary, column As %String = "") As %Status
{
    s pid = $i(^||GlobalTemp)
    s qHandle = $lb(0, pid, 0)
    s ind = 1
    s id = ""
    for {
        s id = $o(^M.T.PersonD(id))
        q:(id = "")
        s data = ^M.T.PersonD(id)
        s i = 1
        s name = $lg(data, $i(i))
        s age = $lg(data, $i(i))
        s no = $lg(data, $i(i))
        s data = $lb(id, name, no)
        q:(id > 5)
        d output
    }   
    s $li(qHandle, 4) = data // Mode 2 This location is required
    q $$$OK

output
    s ^||GlobalTemp(pid, ind)=data  
    s ind = ind + 1
}
Enter fullscreen mode Exit fullscreen mode 
USER>d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery")

Column1:Column2:Column3:
1:yaoxin:314629:
2:yx:685381:
3:Umansky,Josephine Q.:419268:
4:Pape,Ted F.:241661:
5:Russell,Howard T.:873214:
Enter fullscreen mode Exit fullscreen mode
  1. 3. Without passing in the column parameter, customize the column header information through the Execute method, implemented as follows. 
ClassMethod CustomColumnQueryExecute0(ByRef qHandle As %Binary, column As %String = "") As %Status
{
    s pid = $i(^||GlobalTemp)
    s qHandle = $lb(0, pid, 0)
    s ind = 1

    s id = ""
    for {
        s id = $o(^M.T.PersonD(id))
        q:(id = "")
        s data = ^M.T.PersonD(id)
        s i = 1
        s name = $lg(data, $i(i))
        s age = $lg(data, $i(i))
        s no = $lg(data, $i(i))
        s data = $lb(id, name, no)
        q:(id > 5)
        d output
    }    
    s $li(qHandle, 4) = "id,name,age" // Option 3 This position is required
    q $$$OK 

output
    s ^||GlobalTemp(pid, ind)=data  
    s ind = ind + 1
}
Enter fullscreen mode Exit fullscreen mode 
USER>d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery")

id:name:age:
1:yaoxin:314629:
2:yx:685381:
3:Umansky,Josephine Q.:419268:
4:Pape,Ted F.:241661:
5:Russell,Howard T.:873214:
Enter fullscreen mode Exit fullscreen mode

Run CustomColumnQuery

USER>d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery","ID,Name")

ID:Name:
1:yaoxin:
2:yx:
3:Umansky,Josephine Q.:
4:Pape,Ted F.:
5:Russell,Howard T.:
6:Xenia,Ashley U.:
Enter fullscreen mode Exit fullscreen mode

Define a general Query, you only need to implement the Execute method

To implement a general Query, you need to implement it by abstracting the methods and subclasses to override them. So first define the parent class.

Define M.CommonQuery

Class M.BaseQuery Extends %RegisteredObject
{

/// d ##class(%ResultSet).RunQuery("M.BaseQuery","CustomQuery","id,name")
Query CustomQuery(column As %List, arg...) As %Query
{
}

ClassMethod CustomQueryExecute(ByRef qHandle As %Binary, column As %List, arg...) As %Status
{
    s qHandle = $lb(0, 0) // comment1
    s $li(qHandle, 3) = column // comment2
    d ..QueryLogic(arg...) // comment3
    q $$$OK
}

ClassMethod CustomQueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) As %Status
{
    s colinfo = $lb()
    s column = $lg(qHandle ,3)
    s len = $l(column, ",") 
    for i = 1 : 1 : len {
        s $li(colinfo, i) = $lb($p(column, ",", i)) // comment5
    }
    s parminfo = ""
    s idinfo = ""
    q $$$OK
}

ClassMethod CustomQueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = CustomQueryExecute ]
{
    k %zQueryList // comment7
    q $$$OK
}

ClassMethod CustomQueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = CustomQueryExecute ]
{
    s end = $li(qHandle,1)
    s index = $li(qHandle,2)
    s index = $o(%zQueryList(index))
    if index = "" {  // comment6
        s end = 1
        s row = ""
    } else      { 
        s row = %zQueryList(index)
    }
    s qHandle = $lb(end, index)
    Quit $$$OK
}

ClassMethod QueryLogic(arg...) [ Abstract ]
{
    // comment4
}

}
Enter fullscreen mode Exit fullscreen mode
  • column - indicates the variable to customize the parameter column.
  • arg... - The parameters to be passed in.
  • Comment 1，here some changes are made, qHandle only records end and index, because here if it is a global variable or process private Global is only valid for the current process, so pid can be omitted.
  • Comment 2，the third position of qHandle will be passed into the column header name.
  • Comment 3，call the business logic method to be implemented, this method is an abstract method, need to subclass to implement.
  • Comment 4，call the business logic method to be implemented, this method is an abstract method, need to subclass to implement.
  • Comment 5，get the column dynamically set column header.
  • Comment 6，iterate through the global variables.
  • Comment 7，after the traversal, the global variables will be cleared.

Define subclass M.PersonQuery to inherit from M.BaseQuery to implement the QueryLogic method

  • All we need here is to assign a value to the %zQueryList($i(count)) global variable. The fixed template has been abstracted to the parent class. 
ClassMethod QueryLogic(arg...)
{
    s pName = arg(1)
    s id = ""
    for {
        s id = $o(^M.T.PersonD(id))
        q:(id = "")
        s data = ^M.T.PersonD(id)
        s i = 1
        s name = $lg(data, $i(i))
        continue:(pName '= "")&&(name '= pName)
        s age = $lg(data, $i(i))
        s no = $lg(data, $i(i))
        s %zQueryList($i(count)) = $lb(id, name, age)
    }
}
Enter fullscreen mode Exit fullscreen mode

Call the CustomQuery method

USER>d ##class(%ResultSet).RunQuery("M.PersonQuery","CustomQuery","ID,Name,Age", "yaoxin")

ID:Name:Age:
1:yaoxin:21:
Enter fullscreen mode Exit fullscreen mode

Note: Global variables are used here as data passing, so if the data is too large, memory leak may occur. Just change it to process private Global. It is up to the reader to implement it based on this logic.

Note: This way a class can only declare one Query, if you want to declare more than one Query for a class, consider switching to support the traditional Query approach.

Generate Json via Query 

ClassMethod Query2Json(className, queryName, arg...)
{
    s array = []
    s rs = ##class(%ResultSet).%New()
    s rs.ClassName = className
    s rs.QueryName = queryName
    d rs.Execute(arg...)

    s array = []
    #; 属性值
    while (rs.Next()) {
        s valStr = ""
        s obj = {}
        for i = 1 : 1 : rs.GetColumnCount(){
            s columnName = rs.GetColumnName(i)
            s val = rs.Data(columnName)
            d obj.%Set(columnName, val)
        }
        d array.%Push(obj)
    }

    q array.%ToJSON()
}
Enter fullscreen mode Exit fullscreen mode 
USER>w ##class(Util.JsonUtils).Query2Json("%SYSTEM.License","Summary")
[{"LicenseUnitUse":"当前使用的软件许可单元 ","Local":"1","Distributed":"1"},{"Li           censeUnitUse":"使用的最大软件许可单元数 ","Local":"15","Distributed":"15"},{"Lic            enseUnitUse":"授权的软件许可单元 ","Local":"300","Distributed":"300"},{"LicenseU         nitUse":"当前连接 ","Local":"3","Distributed":"3"},{"LicenseUnitUse":"最大连接数          ","Local":"17","Distributed":"17"}]
Enter fullscreen mode Exit fullscreen mode

Generate csv via Query 

ClassMethod Query2Csv(className, queryName, filePath, arg...)
{
    s file = ##class(%FileCharacterStream).%New()
    s file.Filename = filePath

    s array = []
    s rs = ##class(%ResultSet).%New()
    s rs.ClassName = className
    s rs.QueryName = queryName
    d rs.Execute(arg...)

    #; 列名
    s colStr = ""
    for i = 1 : 1 : rs.GetColumnCount(){
        s columnName = rs.GetColumnName(i)
        s colStr = $s(colStr = "" : columnName, 1 : colStr _ "," _ columnName)
    }
    d file.Write(colStr)

    #; 属性值
    while (rs.Next()) {
        s valStr = ""
        for i = 1 : 1 : rs.GetColumnCount(){
            s columnName = rs.GetColumnName(i)
            s val = rs.Data(columnName)
            s valStr = $s(valStr = "" : val, 1 : valStr _ "," _ val)    
        }
        d file.Write($c(10) _ valStr)
    }

    d file.%Save()
    q $$$OK
}
Enter fullscreen mode Exit fullscreen mode 
USER>w ##class(Util.FileUtils).Query2Csv("%SYSTEM.License","Summary","E:\m\CsvFile2.csv")
1
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

Summary

  • Understanding the qHandle parameter and the GetInfomethod is the key to implementing a generic Query.
  • Using a generic Query can improve development efficiency.
  • The use of a generic Query can solve the data adaptation problem.

Above are some of my personal understanding of Query-based. Due to my limited understanding, I welcome your comments and further communication.

If a good idea is banned from later use just because someone thought of it first, it will make the whole human society take many more detours, which is what the spirit of free software has always expressed.
                                                                                                                               - Richard Matthew Stallman

Top comments (0)