Using SQL Functions

---

DataDirect XQuery allows you to invoke any SQL function provided by any supported database, including built-in database functions and stored procedures. See "Example: SQL Function" for an example of invoking a SQL function.

Requirements and Restrictions

The requirements and restrictions for using SQL functions within a query are:

You must declare the SQL function as an external function in the http://www.datadirect.com/xquery/sql-function namespace, which has a predefined prefix of ddtek-sql.

The following example first declares rtrim(), and then invokes it within a query:

declare function ddtek-sql:rtrim($inp as xs:string) as xs:string external; 
for $x in collection('items')//itemno 
return 
   <a>{ddtek-sql:rtrim(concat($x,'  '))}</a> 

You must declare a JDBC Escape function as an external function in the http://www.datadirect.com/xquery/sql-jdbc-escape-function namespace, which has a predefined prefix of ddtek-sql-jdbc.
Functions or procedures returning results through output parameters are not supported.
Functions that return a table are supported if the table function is defined in the source configuration file. This is specified in the tableFunction element of the source configuration file. See "Using SQL Table Functions" for more information.
The SequenceTypes for parameter and return values in the external function declarations must match the mapping of database data types to XML schema types as described in the tables listed in "Data Types".
The SQL function is not supported if the SQL function through an argument or return type refers to a data type for which no mapping is defined. See "Data Types" for the mappings of database data types to XML schema types.
The quantifier of the return type must be ? or 1.

Microsoft SQL Server Examples

Example: ddtek-sql:STUFF()

The syntax of this function is:

STUFF(character_expr,start,length,character_expr) 

The equivalent XQuery declaration is:

declare function ddtek-sql:STUFF( 
  $p1 as xs:string?, 
  $p2 as xs:integer, 
  $p3 as xs:integer, 
  $p4 as xs:string) as xs:string? external; 

TIP: Although ddtek-sql:STUFF() accepts null for its arguments, it is a better idea to provide more precise SequenceType quantifier information, as shown in the preceding XQuery declaration, if the context in which the query is being executed allows such optimization.

An example query using ddtek-sql:STUFF() is:

collection('users')//userid/ddtek-sql:STUFF(.,1,0,
  "userid=") 

The results are:

userid=Jonathan 
userid=Minollo 

Example: ddtek-sql:STDEV()

The syntax of this function is:

STDEV(expression) 

where expression is a numeric expression.

The equivalent XQuery declaration is:

declare function ddtek-sql:STDEV($p as xs:decimal*)  
  as xs:double? external; 

Because STDEV is an aggregate function, the quantifier of the SequenceType for $p must be *.

IMPORTANT: You must use * as a quantifier for the argument of the ddtek-sql:STDEV() declaration. If you do not, a SQL error is raised.

An example of invoking ddtek-sql:STDEV() is:

declare function ddtek-sql:STDEV($p as xs:decimal*)  
  as xs:double? external; 
ddtek-sql:STDEV(collection('historical')//volume) 

Assume that another invocation of ddtek-sql:STDEV() within the same query needs to operate on xs:double arguments. In this situation, you cannot declare the argument of ddtek-sql:STDEV() as xs:decimal*. Instead, declare the argument as xs:anyAtomicType*, as shown:

declare function ddtek-sql:STDEV($p as xs:anyAtomicType*)  
  as xs:double? external; 
ddtek-sql:STDEV( 
  collection(' historical ')//volume/xs:decimal(.)), 
ddtek-sql:STDEV( 
  collection('historical')//actualclose/xs:double(.)) 

DB2 Examples

Example: ddtek-sql:encrypt()

The syntax of this function is:

encrypt(StringDataToEncrypt, PasswordOrPhrase,
 PasswordHint) 

The equivalent XQuery declaration is:

declare function ddtek-sql:encrypt($data as xs:string,  
                                   $password as xs:string,  
                                   $hint as xs:string)  
  as xs:string external; 

An example query using ddtek-sql:encrypt() is:

declare function ddtek-sql:encrypt($data as xs:string,  
                                   $password as xs:string,  
                                   $hint as xs:string)  
  as xs:string external; 
for $x in collection('users')/users 
return 
<user id='{$x/userid}' encrypted='{ddtek-sql:encrypt 
  concat($x/firstname,$x/lastname,$x/othername), 
  'secret','hint')}' /> 

This example returns:

<user  
  id="Jonathan" 
  encrypted= 
 "089B6504E404BFD568696E743A5B64F5A7838CEEEE66DE7F9C5CD92D5E70954C00A81E71"/> 
<user  
  id="Minollo"  
  encrypted="08C04004E404A5D568696E742C93D28C8A2946BF74DB19F6CA6B27BD"/> 

Example: ddtek-sql:variance()

The syntax of this function is:

variance(numeric-expression) 

The equivalent XQuery declaration is:

declare function ddtek-sql:variance($inp as xs:decimal*) as 
  xs:double external; 

An example query using ddtek-sql:variance() is:

declare function ddtek-sql:variance($inp as xs:decimal*) as 
  xs:double external; 
for $x in 
distinct-values(collection('historical')/historical/ticker) 
return 
<ticker-variance  
    ticker='{$x}'  
    variance='{ddtek-sql:variance( 
    collection('historical')/historical[ticker eq 
                $x]/adjustedclose)}' /> 

Because variance is an aggregate function, the quantifier of the SequenceType for $inp must be *.

The example returns:

<ticker-variance ticker="AAPL" variance="136.3814396049211"/> 
<ticker-variance ticker="ADBE" variance="302.2495900777491"/> 
<ticker-variance ticker="AMZN" variance="559.4292663498876"/> 
... 

Oracle Examples

Example: ddtek-sql:DECODE()

The syntax of this function is:

DECODE(expr, search, result 
             [, search, result ]... 
       [, default ] 
      ) 

This function is overloaded; therefore, it must be declared multiple times, for example:

declare function  ddtek-sql:DECODE( 
  $p1 as xs:string,  
  $p2 as xs:string,  
  $p3 as xs:string,  
  $p4 as xs:string) as xs:string external; 
declare function ddtek-sql:DECODE( 
  $p1 as xs:string,  
  $p2 as xs:string,  
  $p3 as xs:string,  
  $p4 as xs:string,  
  $p5 as xs:string,  
  $p6 as xs:string) as xs:string external; 
for $h in collection('holdings')/holdings 
let $ticker := $h/stockticker 
let $description := ddtek-sql:DECODE( 
  $ticker, 
  'PRGS','Progress Software Cooperation',  
  fn:concat('Sorry but ',$ticker,' is not a recognized
    ticker')) 
let $holder := ddtek-sql:DECODE( 
  $h/userid, 
  'Jonathan', 'Mr John',  
  'Minollo', 'Senior Minollo',  
  '????') 
return 
  <who-owns-what  
     description='{$description}'  
     holder='{$holder}' /> 

This example returns:

<who-owns-what description="Progress Software Cooperation" holder="Mr John"/> 
<who-owns-what description="Progress Software Cooperation"  
  holder="Senior Minollo"/> 
<who-owns-what description="Sorry but AMZN is not a recognized ticker" 
  holder="Mr John"/> 
<who-owns-what description="Sorry but AMZN is not a recognized ticker" 
  holder="Senior Minollo"/> 
... 

Using User-Defined Functions

User-defined functions are available for all supported databases. This section provides examples for DB2, Microsoft SQL Server, and Oracle.

For the following examples, assume a user-defined function named FUNC_TAN.

Example: DB2

CREATE FUNCTION FUNC_TAN (X DOUBLE) RETURNS DOUBLE 
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC 
RETURN SIN(X)/COS(X) 

Example: Microsoft SQL Server

CREATE FUNCTION FUNC_TAN (@X float) RETURNS float 
BEGIN RETURN (SIN(@X)/COS(@X)) END 

Example: Oracle

CREATE OR REPLACE FUNCTION FUNC_TAN (X IN FLOAT) RETURN
  FLOAT  
IS TMP FLOAT;  
BEGIN   
TMP := SIN(X)/COS(X);  
RETURN (TMP);  
END; 

The following XQuery expression declares and invokes the function:

declare function ddtek-sql:FUNC_TAN($x as xs:double?)  
  as xs:double? external;  
for $v in collection('some_table')//some_numeric_column 
return ddtek-sql:FUNC_TAN($v) 

Using JDBC Scalar Functions

In addition to allowing you to invoke any SQL function provided by any supported database, including built-in database functions and stored procedures, DataDirect XQuery allows you to use JDBC scalar functions, which are not database specific. Refer to the JDBC specification for a complete list of JDBC scalar functions.

You must declare a JDBC scalar function in the http://www.datadirect.com/xquery/sql-jdbc-escape-function namespace, which has a predefined prefix of ddtek-sql-jdbc.

For example:

declare function ddtek-sql-jdbc:user() as xs:string external; 
<holding-report generated-by='{ddtek-sql-jdbc:user()}'>{ 
  for $h in collection('holdings')/holdings 
  return 
    <holding-info userid="{$h/userid}" ticker="{$h/stockticker}" /> 
}</holding-report> 

Using SQL Table Functions

DataDirect XQuery supports table functions, including user-defined table functions, for DB2, Informix, Oracle, PostgreSQL, and Microsoft SQL Server.

To use SQL table functions, you must declare the function and the structure of the returned table using the tableFunction element in the source configuration file. See "Source Configuration File" for details.

DB2 has a system table function, SYSPROC.DB_PARTITIONS, that returns system information of the DB2 instance. (Refer to your IBM DB2 documentation for details about this table function.) To invoke this function, configure the source configuration file as shown:

<schema name="SYSPROC"> 
... 
   <tableFunction name="DB_PARTITIONS"> 
     <resultSet> 
       <column name="PARTITION_NUMBER" schemaType="short"/> 
       <column name="HOST_NAME" schemaType="string"/> 
       <column name="PORT_NUMBER" schemaType="short"/> 
       <column name="SWITCH_NAME" schemaType="string"/> 
     </resultSet> 
   </tableFunction> 
... 
</schema> 

Once you have configured the source configuration file, declare the function in the ddtek-sql namespace (http://www.datadirect.com/xquery/sql-function) and use a return type of document-node(element()). For example:

declare function ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() as 
  document-node(element()) external; 
ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() 

One possible result for this example is:

<DB_PARTITIONS> 
  <PARTITION_NUMBER>0</PARTITION_NUMBER> 
  <HOST_NAME>the_host</HOST_NAME> 
  <PORT_NUMBER>0</PORT_NUMBER> 
  <SWITCH_NAME>the_switch_name</SWITCH_NAME> 
</DB_PARTITIONS> 

A second example:

declare function ddtek-sql:XVS.SYSPROC.DB_PARTITIONS() as 
  document-node(element()) external; 
for $x in ddtek-sql:XVS.SYSPROC.DB_PARTITIONS()/DB_PARTITIONS 
return 
  <partition number='{$x/PARTITION_NUMBER}' host='{$x/HOST_NAME}' /> 

One possible result for this example is:

<partition number="0" host="the_host"/>