Net.Data provides relational database language environments to help you access your relational data sources. The SQL statements you provide to access the relational data are executed as dynamic SQL. For more information on dynamic SQL, see your DB2 documentation.
The following sections describe the language environments and how to use them:
The Open Database Connectivity (ODBC) language environment executes SQL statements through an ODBC interface. ODBC is based on the X/Open SQL CAE specification, which lets a single application access many database management systems.
To use the ODBC language environment:
To use the ODBC language environment, first obtain and install an ODBC driver and a driver manager. Your ODBC driver documentation describes how to install and configure the ODBC environment.
Verify that the following configuration statement is in the Net.Data initialization file, on one line.
ENVIRONMENT (DTW_ODBC) d:/net.data/lib/dtwodbc.dll ( IN DATABASE, LOGIN, PASSWORD, TRANSACTION_SCOPE, START_ROW_NUM, DTW_SET_TOTAL_ROWS)
Restrictions:
Your database might have different limits; refer to your database documentation to determine if your database has a different limit.
The Oracle language environment provides native access to your Oracle data. You can access Oracle databases from Net.Data when using CGI, FastCGI, NSAPI, ISAPI, or GWAPI. This language environment supports Oracle 7.2, 7.3, and 8.0.
To use the Oracle language environment, verify that the following configuration statement is in the initialization file, on one line.
ENVIRONMENT (DTW_ORA) /net.data/lib/dtwora.so (IN DATABASE, LOGIN, PASSWORD, TRANSACTION_SCOPE, START_ROW_NUM, DTW_SET_TOTAL_ROWS)
See Setting up the Oracle Language Environment to learn how to further set up the Oracle language environment.
Restrictions:
LOGON=admin@ora73
The SQL language environment provides access to DB2 databases. Use this language environment for optimal performance when accessing DB2.
To use the SQL language environment, verify that the following configuration statement is in the initialization file, on one line.
ENVIRONMENT (DTW_SQL) d:/net.data/lib/dtwsql.dll (IN DATABASE, LOGIN, PASSWORD, TRANSACTION_SCOPE, START_ROW_NUM, DTW_SET_TOTAL_ROWS)
Nested SQL Statements
You can call SQL functions within another SQL function. If tables are passed, then ensure you use unique table names in each of the 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) %}
Restrictions:
SQL statements in the inline statement block can be up to 64 KB. DB2 Universal Database has the following restrictions:
Your database might have different limits; refer to your database documentation to determine if your DBMS has a different limit.
Nested SQL can only be used in the SQL or ODBC language environments and cannot be used in conjunction with Live Connection.
When nesting SQL statements, the maximum number of result sets is 32. For example you could nest three levels, each one returning 10 result sets. Or nest 32 levels, returning one result set each.
When you modify the content of a database using insert, delete, or update statements, the modifications do not become persistent until the database receives a commit statement from Net.Data. If an error occurs, Net.Data sends a rollback statement to the database, reversing all modifications since the last commit.
The way in which Net.Data sends the commit and possible rollback depends on the setting of TRANSACTION_SCOPE and whether commit statements are explicitly specified in the macro. The values for TRANSACTION_SCOPE are MULTIPLE and SINGLE.
Specifies that Net.Data issues a commit statement after each successful SQL statement. If the SQL statement returns an error, a rollback statement is issued. Single transaction scope secures a database modification immediately; however, with this scope, it is not possible to undo a modification using a rollback statement later.
To activate this commit method, set TRANSACTION_SCOPE to SINGLE. For example:
@DTW_ASSIGN(TRANSACTION_SCOPE,"SINGLE")
Specifies that Net.Data will execute all SQL statements before a commit and possible rollback statement is issued. Net.Data sends the commit at the end of the request, and if each SQL statement is issued successfully, the commit makes all modifications in the database persistent. If any of the statements returns an error, Net.Data issues a rollback statement, which sets the database back to its original state. MULTIPLE is the default if TRANSACTION_SCOPE is not set.
You can issue a commit statement at the end of any SQL statement in your macro by using the COMMIT SQL statement. By leaving TRANSACTION_SCOPE set to MULTIPLE and issuing commit statements at the end of those groups of statements that you feel qualify as a transaction, you the application developer maintain full control over the commit and rollback behavior in your application. For example, issuing commit statements after each update in your macro can help ensure the integrity of your data.
To issue an SQL commit statement, you can define a function that you can call in at any point in your HTML block:
%FUNCTION(DTW_SQL) user_commit() { commit %} ... %HTML { ... @user_commit() ... %}
Restrictions:
The setting of TRANSACTION_SCOPE cannot be changed after a connection to the database is made. Therefore, all SQL transactions in a macro are subject to the same scope.
If you are using Net.Data as part of Net.Commerce, note that Net.Commerce has its own transaction handling and disables the transaction handling of Net.Data.
You can store large object files (LOBs) in DB2 databases and incorporate them into your dynamic Web pages by using the Net.Data SQL or ODBC language environments.
When the language environment executes an SQL SELECT statement or a stored procedure that returns a LOB, it does not assign the object to a V(n) table processing variable or a Net.Data table field. Instead, it stores the LOB in a file that Net.Data creates and returns only the name of the file in the V(n) table processing variable or a Net.Data table field. In your Net.Data macro you can use the name to reference the LOB file; for example, you can create an HTML anchor element with a hypertext reference or an image element containing a URL for the file. Net.Data places the file containing the LOB in the directory specified by the HTML_PATH path statement, located in the Net.Data initialization file (db2www.ini). Write access to the LOB file is limited to the user ID associated with the Net.Data request that retrieved the LOB.
The file name for the LOB is dynamically constructed, and has the following form:
name[.extension]
Where:
Table 8. LOB extensions used in the SQL language environment
Extension | Object Type |
---|---|
.bmp | bitmap image |
.gif | graphical image format |
.jpg | joint photographic experts group (JPEG) image |
.tif | tagged image file format |
.ps | postscript |
.mid | musical instruments digital interface (midi) audio |
.aif | AIFF audio |
.avi | audio visual interleave audio |
.au | basic audio |
.ra | real audio |
.wav | windows audio visual |
portable document format | |
.rmi | midi sequence |
If the object type for the BLOB is not recognized, no extension is added to the file name.
When Net.Data returns the name of the file containing a LOB, it prefixes the file name with the string /tmplobs/ using the following syntax:
/tmplobs/name.[extension]
This prefix permits you to locate your LOB directory in a directory other than the Web server's document root directory.
To ensure that references to LOB files are correctly resolved, add the following Pass directive to your Web server's configuration file:
Pass /tmplobs/* <html_path>/tmplobs/*
<html_path> is the value specified for the HTML_PATH path statement in the Net.Data initialization file.
Planning tip: Each query that returns LOBs results in files being created in the directory specified by the HTML_PATH path configuration variable. Consider system limitations when using LOBs because they can quickly consume resources. You might want to clean up the directory periodically, or execute the dtwclean daemon. See Managing Temporary LOBS for more information. It is recommended that you use DataLinks, which eliminate the need to store files in directories by the SQL language environment, resulting in better performance and the use of much less system resources.
Example: The following application uses an MPEG audio (.mpa) file. Because the SQL language environment does not recognize this file type, an EXEC variable is used to append the .mpa extension to the file name. A user of this application must click on the file name to invoke the MPEG audio file viewer.
%DEFINE{ docroot="/usr/lpp/internet/server_root/html" myFile=%EXEC "rename $(docroot)$(filename) $(docroot)$(filename).mpa" %} %{ where rename is the command on your operating system to rename files %} %FUNCTION(DTW_SQL) queryData() { SELECT Name, IDPhoto, Voice FROM RepProfile %REPORT{ <p>Here is the information you selected:</p> %ROW{ @DTW_ASSIGN(filename, @DTW_rSUBSTR(V3, @DTW_rLASTPOS("/", V3))) $(myFile) $(V1) <img src="$(V2)"> <a href="$(V3).mpa">Voice sample</a><p> %} %} %} %HTML(REPORT){ @queryData() %}
If the RepProfile table contains information about Kinson Yamamoto and Merilee Lau, then the execution of the REPORT block will add the following HTML to the Web page being generated:
<p>Here is the information you selected:</p> Kinson Yamamoto <img src="/tmplobs/p2345n1.gif"> <a href="/tmplobs/p2345n2.mpa">Voice sample</a><p> Merilee Lau <img src="/tmplobs/p2345n3.gif"> <a href="/tmplobs/p2345n4.mpa">Voice sample</a><p>
The REPORT block in the previous example uses the implicit table variables V1, V2, and V3.
Access rights for LOBs:
The default tmplobs directory for LOBs is under the directory specified by the HTML_PATH in the shipped Net.Data initialization file. It is accessible by any user ID. If the the HTML_PATH value is changed, ensure that the user ID that the Web server is running under has write access to the directory specified by HTML_PATH (see HTML_PATH for more information).
Managing temporary LOBs:
Net.Data stores temporary LOBs in a subdirectory called tmplobs, under the directory specified in the HTML_PATH path configuration variable. These files can be large and should be cleaned out periodically to maintain acceptable performance.
Net.Data provides a daemon called dtwclean that helps you periodically manage the tmplobs directory. dtwclean uses port 7127.
To run the dtwclean daemon: Enter the following command from the command line window:
dtwclean [-t xx] [-d|-l]
Where:
A stored procedure is a compiled program stored in DB2 that can execute SQL statements. In Net.Data, stored procedures are called from Net.Data functions using a CALL statement. Stored procedure parameters are passed in from the Net.Data function parameter list. You can use stored procedures to improve performance and integrity by keeping compiled SQL statements with the database server. Net.Data supports the use of stored procedures with DB2 through the SQL and ODBC language environments.
This section describes following topics:
The syntax of the stored procedure uses the FUNCTION statement, the CALL statement, and optionally a REPORT block.
%FUNCTION (DTW_SQL) function_name ([IN datatype arg1, INOUT datatype arg2, OUT tablename, ...]) { CALL stored_procedure [(resultsetname, ...)] [%REPORT [(resultsetname)] { %}] ... [%REPORT [(resultsetname)] { %}] [%MESSAGE %}] %}
Where:
Table 9. Supported Stored Procedure Data Types
BIGINT | DOUBLEPRECISION | SMALLINT |
CHAR | FLOAT | TIME |
CLOB1 | INTEGER | TIMESTAMP |
DATE | GRAPHIC | VARCHAR |
DECIMAL | LONGVARCHAR | VARGRAPHIC |
DOUBLE | LONGVARGRAPHIC |
|
1 CLOB can only be used as an OUT and INOUT parameter, and Net.Data interprets the size in bytes. For example, if you specify a variable to be OUT CLOB(20000), a CLOB of size 20K is to be used as an out parameter. |
%FUNCTION (DTW_SQL) function_name()
%FUNCTION (DTW_SQL) function_name (IN datatype arg1, INOUT datatype arg2, OUT tablename...)
CALL stored_procedure
%REPORT (resultsetname) { ... %}
Example:
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1) { CALL myproc %REPORT (mytable){ ... %ROW { ... %} ... %} %}
CALL stored_procedure (resultsetname1, resultsetname2, ...)
%REPORT(resultsetname1) { ... %}
Example:
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1, OUT table1) { CALL myproc (table1, table2) %REPORT (table2) { ... %ROW { ... %} ... %} %}
You can pass parameters to a stored procedure and you can have the stored procedure update the parameter values so that the new value is passed back to the Net.Data macro. The number and type of the parameters on the function parameter list must match the number and type defined for the stored procedure. For example, if a parameter on the parameter list defined for the stored procedure is INOUT, then the corresponding parameter on the function parameter list must be INOUT. If a parameter on the list defined for the stored procedure is of type CHAR(30), then the corresponding parameter on the function parameter list must also be CHAR(30).
Example 1: Passing a parameter value to the stored procedure
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) valuein) { CALL myproc ...
Example 2: Returning a value from a stored procedure
%FUNCTION (DTW_SQL) mystoredproc (OUT VARCHAR(9) retvalue) { CALL myproc ...
You can return one or more result sets from a stored procedure using the SQL or ODBC language environments. The result sets can be stored in Net.Data tables for further processing within your macro or processed using a REPORT block. If a stored procedure generates multiple result sets, you must associate a name with each result set generated by the stored procedure. This is done by specifying parameters on the CALL statement. The name you specify for a result set can then be associated with a REPORT block or a Net.Data table, enabling you to determine how each result set is processed by Net.Data. You can:
See Guidelines and Restrictions for Multiple REPORT Blocks for guidelines and restrictions when using multiple report blocks.
To return a single result set and use default reporting:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure %}
For example:
%FUNCTION (DTW_SQL) mystoredproc() { CALL myproc %}
To return a single result set and specify a REPORT block:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure [(resultsetname)] %REPORT [(resultsetname)] { ... %} %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc %REPORT { ... %ROW { ... %} ... %} %}
Alternatively, the following syntax can be used:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure (resultsetname) %REPORT (resultsetname) { ... %} %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc (mytable1) %REPORT (mytable1) { ... %ROW { ... %} ... %} %}
To store a single result set in a Net.Data table for further processing:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name (OUT tablename) { CALL stored_procedure (resultsetname) %}
For example:
%DEFINE DTW_DEFAULT_REPORT = "NO" %FUNCTION (DTW_SQL) mystoredproc (OUT mytable1) { CALL myproc (mytable1) %}
Note that DTW_DEFAULT_REPORT is set to NO so that a default report is not generated for the result set.
To return multiple result sets and display them using default report formatting:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure [(resultsetname1, resultsetname2, ...)] %}
Where no report block is specified.
For example:
%DEFINE DTW_DEFAULT_REPORT = "YES" %FUNCTION (DTW_SQL) mystoredproc () { CALL myproc %}
To return multiple result sets and have the result sets stored in Net.Data tables for further processing:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name (OUT tablename1, tablename2, ...) { CALL stored_procedure (resultsetname1, resultsetname2, ...) %}
For example:
%DEFINE DTW_DEFAULT_REPORT = "NO" %FUNCTION (DTW_SQL) mystoredproc (OUT mytable1, mytable2) { CALL myproc (mytable1, mytable2) %}
Note that DTW_DEFAULT_REPORT is set to NO so that a default report is not generated for the result sets.
To return multiple result sets and specify REPORT blocks for display processing:
Each result set is associated with its one or more REPORT blocks. Use the following syntax:
%FUNCTION (DTW_SQL) function_name (, ...) { CALL stored_procedure (resultsetname1, resultsetname2, ...) %REPORT (tablename1) ... %ROW { ... %} ... %} %REPORT (tablename2) ... %ROW { ... %} ... %} ... %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc (mytable1, mytable2) %REPORT(mytable1) { ... %ROW { ... %} ... %} %REPORT(mytable2) { ... %ROW { ... %} ... %} %}
To return multiple result sets and specify different display or processing options for each result set:
You can specify different processing options for each result set using unique parameter names. For example:
%FUNCTION (DTW_SQL) mystoredproc (OUT mytable2) { CALL myproc (mytable1, mytable2, mytable3) %REPORT(mytable1) ... %ROW { ... %} ... %} %}The result set mytable1 is processed by the corresponding REPORT block and is displayed as specified by the macro writer. The result set mytable2 is stored in the Net.Data table mytable2 and can now be used for further processing, such as being passed to another function. The result set mytable3 is displayed using Net.Data's default report format because no REPORT block was specified for it.
The DataLink data type is one of the basic building blocks for extending the types of data that can be stored in database files. With DataLink, the actual data stored in the column is only a pointer to the file. This file can be any type of file; an image file, a voice recording, or a text file. DataLinks store a URL to resolve the location of the file.
The DATALINK data type requires the use of DataLink File Manager. For more information about the DataLink File Manager, see the DataLinks documentation for your operating system. Before you use the DATALINK data type, you must ensure that the Web server has access to the file system managed by the DB2 File Manager Server.
When a SQL query returns a result set with DataLinks, and the DataLink column is created with FILE LINK CONTROL with READ PERMISSION DB DataLink options, the file paths in the DataLink column contains an access token. DB2 uses the access token to authenticate access to the file. Without this access token, all attempts to access the file fail with an authority violation. However, the access token might include characters that are not usable in a URL to be returned to a browser, such as the semi-colon (;) character. For example:
/datalink/pics/UN1B;0YPVKGG346KEBE;baibien.jpg
The URL is not a valid because it contains semi-colon (;) characters. To make the URL valid, the semi-colons must be encoded using the Net.Data built-in function DTW_URLESCSEQ. However, some string manipulation must be done before applying this function because this function encodes slashes (/), as well.
You can write a Net.Data MACRO_FUNCTION to automate the string manipulation and use the DTW_URLESCSEQ function. Use this technique in every macro that retrieves data from a DATALINK data type column.
Example 1: A MACRO_FUNCTION that automates the encoding of URLs returned from DB2 UDB
%{ TO DO: Apply DTW_URLESCSEQ to a DATALINK URL to make it a valid URL. IN: DATALINK URL from DB2 File Manager column. RETURN: The URL with token portion is URL encoded %} %MACRO_FUNCTION encodeDataLink(in DLURL) { @DTW_rCONCAT( @DTW_rDELSTR( DLURL, @DTW_rADD(@DTW_rLASTPOS("/", DLURL), "1" ) ), @DTW_rURLESCSEQ( @DTW_rSUBSTR(DLURL, @DTW_rADD( @DTW_rLASTPOS("/", DLURL), "1" ) ) ) ) %}
After using this MACRO_FUNCTION, the URL is encoded properly and the file specified in the DATALINK column can be referenced on any Web browser.
Example 2: A Net.Data macro specifying the SQL query that returns the DATALINK URL
%FUNCTION(DTW_SQL)myQuery(){ select name, DLURLCOMPLETE(picture) from myTable where name like '%river%' %REPORT{ %ROW{ <p> $(V1) <br /> Before Encoding: $(V2) <br /> After Encoding: @encodeDataLInk($(V2)) <br /> Make HREF: <a href="@encodeDataLink($(V2))"> click here </a> <br /> <p> %} %} %}
Note that a DataLink File Manager functions is used. The function dlurlcomplete returns a full URL.
The following examples show how you can call the relational database language environments from your macros:
The following example defines and calls multiple function for the ODBC language environment.
%DEFINE { DATABASE="qesq1" SHOWSQL="YES" table="int_null" LOGIN="netdata1" PASSWORD="ibmdb2"%} %function(dtw_odbc) sq1() { create table int_null (int1 int, int2 int) %} %function(dtw_odbc) sql2() { insert into $(table) (int1) values (111) %} %function(dtw_odbc) sql3() { insert into $(table) (int2) values (222) %} %function(dtw_odbc) sql4() { select * from $(table) %} %function(dtw_odbc) sql5() { drop table $(table) %} %HTML(REPORT) { @sql1() @sql2() @sql3() @sql4() %}
%DEFINE { LOGIN="ulogin" PASSWORD="upassword" DATABASE="" table= "utable" %} %FUNCTION(DTW_ORA) myQuery(){ select ename,job,empno,hiredate,sal,deptno from $(table) order by ename %} %MESSAGE{ 100 : "<b>WARNING</b>: No employee were found that met your search criteria.<p>" : continue %} %HTML (REPORT) { @myQuery() %}
The following example shows a macro with a DTW_SQL function definition that calls an SQL stored procedure. It has three parameters of different data types. The DTW_SQL language environment passes each parameter to the stored procedure in accordance with the data type of the parameter. When the stored procedure completes processing, output parameters are returned and Net.Data updates the variables accordingly.
%{*********************************************************** DEFINE BLOCK ************************************************************%} %DEFINE { MACRO_NAME = "TEST ALL TYPES" DTW_HTML_TABLE = "YES" Procedure = "TESTTYPE" parm1 = "1" %{SMALLINT %} parm2 = "11" %{INT %} parm3 = "1.1" %{DECIMAL (2,1) %} %} %FUNCTION(DTW_SQL) myProc (INOUT SMALLINT parm1, INOUT INT parm2, INOUT DECIMAL(2,1) parm3){ CALL $(Procedure) %} %HTML(REPORT) { <head> <title>Net.Data : SQL Stored Procedure: Example '$(MACRO_NAME)'. </title> </head> <body bgcolor="#bbffff" text="#000000" link="#000000"> <p><p> Calling the function to create the stored procedure. <p><p> @CRTPROC() <hr> <h2> Values of the INOUT parameters prior to calling the stored procedure:<p> </h2> <b>parm1 (SMALLINT)</b><br /> $(parm1)<p> <b>parm2 (INT)</b><br /> $(parm2)<p> <b>parm3 (DECIMAL)</b><br /> $(parm3)<p> <p> <hr> <h2> Calling the function that executes the stored procedure. </h2> <p><p> @myProc(parm1,parm2,parm3) <hr> <h2> Values of the INOUT parameters after calling the stored procedure:<p> </h2> <b>parm1 (SMALLINT)</b><br /> $(parm1)<p> <b>parm2 (INT)</b><br /> $(parm2)<p> <b>parm3 (DECIMAL)</b><br /> $(parm3)<p> </body> %}