Calling a SQL Function From Within A Row Block of Another Function

From Support

Calling functions from within %ROW blocks is the same as if you are calling the function from an %HTML block. However, there is one important difference. Within a %ROW block, there may be a Net.Data table variable that is being processed by Net.Data, and if you call another function that uses the same table, things can get messy. The proper way to call a function that does table processing from within the %ROW block of another function is to pass that functions being called a Net.Data table variable defined by you. This ensures that unique tables are being used within function blocks.

EXAMPLE 1

The following example illustrates the use of user-defined tables that are passed to functions that processes tables. On the initial call to function sql1 we pass in a table we defined and named mytable1, and from within this function we call function sql2, passing it a different table, mytable2, in addition to a field we obtained on the first query that is used in the second query to obtain a record from the database.

---

%define DATABASE = "*LOCAL"

%define mytable1 = %TABLE
%define mytable2 = %TABLE

%FUNCTION(DTW_SQL) sql2 (IN p1, OUT t2) {
  select * from NETDATA.STAFFINF where projno='$(p1)'

  %REPORT {
      %ROW {
          <HR>
          <P> $(N1) is @dtw_rHTMLENCODE(V1)
          <P> $(N2) is @dtw_rHTMLENCODE(V2)
          <P> $(N3) is @dtw_rHTMLENCODE(V3)
          <P> $(N4) is @dtw_rHTMLENCODE(V4)
          <P> $(N5) is @dtw_rHTMLENCODE(V5)
      %}
  %}
%}

%FUNCTION(DTW_SQL) sql1 (OUT t1) {
  select * from NETDATA.STAFFINF

  %REPORT {
      %ROW {
        @sql2(V1, mytable2)
      %}
  %}
%}

%HTML(netcall1) {

@sql1(mytable1)  
%}

---

Assuming that the Web macro is stored in library NETDATA, file SQLMAC2, member NESTCALL1, we reference the macro by loading the following URL (from a browser):

  http://hostname/cgi-bin/db2www/qsys.lib/
           netdata.lib/sqlmac2.file/netcall1.mbr/netcall1

Note that this is a simplistic example in that in both SQL functions the same database is queried. However, prior to call sql2(), one can use the Net.Data built-in function DTW_ASSIGN() to set the DATABASE variable to another database (i.e. a remote database), so that when sql2() is called, the query will be done on the remote system.