Sample Custom Scalar Function


The following is the Java source code for the SUBSTRING scalar function. To implement a custom scalar function, follow the pattern in this sample:

 

package com.basis.sql.sqlengine2.scalarfunction;
import com.basis.sql.sqlengine2.value.FunctionConnectionInformation;
import com.basis.sql.sqlengine2.value.SQLValue;
import com.basis.sql.sqlengine2.value.FixedSQLValue;
import com.basis.sql.sqlengine2.value.ScalarFunctionSQLValue;
import com.basis.sql.sqlengine2.value.TypeInfo;

import java.sql.Types;
import java.sql.SQLException;

import java.util.List;

/**
 * Representing a scalar function (SUBSTRING).
 *
 * @author Christopher Hardekopf
 */
public class SUBSTRING extends ScalarFunctionSQLValue
{
    /**
     * Defines the parameter options available for this function. Each element in the array
     * consists of an array of parameter types. If a parameter supports more than one
     * possible type, the types are OR'd together. Available types are ANY_TYPE,
     * CHARACTER_TYPE, NUMERIC_TYPE, DATETIME_TYPE, and BINARY_TYPE.
     */
    private static final int [][]USAGE =
    { { CHARACTER_TYPE|BINARY_TYPE, NUMERIC_TYPE } ,
      { CHARACTER_TYPE|BINARY_TYPE, NUMERIC_TYPE, NUMERIC_TYPE } };

    // Contains the length of the value returned.
    private int m_length;

    /**
     * Constructor
     *
     * @param p_args The list of arguments passed into the method by the SQL parser. See getFixedValue()
     *               for how to access these values.
     * @param p_info Various pieces of info related to the SQL connection. See the m_info variable to access it.
     *
     * @throws SQLException If there is a problem.
     */
    public SUBSTRING (List<SQLValue> p_args, FunctionConnectionInformation p_info)
        throws SQLException
    {
        super(p_args,p_info,USAGE);

        if (isTypeKnown())
        {
            m_length = -1;
            if (getArgumentCount() > 2)
            {
                SQLValue arg3 = getArgument(2);
                if (arg3 instanceof FixedSQLValue)
                {
                    m_length = arg3.getInt();
                }
            }

            TypeInfo arg = getArgument(0);
            if (m_length < 0)
            {
                m_length = arg.getMaxLength();
            }
            setType(Types.VARCHAR);
        }
    }

    /**
     * Method to to get a FixedSQLValue for the _current_
     * value.
     */
    @Override
    public FixedSQLValue getFixedValue ()
        throws SQLException
    {
        // Each getArgument() call returns the SQLValue for the specified zero-indexed argument.
        FixedSQLValue arg = getArgument(0).getFixedValue();
        if (arg.isNull())
        {
            return arg;
        }

        try
        {
            FixedSQLValue arg2 = getArgument(1).getFixedValue();
            if (arg2.isNull())
            {
                return arg2;
            }
            try
            {
        String value1 = arg.getBinaryString(m_info.getCharset());
        int value2 = arg2.getInt() - 1;
        int value3 = value1.length();

                if (getArgumentCount() == 3)
                {
                    FixedSQLValue arg3 = getArgument(2).getFixedValue();
                    if (arg3.isNull())
                    {
                        return arg3;
                    }
                    try
                    {
                        value3 = arg3.getInt();
                    }
                    finally
                    {
                        arg3.clear();
                    }
                }

        if (value2 < 0)
        {
            value2 = 0;
        }
        if (value2 > value1.length())
        {
            value2 = value1.length();
        }
        if (value2 + value3 >= value1.length())
        {
                    value3 = value1.length() - value2;
                    if (value3 < 0)
                    {
                        value3 = 0;
                    }
        }

                return FixedSQLValue.getInstance(value1.substring(value2,(value2+value3)),true, getLocale(), m_info);
            }
            finally
            {
                arg2.clear();
            }
        }
        finally
        {
            arg.clear();
        }
    }

    // Additional functions to be overloaded.

    @Override
    public int getNativeMaxLength ()
        throws SQLException
    {
        return m_length;
    }

    /**
     * Is this function memoizable meaning sortable?
     */
    @Override
    protected boolean memoizable ()
    {
        return true;
    }
}



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.