Tutorial 7

Tables and XML Documents

 

Static Tables that load from static data

 

Static tables are JavaScript objects defined in the file called tables_static.txt.

 

To edit the tables, open the Projects Home Page. Select your project and then use the Edit Static Tables option.

 

Each individual table is defined within a scripting block like this:

 

DefineObjectInstance {

 

     

};

 

The properties defined in each of the objects are:

Property

Description

className

must be "StaticTable" for tables defined in this file

name

A name that uniquely identifies a table within an aXes session. This is the name to specify in the tableName property of the eXtension.

source

"inline" means the table content is defined as an array of rows in the object.

"sql" means the table content is a result of executing a Select SQL command.

validateSession

For table operations that use SQL indicates that the execution of the SQL operation may only be initiated by a signed on 5250 session. The default is false, the other allowable value is true. For example:

DefineObjectInstance {

      className          = "DynamicTable",

      validateSession    = true,

      name               = "TEST_SQL_1",     

selectSQLcommand

When the source is "sql" this is the command to execute

resultColumnNames

When the source is "sql", the names given to the columns in the table. These are the names to refer to in the eXtension – e.g. ROW.<colname>

 

resultColumnCaptions

When being output as CSV data, the captions to be used for each column in the table.

rows

When the source is "inline", the array of columns that defines the table and its content. Here, the column names are implicitly the left operands and will be the names to refer to in the eXtension. For example if you look at the ISOCountry table and want to refer to the value column you would say ROW.value

 

In this exercise you will modify the Maintain Employee screen for adding a new employee. The screen was identified in Tutorial 1 as XHRRPGTRN_Maint.

 

You will add a drop-down next to the Employee Telephone field which will display US regions corresponding to telephone area codes. The values in the drop-down are sourced from a static table you will create.

 

(Whether the screen on your system looks like the following screen shot depends on which other tutorials you have completed.)

 

When the end-user selects a region, the area code is added to the Employee Telephone field.

 

Step 1.

 

Go to the screen identified in Tutorial 1 as XHRRPGTRN_Maint

In XHRRPGTRN_Maint identify this field:

 

Employee Telephone as Employee_Telephone

 

Don’t forget to use the Save button, otherwise the Employee_Telephone identification will not be saved.

 

Step 2.

 

In the Projects Home Page choose the option Edit Static Tables:

Insert a new line and copy/paste this code and then save the file:

 

-- ============================

-- Some US Telephone area codes

-- ============================

 

    DefineObjectInstance {

  

      className = "StaticTable",

      name    = "USAreaCode", 

      source  = "inline", 

      rows = {

                {areacode="202",location="Washington DC"},

                {areacode="208",location="All parts of Idaho"},

                {areacode="209",location="Fresno and Stockton"},

                {areacode="212",location="New York City (Manhattan only)"},

                {areacode="213",location="Los Angeles, California"},

                {areacode="217",location="Springfield, Champaign-Urbana"},

                {areacode="218",location="Duluth, (Northern) Minnesota"},

                {areacode="228",location="Southern Mississippi"},

                {areacode="307",location="All parts of Wyoming"},

                {areacode="615",location="Chattanooga and Nashville"},

                {areacode="630",location="Chicago Metro area"},

                {areacode="650",location="San Francisco area, CA"},

                {areacode="715",location="Eau Claire and Wausau"},

                {areacode="740",location="South Eastern Ohio"},

                {areacode="787",location="Puerto Rico"},

                {areacode="903",location="Texarkana and Paris"},

                {areacode="904",location="Jacksonville and Pensacola"},

                {areacode="909",location="Riverside and San Bernardino"},

                {areacode="918",location="Muskogee and Tulsa"}

 

            }, 

 

      };

 

Step 3.

 

Start an aXes development session and navigate to the XHRRPGTRN_Maint screen.

 

Step 4.

 

Start customising the screen.

 

Step 5.

 

Add a dropdown element from the Extension Toolbox and drag it next to the Employee Telephone input field.

 

 

 

 

 

 

 

Step 6.

 

Set these Drop Down properties:

 

dataSourceType: Static Table

tableName: USAreaCode

onFillDropDown: ROW.location;

onSelectValue: ROW.areacode;

onSelectedValueChanged: FIELDS("Employee_Telephone").setValue(ROW.areacode);

 

 

 

Step 7.

 

Save the extension, then exit from the screen to the XHRRPGTRN_Select screen and click the Add button to add a new employee.

 

In the XHRRPGTRN_Maintain screen use the Region drop-down to set the area code in the Employee Telephone field.

 

Step 8.

 

Notice that when you display the details for a new employee, the Region drop-down always shows the first entry in the USAreaCode table, Washington DC.

 

You can use the Additional Entries property to add an entry to appear at the top of the drop down that says something like "Select a State to set area code". Edit the property and add this code:

 

var oROW = { areacode: "", location: "Select a State to set area code" };

 

this.addTableROW (oROW, true);

 

Restart aXes.

 

Now when employee details are displayed, the drop-down will show the additional entry:

 

 

Static Tables that load from a database file

 

Static tables can load from a database file. The data is loaded the first time the table is used in an aXes session, and remembered thereafter.

 

In this example we will add a dropdown that is populated from a table that already exists, called XHRDepartment.

 

To view the table:

 

Step 1.

 

In the Projects Home Page choose the option Edit Static Tables.

 

The definition of the XHRDepartment table should look like this:

 

-- ================================================================================

-- Departments Table - Select from the shipped demonstration table AXESDEMO.XHRDEPT

-- ================================================================================

 

DefineObjectInstance {

 

     className          = "StaticTable",

     name               = "XHRDepartment",  

     source             = "sql",

     selectSQLcommand   = "XHRDEPCDE,XHRDEPNME from AXESDEMO.XHRDEPT", 

     resultColumnNames  = {"value","text"},

  };

 

 

Note how the source for the table is sql. This means that the data for the table is read from the iSeries database file, using the command in selectSQLcommand

 

that is: read fields XHRDEPCDE and XHRDEPNME from database file XHRDEPT in library AXESDEMO.

 

The data read is mapped into the table columns named in resultColumnNames.

 

So the data in field XHRDEPCDE is mapped into the table column named "value", and the data in field XHRDEPNME is mapped into the table column called "text"

 

 

To associate this table with a drop down:

 

Step 1.

 

Go to the screen identified in Tutorial 1 as XHRRPGTRN_Select

Press F6 to Add an Employee.

 

The Add screen should be recognised as the XHRRPGTRN_Maint

screen.

 

Step 2.

 

Click on the department field and change the visualization to drop down.

 

Change the properties of the drop down as follows

 

dataSourceType: Static Table

tableName        : XHRDepartment

onFillDropDown : ROW.text;

onSelectValue   : ROW.value;

 

Step 3.

 

Also add an "unselected" entry to the drop down, by adding the following script to the additionalEntries property

 

var oROW = { value: "", text: "Select a Department" };

this.addTableROW (oROW, true);

 

 

Step 4.

 

Save your changes, ( Edit and resize the field if necessary).

 

Restart aXes.

 

You now have a drop down that allows the user to select any of the departments in the XHRDEPT database file.

 

 

Static Tables and SQL Variables

Static tables can also be filled by executing SQL commands.

 

Every static table is filled the first time any static table is referenced by a script. They are normally filled only once and they remain constant until the 5250 session ends.

   

The SQL commands used to fill static table can also contain SQLVariableXXXXX substitution names, just like Dynamic Tables

 

Imagine you have a set of static tables whose content relates to the company number that user selects as they log on.

This means you need to pass SQLVariableCompanyNumber (say) to the routine that loads all the static tables.

You also need to do this before any drop down or other scripting tries to use the static table contents.

 

To do this you need to do something like this in your scripting before any drop down or scripting attempts to use any static table:

 

// Create an object that defines all the SQLVariables required

 

   Var SQLVariables = { SQLVariableCompanyNumber : sCompany , 

                        SQLVariableLibrary : USERENV.dftSQLDataLibrary  };  

 

// Ask the table manager to load the static tables and give it the SQLVariables

// that it needs to execute any SQL commands defined in the static table file 

 

  TABLEMANAGER.loadStaticTables(USERENV.staticTablesFile, SQLVariables, false);

 

 

This requests that all the static tables defined in the server file defined in the file named in USERENV.staticTablesFile (eg: "tables_static.txt") should be loaded now.

Where the static tables are being loaded from SQL commands the variables SQLVariableCompanyNumber and SQLVariableLibrary should be substituted.

 

The last parameter indicates that a (re)load of the static tables should be forced even if they have already been loaded. Here it is passed as false, but using true might be appropriate if the user had just changed the company they were using in the 5250 application.

 

Using Several Static Table Files

You don’t have to define all the Static Tables in one server file.

 

By default the server's static tables definition file is defined by USERENV.staticTablesFile which is shipped containing "tables_static.txt".

 

When you manually load tables you could do this to aggregate 3 sets of static tables:

     

   TABLEMANAGER.loadStaticTables("Static_Set_1.txt", null, false);

   TABLEMANAGER.loadStaticTables("Static_Set_2.txt", null, true);

   TABLEMANAGER.loadStaticTables("Static_Set_3.txt", null, true);

 

Or even do this to selectively load the static tables from different data sources:

 

If (CompanyNumber == "01")

   TABLEMANAGER.loadStaticTables("Static_Company_001.txt", null, false);

else

   TABLEMANAGER.loadStaticTables("Static_Company_002.txt", null, false);

 

 

The key thing to remember is that you need to do this in your scripting before any drop down requests that the static tables are loaded. 

 

 

Dynamic Tables

Like static tables, dynamic tables can be used to do a lot more than just load data into combo boxes.

 

The only difference between a dynamic and a static table is that the data sourced from a static table persists for the entire aXes session. Dynamic table data however is refreshed each time there is a screen interaction where the screen contains an eXtension using the dynamic table.

 

 

In this tutorial step you are going to add an employee enquiry to the IBM i Main menu that retrieves employee details based on the employee number:

 

First, open the Projects Home Page. Select your project and then use the Edit Dynamic Tables option to open tables_dynamic.txt. Add this dynamic table definition to it:

 

-- ====================================================================================

-- Look up the details of an Employee

-- ====================================================================================

 

    DefineObjectInstance {

      className         = "DynamicTable",

      name              = "FetchBasicEmployeeInfo",  

      source            = "sql",

      selectSQLcommand  = "XHRSURNME,XHRGIVNME, XHRJOBTLE from AXESDEMO.XHREMPTN where XHREMPID = ':SQLVariable_RequestedNumber' ",

      resultColumnNames  = { "lastName", "firstName", "jobTitle" },

    };

 

This dynamic table is named FetchBasicEmployeeInfo.

 

It requires the caller to provide an employee number in a variable named SQLVariable_RequestedNumber.

 

It reads the name and title details from the employee master file shipped in the AXESDEMO library, returning them with the values lastName, firstName and jobTitle respectively.

 

Now, alter the System i Main menu so that it can support employee enquiries, for example like this:

 

 

To do this add 3 new elements to the 5250 screen.

 

The first is a Default Visualization input field named requestEmployee:

 

 

  

The second is an output result field named requestResult, with a 1px solid red border:

 

 

And the last is a push button with caption Locate Employee:

 

 

The important part is this: add the following code to the onClick script:

 

/* The name of the dynamic table being used */

var sDynamicTable = "FetchBasicEmployeeInfo";

/* Get the employee number input and convert to upper case */

var sRequestedNumber = FIELDS("requestEmployee").getValue();
sRequestedNumber = sRequestedNumber.toUpperCase();

/* As the manager to load the dynamic table, passing the requested employee number */

TABLEMANAGER.loadDynamicTable(sDynamicTable,USERENV.dynamicTablesFile,{SQLVariable_RequestedNumber : sRequestedNumber}, false );

/* Get row 0 from the result produced in the dynamic table */

var oRow = TABLEMANAGER.getTable(sDynamicTable).child(0);  

/* If no resulting row was found then show error message in result field */

if (oRow == null)
FIELDS("requestResult").setValue("** NOT FOUND **");
else

   /* Format up the name and job title into the result field */

FIELDS("requestResult").setValue(oRow.firstName + " " + oRow.lastName + ", " + oRow.jobTitle);

 

 

What this script does is to get the uppercased value of the employee number entered in field requestEmployee. It then asks the table manager to create the dynamic table named FetchBasicEmployee, passing in the employee number as an SQL variable. Then row 0 of the result is checked, and either an error message or the employee details are output onto the screen in the requestResult field.

 

Save the extensions and enter employee numbers like A002450, A004680 or A008550 in the requestEmployee field and click the button. You will see this:

 

 

You have now added a new and quite foreign capability to the System i Main menu!

 

At one level this example is a bit silly, but at another level it demonstrates a very powerful facility.

 

Imagine the screen was not the System i Main Menu, but an Order Details screen, and the button said DHL Delivery Status.

 

When the button is clicked an order/DHL cross reference table is accessed on the server to provide an associated DHL consignment number. The DHL consignment number is then used to open a web browser window (provided by DHL) to display the order's delivery status.

          

Frequently Asked Questions about this Example

 

Q: Does the library name have to be hard coded?

No. You just can use "… from XHREMPTN where …" if you want.

 

Q: What happens if the library name is not specified?

The axes server's current library list is used to locate the table.

 

Q: How can I avoid using a library name?

Leave it out of the SQL commands and put the required library(s) into the aXes server job's library list. 

 

Q: I sign on to an aXes 5250 session as user FRED - is user FRED's library list used to find the file?

No. The library list used is the library list of the aXes server job.

 

Q: Can I dynamically change the aXes server's library list

This would not be advisable. Server requests execute in a multi-threaded, multi-user stateless environment.

Even if you changed the server jobs library list, another thread might change it 2 milliseconds later – even before you could get to perform your SQL request.  

 

Q: So is the library list solution suitable for all situations? 

No. In situations where you need different library lists for different users (say) or different companies (say) to support access to different databases you generally need to specify the exact library name for SQL requests. 

 

Q: How can I make the library name soft?

By making it an SQLVariable like the employee number in this example.

 

The preceding example could be coded as:

 

    DefineObjectInstance {

      className         = "DynamicTable",

      name              = "FetchBasicEmployeeInfo",  

      source            = "sql",

      selectSQLcommand  = "XHRSURNME,XHRGIVNME, XHRJOBTLE from

                           :SQLVariableLibrary.XHREMPTN where

                           XHREMPID = ':SQLVariable_RequestedNumber' ",

      resultColumnNames  = { "lastName", "firstName", "jobTitle" },

    }; 

Now an SQLVarible named SQLVariableLibrary will be substituted into the SQL command before it is executed.

This is no different to substituting the employee number or any other value.

 

Q: Where does SQLVariableLibrary come from?

It needs to be supplied by the eXtension script that causes the SQL command to be executed. 

 

Q How is the value of SQLVariable set?

By normal eXtension scripting. If you look at the drop down eXtension's sqlVariables property you will see this default value:

 

 

So when a drop down is to be filled from an SQL command, by default, the SQL variable named SQLVariableLibrary is passed to the server from the USERENV's dftSQLDataLibrary property. If you look at the USERENV object definition you will see this line:

 

   /* ------------------------------------------------ */

   /* Properties defined as part of the USERENV object */ 

   /* ------------------------------------------------ */

 

   staticTablesFile    : "tables_static.txt",

   dynamicTablesFile   : "tables_dynamic.txt",

   dftSQLDataLibrary   : "QGPL",              

 

 

So by default, any SQL command used to fill a drop down has a variable named SQLVariableLibrary available to it for substitution.

Also, by default, it will contain QGPL as the library name.

 

You could do this in any individual drop down's sqlVariables property:

 

          ENV.SQL.SQLVariableLibrary = "MYDATALIB";

 

or your could do this in the USERENV object:

 

         dftSQLDataLibrary : "MYOTHERLIB",

 

to change the SQLVariableLibrary value being sent to the server. 

 

 

Q: Does the variable have to be named SQLVariableLibrary?

No. You can use any SQLVariablexxxxx name you like.

 

You might also have multiple names like SQLVariableGlobalLibrary and SQLVariableCompanyLibrary to separate shared global databases from individual company databases.    

 

Q: How can I work out different library names for different situations?

However you like.

 

Imagine your 5250 application made the user select a company as they logged on. In the screen onLeave script you could code something like this:

 

var CompanyNumber = FIELDS("CompanyNumber").getValue()

 

switch (CompanyNumber)

{

   case "01": USERENV.dftSQLDataLibrary = "DLCOMP_01"; break;

   case "02": USERENV.dftSQLDataLibrary = "DLCOMP02";   break;

   case "03": USERENV.dftSQLDataLibrary = "DLCOMP_3R"; break;

   case "04": USERENV.dftSQLDataLibrary = "DLCOMP4AG"; break;

}

 

This sets USERENV.dftSQLDataLibrary to different library names based on the selected company number for use by all subsequent SQL commands.   

 

More sophisticated and more generic examples of this can themselves execute an SQL command to look up a table by company number (say) or user id (say) to determine the names of the libraries to be used. This is similar to logic commonly used in many IBM i application 5250 logon programs.  See Some Usage Ideas.  

 

A tip for setting up SQL commands in the dynamic tables file

Before you create a dynamic table definition, it’s a good idea to first try out your SQL command in an SQL command line session to get rid of any mistakes.

 

In a 5250 session use the STRSQL command to start a SQL command line session. Try out your intended SQL command, for example:

 

       SELECT XHREMPID, XHRGIVNME,XHRSTREET,XHRCITY FROM AXESDEMO/XHREMPTN

       WHERE UPPER(XHRGIVNME) LIKE '%IN%'

 

which is a list of all employees whose name (in uppercase) contains the letters "IN".  Test it until you get it right, then copy/paste the command into your aXes dynamic tables file and generalize it as required - for example:

 

    DefineObjectInstance

    {

      className          = "DynamicTable",

      name               = "AnExample",  

      source             = "sql",

      selectSQLcommand   = "XHREMPID, XHRGIVNME,XHRSTREET,XHRCITY FROM       

                           :SQLVariable_DataLibrary.XHREMPTN           

                           WHERE UPPER(XHRGIVNME) LIKE '%:SQLVariable_PartialName%' ",

    };

 

Working this way might save you several cycles of dynamic table updating to get your SQL command right.

 

 

BTW: You have to define the SQL commands in a server based file because this makes it difficult for a browser client to change what columns are selected. For security reasons only SELECT (read) SQL commands should ever be allowed. Developers need to be careful not to get too inventive with SQL variables and accidentally allow simple SQL injection attacks.  

 

 

 

XML Documents

 

In this step you will use data from an XML document to populate a drop down with job titles.

 

Step 1.

 

Use a text editor like notepad to copy/paste this XML:

 

<?xml version="1.0" encoding="iso-8859-1"?>

 

<jobtitles>

   <title>Lawyer</title>

   <title>Auditor</title>

   <title>Maintenance Officer</title>

   <title>Quality Manager</title>

   <title>Funds Manager</title>

</jobtitles>

 

Note: you can add more <title> nodes if you wish.

 

Save this file as JobTitles_en.xml in the axes\ts\screens folder or your private definition set folder, for example:

\axes\ts\screens\eeva (where eeva is the name of your private definition set folder).

 

 

Step 2.

 

Go to the screen identified in Tutorial 1 as XHRRPGTRN_Maint

In XHRRPGTRN_Maint identify the field Job Title as Job_Title

 

Step 3.

 

Double-click the Job_Title field to open the Add eXtension window then select DropDown  from the list and click the Add button.

 

Step 4.

 

Set these Drop Down properties:

 

dataSourceType: XML file

tableName: JobTitles

onFillDropDown: ROW.title

xmlFileName: JobTitles_en.xml

onSelectValue: ROW.title

onSelectedValueChanged: FIELD.setValue(ROW.title)

 

 

Note that the value *DEFAULT for xmlFileLocation means either /ts/screens/<definition set> if there is a definition set specified in the URL, Otherwise it means /ts/screens/.

 

 

Step 5.

 

Save the screen and test the drop-down.

 

Step 6.

 

Note that using the LANGUAGE predefined variable you can fill the drop down with entries sourced from language specific data.

 

Copy the file JobTitles_en.xml to a file called JobTitles_es.xml.

Step 7.

 

Replace the contents with this:

 

<?xml version="1.0" encoding="iso-8859-1"?>

 

<jobtitles>

   <title>Abogado</title>

   <title>Auditor</title>

   <title>Oficial de Mantenimiento</title>

   <title>Gerente de Control de Calidad</title>

   <title>Gerente de Cartera</title>

</jobtitles>

 

Step 7.

 

Edit the screen and change the xmlFileName property like this:

 

ENV.returnValue = "JobTitles_" + LANGUAGE + ".xml";

 

The result of this statement will be the JobTitles_<languagecode>.xml.

 

Step 8.

 

Add lang=es at the end of the URL and you will notice how the drop down will now have the Spanish language entries from the Spanish JobTitles_es.xml.

 

For example http://myhost/ts/ts2/index.html?dev&definitionSet=myproject&lang=es

 

Performance Considerations

 

Once the data has been stored in a table it will be reused for the duration of the session except when a dynamic table is used.

 

Usage of dynamic tables should be carefully considered because they will most definitely have a performance impact.

 

Each time a screen arrives, for each extension whose data is sourced from a Dynamic Table, the query request will run to get the data from the server and store the data in the table.

 

The performance of a dynamic table can be improved if the same request is commonly issued repeatedly, by using the keepLastKey option. This causes Axes to store the key of the last request, and if another request is made for the same key, the results of the last request are reused (without any access to the server).

 

keepLastKey can be specified on the extensions that can use a dynamic table (The drop down and radio buttons extensions), or it can be specified in a script as the 4th parameter of the Axes.TableManager.loadDynamicTable function)

 

Some Usage Ideas 

 

You can use tables for a lot more than just filling combo boxes and setting up radio button sets.

 

For example, a common system design requirement is a set of information that defines the characteristics of a system. If you were writing an RPG server based application you would typically put such information into a data area or a data base table.  

 

Here's an example of doing this with a static table.

 

Open the Projects Home Page. Select your project and then use the Edit Static Tables option.

 

Add this new static table into your application:

 

-- ================================================================================

-- MySystemInfo - Single row static table containing information about my system 

-- ================================================================================

 

   DefineObjectInstance {

      className = "StaticTable",

      name      = "MySystemInfo", 

      source    = "inline", 

      rows      = {

                    {

                      companyName          = "Acme and Acme",

                      companyWebSite       = "www.mycompany.com",  

                      productVersion       = "1.0",

                      defaultLanguage      = "English",

                      defaultOS400Library  = "MYLIBRARY"                  

                    }

                 }   

    }; 

 

 

Now add a push button like this to the IBM i Main menu:

 

 

For the onClick property use this scripting: 

 

/* Load all static tables, in case they are not already been loaded */

/* If they are already loaded this request will just be ignored     */ 

TABLEMANAGER.loadStaticTables(USERENV.staticTablesFile);

/* Now get a reference to the "MySystemInfo" static table defined on the server */
/* Then put a reference to row/child 0 directly into the USERENV object         */  

USERENV.systemInfo = TABLEMANAGER.getTable("MySystemInfo").child(0);  

if (USERENV.systemInfo == null)
{
  window.alert("MySystemInfo row could not be read");
}
else
{
  var sMessage = "";
  sMessage += "Company = " + USERENV.systemInfo.companyName + "\r";
  sMessage += "Website = " + USERENV.systemInfo.companyWebSite + "\r";
  sMessage += "Version = " + USERENV.systemInfo.productVersion + "\r";
  sMessage += "Language = " + USERENV.systemInfo.defaultLanguage + "\r";
  sMessage += "Library = " + USERENV.systemInfo.defaultOS400Library + "\r";
  window.alert(sMessage);
}

/* USERENV.systemInfo is now a real script object, so other scripts can */
/* now just access USERENV.systemInfo.companyWebSite (say) directly     */

 

 

 

Save your screen changes.

 

You may have already loaded the static tables, so to be sure, close and restart your aXes development session.

 

Redisplay the IBM i Main Menu and click the Test Static Table Access button.

 

You should see a message box like this: 

 

 

This example demonstrates how a static table may be used to provide useful control information scripts.  

 

 

Controlling Axes Using a System Definition Table

A common requirement in aXes implementations is to set up soft coded values that can be used to generically control your eXtension scripts and aXes application.

 

Such soft coded values are variously referred to as system values, settings, definitions or properties.

 

Note: Every application ever created has such settings to change how the application behaves at each deployment site or even for each user. Typically the settings are permanently stored in DB2/400 data base tables or in IBM i data areas. 

 

The following material describes a simple and extensible technique for setting up soft coded system definitions for your aXes applications. The definitions are easily accessed by eXtension scripts, almost infinitely extensible and easy to deploy.  

 

If you are interested in IT technologies this technique is a practical example of using JSON in your aXes applications.

 

Step 1 – Set up your system definition data base table 

In this example a data base table (an IBM i physical file) named MYSYSDEF is created in library QGPL. The SQL command to create this table could be like this - but you could just easily create the table using traditional DDS …    

 

CREATE TABLE QGPL/MYSYSDEF (SYSNAME CHAR (10) NOT NULL, JSONDATA CHAR (500) NOT NULL)

 

This table is logically keyed by a char(10) system name.

 

The char(500) JSONDATA field will store the system definition values - referred to as properties from now on.

 

The example system definition properties to be stored in table MYSYSDEF are:

ð  The company name.

ð  The URL to be used for web search requests.

ð  A flag indicating whether users are allowed to do web searches.

Step 2 – Define a dynamic query to read data base table MYSYSDEF 

 

Your eXtension scripting will need to be able to read the MYSYSDEF table - so you need to define an SQL query into your project's Dynamic Tables definition file:

 

DefineObjectInstance {

   className          = "DynamicTable",

   name               = "MYSYSDEF",  

   source             = "sql",

   selectSQLcommand   = "JSONDATA from QGPL.MYSYSDEF where SYSNAME = ':SQLVariable_System' ",

   resultColumnNames  = { "JSONDATA" },

   };

 

Save your changes and restart any developer sessions.

 

Step 3 – Read data base table MYSYSDEF when the user logs on 

 

Put this function into your project's USERENV.JS file - as part of your USERENV object:

 

   /* --------------------------------------------------------------------------- */

   /* Load the system definition from MYSYSDEF file for the system name specified */

   /* --------------------------------------------------------------------------- */

 

   loadSYSDEF : function(sysname)  

   {

      /* Load the SQL table build by reading table MYSYSDEF for the specified system name. The  */

      

      AXES.TableManager.loadDynamicTable("MYSYSDEF",USERENV.dynamicTablesFile,{SQLVariable_System:sysname}, false);                     

      var oTable = AXES.TableManager.getTable("MYSYSDEF");

      var oChild = oTable.child(0);

     

      /* Handle not found */

      

      if (oChild == null)

      {

         USERENV.SYSDEF = {}; /* Create default empty USERENV.SYSDEF object */      

         window.alert("USRENV.loadSYSDEF: MYSYSDEF load failed. No data available for system named " + sysname );

      }

     

      /* Attempt to convert JSON data in USERENV.SYSDEF java script object */

     

      else

      {

          try

          {

             USERENV.SYSDEF = eval("({ " + oChild.JSONDATA + " })");

          }

          catch (oe)

          {

             USERENV.SYSDEF = {};  /* Create default empty USERENV.SYSDEF object */ 

             window.alert("USRENV.loadSYSDEF: Error " + oe.description + " detected when loading JSONDATA from MYSYSDEF."); 

          }

      }

 

      /* Insert the correct default values for all missing USERENV.SYSDEF properties   */

      /* This saves all later scripts from having to check whether the property exists */

 

      {

         var SYSDEF = USERENV.SYSDEF;

         if (SYSDEF.companyName   == null) SYSDEF.companyName  = "NOT AVAILABLE";

         if (SYSDEF.allowSearch   == null) SYSDEF.allowSearch  = false;

         if (SYSDEF.searchEngine  == null) SYSDEF.searchEngine = "http://www.google.com";           

      }

 

      /* Finished */

     

      return;

 

   }, /* <-- remember the trailing comma */

 

 

 

Save your changes and restart any currently open developer sessions.

 

Now edit your project so that at application sign on it executes the new USERENV.loadSYSDEF("SYSTEM1"); function in the onSignOn event, like this:

 

 

Save your changes.

 

Step 4 – Define your system values in the MYSYSDEF table 

 

Using the IBM i command UPDDTA FILE(MYSYSDEF) or a similar application insert a row (record) in to the MYSYSDEF table as follows:

 

SYSNAME Value

JSONDATA Value- exact case is required

 

SYSTEM1

 

companyName : "ACME", allowSearch : true  

 

 

Note: Double check the entry for exact case, quotes, colons and commas.

 

Step 5 – Check that it all works okay  

 

Name the IBM i main system menu and add two push button eXtensions.

 

 

The first, titled Show Values should do this in its onClick event:

 

window.alert("companyname=" + USERENV.SYSDEF.companyName);

window.alert("allowSearch=" + USERENV.SYSDEF.allowSearch.toString());

window.alert("searchEngine=" + USERENV.SYSDEF.searchEngine);

 

The second, titled Search the Web should do this in its onClick event:

 

window.open(USERENV.SYSDEF.searchEngine,"_blank");

 

Additionally, the Search the Web button should have its visibility conditioned by altering the visible property to execute this script:

 

  ENV.returnValue = USERENV.SYSDEF.allowSearch;

 

 

Like this :

 

 

Save your changes and start a new user 5250 session.

 

Click the Show Values button. You should see 3 message boxes like this:

 

 

Click the Search the Web button.

 

You should see a new web page open up with the Google search engine displayed.

 

--------------------------------------------------------------------------------------------

 

Now - update the SYSTEM1 row in the MYSYSDEF data base table so that the field JSONDATA contains this data:

 

    companyName : "Wigets", allowSearch : true, searchEngine : "http://www.bing.com"

 

being careful with case, colons, quotes and commas.

 

Start a new user session so that the updated MYSYSDEF data is loaded at log on. 

 

When you click the Search Values button you should see the company name Widgets and the search engine URL as Microsoft's Bing.

 

When you click the Search the Web button you should see the Microsoft Bing search engine instead of the Google engine displayed.  

 

--------------------------------------------------------------------------------------------

 

Finally, update the JSONDATA field in the MYSYSDEF data base table to be like this:

 

    companyName : "Wigets", allowSearch : false, searchEngine : "http://www.bing.com"

 

Start a new user session to pick up the modified data the MYSYSDEF table.

 

The Search the Web button should not appear because its visibility has been conditioned by USERENV.SYSINFO.allowSearch property – which is now false.

 

The key to all of this is that you have changed the behaviour of your aXes application without changing anything in a an eXtension script – you have only changed a row in the MYSYSDEF data base table.

 

Step 6 – Basic Concepts 

The field or column JSONDATA in your MYSYSDEF table contains a JSON (JavaScript Object Notation) string.

 

When it is read from the MYSYSDEF table it can be converted directly to a JavaScript object by the JavaScript eval() function. This happens in this line of code in USERENV.loadSYSDEF():    

 

 

             USERENV.SYSDEF = eval("({ " + oChild.JSONDATA + " })");

 

The JSON string approach is very powerful because:

 

·         It converts directly into a JavaScript object named USERENV.SYSDEF. 

 

·         It is extremely extensible because it allows you to invent new system definition properties at any time.

 

For example if you change the JSONDATA field/column to:

 

shoeSize :2, hatSize: 14, companyName : "Wigets", allowSearch : false, searchEngine : "http://www.bing.com"

 

then you have just invented two new USERENV.SYSDEF properties that you can immediately reference in your eXtension scripts as USERENV.SYSDEF.shoeSize and USERENV.SYSDEF.hatSize.

 

  1. You can also define arrays and even JavaScript functions in JSONDATA - see the following optional advanced capabilities steps.      

 

Step 7 – Infinite Extensibility , JSON Formatting and Default Values  

 

Adding new properties is easy and almost infinitely extensible – you just add them to the JSONDATA field/column in your MYSYSDEF table and then you can immediately reference them in your eXtension scripts using the name format USERENV.SYSDEF.propertyname.

 

You can add numbers as name : number and strings as name : "string".

 

Strictly speaking the JSON format is:

 

"name" : numeric value or "name" : "string value"

 

Generally you can use either format - but the property names are always case sensitive.  

 

You need to be careful with using the special ":" and "," separation characters.

 

If you get the syntax wrong then this line in USERENV.loadSYSDEF() will pop up:

 

window.alert("USRENV.loadSYSDEF: Error " + oe.description + " detected when loading JSONDATA from MYSYSDEF."); 

 

When adding new properties you need to consider aXes applications that you may have already deployed. For example, you could invent a new property called myNewProperty, add it to your MYSYSDEF table and then immediately start using it in your eXtension scripts.

 

However, when you deploy your application you might find that in the deployed application environment there is not a myNewproperty value defined in the MYSYSDEF table – so your application will get an error when it tries to reference the property.

 

This is very easily solved – when you add a new property always add a default value for it to the USERENV.loadSYSDEF() function. That is why this code exists in USERENV.loadSYSDEF and it is also why the first example displayed the search engine as Google …         

 

  /* Insert the correct default values for all missing USERENV.SYSDEF properties   */

  /* This saves all later scripts from having to check whether the property exists */

 

  {

     var SYSDEF = USERENV.SYSDEF;

     if (SYSDEF.companyName  == null) SYSDEF.companyName  = "NOT AVAILABLE";

     if (SYSDEF.allowSearch  == null) SYSDEF.allowSearch  = false;

     if (SYSDEF.searchEngine == null) SYSDEF.searchEngine = "http://www.google.com";           

  }

 

So you would add a line like this:

 

     if (SYSDEF.myNewProperty == null) SYSDEF.myNewProperty = 500; /* Say */

           

or:

 

     if (SYSDEF.myNewProperty == null) SYSDEF.myNewProperty = "YYYYYXXX"; /* Say */

 

so that USERENV.SYSDEF.myNewProperty always exists – even it is not defined by the JSONDATA string read from the MYSYSDEF data base table.

 

Note: This code is also a good place to document all the properties that exist in your USERENV.SYSDEF object. 

Step 8 – Advanced  Capabilities – Arrays of System Properties  

 

You can easily define arrays in a JSON string.

 

Try adding this to the JSONDATA field/column in your MYSYSDEF data base table:

 

           validTypes : [ "A", "B", "C" ]

 

Following the rule of always setting up a default value for a new property you should add this to your USERENV.loadSYSDEF() function:

 

       if (SYSDEF.validTypes == null) SYSDEF.validTypes = [ ]; /* empty array */

 

In an eXtension script activated by a push button execute this script:

 

var SYSDEF = USERENV.SYSDEF;

var message = "Found valid types :";

 

for (var index in SYSDEF.validTypes)

{

  message += "\r" + SYSDEF.validTypes[index]; /* Insert value preceded by a CR */   

}

 

window.alert(message);

 

When the script is executed you should see:

 

 

 

You can also define arrays of number and even arrays of objects. For example:

 

Sizes : [ {x:1, y:2}, {x:3, y:4} , {x:5, y:6} ]

 

defines an array of objects. The array is named Sizes and each entry in the array is an object containing the properties x and y – which could be processed in a JavaScript loop like this:

 

var SYSDEF = USERENV.SYSDEF;

 

for (var index in SYSDEF.Sizes)

{

   var o = SYSDEF.Sizes[index];

 

   o.x and o.y are now accessible to the code.      

}

Step 9 – Very Advanced Capabilities - Functions and "Soft" Logic  

 

As a very advanced capability you can also define JavaScript functions in a JSON string.

 

First imagine a new function named showSearchEngine in your USERENV.SYSDEF object.

 

This function will contain the logic that is executed when a user requests a web search, rather than just the state that properties contain.  

 

To try this out put a default value for the function into your USERENV.loadSYSDEF() function like this:

 

   if (SYSDEF.showSearchEngine == null) SYSDEF.showSearchEngine = function(){};            

 

So by default the showSearchEngine() function does nothing – it just starts and ends.

 

Save your changes.

 

Now change the "Search the Web" button to do this when it is clicked:

 

   USERENV.SYSDEF.showSearchEngine();

 

This means when the "Web Search" button is clicked the function showSearchEngine() in the USERENV.SYSDEF object will be executed.

 

Start a new user session and verify that your Search the Web button now does nothing at all when it is clicked.

 

--------------------------------------------------------------------------------------------------

 

Now update your SYSTEM1 definition in the MYSYSDEF data base table to include this:

 

    showSearchEngine : function(){ window.open("http://www.google.com"); }

 

Start a new user session. You should find that now when you click the Search the Web button that a Google window opens.

 

--------------------------------------------------------------------------------------------------

 

Now update your SYSTEM1 definition in the MYSYSDEF data base table to include this:

 

    showSearchEngine : function(){ window.alert("Do not click this button"); }

 

Start a new user session. You should find that now when you click the Search the Web button that the message "Do not click this button" appears.

 

--------------------------------------------------------------------------------------------------

 

The key point here is that you have changed the logic in showSearchEngine by changing a record in a data base table on the server – you did not need to change any scripting on the client. 

 

This is an example of how you can not only make USERENV.SYSDEF based properties "soft" – you can also make logic "soft" as well.

 

It would not be viable or sensible to try to script everything (or even most things) this way – but in situations where site dependent calculations or special variable logic is required this capability may be very powerfully used.

 

 

SQL and CCSIDs

 

When using SQL commands to load static or dynamic tables you may need to know about CCSID handling.

 

In this example a data base table named PBEMPF is used. It has these columns:

 

Name

Type

Description

EMPNO

Alpha(5)

Employee Number

EMNAM

Alpha(14) 

Employee Name – Open field - CCSID 937

EMADR

Alpha(62)

Employee Address – Open field - CCSID 937

 

Note: CCSID 937 is Traditional Chinese.

 

The contents of table PBEMPF are to be loaded as a static table named TEST03.

 

This is done adding an instruction like this to the static table definition file:       

 

DefineObjectInstance {

      className          = "StaticTable",

      name               = "TEST03",  

      source             = "sql",

      selectSQLcommand   = "EMPNO, EMNAM, EMADR from QGPL.PBEMPF",

      resultColumnNames  = { "value", "text", "address" },

   };

 

However, when the static tables are being loaded this error is displayed:

 

 

This error is indicating that data in the file connot be converted to the code page being used by the aXes server.

 

To avoid this issue the static table definition is changed to this:

 

  DefineObjectInstance {

      className          = "StaticTable",

      name               = "TEST03",  

      source             = "sql",

      selectSQLcommand   = "EMPNO, CAST(EMNAM AS CHAR(14) CCSID 65535), CAST(EMADR AS CHAR(62) CCSID 65535) from QGPL.PBEMPF",

      resultColumnNames  = { "value", "text", "address" },

      resultColumnCCSID  = { text=937, address=937 };

   };

  

The CAST operations indicate that ENNAM and EMADR are to be read as binary data.

 

The ResultColumnCCSID definition then informs aXes that the result columns logically named text and address should be treated as CCSID 937 (Traditional Chinese) data.

 

The EMPNAM and EMPADR values can now be used in a drop down eXtension, like this example displaying ROW.text + “ – “ + ROW.address:

 

     

Note: Make sure to confirm EMNAM and EMADR’s defined CCSID is 937 to avoid error not related to this example. 

Tip for handling SQL variables and differing CCSIDs

Taking data from a Unicode based browser and passing it around for use in EBCDIC based SQL commands and databases can sometimes be slightly problematic.    

 

Here's a tip for a way you may be able to get around Unicode and EBCDIC code page conversion situations using an SQL feature called a Unicode Literal.

 

In an SQL command you can type requests containing things like:

WHERE GREETING LIKE '%HELLO%'

 

You can also type:

WHERE GREETING LIKE UX'002500480045004C004C004F0025'

 

which is pretty much the same thing except that its meaning is absolutely exact in all code pages. Here 0025=%, 0048=H, 0045=E etc. are in Unicode hex format (for example see http://www.fileformat.info/info/unicode/char/48/index.htm for what  a Unicode “H” is). This technique may be most useful when using SQLVariable substitution variables.

 

In a dynamic table SQL definition you can also code:

CUSTNAME  LIKE ':SQLVariable_Name'

 

and then use the variable in client-side JavaScript like this:

var SearchString = "%" + FIELDS("NAME").getValue() + "%";  /* A %value% scan value */

var SQLVars = { SQLVariable_Name :  SearchString };

TABLEMANAGER.loadDynamicTable("Test", etc, SQLVars, etc );

 

In the SQL definition you might also code something like this:

CUSTNAME LIKE UX':SQLVariable_Name'

 

And in the client-side JavaScript use a function that converts the JavaScript strings into UX style SQL literal values:

var SearchString = "%" + FIELDS("NAME").getValue() + "%";

var SQLVars = { SQLVariable_Name :  USERENV.toSQLUnicode(SearchString) };

TABLEMANAGER.loadDynamicTable("Test", <dynamicTables File>, SQLVars, <keepLastKey option> );

 

The function toSQLUnicode used above is a simple function coded in the USERENV object: 

toSQLUnicode : function(s)

{

   var r = "";

   if (s != null)

   {

       s = s.toString();

       for (var i = 0; i < s.length; i++)

       {

          var cc = s.charCodeAt(i);

          var uc = cc.toString(16).toUpperCase();

          while (uc.length < 4) uc = "0" + uc;

          r += uc; 

       }

   }

   return(r);  

}, /* <- Remember the comma */

 

 

You can go further and specify the exact conversion by making the LIKE clause on the server into something like this:

 

     CUSTNAME LIKE CAST(UX':SQLVariable_Name' AS VARCHAR(50) CCSID 297)  

 

which takes the Unicode literal and then unequivocally converts it to EBCDIC CCSID 297 - which is then used for the LIKE comparison value - and is presumably the same code page as the field CUSTNAME.

 

You don’t normally need to do a CAST like this (you could experiment to confirm this), but casting may be significant with DBCS languages that need SO (Shift-Out) and SI (Shift-In) characters in their EBCDIC representations because shift characters are not normally present in Unicode strings.

 

If you want to try using some SQL UX literals manually using the IBM i's STRSQL command, here is the source for an HTML page that will turn what you type in into SQL UX literals ready for copy/paste into your 5250 STRSQL session:

 

<html>

<head>

<script type="text/javascript">

function  toSQLUnicode(s)

{

   var r = "";

   if (s != null)

   {

       s = s.toString();

       for (var i = 0; i < s.length; i++)

       {

          var cc = s.charCodeAt(i);

          var uc = cc.toString(16).toUpperCase();

          while (uc.length < 4) uc = "0" + uc;

          r += uc; 

       }

   }

   return(r);  

}

</script>

</head>

<body>

<input type="text" id="InputValue" />

<input type="button" value="Convert=&gt;" onclick="OutputValue.value = toSQLUnicode(InputValue.value);" />

<input type="text" id="OutputValue" />

</body>

</html>

 

 

Using Dynamic Tables to Produce Spreadsheets and Reports

 

You can use Dynamic table definitions to produce spreadsheet data. This offers a convenient way to download data to a user’s PC. Once the data is in a spreadsheet the user can format  it, save it and print it as they like.

 

Dynamic tables have to be defined by a professional software developer so only approved and content audited downloads can be executed.

 

Note: Generic downloading tools may present some security and/or data content misinterpretation risks (eg: end users including deleted orders into quarterly sales revenue reports).   

 

In this tutorial three dynamic SQL requests are added to the dynamic tables definition file. Use the aXes Projects Home page Edit Dynamic Tables option:

 

 

Then add these three definitions named Example1, Example2 and Example3:

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "Example1",  

      source             = "sql",

      selectSQLcommand   = "XHRBUABRV, XHRBUSUNT from AXESDEMO.XHRBU",

      resultColumnNames    = { "value", "text" },

      resultColumnCaptions = { "Business Unit Code", "Business Unit Description" },

    };

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "Example2",  

      source             = "sql",

      selectSQLcommand   = " XHREMPID, XHRGIVNME,XHRSTREET,XHRCITY,XHRSTATE from AXESDEMO.XHREMPTN",

      resultColumnCaptions = { "Id", "First Name", "Street Address", "City", "State" },

    };

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "Example3",  

      source             = "sql",

      selectSQLcommand   = "* from AXESDEMO.XHREMPTN where XHREMPID = ':SQLVariable_EmployeeId' ",

    };

 

Note: Any active development session needs to be closed and restarted to pick up these new dynamic table defintions:

 

Next three buttons elements are added to the System I Main Menu:

 

  

      

Note: The Selection or command entry field is named CommandLine in this example.

 

The three new buttons are then given this onClick scripting:

 

Business Unit List Button

 

var result = TABLEMANAGER.convertDynamicTable("Example1",USERENV.dynamicTablesFile);

if (result.error == false) 

{

   var URL = document.location.protocol + "//" + document.location.host + "/ts/" + result.outputFileName;

   window.open(URL,"_blank");

}

 

Employee Address Details Button

 

var result = TABLEMANAGER.convertDynamicTable("Example2",USERENV.dynamicTablesFile);

if (result.error == false)

{

   var URL = document.location.protocol + "//" + document.location.host + "/ts/" + result.outputFileName;

   window.open(URL,"_blank");

}

 

Employee Details Button

 

var empid = FIELDS("CommandLine").getValue();

var requestdetails = { orientation:"V", SQLVariable_EmployeeId:empid };

var result = TABLEMANAGER.convertDynamicTable("Example3",USERENV.dynamicTablesFile,requestdetails);

if (result.error == false)

{

   if (result.outputLineCount == 0)

   {

      window.alert("Employee with number " + empid + " not found. Is the number and the case of the number correct?");

   }

   else

   {

      var URL = document.location.protocol + "//" + document.location.host + "/ts/" + result.outputFileName;

      window.open(URL,"_blank");

   }

}

 

 

When the Business Unit List Button is clicked the SQL command named Example1 is executed. The output sent to a CSV file. The name of the file produced is returned in result.outputFileName. The output file is then opened in a new browser window.

 

So the user should see:

 

 

And when they click Open button they should see:

 

 

From MS-Excel they can save the file to their hard drive, reformat the content, display graphs and pivot tables, print the content, etc, etc. 

 

The Employee Address button processing is similar. It’s processing is designed to demonstrate how quickly more than 900 employee records can be downloaded into a spreadsheet.

 

When the Employee Details is button is clicked an employee number is extracted from the command input area on the screen (eg: A002450). This is passed to the SQL command named Example3 as a SQL variable in the normal manner.  If no lines were output to the file a message is displayed indicating that employee specified could not be found.   

 

Significant Points

 

·         You don’t have to invoke TABLEMANAGER.convertDynamicTable() from a button. You can invoke it from anywhere. eg: From a hyper-link or when a drop entry is selected from an drop down containing 50 selectable spreadsheet reports.

 

·         The definition of the SQL command for Example1 demonstrates use of the resultColumnCaptions property. This allows the captions to be defined for the output fields to be defined. The precedence is that a resultColumnCaption will be used if it exists, then a resultColumnName if it exists, and finally an automatically generated caption like Column_n or Field_n will be used.  

 

·         You can separate your spreadsheet SQL commands from your other SQL commands by putting them in another file. The file that contains the dynamic SQL definitions is specified as property USERENV.dynamicTablesFile - which defaults to "tables_dynamic.txt". You could could change this to TABLEMANAGER.convertDynamicTable(“Example1”,”spreadsheetTables.txt”), for example, where spreadsheetTables.txt contains a different set of dynamic table definitions. Changing the source file name like this can also be used to logically partition access to the SQL commands by user, department, group, etc.          

 

·         The Employee Details example demonstrates extracting information from the current screen and passing it to the SQL request. For example - if the current screen displayed a product, then the product number could be extracted and passed to an SQL command that listed the sales of the product for the last 12 months.

 

·         The Employee Details example also demonstrates adding additional properties to the SQL request in addition to the usual SQL variables. The possibilities include:

 

Name

Meaning

orientation

H or V indicating whether the spreadsheet columns should be arranged horizontally or vertically. The default is H.

outprefix

Output file prefix. Default is TemporaryFile.

outsuffix

Output file suffix. Default is csv.

outfolder

Output file folder. Default is <axes root folder>/ts/

delimiter

String field delimiter. Default is “ (double quote).

seperator

Field separator Default is , (comma)

outputCCSID

Output file CCSID/Code page. Must be valid windows client CCSID such as 950 or 932 Default is 1208 (UTF-8) which is the best choice for most situations. Make sure that the SQL dynamic table is also defined correctly with the source CCSID for columns read.   

 

è The result object returned by TABLEMANAGER.convertDynamicTable() contains these properties: 

 

Name

Meaning

outputFilePath

The output path (folder) in which the result file was produced. This is an IFS folder name, which typically is not the same as a URL reference to the file.

outputFileName

The output file name.

outputLineCount

The number of lines written to the output file, including any column headings.

error

True/false. Indicates that an error was trapped.

errorDesc

A description of the trapped error, if available. This message si automatically displayed and traced so normally you do not need to do this.

 

è The temporary files are put into the specified server folder so they can be accessed over the Internet. They are not automatically deleted because it is impossible to exactly know when to delete them. Typically they are deleted by generic name (eg: TemporaryFile*.csv) as part of overnight batch processing logic, or in high volume situations, they may be quickly scanned every hour or so and all files that are older than one hour are deleted (say).  

 

 

 

More about using Dynamic Tables with SQL

 

Here are some more examples and tips for using SQL with eXtensions: 

 

SQL Example 1 – Using STRSQL to Test your commands first 

 

 

Here's a basic SQL example

 

select the department fields (XHRDEPCDE and XHRDEPNME) from the department file (XHRDEPT).

 

Test it on your iSeries in this format, using STRSQL:

 

SELECT XHRDEPCDE, XHRDEPNME FROM AXESDEMO/XHRDEPT

 

Here it is in the format when defined in an Axes Dynamic table - the slash is replaced with a dot:

 

SELECT XHRDEPCDE, XHRDEPNME FROM AXESDEMO.XHRDEPT

 

If we want to output the result to a MS excel spreadsheet, we first create a dynamic table containing the SQL statement, like this (remembering to remove the initial "select"):

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample01",  

      source             = "sql",

      selectSQLcommand   = " XHRDEPCDE, XHRDEPNME FROM AXESDEMO.XHRDEPT",

      resultColumnCaptions = { "Department Code", "Department Name" },

    };

 

 

Then we create an Axes button that makes use of the dynamic table, with  onClick script as follows:

 

var result = TABLEMANAGER.convertDynamicTable("SQLExample01",USERENV.dynamicTablesFile);

if (result.error == false) 

{

   var URL = document.location.protocol + "//" + document.location.host + "/ts/" + result.outputFileName;

   window.open(URL,"_blank");

}

 

When the button is clicked, it should produce a simple list of departments in a spreadsheet, like this:

 

 

 

SQL Example 2 – Joining Files

 

Suppose we wanted to do a basic JOIN between the Departments (XHRDEPT) and Employees (XHREMPTN) to show the department name for each employee.

 

When a field could come from more than one file, its file has to be specified, so in this code the field XHRDEPCDE becomes XHRDEPT.XHRDEPCDE

 

select XHRDEPT.XHRDEPCDE, XHRDEPT.XHRDEPNME, XHRSURNME, XHRGIVNME              

from AXESDEMO/XHRDEPT, AXESDEMO/XHREMPTN       

where XHRDEPT.XHRDEPCDE = XHREMPTN.XHRDEPCDE 

ORDER BY XHRDEPT.XHRDEPCDE, XHRSURNME

 

or, to make it easier to refer to the files, we could give the files short names (de for Department, em for Employee)

 

select de.XHRDEPCDE, de.XHRDEPNME, XHRSURNME, XHRGIVNME              

from AXESDEMO/XHRDEPT de, AXESDEMO/XHREMPTN em       

where de.XHRDEPCDE = em.XHRDEPCDE 

ORDER BY de.XHRDEPCDE, XHRSURNME

 

we create a dynamic table containing the SQL statement, (remembering to change the / to a dot, and removing the select) like this:

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample02",  

      source             = "sql",

      selectSQLcommand   =

" de.XHRDEPCDE, de.XHRDEPNME, XHRSURNME, XHRGIVNME from AXESDEMO.XHRDEPT de, AXESDEMO.XHREMPTN em where de.XHRDEPCDE = em.XHRDEPCDE ORDER BY de.XHRDEPCDE, XHRSURNME ",

      resultColumnCaptions = { "Department Code", "Department Name", "Employee Surname", "Employee Given Name" },

    };

 

To make it easier to read, we can use square brackets instead of double quotes around the SQL string. This allows us to use returns to break up the SQL statement into its sections.

 

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample02",  

      source             = "sql",

      selectSQLcommand   =

      [[

        de.XHRDEPCDE, de.XHRDEPNME, XHRSURNME, XHRGIVNME

        from AXESDEMO.XHRDEPT de, AXESDEMO.XHREMPTN em

        where de.XHRDEPCDE = em.XHRDEPCDE ORDER BY de.XHRDEPCDE, XHRSURNME

      ]],

     

      resultColumnCaptions = { "Department Code", "Department Name", "Employee  Surname", "Employee Given Name" },

    

    };

 

Save your table, and modify the onClick routine of the Axes button to point to "SQLExample02"

 

If you now click the button you should see

 

 

Note: If you want to do a join where records from the first file always appear, use this syntax:

 

select XHRDEPT.XHRDEPCDE, XHRDEPT.XHRDEPNME, XHRSURNME, XHRGIVNME

from AXESDEMO/XHRDEPT                                           

LEFT OUTER JOIN AXESDEMO/XHREMPTN                               

ON XHRDEPT.XHRDEPCDE = XHREMPTN.XHRDEPCDE                       

ORDER BY XHRDEPT.XHRDEPCDE, XHRSURNME                           

 

Note: If you want to do a join that returns only the records in the first file that don't have a match in the second file, use this syntax

 

select XHRDEPT.XHRDEPCDE, XHRDEPT.XHRDEPNME, XHRSURNME, XHRGIVNME

from AXESDEMO/XHRDEPT                                           

EXCEPTION JOIN AXESDEMO/XHREMPTN                                

ON XHRDEPT.XHRDEPCDE = XHREMPTN.XHRDEPCDE                       

ORDER BY XHRDEPT.XHRDEPCDE, XHRSURNME                           

 

 

SQL Example 3 – Summarizing Data

 

Suppose that instead of showing one line for every employee, we wanted a summary result, with one line for each department. We use the GROUP BY parameter

 

 

select de.XHRDEPCDE              

from AXESDEMO/XHRDEPT de, AXESDEMO/XHREMPTN em       

where de.XHRDEPCDE = em.XHRDEPCDE 

GROUP BY de.XHRDEPCDE

 

 

When we work with a group of records there are several useful things we can do with each group of detail records. Examples are:

 

COUNT

SUM

AVG

MAX

MIN

 

So, in our summary query we can show the number of employees, the average salary, the maximum salary, and the total salary for each department, as follows

 

select de.XHRDEPCDE, MAX(de.XHRDEPNME), COUNT(*), AVG(XHRSALARY),

MAX(XHRSALARY), SUM(XHRSALARY)                                   

from AXESDEMO/XHRDEPT de, AXESDEMO/XHREMPTN em                   

where de.XHRDEPCDE = em.XHRDEPCDE                                

GROUP BY de.XHRDEPCDE                                            

 

we create a dynamic table containing the SQL statement, like this:

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample03",  

      source             = "sql",

      selectSQLcommand   =

      [[ de.XHRDEPCDE, MAX(XHRDEPNME), COUNT(*), AVG(XHRSALARY), MAX(XHRSALARY), SUM(XHRSALARY)                

      FROM AXESDEMO.XHRDEPT de, AXESDEMO.XHREMPTN em

      where de.XHRDEPCDE = em.XHRDEPCDE             

      GROUP BY de.XHRDEPCDE

      ]],

      resultColumnCaptions = { "Department Code", "Department Name", "Number of Employees", "Average Salary", "Maximum Salary", "Total Salary" },

    };

 

Save your table, and modify the onClick routine of the Axes button to point to "SQLExample03"

 

If you now click the button you should see:

 

 

 

SQL Example 4 – Controlling Data Extracted 

 

When you want to show only some of the records on the file, there are two ways to select them.

 

Method 1) When you are selecting records based on field values for individual records, you add extra AND commands to the WHERE

 

using comparison operators like  > < = != <= >= or BETWEEN, LIKE, IN

 

For example - Get all the employees with a salary outside 30,000 to 50,000, who are not in the SALES or LEGAL departments, with surnames beginning with S.

 

 

SELECT XHREMPID, XHRSURNME, XHRGIVNME, XHRSALARY, XHRBUABRV

FROM AXESDEMO/XHREMPTN                                    

WHERE XHRSALARY NOT BETWEEN 30000 AND 50000               

AND XHRBUABRV NOT IN ('SALES', 'LEGAL')                    

AND XHRSURNME LIKE 'S%'

 

 

we create a dynamic table containing the SQL statement, like this:

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample04",  

      source             = "sql",

      selectSQLcommand   =

      [[

      XHREMPID, XHRSURNME, XHRGIVNME, XHRSALARY, XHRBUABRV

      FROM AXESDEMO.XHREMPTN                                    

      WHERE XHRSALARY NOT BETWEEN 30000 AND 50000               

      AND XHRBUABRV NOT IN ('SALES', 'LEGAL')                    

      AND XHRSURNME LIKE 'S%'

      ]],

      resultColumnCaptions = { "Employee ID", "Surname", "Given Name", "Salary", "Business Unit" },

    };

 

Save your table, and modify the onClick routine of the Axes button to point to "SQLExample04"

 

If you now click the button you should see something like:

 

 

 

Note:

Method 2) If you want to select values based solely on summary values, you use a different keyword - HAVING

 

Suppose you wanted to select all the departments with an average above a value

 

SELECT XHRDEPCDE, AVG(XHRSALARY)

FROM AXESDEMO/XHREMPTN         

GROUP BY XHRDEPCDE             

HAVING AVG(XHRSALARY) > 60000  

ORDER BY XHRDEPCDE             

 

 

SQL Example 5 – Nested Queries

 

Suppose you want to do one query and then compare the result of that query with another file. You can nest queries by making the query one of the FROM values:

 

For example, list all the employees with 50% higher than the average for their department.

 

One query works out what the average salary is for all the departments.

The employees file is joined with the result of that query, to determine which employees have 50% higher than the average salaries for the department.

 

 

There are two FROM entries in this query; one is the employees file (named em), one is a nested query (named mysummary)

 

 

select em.XHREMPID, em.XHRSURNME, em.XHRGIVNME, em.XHRSALARY,      

mysummary.empcount, mysummary.avgsal, em.XHRDEPCDE, em.XHRSTATE                                

FROM                                                               

AXESDEMO/XHREMPTN em,                                              

(                                                                  

select XHRDEPCDE, COUNT(*) empcount, AVG(XHRSALARY) avgsal

from AXESDEMO/XHREMPTN                                             

GROUP BY XHRDEPCDE                                        

ORDER BY XHRDEPCDE                                        

) mysummary                                                        

where                                                              

mysummary.XHRDEPCDE = em.XHRDEPCDE AND                             

em.XHRSALARY > (mysummary.avgsal * 1.5)                            

 

 

Note how result columns in the summary query have been assigned names (e.g. avgsal) and then referred to in the WHERE condition.

 

we create a dynamic table containing the SQL statement, like this:

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample05",  

      source             = "sql",

      selectSQLcommand   =

      [[

          em.XHREMPID, em.XHRSURNME, em.XHRGIVNME, em.XHRSALARY,      

          mysummary.empcount, mysummary.avgsal, em.XHRDEPCDE, em.XHRSTATE                                

          FROM                                                                

          AXESDEMO.XHREMPTN em,                                              

          (                                                                  

             select XHRDEPCDE, COUNT(*) empcount, AVG(XHRSALARY) avgsal

             from AXESDEMO.XHREMPTN                                             

             GROUP BY XHRDEPCDE                                        

             ORDER BY XHRDEPCDE                                        

          ) mysummary                                                         

          where                                                              

          mysummary.XHRDEPCDE = em.XHRDEPCDE AND                             

          em.XHRSALARY > (mysummary.avgsal * 1.5)                            

      ]],

      resultColumnCaptions = { "Employee ID", "Surname", "Given Name", "Salary", "Num Employees", "Average Salary", "Department", "State"  },

    };

 

 

Save your table, and modify the onClick routine of the Axes button to point to "SQLExample05"

 

If you now click the button you should see something like:

 

 

 

 

SQL Example 6 – Data Manipulation and Date Handling

 

Manipulation of data in fields, and Dates.

 

You may need to rearrange data within fields, in order to do comparisons.

 

The following example (converting a numeric YYYYMMDD date into an ISO date) demonstrates

a) number to alphanumeric conversion (CHAR)

b) substring (SUBSTR)

c) concatenation (||)

 

 

SELECT XHREMPID, YYYYMMDD,

Date(  

  Days( SUBSTR(CHAR(YYYYMMDD),1,4)||'-'|| SUBSTR(CHAR(YYYYMMDD),5,2)|| '-'|| SUBSTR(CHAR(YYYYMMDD),7,2)  )

    ) 

)

FROM EMPLOYEEFILE

 

It also demonstrates some of the DATE functions that are available when the date is in ISO (YYYY-MM-DD) format

Once a date is in ISO format, you can convert it to a date value, and do date arithmetic, like this:

 

SELECT XHREMPID, XHRSTDTE, 

DATE(XHRSTDTE) + 6 MONTHS  

FROM AXESDEMO/XHREMPTN     

 

Other examples of useful functions are

 

DATE

DAY

DAYNAME

CURDATE

DAYOFWEEK

DAYOFMONTH

DAYOFYEAR

DAYS

MONTH

MONTHNAME

MONTHSBETWEEN

WEEK

YEAR

 

You can add durations of

YEARS

MONTHS

DAYS

 

to a date.

 

See SQL reference for more details about DateTime arithmetic

 

we create a dynamic table containing the SQL statement, like this:

 

 

    DefineObjectInstance {

      className          = "DynamicTable",

      name               = "SQLExample06",  

      source             = "sql",

      selectSQLcommand   =

      [[

          XHREMPID, XHRSTDTE, 

         DATE(XHRSTDTE) + 6 MONTHS  

         FROM AXESDEMO.XHREMPTN     

      ]],

 

      resultColumnCaptions = { "Employee ID", "Start Date", "Start Date + 6 Months"  },

    };

 

Save your table, and modify the onClick routine of the Axes button to point to "SQLExample06"

 

If you now click the button you should see something like:

 

 

 

 

References:

http://www.w3schools.com/Sql/default.asp

(Good examples, but the syntax is sometimes wrong for iSeries)

 

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp

(search for SQL)

                Database SQL Programming

                DB2 for i SQL Reference