Data Sources

Specifying an SQL Query

A <tablePane> can provide a complete SQL statement as the value of its sql attribute. For example:

<tablePane id="table"
           sql="SELECT ID,Name FROM MyApp.Employees
                WHERE Name %STARTSWITH ? ORDER BY Name"
           >
     <parameter value="Z"/>
</tablePane>

The following table provides sql details.

The sql attribute is the XML projection of the %ZEN.Component.tablePaneOpens in a new tab property sql. Therefore, the sql attribute value must escape any XML special characters. For example, in place of the less-than symbol < you must substitute the XML entity &lt; as follows:

sql="select * from infonet_daten.abopos where lieferadresse=? and status<9"

The following table lists XML special characters that cause problems when they appear in sql strings, and the XML entities to substitute for them.

Unlike most other %ZEN.Component.tablePaneOpens in a new tab properties, you cannot set the sql property from the client at runtime. You can set it only from XData Contents. This is because sql is an encrypted attribute. The sql attribute value is encrypted (using the current session key) when it is sent to the client. If this value is returned to the server, it is automatically decrypted.

This prevents users from seeing the definition of an SQL statement if they view page source within their browser and prevents client logic from constructing arbitrary queries. For security reasons, query activities should always be restricted to the server.

Generating an SQL Query

The <tablePane> component supports attributes that allow you to automatically generate the query based on a simple description. This approach is similar to using a callback method, as described in a later topic, except that Zen generates the callback method for you, based on your description of the query. <dataListBox> and <dataCombo> also supports these attributes.

To have the <tablePane> generate an SQL query, you must do the following:

1. Provide a value for the tableName attribute.

2. Do not provide values for the sql, queryClass, queryName, or OnCreateResultSet. All of these attributes take precedence over the behavior described in this topic.

   After you satisfy the first two conditions, Zen assumes a "columns" value for dataSource.

3. Define the names of one or more columns by providing <column> elements inside the <tablePane>. You must define at least one column or Zen generates a “Missing SELECT list” error.

4. You can add query parameters by providing <parameter> elements inside the <tablePane>.

5. You can add clauses for the generated query by providing the <tablePane> attributes groupByClause, orderByClause, or whereClause, or by allowing defaults to prevail as described in the table above.

The following is a simple example:

<tablePane id="table"
    tableName="MyApp.Employee">
    <column colName="ID" hidden="true"/>
    <column colName="Name"/>
</tablePane>

This <tablePane> example generates an SQL statement similar to the following:

SELECT ID,Name FROM MyApp.Employee

Zen executes this query to provide the contents of the table. In the example, the ID column is marked as hidden. This means that its value is fetched (it can be used for conditions or actions) but not displayed. For details about hidden and other <column> attributes, see the section “Table Columns.”

Referencing a Class Query

A <tablePane> can reference a pre-existing class query to obtain a %ResultSetOpens in a new tab object. The following components also support this approach: <radioSet>, <select>, <dataListBox>, <dataCombo>, and <repeatingGroup>.

You may provide any input parameter values for the query by placing <parameter> elements inside the <tablePane>. For example:

<tablePane id="table"
           queryClass="MyApp.Employee"
           queryName="ListEmployees">
    <parameter value="Sales"/>
    <parameter value="NEW YORK"/>
</tablePane>

The value of the parameter in the <tablePane> is the value used to create the %ResultSetOpens in a new tab object. It overrides any default value set in the class query in all cases.

Using a Callback Method

A <tablePane> can use a callback method to obtain a %ResultSetOpens in a new tab object. The following <tablePane> attributes support this approach. <dataListBox>, <dataCombo>, and <altJSONSQLProvider> also support these attributes.

OnCreateResultSet

Name of a server-side callback method in the Zen page class. This method instantiates the %ResultSetOpens in a new tab object and set any %ZEN.Auxiliary.QueryInfoOpens in a new tab “QueryInfo Properties” appropriately. Zen invokes this method whenever it draws the table, automatically passing it the following parameters:

• %StatusOpens in a new tab — an output parameter for method status

• %ZEN.Auxiliary.QueryInfoOpens in a new tab — the QueryInfo object for the <tablePane>

The callback must return a %ResultSetOpens in a new tab object. The following is a valid method signature. Also see the detailed example following this table:

Method CreateRS(Output tSC As %Status,
     pInfo As %ZEN.Auxiliary.QueryInfo)
     As %ResultSet
{ }

To use the above method as the callback, the developer would set OnCreateResultSet="CreateRS" for the <tablePane>.

If defined, OnCreateResultSet takes precedence over any other techniques for providing data for a <tablePane>.

OnExecuteResultSet

Name of a server-side callback method in the Zen page class. This method executes the %ResultSetOpens in a new tab object returned by the OnCreateResultSet callback. Zen automatically invokes the OnExecuteResultSet callback after the OnCreateResultSet callback, passing it the following parameters:

• %ResultSetOpens in a new tab — the result set from OnCreateResultSet

• %StatusOpens in a new tab — an output parameter for method status

• %ZEN.Auxiliary.QueryInfoOpens in a new tab — the QueryInfo object for the <tablePane>

Optionally, you can suppress invocation of the OnExecuteResultSet callback by setting the QueryInfo queryExecuted property to true at the end of the OnCreateResultSet callback.

The OnExecuteResultSet callback must return a%ZEN.Datatype.booleanOpens in a new tab indicating whether or not the result set was executed. The following is a valid signature for this callback:

Method ExecuteRS(myRS As %ResultSet,
     Output pSC As %Status,
     pInfo As %ZEN.Auxiliary.QueryInfo)
     As %Boolean
{ }

To use the above method as the callback, the developer would set OnExecuteResultSet="ExecuteRS" for the <tablePane>.

Callback Example

The following is a detailed example of using a callback method to create a %ResultSetOpens in a new tab for a tablePane. The callback method constructs a dynamic SQL statement in response to current values of tablePane properties sortOrder and sortColumn. The tablePane automatically passes these values to the callback method as the corresponding properties of the input %ZEN.Auxiliary.QueryInfoOpens in a new tab object.

Also note how the method places the SQL statement text in the queryText property of QueryInfo before exiting. If the <tablePane> showQuery value is true, this table displays itself, plus the query that generated it.

Method CreateRS(Output tSC As %Status,
                pInfo As %ZEN.Auxiliary.QueryInfo) As %ResultSet
{
    Set tRS = ""
    Set tSC = $$$OK

    Set tSELECT = "ID,Name,Title"
    Set tFROM = "MyApp.Employee"
    Set tORDERBY = pInfo.sortColumn
    Set tSORTORDER = pInfo.sortOrder
    Set tWHERE = ""

    // Build WHERE clause based on filters
    If ($GET(pInfo.filters("Name"))'="") {
        Set tWHERE = tWHERE _ $SELECT(tWHERE="":"",1:" AND ") _
                "Name %STARTSWITH '" _ pInfo.filters("Name") _ "'"
    }
    If ($GET(pInfo.filters("Title"))'="") {
        Set tWHERE = tWHERE _ $SELECT(tWHERE="":"",1:" AND ") _
                "Title %STARTSWITH '" _ pInfo.filters("Title") _ "'"
    }

    Set sql = "SELECT " _ tSELECT _ " FROM " _ tFROM
    Set:tWHERE'="" sql = sql _ " WHERE " _tWHERE
    Set:tORDERBY'="" sql =
       sql _ " ORDER BY " _tORDERBY _ $SELECT(tSORTORDER="desc":" desc",1:"")

    Set tRS = ##class(%ResultSet).%New()
    Set tSC = tRS.Prepare(sql)
    Set pInfo.queryText = sql

    Quit tRS
}

Changing the Data Source Programmatically

You can change the data source for a <tablePane> at runtime in a variety of ways. The principle at work here is that the data source is on the server, so if you want to change the data source for a table based on user actions on the client side, you must work your way back to the server, as this topic shows.

In the following example, queryClass and queryName were used to define the data source, so we need to change them to new values on the server side. This example uses a JavaScript method in step 2 and a ZenMethod in step 3. Using an intervening JavaScript method as in step 2 is convenient if there are other actions you need to perform on the client side while changing the data source. Alternatively, the onclick from step 1 could invoke the ZenMethod directly, bypassing step 2:

1. There is a component on the Zen page whose response to onclick (or to some other user action) is to invoke a client side JavaScript method. For example:

   <button caption="Display Form" onclick="zenPage.setUpContextForm()" />
   
   

   

2. The client side JavaScript method invokes a server side ZenMethod that manipulates data source properties of the <tablePane>. For example:

    ClientMethod setUpContextForm() [ Language = javascript ]
   {
     this.SetQueryClassAndName("LTD.DomainModel.ContextList","GetAll")
     ctrl = this.getComponentById('ctrlList')
     ctrl.setModelClass('LTD.DomainModel.ContextList',this.getCurrentListId())
     zenSetProp('ContextId', 'hidden', 0)
     zenSetProp('ContextType', 'hidden',0)
   }

   

3. The server side ZenMethod gets the <tablePane> component and set its data source properties. For example:

   Method SetQueryClassAndName(queryClass As %String, 
                               queryName as %String) 
                               As %Status [ ZenMethod ]
   {
     Set obj=%page.%GetComponentById("listTable")
     Set obj.queryClass = queryClass
     Set obj.queryName = queryName
     Quit $$$OK
   }