Hi,

Here you go!!!

This is an even better solution – now it is not “black-box” now you have 100% control of the format – and you can “connect” any json format you like to the SQL engine. In this sample I use jtable as an example. But any JSON (or html or CVS) will do the trick.

I suggest you put the “renderJtabe” in a separate member and compile it as a service program or just a module – and then use a bnddir ( Bind Directory) to glue it to your program.

<%
//' -------------------------------------------------------------------------------------------
//' Sample: producing a JSON data store for jTable
//' -------------------------------------------------------------------------------------------
/include qasphdr,icebreak
/include qasphdr,sqlvar

d*Name++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++Comments++++++++++++
d Error         s                 N
d SqlCmd        s             1024      varying
d FromRow       s               10i 0
d MaxRows       s               10i 0

// Put this in you prototype include file
d renderJtable...
d                 pr             n
d CellValue                   4096 varying
d Row                           10i 0 value
d OfRows                        10i 0 value
d Col                           10i 0 value
d OfCols                        10i 0 value
d SqlVar                              likeds(I_sqlvar)

/free

// Setup the select statement
SqlCmd = 'Select * '
       + 'from product '
       + 'order by MANUID';

FromRow = 1;
maxRows = 10000;

// Run the SQL
SQL_Execute_CallBack(
    SqlCmd:               // The Select * from ...
    FromRow:              // From row number in the result set where 1 is the first
    MaxRows:              // Maximum number of rows returned in the resultset
    %paddr(renderJtable)  // Connect the Custom Cell rendering call-back function 
to the IceBreak SQL engine
);

   *inrt = *on;

/end-free

//' -------------------------------------------------------------------------------------------
//' Note !!! Put this procedure in a module or service program for later reuse
//' The "RenderjTable" is called for each cell in the result set
//' -------------------------------------------------------------------------------------------
p*name++++++++++..b...................keywords+++++++++++++++++++++++++++++comments++++++++++++
p RenderJtable...
p                 B
d                 PI                n
d CellValue                     4096    varying
d Row                             10i 0 value
d Rows                            10i 0 value
d Col                             10i 0 value
d Cols                            10i 0 value
d SqlVar                                likeds(I_sqlvar)

/free

   Select;

      // Row zero and the firs column is the header only
      When ( row = 0 and col = 1);

      // Output from now has to be in json encoded format
      setContentType('application/json; charset=utf-8');
      setEncodingType('*JSON');

         %>{"Result":"OK",
          "Records":[
         <%

      // Row one and ahead is cell data from the result set
      When ( row >= 1);

         if (col = 1); // First coloumn start the json object
            if (row >=2); // If after the first row we need to separate with a comma
               %>,<%
            endif;
            %>{<%
         endif;

         // Print the name / value pair
         %>"<%= sqlVar.SqlName %>":"<% = CellValue %>"<%

         if (col < cols); // all exept the last has an comma
            %>,<%
          endif;

         if (col = cols); // Last coloumn terminates the the object
            %>}<%
         endif;

      // Row -1 indicates that is an EOF condition - no data is returned. This event 
always comes once as the last event’
      // Terminate the array of rows and the surrounding object
       When ( row = -1);
         %>]}<%
   Endsl;

   // Return *ON as long a you want to iterate. If you want to break the loop then return *OFF
   return *ON;

 /end-free
p                 e
%>

Best regards,

Niels Liisberg