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:
.-AND----------------------------------------. V | >>----simple location path--+- =----+---value---+-------------->< +- >----+ +- <----+ +- <>---+ +- >=---+ +- <=---+ '- LIKE-'
Where:
You can have optional spaces around the operations; spaces are mandatory around the LIKE operator.
When the XML_OVERRIDE value is specified, the condition for the RDB_node in the text_node or attribute_node that matches the simple location path is overridden by the specified expression.
XML_OVERRIDE is not completely XPath compliant. The simple location path is only used to identify the element or attribute that is mapped to a column.
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.