While using SQL to perform database queries is most common, it is important to understand that almost any SQL statement can be invoked from Net.data. This is because Net.data has been built to support various language environments and SQL is just one of them. In essence, Net.data simply reads any SQL statement that you embed in a DTW_SQL function block and passes it to the native SQL environment for execution. A convenient technique is to first test an SQL statement by starting interactive SQL from a green green, STRSQL, entering the SQL statement there and when satisified cut and paste it into a Net.data SQL block.

• An SQL stored procedure is invoked by using the SQL CALL statement. In fact any existing external program, an RPG program for example, can be considered to be a stored procedure since SQL will attempt to call it without the existence of a specific SQL PROCEDURE to launch the external program. Leaving external RPG programs aside for now, we will take this opportunity to show that we can employ Net.data not only to invoke the CALL but also the create the stored procedure in the first place. The complete source this example an be found at sqldemo4
• The %html block for this example is as follows -

  %html(macro) {
  <center>
  <b>SQL Demo Example 4
  <p>Deleting stored procedure QGPL/DEMOPROC01
   @deleteProc()
  
   <p>Creating stored procedure QGPL/DEMOPROC01
   @createProc()
  
  <p>Executing stored procedure QGPL/DEMOPROC01
   @runProc()
  
   <p>List database file QGPL/SQLDEMOTBL created by DEMOPROC01
   @list()
  
  </center>
  %}
  

  Prior to the %html block there are 4 DTW_SQL function blocks defined. Each of these functions is executed within the %html block. However, only the final @list function generates any output. The preceding functions manage the creation and execution of an SQL PROCEDURE. The aim of this example is to create an SQL PROCEDURE named DEMOPROC01 whose purpose is to create a database table name SQLDEMOTBL in QGPL and also populate it with some test records. The resulting database table is subsequently displayed using the @list() function.
• Examination of the first 3 DTW_SQL function blocks reveals that there is nothing very special about them. From a Net.data perspective they each run one of the following SQL statements respectively. DROP PROCEDURE QGPL/DEMOPROC01 CREATE PROCEDURE QGPL/DEMOPROC01 CALL QGPL/DEMOPROC01 For these 3 functions Net.data is doing very little. Most of the task is performed within the SQL environment. Net.data passes to it the code necessary to create the SQL PROCEDURE whose function includes dropping, creating, labeling and then populating the test table SQLDEMOTBL. Net.data then passes a request to call the SQL procedure, which results in those functions being completed by the SQL environment.
• Upon returning we run the @list function which performs a simple listing of the file. In this example the @list function does not perform any html rendering, letting it default to the Net.data grid report for rendering a result set. The resulting display should appear as follows - SQL Demo Example 4

  Deleting stored procedure QGPL/DEMOPROC01

  Creating stored procedure QGPL/DEMOPROC01

  Executing stored procedure QGPL/DEMOPROC01

  List database file QGPL/SQLDEMOTBL created by DEMOPROC01

   CLIENTNO | AREA | CLIENTNAME |
  -------------------------------
   1001     | CA   | JOHN DOE   |
  -------------------------------
   1010     | DC   | FANNY MAE  |
  -------------------------------