Extending SQL Access to Data with Custom Value Processors

The BBj SQL engine offers a powerful gateway to your keyed data files, allowing you to access and manipulate the data in MKEYED, XKEYED, and VKEYED files using familiar SQL queries. Traditionally, the record data within these files is stored in a raw byte array format. While BASIS's SQL engine interprets this data based on string templates, these templates are inherently limited to supporting only string and numeric types. This limitation can pose a challenge when your data logically represents more complex SQL data types like BOOLEAN, DATE, or other types.

This is where custom value processors become indispensable. A custom value processor allows you to bridge this gap by providing a mechanism to define how the SQL engine should interpret and convert the underlying raw data into a specific SQL data type, even when the template defines it as a string or number.

By assigning a custom ValueProcessor to a column definition, you are providing the SQL engine with a set of instructions on how to transform the string or numeric data parsed by the string template into the desired SQL type. For example, you can take a single character in your legacy file (like 'Y' or 'N') and have it automatically interpreted as a SQL BOOLEAN (TRUE or FALSE) when queried. Similarly, a string representing a date can be converted into a proper SQL DATE object, enabling date-specific operations directly through SQL.

The com.basis.startup.type.sql.ValueProcessor abstract class defines the necessary logic for both converting the raw data into its SQL representation and converting SQL values back into the raw format for storage or updates. This ensures a transparent experience for users accessing the data through SQL.

The ValueProcessor Abstract Class

An implementation of the ValueProcessor abstract class, found in the com.basis.startup.type.sql package, facilitates the conversion of data between its native format within a data file and standard SQL data types.

A simple example of a ValueProcessor is transforming a simple C(1) type column that stores "Y" or "N" characters into a true SQL BOOLEAN type (true/false). By implementing this interface, you gain fine-grained control over how your data is interpreted and presented.

Key Methods to Implement

When creating your own ValueProcessor implementation, you'll need to override several abstract methods. These methods define the core behavior of your custom processor:

The ValueProcessorBBjSQLFactory Interface

To make your custom value processor available to BBjServices, you will need to create an implementation of the com.basis.startup.type.sql.ValueProcessorBBjSQLFactory interface. This factory contains a single method to return a list of the custom value processors that you have implemented.

Example: ValueProcessor Implementation

The following example is the source code used by the built-in YNBooleanValueProcessor. This processor takes values stored as “Y” and “N” and returns them as true SQL BOOLEAN type true and false values, respectively. If your data is stored as 1 and 0 rather than “Y” and “N” this example would be a good foundation for creating your own 1/0 boolean value processor. We’ve changed the package and class names here to create a new value processor.

package com.foo;

import java.sql.SQLException;
import java.sql.Types;
import com.basis.startup.type.sql.BBjSQLLiteral;
import com.basis.startup.type.sql.ValueProcessor;

public class MyProcessor extends ValueProcessor
{
   @Override
   public Object computeReturnedValue(Object p_value) throws SQLException
   {
       String strVal = p_value == null ? null : p_value.toString();
       if (strVal == null || strVal.length() == 0)
       {
           return false;
       }
       else
       {
           strVal = strVal.toLowerCase();
           return strVal.startsWith("y") || strVal.startsWith("t");
       }
   }

   @Override
   public Object computeRawValue(BBjSQLLiteral p_value) throws SQLException
   {
       if (p_value == null)
       {
           return false;
       }
       String strVal = p_value.getString();
       return Boolean.parseBoolean(strVal) ? "Y" : "N";
   }

   @Override
   public int getSqlType()
   {
       return Types.BOOLEAN;
   }

   @Override
   public String getDisplayName()
   {
       return "My Custom Processor";
   }

   @Override
   public String getDescription()
   {
       return "My custom processor.";
   }

   @Override
   public boolean isLexicallyOrdered()
   {
       return true;
   }
}

Example: ValueProcessorBBjSQLFactory Implementation

package com.foo;

import com.basis.startup.type.sql.ValueProcessorBBjSQLFactory;
import com.basis.startup.type.sql.ValueProcessor;
import java.sql.SQLException;
import java.util.List;
import java.util.Collection;

public class MyFactory implements ValueProcessorBBjSQLFactory {

   @Override
   public Collection<ValueProcessor> getValueProcessors() {
       return List.of(new MyProcessor());
   }
}

Add and Use Your Value Processor to BBjServices

For the SQL engine to use your value processor, package the ValueProcessor and ValueProcessorBBjSQLFactory implementations as a standard Java JAR file and add it to the BBjServices classpath:

Next, configure your database to use the ValueProcessorBBjSQLFactory:

Next, create a type definition that uses this value processor. From the database settings page, select the “Types” tab at the bottom of the page. To add your new type definition, click the add button (+).

Finally, once you’ve saved your changes, you can assign this type to columns in your tables:

See Also

EM: BBjServices > Java Settings

EM: Databases/SQL > Databases