|
>Home>Learn XQuery>White Papers>Generating SQL>External Variables
Print
External Variables
Prepared queries in XQJ are analogous to SQL prepared statements; they use XQuery external variables in the same way that SQL uses parameter markers. The DataDirect XQuery® SQL Adapter converts queries with external variables into SQL prepared statements (except when an external variable contains a sequence — this case is discussed in Example 11).
Example 10. XQuery External Variables
XQuery
declare variable $shares as xs:integer external;
for $h in collection('HOLDINGS')/HOLDINGS
where $h/SHARES < $shares
return $h/USERID
Generated SQL
SELECT ALL
nrm4."USERID" AS RACOL1
FROM
"PEPPINO"."HOLDINGS" nrm4
WHERE
nrm4."SHARES" < ? Example 11. Sequences as External Variables
XQuery supports sequences as external variables, but in SQL, a parameter is a single value. DataDirect XQuery® uses temporary tables in the database to represent XQuery external variables that contain sequences. The sequence is inserted into the temporary table using batch insert in order to minimize the number of database round trips.
XQuery
declare variable $shares as xs:int* external;
for $h in collection('HOLDINGS')/HOLDINGS
where $h/SHARES = $shares
return $h/USERID
Generated SQL
INSERT INTO RATEMP009368710001( RAVAR, RAINT) VALUES(0, ?);
SELECT ALL
nrm4."USERID" AS RACOL2
FROM
"PEPPINO"."HOLDINGS" nrm4
WHERE
EXISTS(SELECT ALL
1 AS RACOL1
FROM
RATEMP009368710001 nrm7
WHERE
nrm7.RAVAR = 0 AND nrm4."SHARES" = nrm7.RAINT)
;
DELETE FROM RATEMP009368710001 WHERE RAVAR IN (0)
|
Learn XQuery
