Home > Class Reference > %SYS namespace > %ZEN.Auxiliary.altJSONSQLProvider

%ZEN.Auxiliary.altJSONSQLProvider

class %ZEN.Auxiliary.altJSONSQLProvider extends %ZEN.Auxiliary.jsonProvider, %ZEN.Component.querySource

A version of the JSON provider that can use an SQL statement to supply data that is served to the client in JSON format. This can be used in the same manner as the jsonProvider component. The key difference between this widget and the legacy jsonProvider or jsonSQLProvider is that this widget uses abstract entities (%DynamicObject and %DynamicArray) internally for faster, in memory manipulation of the results set and JSON rendering.
For example, placing this within your page definition:
<altJSONSQLProvider id="json" sql="SELECT TOP 2 ID, Name FROM Sample.Person" />
would cause the given SQL statement to execute when the page is requested and the results of the query to be written to the page in JSON format:
o.content = {
	"children": [
		{
			"ID": 1,
			"Name": "Schaefer,Thelma W."
		}
		{
			"ID": 2,
			"Name": "Huff,Kevin T."
		}
	]
};
The JSON content object will contain a children property which is an array of objects that correspond to the rows returned by the SQL statement. The property names match the column names within the query.
If you need more control over the SQL query, you can use the OnGetSQL callback method.
This component ignores all of the data source callback methods inherited from the jsonProvider class. as well as the whitespace pretty printing options previously supported by jsonProvider

Property Inventory

Method Inventory

Properties

property OnGetSQL as %ZEN.Datatype.delegator (FORMALSPEC = "&pParameters:%String,&pSQL:%String,pCriteria:%ZEN.proxyObject,&pPagingInfo:%String", RETURNTYPE = "%Status");
This specifies a callback method that returns an SQL query (string) that will drive this provider. This is identical in behavior to (and replaces) the sql property. The method can make it easier to create queries based on parameter values or search criteria passed via the criteria property.
Property methods: OnGetSQLDisplayToLogical(), OnGetSQLGet(), OnGetSQLIsValid(), OnGetSQLLogicalToDisplay(), OnGetSQLLogicalToOdbc(), OnGetSQLNormalize(), OnGetSQLSet()
property arrayName as %String [ InitialExpression = "children" ];
Property methods: arrayNameDisplayToLogical(), arrayNameGet(), arrayNameIsValid(), arrayNameLogicalToDisplay(), arrayNameLogicalToOdbc(), arrayNameNormalize(), arrayNameSet()
property currPage as %ZEN.Datatype.integer [ InitialExpression = 1 ];
If the provider is using server-side data paging, this is the number (1-based) of the current page.
Property methods: currPageDisplayToLogical(), currPageGet(), currPageIsValid(), currPageLogicalToDisplay(), currPageLogicalToOdbc(), currPageNormalize(), currPageSet()
property pageSize as %ZEN.Datatype.integer [ InitialExpression = 25 ];
If the provider is using server-side data paging, this is the number of records in each page.
Property methods: pageSizeDisplayToLogical(), pageSizeGet(), pageSizeIsValid(), pageSizeLogicalToDisplay(), pageSizeLogicalToOdbc(), pageSizeNormalize(), pageSizeSet()
property recordCount as %ZEN.Datatype.integer;
If the provider is using server-side data paging, this is the total number of records.
Property methods: recordCountDisplayToLogical(), recordCountGet(), recordCountIsValid(), recordCountLogicalToDisplay(), recordCountLogicalToOdbc(), recordCountNormalize(), recordCountSet()

Methods

classmethod %WriteJSONFromSQL(pVar As %String = "", pSQL As %String, pFormat As %String = "tw", pMaxRows As %Integer = 100, pReturnStatus As %Boolean = 0, tProvider As %ZEN.Auxiliary.altJSONSQLProvider = "") as %String
Utility method to allow direct use of JSON from a non-ZEN context (such as a CSP page).
The JSON notation is written out the current device. pVar is the optional name of the client-side javascript variable that refers to the JSON notation.
pSQL is the SQL statement that is executed and provides the JSON content.
From a CSP page, you could invoke the method as follows:
#(##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONFromSQL("json","SELECT Name FROM Sample.Person"))#
pFormat is provided for signature compatibility with the legacy jsonProvider class, but using it to pretty-print the output stream is not supported by this class.
pMaxRows controls the maximum number of rows to return and follows the semantics for the maxRows property where 0 indicates all rows should be returned. tProvider If you wish to use a class query or pass in parameters using ? then create and populate an instance of the jsonSQLProvider and pass it in.
classmethod %WriteJSONStreamFromSQL(ByRef pStream As %Stream.Object, pSQL As %String, pFormat As %String = "tw", pMaxRows As %Integer = 100, pRewindStream As %Boolean = 0, tProvider As %ZEN.Auxiliary.altJSONSQLProvider) as %Status
Utility method to allow JSON output generated by the SQL query defined in pSQL to be written to the stream supplied in pStream from a general non-ZEN context. Note that pStream will be created if not supplied, hence the ByRef designation.
pRewindStream controls whether the stream should be rewound after the data is written to it.
pFormat is provided for signature compatibility with the legacy jsonProvider class, but using it to pretty-print the output stream is not supported by this class.
pMaxRows controls the maximum number of rows to return and follows the semantics for the maxRows property where 0 indicates all rows should be returned. tProvider If you wish to use a class query or pass in parameters using ? then create and populate an instance of the jsonSQLProvider and pass it in.
clientmethod getCurrPage() [ Language = javascript ]
If this provider is using server-side paging, this is the current page number (1-based).
clientmethod getPageSize() [ Language = javascript ]
If this provider is using server-side paging, this is the number of records in one page.
clientmethod getRecordCount() [ Language = javascript ]
If this provider is using server-side paging, this is the total number of records in the current query.
clientmethod setCurrPage(page) [ Language = javascript ]
If this provider is using server-side paging, set the current page number (1-based).

Inherited Methods