New post
AllAccepted answers
Robert Cemper · Dec 18, 2017

Kevin,

if you call a ClassMethod() it will return whatever the type of return value is like.
some variants:

- your Classmethod generates the CSV file as you do and the return value is the filename

ClassMethod Kevin1() as %String [SqlProc]
{                                        
....... generate CVS File                
 quit Filename                           
}                                        

 

- instead of a file you generate a 2 dimensional $Piece String (, as field separator and ^ as record separator)

ClassMethod Kevin2() as %String(MAXLEN="") [SqlProc]
{                                                   
....... setup ResultSet                             
    set output=""                                   
    while rs.Next() {                               
        for col=1:1:rcws.GetColumnCount() { set output=output_rs.GetData(col)_"," }
        set output=output_"^"                       
        }                                           
    quit output                                     
}                                                   

 

- a similar thing could be rows as a list of JSON sets

ClassMethod Kevin3() as %String(MAXLEN="") [SqlProc]
{                                                   
    set output=""
    &SQL( SELECT LIST(line) into :output FROM (
             Select top 3 JSON_OBJECT('Name':Name
                                      ,'ZIP':Home_ZIP
                                      ,'State':Home_State) as line
                      from sample.Person where name [ 'A' )
             )
         )                                      
    quit output                                     
} 
///  {"Name":"Xiang,Agnes O.","ZIP":56604,"State":"MT"},{"Name":"Zubik,Bill A.","ZIP":78872,"State":"NV"},"Name":"Schaefer,Alvin X.","ZIP":63607,"State":"ME"}                                                 

in all cases you have to disassemble the result on the EXCEL end.

BUT as this is MS-EXCEL

my personal preference would be anyhow  to connect to Cache over ODBC with a local DSN
and just call your data via SQL and get back a full working XLS table that feeds the rest in your XLS.
This is standard in EXCEL and except for the DSN the same for ACCESS  or real DBs.
(as I don't own and use EXCEL any more  I can't offer an example)

- your initial question didn't mention CSV nor MS-EXCEL
so my Article on Light weight EXCEL Download may apply
https://community.intersystems.com/post/light-weight-excel-download

or  Tips & Tricks - SQL to Excel
https://community.intersystems.com/post/tips-tricks-sql-excel

HTH,
Robert

Robert Cemper · Dec 15, 2017

An alternate approach to SOAP:

a) you run your query over  JDBC on your DataServer
     so you can query what ever you like

b) slightly more sophisticated
    you create a ClassMethod with [SqlProc] parameter and also call it over JDBC
    like this.  SELECT Pkg.Class_ClassMethod()  
    It's totally up to you what the content of your return value is.   

I personally prefer JDBC as it's platform independent

in fact b) is pretty much the same as SOAP just with a different transport that avoids %CSP and its side effects.......

Robert Cemper · Dec 13, 2017

Proposal:

Store your JSON String as %String(MAXLEN="")  like a log file

for the typical components  type, age, firstname, lastname, ...
create calculated properties that find its content by $piece,  $find or some JSON specific stuff.
setup an  index on those calculated properties to find your record.

With this approach you keep the origin as you get it and get te indices you may require.
It's pretty similar to what I did to mimic an XML DB with Caché

so 1) =NO  2) =see above 3) =YES 4) = it's just another challenge

Robert Cemper · Dec 8, 2017

check side conditions of the related column.

from docs:

property onclick as %ZEN.Datatype.eventHandler;
onclick event handler: This event is fired when the mouse is clicked within a cell in this column. If the column does not have data associated with it you will have to set the linkCaption property.
Note that this callback is called before the new row in the table is selected so you will not have the current selectedIndex for the table when this callback is fired. If you need that, use the tablePane's onselectrow callback.
Robert Cemper · Dec 8, 2017

See details here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

especially:

Sample Global Names and Their Uses

The following are examples of the various kinds of global names and how each is used:
your case
2) is a broken call for a label in a routine and has nothing to to with globals
probably        do a^myroutine
3) looks like a  broken condition on variable a also no globals around
a|  could be the beginning of a OR ....
Robert Cemper · Dec 7, 2017

If your connection is not stable enough I'd suggest to take a closer look to good old Shadowing.

It's really jungle proof.
I used it in past to transfer data from an oil drill platform  somewhere out on the ocean over a satellite link with just 64k bd bandwidth without any data loss. And this link was far from whatever you would expect from a wired connection on ground.
Shadowing did id with incredible stability and no loss.
Cascading of shadowing is also almost unlimited with no  issue. 
 

Robert Cemper · Nov 30, 2017

Example WebService:

/// My.SOAP
Class My.SOAP Extends %SOAP.WebService [ ProcedureBlock ]
{

/// Name des WebService.
Parameter SERVICENAME = "MyService";

/// SOAP Namespace für den WebService
Parameter NAMESPACE = "http://tempuri.org";

/// Namespaces von referenzierten Klassen werden in der WSDL verwendet.
Parameter USECLASSNAMESPACES = 1;

Method GetCustomerInfo(schema1 As My.Schema1) As My.Schema2 [ WebMethod ]
{
 set max   =schema1.MaxResult
    ,home  =schema1.HomeState
    ,office=schema1.OfficeState
    ,start =schema1.NameStartswith
 set:'max max=1
 set:office="" office=home
 set schema2=##class(My.Schema2).%New()
    ,rs=##class(%ResultSet).%New()
    ,sql="SELECT top ? %ID FROM Sample.Employee "
        _"WHERE Home_State = ? "
        _"AND Office_state = ? "
        _"AND Name %STARTSWITH ? "
       ,tSC=rs.Prepare(sql)
 set:tSC tSC=rs.Execute(max,home,office,start)
 while rs.Next()&&tSC {
   set employee=rs.GetObject()
      ,res=##class(My.Schema2Result).%New()
      ,res.HomeAddress=employee.Home
      ,res.OfficeAddress=employee.Office
      ,res.Name=employee.Name
      ,res.pid=employee.%Id()
   do schema2.Results.Insert(res)
  }
 quit schema2
}
}
Robert Cemper · Nov 30, 2017

Example Schema1
<?xml version="1.0" encoding="UTF-8"?>
<s:schema xmlns:s="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<s:complexType name="Schema1">
    <s:sequence>
        <s:element name="Max_Results">
            <s:simpleType>
                <s:restriction base="s:long">
                    <s:minInclusive value="1"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
        <s:element name="Home-State">
            <s:simpleType>
                <s:restriction base="s:string">
                    <s:maxLength value="2"/>
                    <s:minLength value="2"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
        <s:element minOccurs="0" name="Office-State" type="s:string"/>
        <s:element minOccurs="0" name="Name-Startswith" type="s:string"/>
    </s:sequence>
</s:complexType>
</s:schema>

Robert Cemper · Nov 30, 2017

Example Schema2

<?xml version="1.0" encoding="UTF-8"?>
<s:schema xmlns:s="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
    <s:complexType name="Schema2">
    <s:sequence>
        <s:element minOccurs="0" name="Results" type="ArrayOfSchema2Result"/>
    </s:sequence>
</s:complexType>
<s:complexType name="ArrayOfSchema2Result">
    <s:sequence>
        <s:element maxOccurs="unbounded" minOccurs="0" name="Schema2Result" nillable="true" type="Schema2Result"/>
    </s:sequence>
</s:complexType>
<s:complexType name="Schema2Result">
    <s:sequence>
        <s:element minOccurs="0" name="Home-Address" type="Address"/>
        <s:element minOccurs="0" name="Office-Address" type="Address"/>
        <s:element minOccurs="0" name="Person-Name" type="s:string"/>
        <s:element minOccurs="0" name="Person-ID" type="s:long"/>
    </s:sequence>
</s:complexType>
<s:complexType name="Address">
    <s:sequence>
        <s:element minOccurs="0" name="Street">
            <s:simpleType>
                <s:restriction base="s:string">
                    <s:maxLength value="80"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
        <s:element minOccurs="0" name="City">
            <s:simpleType>
                <s:restriction base="s:string">
                    <s:maxLength value="80"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
        <s:element minOccurs="0" name="State">
            <s:simpleType>
                <s:restriction base="s:string">
                    <s:maxLength value="2"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
        <s:element minOccurs="0" name="Zip">
            <s:simpleType>
                <s:restriction base="s:string">
                    <s:maxLength value="5"/>
                </s:restriction>
            </s:simpleType>
        </s:element>
    </s:sequence>
</s:complexType>
</s:schema>
 

Robert Cemper · Nov 30, 2017

You may take this approach:

create an object based on Schema1: My.Schema1
create an object based on Schema2: My.Schema2

HowTo: => XML Schema Wizzard 
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Then you may use this method.

Method GetCustomerInfo(schema1 as My.Schema1) As My.Schema2 [WebMethod]


My.Schema1 is checked as input parameter according tothe generated class
inside your Method you transform it to My.Schema2
and return it. 

The most tricky thing might be how to reply to a request that doesn't match Schema1.