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:
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
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
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();
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