Data Binding in the Sequel Web Interface serves data so that users can sort the data by any of the columns returned by the query. To provide the ability to sort data in SWI, the data binding feature must be turned on using either of the following methods:
- Change the product default (SWISETDFT) for data binding
- Add the &databind=Y parameter to the URL string that displays a given view
The process to display a view with data binding off is very different than with data binding turned on. With data binding off, SWI simply runs the query with the data returned as HTML. SWI serves the data to the browser along with the standard header and footer HTML. With data binding on, SWI runs the view by executing the results (as a .CSV file). This file is created with a PC Format (PCFMT) of *MER- GETDC and is placed in the path for data binding work files as defined in the SWI defaults. At the same time, it generates a related HTML file in the same directory. It then serves this HTML file along with the standard header and footer to the browser. This HTML file runs the Microsoft ActiveX Tabular Data Control (TDC) to display the data and manage the column sorting.
Data Binding and Hyperlinked Results Created with HREF
Many users create drill down links (hyperlinks) with the HREF function in a summary view in order to link to detail information in a second view. Unfortunately, the TDC does not render the standard HTML hyperlink correctly. Instead of being rendered as a hyperlink, the HTML produced by the HREF function is simply displayed as a text string in the cell.
As an example, this HREF function:
produces the following HTML code:
which displays as a hyperlink when the HTML is sent directly to the browser with data binding off. If data binding is on, you would see just the raw HTML code.
Data binding has a unique requirement for hyperlinks. It takes data from a designated field and uses that as the link reference data (the hidden data that supplies the hyperlink target). It takes the next field in your view as the label portion of the hyperlink (the visable data). Link reference data is indicated by a field naming convention—link$01, link$02, link$03, and so on.
In order to use data binding to display data containing drill down links, the links must be constructed as in the following example.
Here is an expression that generates the HTML drill down link and allows for sorting with data binding turned on:
SELECT CAT(‘?oBj=DDCUSNOB&LIB=SWISAMPLE&%26REGON=’, TRIM(CHAR(REGON)), ’&DATABIND=Y’) NAME(LINK$01), REGON COLHDG(“REGION”) ....
The SQL fragment above defines two result columns. When run in a browser, the two columns appear as one. All the user sees is the value from the ‘REGON’ field underlined and blue to signal the hyperlink. The data from the hidden first column is incorporated by the tabular data control to complete the functional hyperlink.
|Elements in the CAT
|?obj=||The name of the view to run when user clicks on the hyper link|
|&lib=||The library name that contains the view|
|&%26regon=||This is the variable name in the view. %26 gets translated to an & to be the first letter in the variable name: ®on. The & character has specific meaning to the browser and must remain coded as %26 to prevent being removed by the browser.|
|TRIM(CHAR(regon))||This section provides the run-time value to be passed to the ®on variable in the view|
|&databind=Y||This is optional. Y will cause the drill down results to display with data binding.|
There is an alternate method to specify the link reference data fields directly in the URL request string, such as:
Note the “®on=LINK”. This tells the SWI that the REGON field is to be rendered as a hyperlink when data binding is used. Using this method, it does not matter how the column before REGON is named.
Data Binding in Scripts
When running views with data binding turned on, SWI is able to create the necessary .CSV and .HTM files automatically. However, when running a script, the SWI software is not involved in processing the individual script steps, and therefore has no ‘knowledge’ of what the script is doing. Because of this, the automatic processing that SWI provides to support data binding cannot occur when running views- with or without drill down links- from a script. Instead, the script must be designed to create and use the required files.
The easy part is running the view to create the .CSV data file. This is accomplished with the EXECUTE command using PCFMT(*MERGETDC) which writes the file to a predetermined folder on the IFS.
The more difficult part is obtaining the generated .HTM file that runs the TDC to serve the data. The simplest way to do this is to run the view directly in SWI and capture the resulting .HTM file. You need to move quickly to do this because SWI automatically (with data binding on) deletes these files after about 1 minute. SWI uses the path defined in the ‘path for data binding work files’ in the product defaut- ls (SWISETDFT). If you use the same path for your script created files, you will have less to modify in the .HTM file you capture. The numbered steps below explain the process in more detail.
Suppose you have a summary view by region (REGIONSUM) that you want to run in a script with data binding. The region column in this view will have a drill down link to a detail view (DDCUSNO) show- ing customers in the selected region. Again, because of the way the TDC renders hyperlinks, you can’t use the HREF function. An expression is needed to create the hyperlink and turn data binding on.
So, instead of using the HREF function like so:
HREF(REGON, “DDCUSNO”,“SWISAMPLE”,“REGON”,REGON,“=&DATABIND”, “Y”) NAME(REGION) COLHDG(“REGION”)
an expression is required to create the hyperlink that will support data binding:
CAT(‘?OBJ=DDCUSNO&LIB=SWISAMPLE&%26REGON=’,TRIM(CHAR(REGON)), ‘&DATABIND=Y’) NAME(LINK$01), REGON COLHDG(“REGION”)
Note: In the data binding example the field we are drilling down on, REGON, is also included as the next field in the select clause.
Once you get your view set up to work correctly with data binding, you need to run it outside the script so that you can capture the required .HTM file that we create to serve the related data. Here are the basic steps:
- Run the view in the SWI.
- Quickly (before the file is deleted) copy the .HTM file created in your SWISETDFT path. You will save this as a permanent reusable file for each time you want to run this view in a script for data binding.
- Edit the .HTM file using note pad (or your favorite HTML editor) and note the line: <param name=”DataURL” value=”/tmp/dynamic data binding/REGIONSUM120440.csv”>
This is the line that identifies the IFS data file produced by running the query. You will use the same name in the TOSTMF parameter in step 4. As an option, you can add HTML code to display the desired SWI header and footer for your web page.
- Edit your script to EXECUTE the view (instead of using DISPLAY).
EXECUTE VIEW(SWISAMPLE/REGIONSUM) PCFMT(*MERGETDC) TOSTMF(‘/TMP/DYNAMIC DATA BINDING/REGIONSUM120440.CSV’) REPLACE(*YES)
- Add this line to the script to display the data:
SWISTMF STMF(‘/TMP/DYNAMIC DATA BINDING/REGIONSUM120441.HTM’)