The following data types supported by the SQL language environment need special consideration.
You can store large object files (LOBs) in DB2 databases and access them using the SQL language environment for your Web applications.
The SQL language environment does not store large objects in Net.Data table processing variables (such as V1 or V2), or Net.Data table fields when a SQL query returns LOBs in a result set. Instead, when Net.Data encounters a LOB, it stores the LOB in a file that Net.Data creates. This file is in a directory specified by the HTML_PATH path configuration variable. The values of Net.Data table fields and table processing variables are set to the path of the file. Access to the file is limited to the user ID under which Net.Data is running.
The file name that the LOB is stored in is dynamically constructed, and has the following form:
name[.extension]
Where:
If the object type of the BLOB is not recognized, no extension is added to the file name.
When the LOB is referenced in the macro file, the SQL language environment returns the file name with the/tmplobs/ string prepended to the LOB file name, using the following syntax:
/tmplobs/name.[extension]
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 create a batch program that cleans the directory up periodically. 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 application user must click on the file name to invoke the viewer because the application uses a MPEG audio (.MPA) file. The SQL language environment does not recognize this file type so an EXEC variable is used to append the extension to the file.
%DEFINE{
lobpath = "@DTW_RGETINIDATA("HTML_PATH")"
filename = "@DTW_RREPLACE($(V3), "/tmplobs/", "", "1", "F")"
myFile=%EXEC "REN '$(lobpath)/$(filename)' '$(filename).mpa'"
%}
%{ where rename is the rename command on your operating system %}
%FUNCTION(DTW_SQL) queryData() {
SELECT Name, IDPhoto, Voice FROM RepProfile
%REPORT{
<P>Here is the information you selected:<P>
%ROW{
$(myFile)
$(V1) Voice sample <IMG SRC="$(V2)">
<A HREF="$(V3).mpa")>Voice sample</A><P>
%}
%}
%}
%HTML(REPORT){
@queryData()
%}
The queryData function returns the following HTML output:
<P>Here are the images 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: Ensure that the user ID that the Web server is running under has write access to the directory specified by HTML_PATH.
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.