JDBC Connect User's Guide
CHAPTER 5
The following sections describe the similarities and differences between default and custom result mapping for the Execute SQL action. Custom mapping features are described in detail.
The mapping of data returned from an Execute SQL action is determined by specifications on the Result Mapping tab in the SQL Mapping pane. The two Result Row Placement controls allow you to determine where in the target document to place the result set data. The drop down list specifies the Message Part or Repeat alias context and the Expression edit box specifies the XPath location within the Context.
The Context is either the name of a Part in the component or the name of a Repeat alias already specified in the component (where the Repeat alias itself represents a Message Part context and XPath location). The Expression edit box specifies an XPath, the last element of which acts as the parent element for the returned results and will receive the data. The last element that receives the data is called the Row Target. If multiple rows are returned, then multiple Row Targets will be created. Each column returned in a row will appear as a child element of each Row Target.
By default, the Row Target is named "ROW" and is a child of a root element named "RESULTINFO,"and the results are written to Output, as shown above. Notice that no checkboxes are checked in the Result Mapping pane.
You can change the result mapping to use any target XPath of your choice. For example, you can use the Result Mapping tab to specify a Row Target such as Temp/RESULTINFO/Result as shown in the graphic below.
Result Mapping functionality includes the following default behaviors:
Target element names created in the document are the same as column names returned in the result set
All columns returned in the result set are mapped to the target document
NOTE: Any column names that contain spaces will have the spaces replaced with the underscore character since XML does not permit spaces in element names.
Use custom result mapping to:
Custom result mapping is accessed via the Custom ... button on the Result Mapping tab.
If you click this button, you will be presented with a dialog that has three tabs, labeled Map Target, Detail Rows, and Declare Group/Repeat.
The use of this dialog is discussed in detail below.
Novell exteNd Composer's default mapping behavior is to iterate through a list of one or more nodes (i.e., elements specified by an XPath pattern) from a source document, and map them to a single target document XPath location. If the target location doesn't exist, Composer creates it. If you know the source list is greater than one, you must indicate to exteNd Composer whether you wish to map to the same physical target location for each member of the source list (i.e., overwrite the data in the specified physical target location), or create a new physical target location for each member of the source list (i.e., add new target locations as the repeated source is mapped). You indicate that you want to map each member of the source list to the same physical target location by specifying the Context as an actual DOM name. You indicate that you want to map each member of the source list to a new physical target location by specifying the Context using an alias.
NOTE: This is also true for the Repeat for Element and the Repeat for Group actions.
You can think of the multiple rows of data returned by a SELECT statement as a repeating set of elements in an XML document. In that case, you may choose to create a Declare Group action creating a list of Groups and Detail elements within the Groups. Then you would create a Repeat for Group action to process the Group list or detail of each Group. The Custom Map Target, Detail Rows, and Declare Group/Repeat tabs provide a similar alias ability for repeating rows in SQL result sets as the Declare Group and Repeat for Group actions do for repeating elements in a document.
The Map Target tab is used to:
The Map Target tab controls the mapping of each returned row's individual columns. For each column, you specify a Context – Target XPath combination. The Context – Target XPath combination is specified for each column in the order they are listed in the projection list for the SELECT statement in your Execute SQL action. You cannot use Custom Result Mapping without filling in the Map Target tab.
The Map Target table will initially appear without any rows. Use the + icon to add additional rows. Use the - icon to delete rows. Use the up and down arrows to arrange the rows of the Map Target table.
Column: This number refers to the columns in the order they are listed in your SELECT statement.
Context: This specifies the target document for the column. The Target XPath will be appended to the Context to produce the full XPath location for the column in the target document. The Context can be a:
Document – You may use this choice if your result set contains only one row, otherwise each additional row will overwrite the previous row's data.
Detail Alias – A Detail Alias is defined on the Detail Rows tab and consists of a Document name and partial Target XPath. Or the Detail Alias may consist of a Group Alias (defined on the Declare Group/Repeat tab) and partial Target XPath location. Using a Detail Alias tells exteNd Composer to create a new physical target location for each member of the source list (i.e., each row in a result set).
Group Alias – A Group Alias is defined on the Declare Group/Repeat tab and consists of a Document name and partial XPath location. Using a Group Alias tells exteNd Composer to create a new physical target location once for each Group in the source list (i.e., where each group represents multiple rows in a result set).
Repeat Alias – If the Execute SQL action is contained with a Repeat action in your Action Model you may choose its Target alias. In this case, the Context will resolve to a Document and partial XPath to which the Target XPath (see below) will be appended.
When grouping and mapping detail column data, the Declare Group/Repeat, Detail Rows, and Map Target tab work together to define the complete XPath location for the column. (See illustration.) For instance, a column on the Map Target tab will be represented by a Context and XPath. The Context may be a Detail Alias defined on the Detail Rows tab. The Detail Alias in turn will represent another Context and XPath. Its Context may be a Group Alias defined on the Declare Group/Repeat tab. Finally the Group Alias itself will represent another Context and XPath.
By defining the Group and Detail aliases separately, you are able to map rows with duplicate column data (the basis for your groups) just once into group header elements by using the Group alias as a context, and map columns with unique data (the detail of your groups) multiple times within the group header elements by using a Detail Alias whose Context is a Group Alias.
Target XPath: This is an XPath fragment that specifies the custom name to be given to the column and optionally pre-pended by any additional parent elements. The Target XPath will be pre-pended by the Context to produce the final location for the column in the target document.
Base64 encode: The checkbox in this column allows you to convert binary data to an XML-safe representation for use in a DOM element.
NOTE: Composer's default behavior is to automatically Base64-encode binary data returned from a database during a SELECT or other "read" operation. This is necessary to ensure that the target XML node contains no "illegal characters." See Handling of Binary Data in the previous Chapter for additional info.
A processing summary for the Map Target tab is shown in the table below.
Let's assume your Execute SQL action issued the following statement:
SELECT CATEGORY, SKU, ONHAND, COST FROM InventorySystem
Which returned the following row data:
|
Category |
SKU |
Onhand |
Cost |
|---|---|---|---|
|
3 |
CHR1111 |
0 |
999 |
|
2 |
DAD7777 |
89 |
245 |
|
4 |
GAR1234 |
17 |
100 |
|
1 |
LOR8437 |
0 |
275 |
|
1 |
LOR8438 |
21 |
375 |
|
4 |
MOM4666 |
233 |
300 |
|
4 |
RAC4567 |
156 |
230 |
|
4 |
ZAC9080 |
4 |
555 |
You could fill out the Map Target tab as shown below:
Column one according to the SELECT statement will be CATEGORY. The Context is a document named "MyTemp" and the target XPath location within the Context will be "INVENTORY/PRODUCTDetail/TheCATEGORY". Notice that CATEGORY is being renamed to TheCATEGORY and being pre-pended with parent elements of INVENTORY/PRODUCTDetail. This same logic applies to the remaining columns.
However, since we have yet to define or use any aliases, each row's column data will be written to the same four physical target locations specified on the tab. If only one row is returned, then its data will be mapped to the target document with no problems. If multiple rows are returned as in our example, then each successive row's data will overwrite the previous row's data until only the last row's data exists. (Only in rare cases will this situation be desirable.)
Normally, you use the Map Target tab by itself if only one row is returned and all you wish to do is change the names of the target elements to something different than the column names. (Or if you want to assign different parent elements to individual columns.)
To avoid overwriting data with multiple result-set rows, you need to use a Detail Alias from the Detail Rows tab telling exteNd Composer to create a new physical target location for each row mapped.
The Detail Rows tab allows you to create a mapping alias tied to either a document Context or a Group/Repeat alias Context. Use of the Detail Rows tab is optional.
Detail Alias: This is a name you specify that will be referenced as a Context on the Map Target tab for mapping columns in a result set row.
Context: This is a document name or Group/Repeat alias you specify. The Target XPath will be appended to this Context to produce part of the final location for the column in the target document (the remaining part comes from the Target XPath on the Map Target tab). The Context can be a:
Document – Using a Document name tells exteNd Composer to create a new physical target location once for each row in the result set.
Group Alias – A Group Alias is defined on the Declare Group/Repeat tab and consists of a Document name and partial Target XPath location. Using a Group Alias tells exteNd Composer to create a new physical target location once for each detail row belonging to each Group (i.e., each group represents multiple rows in a result set).
Target XPath: This is an XPath fragment that you specify. It will be pre-pended by the Context on this tab and appended with the Target XPath on the Map Target tab to complete the final location for the column in the target document.
Assuming your Execute SQL action issued the following statement:
SELECT CATEGORY, SKU, ONHAND, COST FROM InventorySystem
You could fill out the Detail Rows tab as shown below:
Since the Context MyTemp and Target XPath fragment INVENTORY/PRODUCTDetail are now specified on the Detail Rows tab (creating a new physical target location for each row), references to them must be replaced on the Map Target tab with the Detail Alias "MyTempDetail." Continuing the example used in the previous section, you would update the Map Target tab as follows:
By using a Detail Alias specified on the Detail Rows tab, you will ensure that if multiple rows are returned in the result set, each row will create a new physical target location under INVENTORY/PRODUCTDetail.
When not used in conjunction with the Declare Group/Repeat tab, you can think of the Detail Rows tab as creating a "Repeat for Row" alias. If the Context for a Column on the Map Target tab is a Detail Alias (instead of a document), then exteNd creates a new Target XPath each time a row mapping occurs. In this way, multiple rows in the result set create multiple Row Targets in the document without overwriting the previous row's data. This is the same functionality provided by the Result Mapping tab's Custom... option, except that you get to rename the columns.
The result set data may not be arranged exactly the way we want, however. For example, the sub-trees under PRODUCTDetail (see illustration above) are listed without regard to product category information. If you look under PRODUCTDetail/TheCATEGORY, you can see that two rows belong to category 1, and one row each belong to categories 2 and 3. (This example is in the Action Examples project under the Sample directory in your Composer installation. You might want to step through the JDBC Component from which the above screen shot was taken, which is called "Custom Result Mapping in JDBC.")
Perhaps you'd rather see row data grouped according to category. To do this, you need to use a Group Alias from the Declare Group/Repeat tab.
The Declare Group/Repeat tab is used to:
Create groups of result set records based on one or more result set columns
Create a Group Alias to use as a Context for Map Targets (creating Group Headers)
By declaring a Group Alias you create a list comprised of the unique values found in a column across multiple rows. Any Map Target column that uses the Group Alias will map its column data only once for each unique Group essentially creating group header information.
In addition, each unique group value points to a list of the rows that belong to it. Any Detail Alias on the Detail Rows tab that uses the Group Alias will map its rows together for that group.
Group Alias: This is a name you specify that is referenced as a Context on the Map Target and/or Detail Rows tabs.
Columns: Specify one or more columns separated by a comma to create your groups. Using two columns means that only unique combinations of the concatenated values of the two columns will create a group.
NOTE: The columns you specify must form the basis of an ORDER BY clause in the SELECT statement for the Execute SQL action. If you omit the ORDER BY clause, your results will be unpredictable.
Context: This is a document name in the component or Repeat for Group or Repeat for Element alias in the Action Model that contains the Execute SQL action. The Target XPath is appended to this Context to produce part of the final location for the column in the target document. (The remaining part comes from the Target XPath on the Map Target tab and optionally from the Target XPath on the Detail Rows tab.) The Context can be a:
Document – Using a Document name tells exteNd Composer to write to the same physical document for each Group.
Repeat for Group Alias – If your Execute SQL action is inside a Repeat for Group action in your Action Model, then you may use its target alias as the Context for each Group. This tells exteNd Composer to create new Groups once for each Group processed in the enclosing Repeat for Group action.
Repeat for Element Alias - If your Execute SQL action is inside a Repeat for Element action in your Action Model, then you may use its target alias as the Context for each Group. This tells exteNd Composer to create new Groups once for each repeating element processed in the enclosing Repeat for Element action.
Target XPath: This is an XPath fragment that you specify. It is pre-pended by the Context on this tab and appended with the Target XPath on the Map Target tab (and optionally with the Target XPath on the Detail Rows tab) to complete the final location for the column in the target document.
Assuming your Execute SQL action issued the following statement:
SELECT CATEGORY, SKU, ONHAND, COST FROM InventorySystem order by CATEGORY
You could fill out the Detail Rows tab as shown below:
Similar to the example for Detail Rows, since the Context MyTemp (and Target XPath fragment INVENTORY/PRODUCT) is now specified on the Declare Group/Repeat tab, references to it must be replaced on the Detail Rows tab with the Group Alias "gCATEGORY." In addition, you are no longer listing just PRODUCTDetail under INVENTORY but rather groups of PRODUCTDetail so a new element is introduced into the Group's Target XPath called "ACATEGORYGroup." Thus for each Group mapped, a new ACATEGORYGroup element is created.
Continuing the example used in the previous two sections, you would update the Detail Rows tab as follows:
Notice that the Context of "MyTemp" has been replaced by the Group Alias gCATEGORY which represents MyTemp/INVENTORY/ACATEGORYGroup. This means that Detail Rows belonging to the Group are the only ones mapped, instead of all the Detail Rows.
Continuing the example used in the previous two sections, you would update the Map Target tab as follows:
We have replaced the Context for the CATEGORY column with the Group Alias. This means that CATEGORY is only mapped once for each Group instead of once for each detail row.
When you declare a Group Alias, the result set rows are scanned and organized into groups establishing how many processing loops will occur during mapping. If eight rows are in the result set with only four different values (e.g., 3, 2, 4, 1, 1, 4, 4, 4) then there will be four group mapping loops (e.g., 1, 2, 3, 4) and eight detail loops tied to their appropriate group mapping loops (e.g., group one has its two detail rows, group two has its one detail row, group three has its one detail row, and group four has its four detail rows).
Using the prior graphics, you can trace how the final context for the Map Target columns is constructed for Column one and Column two. Column one is the CATEGORY from the result set. Its name in the DOM will be TheCATEGORY. Its ancestor elements are determined by the context "gCATEGORY" defined as MyTemp/INVENTORY/ACATEGORYGroup on the Declare Group/Repeat tab. So the final XPath for CATEGORY is:
Output/INVENTORY/ACATEGORYGroup/TheCATEGORY
Since the context for TheCATEGORY is a Group alias, it will be mapped once for each group or four times as determined earlier.
Column two is the SKU data from the result set. Its name in the DOM will be MySKU. Its ancestor elements are determined by the context "MyTempDetail" defined to be gCATEGORY (defined above) plus PRODUCTDetail. So the final context for the column will be MyTemp/INVENTORY/ACATEGORYGroup/PRODUCTDetail/MySKU. Since the context for MySKU is a Detail Alias, it is mapped once for each Detail Row. However, each Detail Row has a Context of a Group Alias limiting mapping to only those detail rows that belong to the Group.
Copyright © 2004 Novell, Inc. All rights reserved. Copyright © 1997, 1998, 1999, 2000, 2001, 2002, 2003 SilverStream Software, LLC. All rights reserved. more ...