Using eXtremeSQL String, Math and User-defined Functions

String and math functions are often used to modify, convert or compute derived values in the result set returned from a SQL query or to assign values in a SQL insert or update. The following table lists the eXtremeSQL built-in functions:

Name	Argument Type	Return Type	Description
abs(i)	integer	integer	absolute value of the argument
abs(r)	real	real	absolute value of the argument
sin(r)	real	real	sin (rad)
cos(r)	real	real	cos (rad)
tan(r)	real	real	tan (rad)
asin(r)	real	real	arcsin
acos(r)	real	real	arcos
atan(r)	real	real	arctan
exp(r)	real	real	exponent
log(r)	real	real	natural logarithm
ceil(r)	real	real	the smallest integer value that is not less than r
floor(r)	real	real	the largest integer value that is not less than r
integer(r)	real	integer	conversion of real to integer
integer(s)	string	integer	conversion of string to integer
length(a)	array	integer	number of elements in array
length(s)	string	integer	length of string
lower(s)	string	string	lowercase string
real(i)	integer	real	conversion of integer to real
real(s)	string	real	conversion of string to real
string(i)	integer	string	conversion of integer to string
string(r)	real	string	conversion of real to string
substr(s,m[,n])	string,integer[,integer]	string	substring of s, beginning at character m, n characters long (if n is omitted, to the end of string)
upper	string	string	uppercase string

The built-in functions can be called from normal SQL select statements as follows:

     
    select string(age) from Persons;
    select upper(name) from Persons;
    select abs(amount) from Contributions;
     

 

User-defined functions

eXtremeSQL also allows developers to create their own user-defined functions. The user-defined function (UDF) must be defined as returning type “static String*” and can accept arguments of type “Value*”. Then it must be “registered” with the eXtremeSQL runtime by declaring a function of type “static SqlFunctionDeclaration udf()” with arguments specifying the type returned, the name used to call it, a pointer to the function and the number of arguments required.

The following code snippet defines then registers a UDF:

     
    // Delcare and instantiate the user-defined DateFormat function
    static String* dateformat( McoSql::Value* date)
    {
        char str[64];
        int date_val = (int)date->intValue();
        int yy = date_val / 10000;
        int mm = date_val / 100 % 100;
        int dd = date_val % 100;
         
        // Format dd.mm.yyyy assuming all dates are after 2000
        sprintf( str, "%d.%d.20%02d", dd, mm, yy );
        return String::create( str );
    }
     
    static SqlFunctionDeclaration udf
    (
        tpString,          // tpInt is the return value of the UDF
        "dateformat",      // the name of the function as we’ll use it in a query
        (void*)dateformat, // the function pointer
        1                  // the number of arguments to the UDF
    );
     

The UDF can then be called in a normal SQL select statement. For example, the following statement formats the date values in the result set by calling the UDF:

     
    select dateformat(date_val) from Contributions;