Skip to main content

%ZEN.Auxiliary.jsonSQLProvider

deprecated class %ZEN.Auxiliary.jsonSQLProvider 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.
For example, placing this within your page definition: 
<jsonSQLProvider 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.

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.jsonSQLProvider = "") 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 a string of flags that control output formatting options.
The following character option codes are supported:
1-9 : indent with this number of spaces (4 is the default with the 'i' format specifier)
i - indent with 4 spaces unless 't' or 1-9
n - newline (lf)
s - use strict JSON output - NOTE: special care should be taken when sending data to a browser, as using this flag may expose you to cross site scripting (XSS) vulnerabilities if the data is sent inside <script> tags. Zen uses this technique extensively, so this flag should NOT be specified for jsonProviders in Zen pages.
t - indent with tab character
u - output pre-converted to UTF-8 instead of in native internal format
w - Windows-style cr/lf newline
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.jsonSQLProvider = "") 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 a string of flags that control output formatting options.
The following character option codes are supported:
1-9 : indent with this number of spaces (4 is the default with the 'i' format specifier)
i - indent with 4 spaces unless 't' or 1-9
n - newline (lf)
s - use strict JSON output - NOTE: special care should be taken when sending data to a browser, as using this flag may expose you to cross site scripting (XSS) vulnerabilities if the data is sent inside <script> tags. Zen uses this technique extensively, so this flag should NOT be specified for jsonProviders in Zen pages.
t - indent with tab character
u - output pre-converted to UTF-8 instead of in native internal format
w - Windows-style cr/lf newline
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 Members

Inherited Properties

Inherited Methods

FeedbackOpens in a new tab