New post
AllAccepted answers
Timothy Leavitt · Jan 12, 2021

First off, you should strongly consider using a production webserver, not the built-in one. On configuring the CSPGateway for this, see https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

You might also need to investigate CORS settings - https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=GREST_... is helpful reading on this.

Timothy Leavitt · Jan 11, 2021

I was going through and looking at my old questions without accepted answers - this is the right answer but a SAMPLES namespace is harder to come by these days, so posting the actual code for my own future reference:

ClassMethod PersonSets(name As %String = "", state As %String = "MA") As %Integer [ ReturnResultsets, SqlName = PersonSets, SqlProc ]
{
        // %sqlcontext is automatically created for a method that defines SQLPROC

        // SQL result set classes can be easily prepared using dynamic SQL. %Prepare returns a
        // status value. The statement's prepare() method can also be called directly. prepare() throws
        // an exception if something goes wrong instead of returning a status value.
    set tStatement = ##class(%SQL.Statement).%New()
    try {
        do tStatement.prepare("select name,dob,spouse from sample.person where name %STARTSWITH ? order by 1")
        set tResult = tStatement.%Execute(name)
        do %sqlcontext.AddResultSet(tResult)
        do tStatement.prepare("select name,age,home_city,home_state from sample.person where home_state = ? order by 4, 1")
        set tResult = tStatement.%Execute(state)
        do %sqlcontext.AddResultSet(tResult)
        set tReturn = 1
    }
    catch tException {
        #dim tException as %Exception.AbstractException
        set %sqlcontext.%SQLCODE = tException.AsSQLCODE(), %sqlcontext.%Message = tException.SQLMessageString()
        set tReturn = 0
    }
    quit tReturn
}
Timothy Leavitt · Jan 8, 2021

Here's an updated example for that. I've also updated the example to use %SQL.Statement, which is newer and better than %ResultSet; I didn't realize it would work with OnCreateResultSet initially.

Class DC.Demo.ZenPage Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<fieldSet legend="Filter" layout="horizontal">
<text label="Name Starts With:" onchange="zen('myTable').setProperty('parameters',1,zenThis.getValue())" />
<dateText label="Start Date:" onchange="zen('myTable').setProperty('parameters',2,zenThis.getValue())" />
<dateText label="End Date:" onchange="zen('myTable').setProperty('parameters',3,zenThis.getValue())" />
</fieldSet>
<tablePane id="myTable" OnCreateResultSet="CreateResultSet">
<parameter value="" />
<parameter value="" />
<parameter value="" />
</tablePane>
<button onclick="zenPage.Populate()" caption="Repopulate Data" />
</page>
}

ClassMethod CreateResultSet(Output pSC As %Status, pInfo As %ZEN.Auxiliary.QueryInfo) As %SQL.Statement
{
    Set nameFilter = pInfo.parms(1)
    Set startDateFilter = pInfo.parms(2) // Will be in ODBC format
    Set endDateFilter = pInfo.parms(3) // Will be in ODBC format
    Set query = ##class(%SQL.Statement).%New()
    Set query.%SelectMode = 1
    Set sql = "select Name, SomeDate from DC_Demo.SampleData"
    Set conditions = ""
    If (nameFilter '= "") {
        Set conditions = conditions_$ListBuild("Name %STARTSWITH ?")
        Set parameters($i(parameters)) = nameFilter
    }
    If (startDateFilter '= "") && (endDateFilter '= "") {
        // Yes, this could just be independent AND'ed conditions on start/end date,
        // which would reduce code complexity, but you wanted to see BETWEEN, so... :)
        Set conditions = conditions_$ListBuild("SomeDate BETWEEN ? and ?")
        Set parameters($i(parameters)) = startDateFilter
        Set parameters($i(parameters)) = endDateFilter
    } ElseIf (startDateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate >= ?")
        Set parameters($i(parameters)) = startDateFilter
    } ElseIf (endDateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate <= ?")
        Set parameters($i(parameters)) = endDateFilter
    }
    If (conditions '= "") {
        Set sql = sql _ " where "_$ListToString(conditions," and ")
    }
    Set pSC = query.%Prepare(sql)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    
    //Important: Reduce to only the parameters specified/used.
    Kill pInfo.parms
    Merge pInfo.parms = parameters
    Quit query
}

ClassMethod Populate() [ ZenMethod ]
{
    Do ##class(DC.Demo.SampleData).%KillExtent()
    Do ##class(DC.Demo.SampleData).Populate(20,,,,0)
    &js<zen('myTable').executeQuery();>
}

}

Note - I got something slightly wrong in the original example. The responsibility of OnCreateResultSet is just to create the ResultSet, not to execute the query. Most importantly, the parameters in the QueryInfo object need to be reset to just the subset used in our method of generating the query; otherwise, the original parms array would be used to execute the query, which could produce incorrect results (but didn't happen to in the simpler example). An alternative approach would be adding a server-side callback for ExecuteResultSet as well, but in this case it's simpler to just manipulate the parms array.

Timothy Leavitt · Jan 8, 2021

Also, @ED Coder , it's perhaps worth stepping back and asking the context of your work. If you're looking to build a brand new application of any real complexity, Zen probably isn't the best approach these days. I really enjoy Zen and have spent many years working with it, but starting on a new project I'd probably look toward a popular client-side framework like Angular or React and using REST APIs to interact with the database (probably using https://openexchange.intersystems.com/package/apps-rest to match the rapid development features of Zen in a REST API context).

If you're jumping into a large, existing Zen application and trying to make sense of the technology, that's a lot more understandable. Just curious. smiley

Timothy Leavitt · Jan 8, 2021

A ZenMethod is written in ObjectScript, not JavaScript, so that's why getValue() doesn't work. You can only access page components in a ClassMethod if you pass them in as arguments; otherwise, you need to make the method an instance method (that is, just Method readValues). Then you can use:

Set clinic = ..%GetComponentById('clinic').value

If you do need to do things in JavaScript in a ZenMethod, you can do them in an &js block - e.g.:

&js<alert(#(..QuoteJS(clinic))#);>

Which, in combination with the line above, would safely quote the string "clinic" for use in JavaScript (e.g., escaping quotes within the string), and this JavaScript will run on the client, after the method returns. (That is, it isn't immediate; if you had a "hang 5" command after the &js block you wouldn't see an alert on the client until after the method ends.)

Instance methods in Zen are expensive because the whole page needs to be serialized and sent to the server (so that you can access and potentially modify all the components on the page). However, your method signature suggests that it's expecting a Zen proxyObject. You could build a Zen proxyObject with all of the form field values you care about in a JavaScript method on the client, and send that to the server by passing it to your ClassMethod, and that would be more efficient (if all you want to do is retrieve data from the form).

Timothy Leavitt · Jan 7, 2021

I generally don't use OnCreateResultSet, but here's a sample with it:

Class DC.Demo.ZenPage Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<fieldSet legend="Filter" layout="horizontal">
<text label="Name Starts With:" onchange="zen('myTable').setProperty('parameters',1,zenThis.getValue())" />
<dateText label="Date:" onchange="zen('myTable').setProperty('parameters',2,zenThis.getValue())" />
<button onclick="zen('myTable').executeQuery()" caption="Filter" />
</fieldSet>
<tablePane id="myTable" OnCreateResultSet="CreateResultSet">
<parameter value="" />
<parameter value="" />
</tablePane>
<button onclick="zenPage.Populate()" caption="Repopulate Data" />
</page>
}

ClassMethod CreateResultSet(Output pSC As %Status, pInfo As %ZEN.Auxiliary.QueryInfo) As %ResultSet
{
    Set nameFilter = pInfo.parms(1)
    Set dateFilter = pInfo.parms(2) // Will be in ODBC format
    Set query = ##class(%ResultSet).%New()
    Set query.RuntimeMode = 1 // ODBC
    Set sql = "select Name, SomeDate from DC_Demo.SampleData"
    Set conditions = ""
    If (nameFilter '= "") {
        Set conditions = conditions_$ListBuild("Name %STARTSWITH ?")
        Set parameters($i(parameters)) = nameFilter
    }
    If (dateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate = ?")
        Set parameters($i(parameters)) = dateFilter
    }
    If (conditions '= "") {
        Set sql = sql _ " where "_$ListToString(conditions," and ")
    }
    Set pSC = query.Prepare(sql)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    Set pSC = query.Execute(parameters...)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    Quit query
}

ClassMethod Populate() [ ZenMethod ]
{
    Do ##class(DC.Demo.SampleData).%KillExtent()
    Do ##class(DC.Demo.SampleData).Populate(20,,,,0)
    &js<zen('myTable').executeQuery();>
}

}

And the data behind it (minus storage definition):

Class DC.Demo.SampleData Extends (%Persistent, %Populate)
{

Property Name As %String;

Property SomeDate As %Date;

}
Timothy Leavitt · Nov 16, 2020

From my experience, foreign keys are really underrated/underused among ObjectScript developers. With relationships you don't need to worry about them, but really any time you have an object-valued property (not a relationship) there should almost certainly be a foreign key defined on it. (Same thing goes of course for non-object references to uniquely identifying fields in other tables.)

Timothy Leavitt · Sep 21, 2020

I already use this approach as much as possible, both for the applications I develop and my Open Exchange projects. It makes it easy to trace from test to tested unit, and (in the community package manager world) it avoids collisions between different packages all trying to use the same unit test package. I strongly agree with Evgeny's recommendation.