Administration and Programming Guide for OS/400

Executing SQL Statements

You can execute any SQL statement that is supported by dynamic SQL.

To execute SQL statements, define a function that uses the SQL (DTW_SQL) language environment and contains the SQL statements in the language environment executable section of the function.

Example: An SQL function that runs an SQL SELECT statement:

%function(DTW_SQL) getOrders() {  
   SELECT cust, custid, custorder FROM mylibrary.customers  
%}

Commitment Control

The SQL language environment by default runs under commitment control and follows all rules for governing commitment control.

• Journal all files or tables that are accessed through the DTW_SQL, except when the SQL statement is SELECT.
• Optionally change the commitment level by specifying DTW_SQL_ISOLATION in the Net.Data initialization file. See DTW_SQL_ISOLATION: DB2 Isolation Variable for details about the isolation levels that the SQL language environment supports.

For more information on transaction management, see Managing Transactions in a Net.Data Application.

OUT and INOUT Tables

If you specify OUT or INOUT Net.Data tables on the function definition, and the SQL statement returns result sets, the SQL language environment stores each result set in the specified tables. You can then use the table later in the macro. If an OUT table is not specified, the SQL language environment uses a default table.

Nested SQL Statements

You can call other SQL functions from within a ROW block of another SQL function. Use unique Net.Data table names in each of the SQL functions, otherwise, unpredictable results might occur.

Example: Calls an SQL function from the ROW block of another SQL function

%define mytable1 = %TABLE   
%define mytable2 = %TABLE     
 
%FUNCTION(DTW_SQL) sql2 (IN p1, OUT t2) {
   select * from NETDATA.STAFFINF where projno='$(p1)'
 %REPORT {    
  %ROW { $(N1) is $(V1) %}
 %}
%}  
 
%FUNCTION(DTW_SQL) sql1 (OUT t1) { 
   select * from NETDATA.STAFFINF
 %REPORT {  
   %ROW { @sql2(V1, mytable2) %}  
 %}   
%}   
 
%HTML(netcall1) { @sql1(mytable1) %}

Supported Language Environment Variables

The SQL language environment supports variables designed to support DB2. For example, the DATABASE variable specifies the data source that the SQL language environment connects to when executing an SQL statement. The following list specifies which variables are supported for the SQL language environment. See Net.Data Reference for description, syntax, and examples for these variables.

• DATABASE
• DB_CASE
• DTW_EDIT_CODES
• DTW_PAD_PGM_PARMS
• DTW_SET_TOTAL_ROWS
• LOGIN
• NULL_RPT_FIELD
• PASSWORD
• SHOWSQL
• SQL_STATE
• TRANSACTION_SCOPE

Supported Data Types

The SQL language environment supports the data types listed in Table 5

Table 5. Data Types

(1) These data types cannot be passed as parameters to a stored procedure call. To learn which data types are support for stored procedures, see Stored Procedure Syntax

See Data Type Considerations to learn about special considerations for the LOBs and DATALINK data types.

SQL Language Environment Restrictions

Consider the following restrictions when planning your environment:

• Do not use the SQL language environment if at least one of the following conditions exists:
  • A user-defined language environment is created that uses the database access class library or the SQL call level interface (CLI) and the user-defined language environment is referenced in a macro
  • An application that uses the SQL CLI will be running in the same process as Net.Data
• SQL statements in the inline statement block can be up to 32KB.
• You can use up to 50 local or remote database connections. When using multiple connection, consider the following restrictions:
  • Net.Data does not allow concurrent connections to the same remote database.
  • You cannot change login IDs after you have accessed a remote database if TRANSACTION_SCOPE=MULTIPLE, which is the default. See Managing Transactions in a Net.Data Application.

  See Managing Multiple Database Connections for more information about these restrictions.