Tutorial for Middle/Senior Level Developer: General Query Solution
Tags: tutorial, programming, sql
Published: 2023-04-25 17:31:43



# <center>Brief</center>

### 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
}
```

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"
```
  
  - `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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9kg5kl0xbex2ymcu5ads.png)



- Undeclared call method - `d ##class(%ResultSet).RunQuery(className, queryName, arg...)`

```java
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:
```

### 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**

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

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

---

**QueryNameExecute Definition**

```java
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
}
```

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

```java
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
}
```

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

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

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

```java
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:
```

- The query here is for all people who are older than 20 years old and whose id is less than 10.

# <center>Challenges</center>

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.

# <center>Solution</center>

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

```java
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
}
```

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：

```java
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
}
```

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

```json
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
}
```

Run：

```json
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:\\"}]
```
---

**Define QueryName**

```java
Query Json2Query(className As %String, methodName As %String, arg...) As %Query
{
}
```

其中：

- `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**

```java
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
}
```

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

```java
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
}
```

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

```java
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
}
```

- 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
}
```

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

```java
USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","0","D")
 
type:drive:
D:D::
D:E::
D:F::
D:G::

```

```java
USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","1","D")
 
type:drive:
D:D:\:
D:E:\:
D:F:\:
D:G:\:
```
---
## Dynamically generate Query by Select Sql statement

---

**Define QueryName**

```java
Query Sql2Query(sql As %String, mode As %String = 1) As %Query
{
}
```

- `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**

```java
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
}
```

- 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`.
  - `sqlResult`is used to traverse the data used.
  - `sqlStatement`is used to get the data column header information.

---

**Define QueryNameGetInfo**

```java
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
}
```

- Comment 1，get `sqlStatement` object by `qHandle`.
- Comment 2，give `colinfo` list for cyclic assignment of column header information.

---

**Define QueryNameFetch**

```java
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
}
```

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

```java
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:
```

```java
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.:
```

```java
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:
```



## Call the Sql2Query method

---

**Define QueryName**

```java
Query Query2Query(className As %String, queryName As %String, arg...) As %Query
{
}
```

- `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**

```java
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
}
```

- Similar with `Sql2Query`

---

**Define QueryNameGetInfo**

```java
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
}
```

- Similar with `Sql2Query`

---

**Define QueryNameFetch**

```java
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
}
```

- Similar with `Sql2Query`

---

**Call `Query2Query`**

```java
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:
```

---
## 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**

```java
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
}

}


```

---

**Define QueryName**

```java
Query CustomColumnQuery(column As %List) As M.CommonQuery
{
}
```

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

```java
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
}
```

```java
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.:
```
2. Without passing in the `column` parameter, the column headers are automatically generated based on the number of list data, implemented as follows.

```java
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
}
```

```java
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:
```

3. **3.** Without passing in the `column` parameter, customize the column header information through the `Execute` method, implemented as follows.

```java
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
}
```

```java
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:
```

---
**Run `CustomColumnQuery`**

```java
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.:
```

---
## 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**

```java
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
}

}

```

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

```java
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)
	}
}
```

---

**Call the CustomQuery method**

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

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

```java
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()
}

```

```java
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"}]
```

---
## Generate csv via Query

```java
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
}
```

```java
USER>w ##class(Util.FileUtils).Query2Csv("%SYSTEM.License","Summary","E:\m\CsvFile2.csv")
1
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vpxdnq6dmd2rnun3up5n.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mkzvbu8itqllbdfhgbxg.png)


# <center>Summary</center>

- Understanding the `qHandle` parameter and the `GetInfo `method 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.
> 	   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\- Richard Matthew Stallman