Calling a SQL Function from within a Row Block of Another Function

 

Calling functions from within %ROW blocks is the same as calling the function from an %HTML block with one important difference. Within a %ROW block, Net.Data may be processing a table variable, and if you call another function that uses the same table, it can cause issues. To call a function that does table processing from within the %ROW block of another function, define a Net.Data table variable and pass it to the functions that are being called. This ensures that unique tables are used within function blocks.

For example: The following sample passes user-defined tables to functions that processes tables. The initial call to the sql1 function passes the defined table mytable1. sql1 calls the sql2 function and passes it a different table, mytable2, along with a field that the second query uses to obtain a record from the database.

Note: In this simple example, both SQL functions query the same database. To query another remote database for sql2, before you call sql2(), use the Net.Data built-in function DTW_ASSIGN() to set the DATABASE variable to the other database, so that sql2() queries the remote system.

%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) 
%}

Reference the macro by going to the following URL, where NETDATA is the library where the web macro is stored, SQLMAC2 is the file name, and NESTCALL1 is the member:

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