|
IRISLIB database
|
|
|
IRISLIB database
|
|
Public Member Functions | |
| CloseCursor () | |
| OpenCursor () | |
 Public Member Functions inherited from RegisteredObject | |
| _.Library.Status | OnAddToSaveSet (_.Library.Integer depth, _.Library.Integer insert, _.Library.Integer callcount) |
| This callback method is invoked when the current object is added to the SaveSet,. More... | |
| _.Library.Status | OnClose () |
| This callback method is invoked by the <METHOD>Close</METHOD> method to. More... | |
| _.Library.Status | OnConstructClone (_.Library.RegisteredObject object, _.Library.Boolean deep, _.Library.String cloned) |
| This callback method is invoked by the <METHOD>ConstructClone</METHOD> method to. More... | |
| _.Library.Status | OnNew () |
| This callback method is invoked by the <METHOD>New</METHOD> method to. More... | |
| _.Library.Status | OnValidateObject () |
| This callback method is invoked by the <METHOD>ValidateObject</METHOD> method to. More... | |
Static Public Attributes | |
| SQLNAME = None | |
| Override this parameter to define the SQLNAME of the query procedure. More... | |
| Static Public Attributes inherited from IResultSet | |
| StatementIndexHash | |
| Hash of associated statement index entry. | |
| Static Public Attributes inherited from RegisteredObject | |
| CAPTION = None | |
| Optional name used by the Form Wizard for a class when generating forms. More... | |
| JAVATYPE = None | |
| The Java type to be used when exported. | |
| PROPERTYVALIDATION = None | |
| This parameter controls the default validation behavior for the object. More... | |
Private Member Functions | |
| _.Library.Integer | FetchCursor () |
| _.Library.String | Get (_.Library.String colname) |
| Returns the value of the column with the name colname in the current row of the result set. More... | |
| _.Library.String | GetData (_.Library.Integer colnbr) |
| GetData(<column number>) More... | |
| _.Library.Integer | SendODBC () |
| Fetch and send a series of rows for the ODBC/JDBC server. More... | |
Static Private Member Functions | |
| _.Library.Status | GetSerializedMetadata (_.Library.RawString pMetadata) |
| Get the serialized Metadata property value. | |
SQL.CustomQuery is the root class for custom query function classes. You can extend this class to implement SQL Table-Valued Functions that can be used in SQL statements. Custom query functions are similar to queries with TYPE = Library.Query. Subclassing SQL.CustomQuery has a few advantages over custom class queries. The result set returned by executing, either directly or as a function in an SQL statement, is more efficient when interacting with the server. The metadata for a result set is constructed from the class definition so there is never a need to explicitly define a ROWSPEC.
When subclassing SQL.CustomQuery, there are a few steps that you must follow in order to produce a working table-valued function.
The following class definition is an example of a simple custom query. This example accepts a JSON array and presents the name and city properties from each element of the array as a row.
Class User.TVF Extends SQL.CustomQuery
{
Parameter SQLNAME = "custom_tvf";
Property name As String;
Property city As String;
Property sourceData As Library.DynamicArray [ Private ];
Property iterator As Iterator.Array [ Private ];
Method OpenCursor(sourceData As String(MAXLEN="")) [ Private ]
{
if ('$isObject(sourceData)) || ('sourceData.IsA("%Library.DynamicAbstractObject")) {
set ..sourceData = ##class(Library.DynamicAbstractObject).FromJSON(sourceData)
} else {
set ..sourceData = sourceData
}
set ..iterator = ..sourceData.GetIterator()
}
Method CloseCursor() [ PlaceAfter = Next, Private ]
{
set ..iterator = ""
set ..sourceData = ""
}
Method FetchCursor(ByRef sc As Library.Status = {$$$OK}) As Library.Integer
{
if ($isObject(..iterator)) && (..iterator.GetNext(.key,.value)) {
set ..name = value.name
set ..city = value.city
} else {
set ..iterator = ""
return 0
}
return 1
}
}
The above example can be referenced in the from clause of a SELECT statement.
select * from custom_tvf('[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}]') order by city
name
city
Orr, Bobby
Boston, MA
Hermann, George
New York, NY
2 Rows(s) Affected
A custom query function can also be instantiated directly and iterated over using the result set interface. The first argument passed to New() is SELECTMODE, as is defined by the <class>SQL.IResultSet</class> interface. Subsequent arguments are as defined by the <method>OpenCursor</method>.
USER>set cursor = ##class(TVF).New(,[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}])
USER>write cursor.Next()
1
USER>write cursor.name
Hermann, George
USER>write cursor.city
New York, NY
USER>write cursor.SQLCODE
0
USER>write cursor.Next()
1
USER>write cursor.name
Orr, Bobby
USER>write cursor.Next()
0
USER>write cursor.SQLCODE
100
| CloseCursor | ( | ) |
Implement CloseCursor to clean up any temporary structures that are used by the custom query such as temporary globals, etc. This method is invoked by the object destructor.
method CloseCursor() [ private ]
{
&sql(close myCursor)
kill ^||mytempglobal
quit $$$OK
}
|
private |
Advance the cursor to the next row. If the cursor is found to be at the end of the data then return 0, otherwise populate the row properties and return 1.
To report an error set <property>SQLCODE</property> and <property>Message</property> to appropriate values. It is also acceptable to simply throw an exception and the system will assign appropriate values to <property>SQLCODE</property> and <property>Message</property>.
|
private |
Returns the value of the column with the name colname in the current row of the result set.
If colname is not a valid column name, this method throws a <PROPERTY DOES NOT EXIST> error.
Reimplemented from StatementResult.
|
private |
GetData(<column number>)
Returns the value of the column referenced by <var>colnbr</var>. Object values are not swizzled automatically.
Reimplemented from StatementResult.
| OpenCursor | ( | ) |
Implement code to open the cursor here. You may also define new formal arguments. Any arguments defined will be automatically added to the table-valued function project to SQL and to the constructor interface. Callers can specify actual arguments in the call to New() when invoking the function from an SQL statement.
method OpenCursor(pBeginDate as Date = "", pEndDate as Date = "") as Library.Status [ private ]
{
set ..BeginDate = pBeginDate
set ..EndDate = $Select(pEndDate'="":pEndDate,1:$H)
}
Given the above example implementation of OpenCursor, the following is a valid call to instantiate a new instance.
set tResult = ##class(MyCustom.Query).New(,$H-30,$H-10)
To report an error set <property>SQLCODE</property> and <property>Message</property> to appropriate values. To report no rows found, set <property>SQLCODE</property> to 100. Errors are indicated by negative ..SQLCODE values. It is also acceptable to simply throw an exception and the system will assign appropriate values to <property>SQLCODE</property> and <property>Message</property>.
|
private |
Fetch and send a series of rows for the ODBC/JDBC server.
For internal use only.
|
static |
Override this parameter to define the SQLNAME of the query procedure.
SQL.CustomQuery is the root class for custom query function classes. You can extend this class to implement SQL Table-Valued Functions that can be used in SQL statements. Custom query functions are similar to queries with TYPE = Library.Query. Subclassing SQL.CustomQuery has a few advantages over custom class queries. The result set returned by executing, either directly or as a function in an SQL statement, is more efficient when interacting with the server. The metadata for a result set is constructed from the class definition so there is never a need to explicitly define a ROWSPEC.
When subclassing SQL.CustomQuery, there are a few steps that you must follow in order to produce a working table-valued function.
The following class definition is an example of a simple custom query. This example accepts a JSON array and presents the name and city properties from each element of the array as a row.
Class User.TVF Extends SQL.CustomQuery
{
Parameter SQLNAME = "custom_tvf";
Property name As String;
Property city As String;
Property sourceData As Library.DynamicArray [ Private ];
Property iterator As Iterator.Array [ Private ];
Method OpenCursor(sourceData As String(MAXLEN="")) [ Private ]
{
if ('$isObject(sourceData)) || ('sourceData.IsA("%Library.DynamicAbstractObject")) {
set ..sourceData = ##class(Library.DynamicAbstractObject).FromJSON(sourceData)
} else {
set ..sourceData = sourceData
}
set ..iterator = ..sourceData.GetIterator()
}
Method CloseCursor() [ PlaceAfter = Next, Private ]
{
set ..iterator = ""
set ..sourceData = ""
}
Method FetchCursor(ByRef sc As Library.Status = {$$$OK}) As Library.Integer
{
if ($isObject(..iterator)) && (..iterator.GetNext(.key,.value)) {
set ..name = value.name
set ..city = value.city
} else {
set ..iterator = ""
return 0
}
return 1
}
}
The above example can be referenced in the from clause of a SELECT statement.
select * from custom_tvf('[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}]') order by city
name
city
Orr, Bobby
Boston, MA
Hermann, George
New York, NY
2 Rows(s) Affected
A custom query function can also be instantiated directly and iterated over using the result set interface. The first argument passed to New() is SELECTMODE, as is defined by the <class>SQL.IResultSet</class> interface. Subsequent arguments are as defined by the <method>OpenCursor</method>.
USER>set cursor = ##class(TVF).New(,[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}])
USER>write cursor.Next()
1
USER>write cursor.name
Hermann, George
USER>write cursor.city
New York, NY
USER>write cursor.SQLCODE
0
USER>write cursor.Next()
1
USER>write cursor.name
Orr, Bobby
USER>write cursor.Next()
0
USER>write cursor.SQLCODE
100
If this value is not
specified then a default procedure name will be generated using the standard procedure naming convention.
1.9.1