IBM Books

XML Extender Administration and Programming

Dynamically overriding values in the DAD file

For dynamic queries you can use two optional parameters to override conditions in the DAD file: override and overrideType. Based on the input from overrideType, the application can override the <SQL_stmt> tag values for SQL mapping or the conditions in RDB_nodes for RDB_node mapping in the DAD.

These parameters have the following values and rules:

overrideType
This parameter is a required input parameter (IN) that flags the type of the override parameter. overrideType has the following values:

NO_OVERRIDE
Specifies not to override a condition in the DAD file.

SQL_OVERRIDE
Specifies to override a condition in DAD file with an SQL statement.

XML_OVERRIDE
Specifies to override a condition in the DAD file with an XPath-based condition.

override
This parameter is an optional input parameter (IN) that specifies the override condition for the DAD file. The input value syntax corresponds to the value specifed on the overrideType.

Examples:

The following examples show dynamic override using SQL_OVERRIDE and XML_OVERRIDE. Most stored procedure examples in this book use NO_OVERRIDE.

Example: A stored procedure using SQL_OVERRIDE.

include "dxx.h"
include "dxxrc.h"
 
       EXEC SQL INCLUDE SQLCA;
       EXEC SQL BEGIN DECLARE SECTION;
         char    collection[32];    /* dad buffer */
         char    result_tab[32];    /* name of the result table */
         char    override[256];     /* override, SQL_stmt */
         short   overrideType;      /* defined in dxx.h */
         short   max_row;           /* maximum number of rows */
         short   num_row;           /* actual number of rows */
         long    returnCode;        /* return error code */
         char    returnMsg[1024];   /* error message text */
         short   rtab_ind;
         short   ovtype_ind;
         short   ov_inde;
         short   maxrow_ind;
         short   numrow_ind;
         short   returnCode_ind;
         short   returnMsg_ind;
 
         EXEC SQL END DECLARE SECTION;
         
         /* create table */
         EXEC CREATE TABLE xml_order_tab (xmlorder XMLVarchar);
 
         /* initialize host variable and indicators */
         strcpy(collection,"sales_ord");
         strcpy(result_tab,"xml_order_tab");
         sprintf(override,"%s %s %s %s %s %s %s",
                   "SELECT o.order_key, customer, p.part_key, quantity, price,",
                   "tax, ship_id, date, mode ",
                   "FROM order_tab o, part_tab p,", 
                   "table(select substr(char(timestamp(generate_unique())),16",
                   "as ship_id,date,mode from ship_tab)as s",
                   "WHERE p.price > 50.00 and s.date >'1998-12-01' AND",
                   "p.order_key = o.order_key and s.part_key = p.part_key");
        overrideType = SQL_OVERRIDE;
         max_row = 500;
         num_row = 0;
         returnCode = 0;
         msg_txt[0] = '\0';
         collection_ind = 0;
         rtab_ind = 0;
         ov_ind = 0;
         ovtype_ind = 0;
         maxrow_ind = 0;
         numrow_ind = -1;
         returnCode_ind = -1;
         returnMsg_ind = -1;
 
         /* Call the store procedure */
         EXEC SQL CALL dxxRetrieve(:collection:collection_ind;
                :result_tab:rtab_ind,
                :overrideType:ovtype_ind,:override:ov_ind,
                :max_row:maxrow_ind,:num_row:numrow_ind,
                :returnCode:returnCode_ind,:returnMsg:returnMsg_ind); 

In this example, the <xcollection> element in the DAD file must have an <SQL_stmt> element. The override parameter overrides the value of <SQL_stmt>, by changing the price to be greater than 50.00, and the date is changed to be greater than 1998-12-01.

Example: A stored procedure using XML_OVERRIDE.

include "dxx.h"
include "dxxrc.h"
 
       EXEC SQL INCLUDE SQLCA;
       EXEC SQL BEGIN DECLARE SECTION;
         char    collection[32];  /* dad buffer */
         char    result_tab[32];  /* name of the result table */
         char    override[256];   /* override, SQL_stmt */
         short   overrideType;    /* defined in dxx.h */
         short   max_row;         /* maximum number of rows */
         short   num_row;         /* actual number of rows */
         long    returnCode;      /* return error code */
         char    returnMsg[1024]; /* error message text */
         short   dadbuf_ind;
         short   rtab_ind;
         short   ovtype_ind;
         short   ov_inde;
         short   maxrow_ind;
         short   numrow_ind;
         short   returnCode_ind;
         short   returnMsg_ind;
 
         EXEC SQL END DECLARE SECTION;
         
         /* create table */
         EXEC CREATE TABLE xml_order_tab (xmlorder XMLVarchar);
 
         /* initialize host variable and indicators */
         strcpy(collection,"sales_ord");
         strcpy(result_tab,"xml_order_tab");
         sprintf(override,"%s %s",
                   "/Order/Part/Price > 50.00 AND ",
                   "Order/Part/Shipment/ShipDate > '1998-12-01'");
        overrideType = XML_OVERRIDE;
         max_row = 500;
         num_row = 0;
         returnCode = 0;
         msg_txt[0] = '\0';
         collection_ind = 0;
         rtab_ind = 0;
         ov_ind = 0;
         ovtype_ind = 0;
         maxrow_ind = 0;
         numrow_ind = -1;
         returnCode_ind = -1;
         returnMsg_ind = -1;
 
         /* Call the store procedure */
         EXEC SQL CALL dxxRetrieve(:collection:collection_ind;
                :result_tab:rtab_ind,
                :overrideType:ovtype_ind,:override:ov_ind,
                :max_row:maxrow_ind,:num_row:numrow_ind,
                :returnCode:returnCode_ind,:returnMsg:returnMsg_ind);

In this example, the <collection> element in the DAD file has an RDB_node for the root element_node. The override value is XML-content based. The XML Extender converts the simple location path to the mapped DB2 column.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]