%ZEN.Auxiliary.jsonSQLProvider
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" />
o.content = { "children": [ { "ID": 1, "Name": "Schaefer,Thelma W." } { "ID": 2, "Name": "Huff,Kevin T." } ] };
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
- %WriteJSONFromSQL()
- %WriteJSONStreamFromSQL()
- getCurrPage()
- getPageSize()
- getRecordCount()
- setCurrPage()
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:
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
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.
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"))#
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
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.
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
- %Format
- %condition
- %import
- %includeFiles
- %page
- %resource
- OnCreateResultSet
- OnExecuteResultSet
- OnGetArray
- OnGetTargetObject
- OnRenderJSON
- OnSubmitContent
- alertOnError
- align
- autoRefresh
- aux
- columnName
- composite
- containerStyle
- countRows
- defaultSeries
- documentId
- dragEnabled
- dropEnabled
- enclosingClass
- enclosingStyle
- error
- groupByClause
- height
- hidden
- hint
- hintClass
- hintStyle
- id
- index
- label
- labelClass
- labelDisabledClass
- labelStyle
- maxRows
- modelError
- modelId
- name
- onafterdrag
- onbeforedrag
- oncreate
- ondelete
- ondrag
- ondrop
- onerror
- onhide
- onnotifyController
- onrefresh
- onsave
- onshow
- onupdate
- orderByClause
- parameters
- parent
- propertyList
- queryClass
- queryName
- readOnly
- seriesNameProperty
- showLabel
- slice
- sql
- tableName
- targetClass
- title
- tuple
- valign
- visible
- whereClause
- width
- window
Inherited Methods
- %AddToSaveSet()
- %ArrayToJSON()
- %Attr()
- %BindExport()
- %ClassIsLatestVersion()
- %ClassName()
- %ConstructClone()
- %ConvertJSONToObject()
- %CreateResultSet()
- %DeleteModelInstance()
- %DispatchClassMethod()
- %DispatchGetModified()
- %DispatchGetProperty()
- %DispatchMethod()
- %DispatchSetModified()
- %DispatchSetMultidimProperty()
- %DispatchSetProperty()
- %DrawComponentHTML()
- %DrawHTML()
- %DrawJSStrings()
- %EnclosingDivId()
- %Eval()
- %EvalC()
- %Extends()
- %ForceClientRender()
- %GetDataByName()
- %GetEventHandlers()
- %GetModelPropertyList()
- %GetParameter()
- %GetTypeByName()
- %GetXMLName()
- %IsA()
- %IsModified()
- %MakeId()
- %New()
- %NormalizeObject()
- %ObjectModified()
- %ObjectToJSON()
- %OnAddToPageAfter()
- %OnAddToPageBefore()
- %OnCreateResultSet()
- %OnDrawEnclosingDiv()
- %OnExecuteResultSet()
- %OnZENDeserialize()
- %OriginalNamespace()
- %PackageName()
- %QuoteValue()
- %QuoteValueL10N()
- %RemoveFromSaveSet()
- %Self()
- %SerializeObject()
- %SetDataByName()
- %SetModified()
- %SetTargetObject()
- %ValidateObject()
- %WriteJSONFromArray()
- %WriteJSONFromObject()
- %WriteJSONStreamFromArray()
- %WriteJSONStreamFromObject()
- XMLDTD()
- XMLExport()
- XMLExportToStream()
- XMLExportToString()
- XMLNew()
- XMLSchema()
- XMLSchemaNamespace()
- XMLSchemaType()
- createNewObject()
- deleteId()
- dragFinishHandler()
- dragHandler()
- dragNotifyHandler()
- dragStartHandler()
- dropHandler()
- dropStartHandler()
- exposeComponent()
- findElement()
- fireOnUpdateEvent()
- getContentObject()
- getContentType()
- getData()
- getDataAsArrays()
- getDataAsObject()
- getDataSourceCaption()
- getDimSize()
- getEnclosingDiv()
- getError()
- getHidden()
- getHintElement()
- getLabel()
- getLabelElement()
- getModelId()
- getProperty()
- getPropertyName()
- getSettings()
- getType()
- getTypeByName()
- hasData()
- invokeAction()
- invokeSuper()
- isModelReadOnly()
- isOfType()
- isPropertyValid()
- makeId()
- onCreate()
- onDelete()
- onDisplayHandler()
- onEndModalHandler()
- onPopupAction()
- onRefreshContents()
- onSerialize()
- onStartModalHandler()
- onloadHandler()
- onunloadHandler()
- onupdateHandler()
- raiseDataChange()
- refreshContent()
- refreshContents()
- reloadContents()
- reloadContentsAsynch()
- render()
- renderContents()
- renderSVG()
- save()
- sendEventToViews()
- setContentObject()
- setContentText()
- setContentType()
- setHidden()
- setModelId()
- setOverlayMode()
- startProgressBar()
- stopProgressBar()
- submitContent()
- update()