An often-overlooked feature of xCommerce is the use of Code Tables and Code Table Maps.  Code Tables can be found in the main Designer window under Resources, and they allow you to store simple code-to-code or code-to-description lookups in XML files rather than, for example, in a relational database table. The Code Table Map provides the relationship between the Code Table documents (which can be as simple or as complex as you like).    This document gives some tips for working with Code Tables and Code Table Maps, and shows how you can use xCommerce Components to help automatically create Code Tables from existing information sources.

 Each Code Table contains three "columns": the code, a brief description, and a long description.  Once two tables are generated, you create a "Code Table Map", which allows you to match data between the tables in a one-to-one or many-to-one relationship.  You may also specify default information to be used if the code you are trying to match doesn't exist.

 A quick example will help explain how this works: The xCommerce tutorial project contains two Code Table resources (Display Codes and Inventory Codes) and a Code Table Map resource (InventoryDisplay).  The Display Codes resource is a table of warehouse names (descriptions), whereas Inventory Codes is a table of warehouse numbers (codes).  The Code Table Map (InventoryDisplay) matches a description to each number.  Now, within the xCommerce "Map" action, you can specify "Map Via Code Table" to automatically convert the code into a description (as seen below).

The above example takes the numeric code in Temp/RESULTINFO/ROW/CATEGORY and converts it into a (string) description before mapping to Output/INVENTORYSTATUS/CATEGORY.  The checkbox in the map action indicates that it uses a Code Table Map called InventoryDisplay to perform the translation (where 1= General, 2=Kitchen, etc).

The advantage of using Code Table Maps within your projects is that you have access to the resource in all services and components.  If left in SQL tables, you would only have access to them via a JDBC component, which means you would also take the performance hit associated with the call to JDBC (and tie up connection resources in the process).

A common misconception is that Code Tables must be created manually.  But Code Tables are just mappings of string values to string values. Why not use the power of xCommerce to create components that will generate the mappings for you?  This Article will discuss one approach to accomplish exactly that.  We'll look at creating Code Tables from SQL and HTML pages.

You can download the project to try this solution out yourself.  It was written using xCommerce 2.5.  The SQL example uses a Microsoft Access database (DSN=CodeTableDB).  The HTML example was written using the version 2.5 of our HTML Enterprise Enabler, and requires internet access (or preferably, for performance, loading a sample HTML document to a local application server).

This example creates two Code Tables.  One is a "long description" of month names (e.g., January, February, and so on).  The second is a table of common abbreviations (e.g., Jan, 01, etc).  A Code Table Map can then be created that maps all abbreviations to their corresponding long name(s).

First, we created a Code Table Template that contains all information required for that specific type of xObject.  To do this, we simply created a dummy Code Table, saved it, and then edited the XML file (the xObject "source") in an editor.  We removed all user input data and made note of those element names.  We also made note of the names of the elements used to store the actual codes, then REMOVED those elements from the template.

(Sample XML of a Code Table):

Below is the template created from the above example.  Notice that we've completely eliminated the <CTCODE> elements (and children).  We'll be creating these elements dynamically within the component.  The data highlighted in blue represents data passed into the component.

(CodeTable Template)

Next, we created another template, containing some vital information in the creation of a Code Table from SQL.  The values of this second template will need to be reset before each run (or passed as the Input document if you chose to deploy as a service).

  Finally, the component (CreateFromSQL) was created using the two templates as Input Documents, with no Output Template defined.  The only piece of this component that should ever need changing is the DSN in the connection.  Otherwise it relies solely on data provided by the Input Document.  Here's what it does: 

• Initialize some local host variables (SQL projection list and SQL Table)

• Move the entire Input1 Template (blank template) to Output

• Move in the Code Table Name & Description (provided in Input document)

• Dynamically creates a Select statement using data from Input

• Execute the SQL.

• we used "custom SQL result mapping" to avoid having the resulting elements returned as column names.  we now have control over the names of the elements returned from the query.

• Loop through the result set in a "For Each Element" loop

• Create new elements for:

• Code

• Brief Description

• Long Description for each item in the result set (Temp DOM)

• Using XML Interchange, write the new Code Table to the file system using the path provided in a Project Variable and the name of the Code Table as provided in the Input document.

 (Listing of CreatefromSQL component) 

To run it:

            1)      Download the project. (http://ftp1.novell.com/pub/xcommerce/CodeTableProject.zip)

2)      Create a System DSN called CodeTableDB pointing to "CT.mdb" (Access Database provided in CodeTableDB folder - no ID / PWD)

3)      Change the Project Variable "ProjectRoot" to reflect the path on which you have placed your project.  (This is the location of the .xcp file.)

4)      Select Code Tables on the main Designer screen- note there are none.

5)      Open the JDBC component ("CreateFromSQL")

6)      Load the sample document "..\xmlDocs\CodeTableInput_Sample.xml", by clicking on the menu items File / Load XML Samples

7)      Now Run (or Animate it).

8)      Return to the main Designer screen and click on Code Tables: there should now be a new table.  Open it up and take a look.

  9)      Go back to the JDBC component, and run it again using a new sample document ("..\xmlDocs\CodeTableInput_Sample.xml").

  10)  Look at the second table created.

  11)  You would now create a Code Table Map, which would match all month abbreviations to their appropriate long name.