Superseded: SQL — Custom Scalar Functions

As of BBj 19.10, this information on Custom Scalar Functions has been superseded.
See SQL - Custom Functions for current information.


In addition to supporting the set of scalar functions specified in the ODBC and JDBC standards, it also allows for the creation of user-defined or custom scalar functions. This allows developers to easily extend the set of scalar functions provided with the BBj SQL Engine with custom functions.

The example described below provides a way to better understand the power of custom scalar functions.

If a developer needs a way to store encrypted passwords in a database using a one-way hash algorithm such as MD5, this could be done programmatically using Visual PRO/5, BBj, or another ODBC or JDBC compliant programming language. However, this can also be accomplished using a custom scalar function that is executed by the BBj SQL Engine.

Creating Custom Scalar Functions

All BBj SQL Engine custom scalar functions must be written using the Java programming language. A custom scalar function consists of a single Java class that meets the following requirements:

  • Extends the class: com.basis.sql.scalarfunction.ScalarFunction.

  • Implements six methods defined in the base class (described below).

  • Uses all capital letters for its class name.

  • The class is located in a package called com.basis.sql.scalarfunction.

Although the process of creating a custom scalar function does not involve a lot of code, it does require a basic understanding of the Java language, and in particular, an understanding of the java.util.List class. Visit http://java.sun.com for more information, such as complete documentation and tutorials. This site also provides a way to get the current Java 2 Software Development Kit (J2SDK), which is required to compile any custom scalar functions.

To begin, the CLASSPATH must include the BBjSQL.jar and BBjUtil.jar files and the class should include the following imports:

   import java.sql.*;

   import com.basis.sql.scalarfunction.ScalarFunction;

   import com.basis.sql.expression.*;

   import com.basis.sql.*;

   import com.basis.util.common.*;

Listed below is the definition of each method that must be implemented and a complete description of what the method is responsible for accomplishing:

public void isValid (java.util.List p_argumentList) throws java.sql.SQLException;

This method is responsible for verifying the validity of the arguments passed into the function in an SQL statement. This list consists of zero or more arguments, each one is a com.basis.sql.expression.Expression object.

Since the SQL parser can accept an arbitrary number of arguments to scalar functions, custom scalar functions can allow for various numbers and types of arguments. For example, the DATE scalar function can take one Julian number, or one Julian number and a date mask string.

The isValid() method should check the argument list for the correct number of arguments. This can be done by calling the size() method on the p_argumentList object. This method must also verify that the data type of each argument is valid. For example, if the user passes a Julian number and a floating point number as arguments to the DATE function, this method should throw a java.sql.SQLException with an appropriate error message since it should take a Julian number and a string, only.

In order to check for type validity of each argument in the argument list, the developer should cast the appropriate argument from the list to a com.basis.sql.expression.Expression and call the getType(), isNumeric(), isCharacter, or isDatetime() method on the result. The getType() method returns the actual SQL data type of the argument that is one of the type constants found in the java.sql.Types class. The isNumeric() method returns true if the argument is a numeric type (INTEGER, FLOAT, REAL, NUMERIC etc.). The isCharacter() method returns true if the argument is a string type (CHAR, VARCHAR, etc.), and the isDatetime() method returns true if the argument is a date (DATE, TIME, or TIMESTAMP).
Please see Sample Custom Scalar Function Example at the end of this topic for an example of how to use each method.

public com.basis.sql.expression.LiteralExpression evaluate() throws SQLException;

The evaluate() method is responsible for doing the actual processing of the arguments and returning the desired result. This method can assume that the arguments passed in during the call to isValid() have already been type checked.

In order to access each argument in the argument list, the developer should call the getArgument() method passing in the desired (zero based) argument number in as a parameter. This method returns a com.basis.sql.expression.LiteralExpression object, representing the value of the argument. To get a usable value from this, the developer should call getString(), getInt(), getDouble(), getBigDecimal(), getDate(), getFloat(), or getLong(). Please see Sample Custom Scalar Function at the end of this topic for an example of how to use each method.

The evaluate() method should return a new instance of com.basis.sql.expression.LiteralExpression. The constructor for a LiteralExpression can take a String, int, Integer, long, Long, double, Double, Time, java.sql.Date, or BBjNumber.

NOTE: A BBjNumber works just like a java.math.BigDecimal (see BigDecimal documentation in the Java API Documentation for details and constructor options) but is faster. A BBjNumber is a true number (i.e. it does not use IEEE floating point format). When you do math operations on BBjNumber objects, you do not get the rounding problems that you can with IEEE types (i.e. float, double, etc.).

public int getReturnType();

This method is responsible for returning the java.sql.Types type that the return value of this scalar function will be.

public int getMaxSize();

This method is responsible for returning the maximum size that the returned value can be. For numeric types, this should be 0. For character types, this should be the maximum number of characters that can be returned by this scalar function.

public boolean canReturnNumeric();

and

public Boolean canReturnString();

In the base class (ScalarFunction) these methods return false by default. The developer should override only one of these methods, returning true, for the appropriate method based on the return type in getReturnType().

Sample Custom Scalar Function

package com.basis.sql.scalarfunction;

import com.basis.sql.codes.*;
import com.basis.sql.expression.*;
import java.sql.*;
import java.util.*;


/**
 * Returns the cosine of <i>float_exp</i>, where <i>float_exp</i> is an
 * angle expressed in radians.
 * <p>
 * USAGE: COS(<i>float_exp</i>)
 */
public class COS extends ScalarFunction
{
    public void isValid(List p_argumentList) throws SQLException
    {
        if (p_argumentList.size() != 1)
        {
            throw new SQLException("Invalid number of arguments. 1 argument expected.",
                                   SqlState.GENERAL_WARNING,
                                   VendorCode.GENERAL_WARNING);
        }
        
        if (!((Expression)p_argumentList.get(0)).isNumeric())
        {
            throw new SQLException("Invalid argument type.",
                                   SqlState.GENERAL_WARNING,
                                   VendorCode.GENERAL_WARNING);
        }

        m_size = ((Expression)p_argumentList.get(0)).getMaxSize();
    }    
    
    public LiteralExpression evaluate() throws SQLException
    {
        Double value = new Double(Math.cos(getArgument(0).getDouble()));
        return new LiteralExpression(value);
    }
    
    public int getReturnType()
    {
        return Types.FLOAT;
    }
    
    public int getMaxSize()
    {
        return m_size;
    }

    public boolean canReturnNumeric ()
    {
        return true;
    }
}