Arithmetic operators +, -, /, %, * are supported as well as
generic expressions. Functions are not specified according to SQL
keyword syntax but rather as a function identifier with parameters
similar to programming languages. The format of functions is:
function (param1, param2, ...).
The following comparison operators are available:
Table 6.1. Comparison Operators
|<=||less than or equal to|
|>=||greater than or equal to|
|IS [NOT] NULL||tests if value is NULL|
|IS [NOT] [TRUE | FALSE]||tests if value is true or false|
The following mathematical operators are supported:
Table 6.2. Mathematical Operators
|+||addition (and string concatenation for strings)|
|%||modulus (remainder of integer division)|
The following are a few of the mathematical functions supported. A complete list of functions is available on the web site.
Table 6.3. Mathematical Functions
|abs(x)||Same as x||abs(-17.4)||17.4||Absolute value|
|ceil(x)||Same as input||ceil(-42.8)||-42||Smallest integer not less than argument|
|exp(x)||Same as input||exp(1.0)||2.718||exponential|
|floor(x)||Same as input||ln(2.0)||0.69314||natural logarithm|
|log(x)||Same as input||log(100.0)||2||base 10 logarithm|
|power(a, b)||double precision||power(9,3)||729||a raised to the power of b|
|random()||double precision||random()||random value between 0.0 and 1.0|
|sqrt(x)||double precision||sqrt(2.0)||1.4142||square root|
The following are a few of the string functions supported. A complete list of functions is available on the web site.
Table 6.4. String Functions
|<str> + <str>||String||'Unity' + 'JDBC'||UnityJDBC||String concatenation|
|ascii(string)||int||ascii('xyz')||120||ASCII code of the first character of the input string|
|length(string)||int||length('UnityJDBC')||9||Length of string in characters|
|lower(string)||String||lower('JDBC')||jdbc||Convert string to lower case|
|position(search, target)||int||position('J','UnityJDBC')||5||Location of search in target (indexed from 1)|
|replace(source, search, replace)||String||replace( 'abUnityabJDBC', 'ab', 'XX')||XXUnityXXJDBC||Replace all occurrences of search string in source string with replace string|
|substring(string, start)||String||substring('UnityJDBC',6)||JDBC||substring starting at position start|
|substring(string, start, count)||String||substring('UnityJDBC,6,2)||JD||substring starting at position start and taking count characters|
|trim(string)||String||trim(' UnityJDBC ')||UnityJDBC||remove leading and trailing spaces from string|
|ltrim(string) OR trim(string, 'LEADING')||String||trim(' UnityJDBC ')||'UnityJDBC '||remove leading spaces from string|
|rtrim(string) OR trim(string, 'TRAILING')||String||trim(' UnityJDBC ')||' UnityJDBC'||remove trailing spaces from string|
|trim(string, ['BOTH', 'LEADING', 'TRAILING'], [<chars>])||String||trim('aaaUnityJDBCbbb', 'BOTH', 'ab')||UnityJDBC||remove leading, trailing or both from string where characters removed may be optionally specified in <chars>|
|upper(string)||String||upper('jdbc')||JDBC||Convert string to upper case|
Pattern matching is supported using the
'abcdef' LIKE 'ab%' is true. The
%' is used to match one or more characters, and
_' is used to match a single character.
Data type conversions are performed using the
CAST(x,y) function. The
function takes any object as the first parameter and takes a string
literal representation of the type to cast to as the second parameter.
Note that the type must be put in single quotes as a string literal.
CAST(45, 'VARCHAR') creates
Possible type names are:
The following are a few of the date functions supported. A complete list is on the website.
Table 6.5. Date Functions
|CURRENT_TIMESTAMP||TIMESTAMP||CURRENT_TIMESTAMP||2011-07-06 12:53:45||Returns the current date. Format: "yyyy-MM-dd HH:mm:ss"|
|CURRENT_TIME||TIME||CURRENT_TIME||12:53:45||Returns the current time. Format: "HH:mm:ss"|
|CURRENT_DATE||DATE||CURRENT_DATE||2011-07-06||Returns the current date. Format: "yyyy-MM-dd"|
|YEAR||INT||YEAR('2011-07-06)||2011||Returns the year of the given date expression.|
|MONTH||INT||MONTH('2011-07-06)||7||Returns the month of the given date expression.|
|DAY||INT||DAY('2011-07-06)||6||Returns the day of the given date expression.|
|DATEADD||TIMESTAMP||DATEADD('2011-07-06', INTERVAL 3 days)||2006-07-06 12:53:45||Allows the addition of a given date field to a datetime expression. Intervals are supported and are translated as necessary for systems that do not support them.|
The following aggregate functions are supported:
Table 6.6. Aggregate Functions
|Function||Argument Type||Return Type||Description|
|avg(x)||int, float, double precision type||int for integer types, double precision for float/double types||Average of all input values|
|count(*)||N/A||int||Count of number of input values|
|count(x)||any||int||Count of number of non-null input values|
|group_concat(x)||any||varchar||Returns a comma-separated list of all input values.|
|max(x)||any comparable type||same as input||Maximum of all input values|
|min(x)||any comparable type||same as input||Minimum of all input values|
|sum(x)||int, float, double precision type||int for integer types, double precision for float/double types||Sum of all input values|
For queries on a single database, UnityJDBC parses functions and passes them directly to the database engine for execution. Thus, all functions that can be executed at the source are available. UnityJDBC and user-defined functions are used only when applying functions to data after it is extracted from the sources. UnityJDBC will parse queries containing functions that it itself cannot process in its internal database engine. These functions are passed down to the database engine and executed locally. Only functions that require inputs from more than one database are processed in the UnityJDBC database engine. All other functions are passed down to the sources.
UnityJDBC supports user-defined functions (UDFs). Adding your own user-defined
function is easy. There are two types of functions: row functions and aggregate
functions. A row function operates on one row at a time for its data and includes
ABS(). An aggregate
function is used in
GROUP BY queries and aggregates an expression
(usually a column) across multiple rows in a group to produce a single value. Examples
To create a row function, you must create a Java class that extends the Function
class. A template example is in the file
This class must implement a constructor, an
evaluate() method, and provide information on
the parameters it requires. Once completed, as long as this function is available in the
CLASSPATH, UnityJDBC will search for it when called. A similar
template is available for aggregate functions,
A_Aggregrate_Template.java. Sample code is provided in the
UnityJDBC has a database of known functions. This database contains information on what functions are supported on each data source. This is how UnityJDBC processes functions:
UnityJDBC does not support function - If a function is not in the UnityJDBC database, it is passed down as-is to the underlying source. If the source is able to execute it successfully, the query continues. If not, an error is thrown.
UnityJDBC supports function, data source requires translation - If the function requested in the query is not directly supported by the data source (different name, different parameters, etc.), but UnityJDBC contains a mapping in its database, the function is translated to the correct form on the data source and executed on the data source.
UnityJDBC supports function, data source does not support function - If UnityJDBC supports the function but not the data source, then the query is optimized to perform as much of the processing as possible on the source, but the function execution is performed internally in UnityJDBC. This way your query can execute on data sources with the help of UnityJDBC that do not support the required functions.
UnityJDBC is running with local execution - If the local execution flag is set for the UnityStatement object executing the query, all functions except aggregate functions are executed by UnityJDBC. This setting may be useful to reduce load on the source or to guarantee absolute consistency of function execution across different sources.
The UnityJDBC function database is encrypted and
stored in the
unityjdbc.jar. To add user-defined functions to the
function database, create a
mapping.xml file in the JRE classpath
(execution directory, etc.) that stores the information on the
function. An example is included in the release and more information
is available on the web site.
UnityJDBC attempts to support most of the SQL standard. If there
is a function or feature not supported, it is possible to use the
function to pass the query string directly to the data source
by-passing UnityJDBC validation. This may be used to support non-standard functions or SQL syntax. Below are several
Query: SELECT N1.n_nationkey, NP('OrderDB','n_name','varchar') FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1; Result: (n_name is substituted directly into the query) SELECT n_nationkey, n_name FROM Nation N1 WHERE N1.n_nationkey = 1 Query: SELECT N1.n_nationkey, NP('OrderDB','(select n_name from nation n2 where N1.n_nationkey = N2.n_nationkey)','varchar') as name FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1 Result: SELECT N1.N_NATIONKEY, (select n_name from nation n2 where N1.n_nationkey = N2.n_nationkey) name FROM NATION N1 WHERE N1.N_NATIONKEY = 1 Query: SELECT N2.* FROM NP('OrderDB', '(select n_name,n_nationkey from nation)','n_name,n_nationkey') N1, NP('PartDB', '(select n_name,n_nationkey from nation)','n_name,n_nationkey') as N2 where N2.n_nationkey < 2 and N1.n_nationkey = N2.n_nationkey; Result: // Substitutes subquery for each of the two data sources (OrderDB and PartDB). // The result of the two subqueries is then joined at the UnityJDBC level. // OrderDB: SELECT N2.n_name, N2.n_nationkey FROM (select n_name,n_nationkey from nation) N2\n WHERE N2.n_nationkey < 2 // PartDB: SELECT N1.n_nationkey FROM (select n_name,n_nationkey from nation) N1
More information on non-parsed functions is available on the web site.