Using External Functions


This topic explains the types of external functions supported by DataDirect XQuery and how to use them. This topic covers the following subjects:

Supported External Functions

External functions are functions that are implemented outside the query environment. DataDirect XQuery supports two types of external functions: Java and SQL.

Java functions might be used to return system information, to invoke a Web service call, or simply to make available a function that is not in the XQuery function library.

SQL functions might be used to invoke a stored procedure or to make available a function that is not in the XQuery function library.

All external functions are namespace qualified, and the namespace that is used tells DataDirect XQuery whether the external function is written in Java or in SQL. Before calling an external function, it must be declared in the query.

Example: Java Function (Static Method)

Suppose an application needs to return information about the Java environment in which a query runs. The following Java code defines a class that contains the function to retrieve this information in Java:

public class myClass extends Object { 
... 
    public static String myExternalFunction() { 
        StringBuffer returnBuffer = new StringBuffer(); 
        Properties systemProperties = System.getProperties(); 
        returnBuffer.append("VM Version:" 
            + systemProperties.get("java.vm.version") + "\n"); 
        returnBuffer.append("VM Vendor:" 
            + systemProperties.get("java.vm.vendor") + "\n"); 
        returnBuffer.append("VM Name:"  
            + systemProperties.get("java.vm.name")); 
        return returnBuffer.toString(); 
    } 
} 

Now, the function must be declared, as shown in the following query:

declare namespace ex="ddtekjava:myClass"; 
declare function ex:myExternalFunction() as xs:string external; 
let $infoString := ex:myExternalFunction() 
return              
  let($infoString := ex:myExternalFunction() 
  return <vm_info>{$infoString}</vm_info>) 

In the preceding XQuery expression, the XQuery binds a namespace prefix (ex) to the fully qualified name of the class that defines the function.

Example: SQL Function

A SQL function can be called in a similar way to calling a Java function. The namespace for a SQL function is the predefined namespace, in this case http://www.datadirect.com/xquery
/sql-function, which is bound to the predefined prefix ddtek-sql. The following example calls the SQL function rtrim().

declare function ddtek-sql:reverse($namevalue as xs:string) as 
  xs:string external;

for $username in collection('users')//lastname
return
<last_name>{ddtek-sql:reverse($username)}</last_name>  

Using Java Functions

DataDirect XQuery supports Java static methods, Java instance methods, and Java constructors.

The DataDirect XQuery type ddtek:javaObject allows you to invoke Java methods. The predeclared DataDirect XQuery namespace prefix is ddtek, and is bound to http://www.datadirect.com/xquery. The examples in this section demonstrate the usage of ddtek:javaObject.

Declaring Java Functions

Two steps are required to declare Java functions:

  1. Import the class into the XQuery environment.
  2. Declare the function.

1. Importing the Class

Before you can declare a Java class, you must import it into the XQuery environment. To do this, you must declare a namespace with a specific URI. The syntax of an import is:

declare namespace prefix = "ddtekjava:java class name"; 

where:

prefix is a namespace prefix to associate with the Java class.

java class name contains the complete package and class name of the Java class to import. The specified Java class must be accessed using the Java class loader in the environment in which the query is executed. Typically, this means that the CLASSPATH must contain a reference to the directory or jar file where the Java class can be found. In J2EE server environments, other requirements might exist – the jar or class file might have to be stored in a specific directory, for example, as shown here:

declare namespace file = "ddtekjava:java.io.File"; 

If DataDirect XQuery cannot find the Java class, it generates a static error.

2. Declaring the Function

Before you can invoke a Java function in a query, you must declare it. How you declare it depends on whether the Java function is a static method or an instance method.

Static Method

To declare a static method, use the following syntax:

declare namespace file = "ddtekjava:java.io.File"; 
declare function namespace:function name(argument list) as return type 
  external; 

For example:

declare namespace file = "ddtekjava:java.io.File"; 
declare function file:createTempFile($prefix as xs:string,$suffix as 
  xs:string) as ddtek:javaObject external; 

See also Example: Java Function (Static Method).

Instance Method

To declare an instance method, follow these steps:

  1. Import the class to a namespace (see 1. Importing the Class).
  2. In the cases where you need to explicitly create a new Java object instance, declare a function mapping to a Java constructor unless the Java object type you are declaring has a defined XQuery mapping. A class instance can be an XML type for which a mapping is defined.
  3. If step 2 is required, declare a function mapping to an instance method declaring ddtek:javaObject as the value for the first parameter. Otherwise, the value of the first parameter is the appropriate XQuery data type.
  4. Invoke the function using the class instance on which the instance method is invoked as the first argument.

The numbers in the following example correspond to the preceding steps:

declare namespace BigInteger = "ddtekjava:java.math.BigInteger"; 1 
declare function BigInteger:BigInteger($v as xs:string) 2 
    as ddtek:javaObject external; 
declare function BigInteger:gcd(  
    $this as ddtek:javaObject,  
    $val as xs:integer) as xs:integer external; 3 
 
    BigInteger:gcd(BigInteger:BigInteger("12"),4) 4 

The following example does not include step 2 because the Java object to be declared has a defined XQuery mapping.

declare namespace BigInteger = "ddtekjava:java.math.BigInteger"; 1 
    declare function BigInteger:gcd( 
      $this as xs:integer,  
      $val as xs:integer) as xs:integer external; 3 
 
     BigInteger:gcd(12,4) 4 

Mapping Types Between Java and XQuery for Java External Functions

Before DataDirect XQuery passes XQuery arguments to a Java method, it converts the arguments from the XQuery data type to a Java type using the SequenceType specified for the external function when it was declared.

Table 12-5 shows how to map arguments and return types from the Java function declaration to XQuery SequenceTypes to be used in the XQuery function declaration.

Table 12-5. Mapping Types Between Java and XQuery  
Java Type
XQuery SequenceType
boolean
xs:boolean
byte
xs:byte
byte[]
ddtek:javaObject
xs:base64binary
xs:hexBinary
double
xs:double
float
xs:float
int
xs:int
long
xs:long
short
xs:short
void
empty-sequence() 1
java.lang.Boolean
ddtek:javaObject
xs:boolean[?]
java.lang.Byte
ddtek:javaObject
xs:byte[?]
java.lang.Double
ddtek:javaObject
xs:double[?]
java.lang.Float
ddtek:javaObject
xs:float[?]
java.lang.Integer
ddtek:javaObject
xs:int[?]
java.lang.Long
ddtek:javaObject
xs:long[?]
java.lang.Object 2
ddtek:javaObject
java.lang.Short
ddtek:javaObject
xs:short[?]
java.lang.String
ddtek:javaObject
xs:untypedAtomic[?]
xs:string[?]
java.math.BigDecimal
ddtek:javaObject
xs:decimal[?]
java.math.BigInteger
ddtek:javaObject
xs:integer[?]
java.net.URI
ddtek:javaObject
xs:anyURI[?]
java.xml.namespace.QName
ddtek:javaObject
xs:QName[?]
javax.xml.transform.Source3
ddtek:javaObject
document-node( )
org.w3c.doc.ProcessingInstruction
ddtek:javaObject
processing-instruction() [?]
org.w3c.dom.Attr
ddtek:javaObject
attribute() [?]
org.w3c.dom.Comment
ddtek:javaObject
comment() [?]
org.w3c.dom.Document
ddtek:javaObject
document-node() [?]
org.w3c.dom.Element
ddtek:javaObject
element() [?]
org.w3c.dom.Node
ddtek:javaObject
document-node() [?]
element() [?]
attribute() [?]
comment() [?]
text() [?]
processing-instruction() [?]
org.w3c.dom.Text
ddtek:javaObject
text() [?]
boolean, byte, double, float, int, long, short 4
xs:anyAtomicType? d
byte[], java.lang.Byte, java.lang.Double, java.lang.Float, java.lang.Integer, java.lang.Long, java.lang.Short, java.lang.String, java.math.BigDecimal, java.math.BigInteger, java.net.URI, java.xml.namespace.QName
xs:anyAtomicType? d
Any Java data type in this table
item() d
Any Java object type in this table
item() ? d
Any Java type in this table
item()* a
item() + a
java type [] 5
ddtek:javaObject
XQuery type of this table(*|+)
1 Only for return types.
2 java.lang.Object or another Java class not listed in this table.
3 Must be one of the following interfaces:
javax.xml.transform.stream.StreamSource,
javax.xml.transform.sax.SAXSource,
javax.xml.transform.dom.DOMSource,
javax.xml.transform.stax.StAXSource, or
com.ddtek.xquery.StAXSource.
3 Note that only javax.xml.transform.dom.DOMSource is supported for the function’s parameters.
4 Useful to declare Java methods overloaded on argument type. See Resolving Function Calls.
5 When the Java parameter type or method return type is an array, either declare the XQuery function to receive or return one of the matching types from this table and add a * or + quantifier.You can also use ddtek:javaObject with a * or + quantifier.

In cases where Table 12-5 lists multiple mapping options, consider the following information:

Resolving Function Calls

Before DataDirect XQuery attempts to resolve the Java method used to invoke a given XQuery function call, DataDirect XQuery identifies the external function declaration.

Then, like any other XQuery function call, the static types of the argument expressions are matched with the SequenceType of the function declaration parameters. Static type errors are detected and reported.

Finally, the Java method must be resolved. Sometimes, DataDirect XQuery cannot determine how to map an XQuery function declaration and the associated function call to a Java instance method. Ambiguity can occur for the following reasons:

DataDirect XQuery uses the following steps to determine the Java method to invoke for a given function call. Except for the first step, each of the consecutive steps reduces the number of Java functions that are potential candidates to which to map the XQuery function call.

  1. Add all public (static and instance) methods and public constructors of the Java class identified through the function’s namespace.
  2. Use a public constructor if the name of the function equals the class name. In the absence of matching public constructors, DataDirect XQuery considers Java methods with the same name.
  3. Retain only Java methods whose names match the name of the function if the name of the function being invoked does not equal the class name.
  4. Remove all instance methods, unless the first argument in the function call is typed as ddtek:javaObject and the Java class associated with the ddtek:javaObject equals the class identified by the namespace of the function (see Notes About Using Java Instance Methods).
  5. Remove all static methods and constructors whose number of parameters does not match the number of parameters in the XQuery function declaration.
  6. Remove all instance methods whose number of parameters does not equal the number of parameters of the XQuery function declaration minus one. (This takes into account the first artificial "this" argument that is required when invoking an instance method from XQuery.)
  7. Remove all Java methods and constructors for which the SequenceType of the argument as specified in the function declaration does not match the type of the Java method. The type matching requires a mapping from an XQuery SequenceType to a Java type. This mapping is documented in Table 12-5. Note that when the function argument is declared as item(), xs:anyAtomicType, or ddtek:javaObject, DataDirect uses the static type of the argument expression of the function call instead of the SequenceType of the function declaration. This typically is more accurate and allows correct method resolution in more scenarios.

Unless exactly one method remains after the previously described procedure, one of the following static errors is generated:

Static error during resolving of external Java function. 
Ambiguous call to Java external function '<function>' 

or

Static error during resolving of external Java function. No 
matching Java external function found for '<function>' 

Note that at execution time:

NOTE: Generic methods introduced with J2SE 5.0 are not supported.

Notes About Using Java Instance Methods

See also Resolving Function Calls.

Disabling Java Functions

You can disable the ability to invoke Java functions, for example, for security reasons, by specifying the AllowJavaFunctions property of DDXQDataSource. See DDXQDataSource and DDXQJDBCConnection Properties.

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:

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> 

Microsoft SQL Server Examples

This section presents examples of Microsoft SQL Server functions and describes their equivalents in XQuery.

Example: ddtek-sql:STUFF

The STUFF function inserts the contents of one string in another. 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 STDEV function returns the standard deviation of a set of values. 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

This section presents examples of DB2 functions and describes their equivalents in XQuery.

Example: ddtek-sql:encrypt

The encrypt function returns an encrypted value. 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 variance function returns the variance of a set of numbers. 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

This section presents examples of Oracle functions and describes their equivalents in XQuery.

Example: ddtek-sql:DECODE

The DECODE function behaves as a SQL IF-THEN-ELSE statement. 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, 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.

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"/>