The SimbaEngine SDK™ allows you to build a custom ODBC driver with which you can connect your data source to any ODBC application. The SQLEngine within the SimbaEngine SDK is flexiblie, and allows you to implement your own custom scalar and aggregate functions for your data source. In many new data sources, new functionality is revealed through scalar functions that are not in the standard SQL specification. For example, a new in-memory database can calculate the average of N columns over a span of N tables extremely efficiently by calling a scalar function. Without the ability to expose this functionality, your customers would not be able to take full advantage of your data source.

In this blog, I will walk through a simple example of implementing a custom scalar function in the SimbaEngine SDK’s Quickstart sample. The custom scalar function will be a function to check if a string contains the character ‘e’, case insensitive. This function will only take one argument that is a character value, and will check to see if there is a character ‘e’ in it. Then it will return a SQL_BIT that represents the Boolean value of the scalar function. For example a SQL query that uses our custom scalar function can look like and this query will return every row where LAST_NAME contains an ‘e’. The sample code for this example is available at the bottom of the page and can be modified to fit your needs.

But how will you execute your scalar function? There are two options to execute a scalar function in an ODBC driver: One is to have it executed by the driver, and the other is to let your data source execute it. Your choice depends on what your scalar function does. For example, if your scalar function is a complex function that can be easily and efficiently executed in your data source, then letting the database execute it would be a better option than implementing it inside the driver. If you are connecting to a database over a network connection and you want to reduce the number of calls to the database, then implementing your scalar function in your driver may be a better option. How you will implement any custom scalar function will depend on your business needs.

Once you have decided on how you are going to implement your scalar function, you implement a class that derives from

  1. Create a class called QSScalarFnContainsE which inherits from DSIExtScalarFunction and implement each virtual function. The constructor for QSScalarFnContainsE takes a pointer to the ILogger object, so any actions in the class can be logged for debugging purposes. In addition to the logging object, a pointer to the driver settings struct (is passed in which is used to set some of the metadata information. If you had an API or some other object to communicate with your data source, you could pass it in to the constructor or place it in the driver setting struct. In the constructor you will create the column metadata for the input and output of the scalar function. This column metadata will tell our SQLEngine and any other applications what to expect when using this scalar function like what type should the input and output be. This custom scalar function will take 1 input which will be of character type and will have 1 output which will be a SQL_BIT to indicate the Boolean value of the function. Simple getters are implemented for the input and output metadata as well as the name of the custom scalar function.
  1. In the function UpdateMetadata(), you will update the input metadata for when the scalar function is executed to ensure we have the correct metadata during execution. In this scalar function you are only going to update the metadata during execution and not during the prepare phase because the metadata in the prepare phase can be invalid. For example if your query contained a parameter as one of the scalar function arguments, then you do not have to specify the type of the parameter until execution. When you call SQLPrepare on your SQL query, your metadata for that parameter could say it is of SQL_INTEGER type because you have not specified what the type of your parameter is. When you actually bind a parameter you can say the type is SQL_WVARCHAR and then execute the SQL query. In UpdateMetadata(), you will first check to see that the input metadata is a character type and then update the precision of the character type. For a character type the precision represents the size of the character data. If the input metadata is not of character type then we throw an exception saying that the input is invalid because this scalar function does not support an argument that is not a character type.
  1. The Execute() function has the actual logic for the scalar function. In this example our scalar function will check to see if there is an instance of the character ‘e’ in the string given as the input, and store this result in a member variable in the class for later use. The SQLEngine will call RetrieveData() after Execute() to get the result of the scalar function execution, and our custom scalar function will return the result as SQL_BIT by using our member variable.

  1. In order for the Simba SQLEngine to use the custom scalar function, you must implement a method in the data engine class called OpenScalarFunction(). In this function you will check to see that the scalar function that is being called is “ContainsE,” and that the number of arguments is one. If the custom scalar function meets all the requirements, then you instantiate the custom scalar function class and return it to the SQLEngine for use. If the number of arguments is not one, throw an exception indicating the invalid argument count. If there is no custom scalar function with the specified name then we return NULL to indicate that there is no matching custom scalar function.

When you build the driver, you can get an error saying that it could not include the file that is included in your new scalar function. Because custom scalar functions are atypical driver features, we add in additional paths to build the driver. For our example, get a file located in the “ETree” package of the SimbaEngine SDK.

  1. Right-click on your driver project and click Properties. This will open your Property Page for the Visual Studios project. On the left hand side there should be a drop down menu for “Configuration Properties,” expand the tab, and expand the “C/C++” sub tab. Under the “C/C++” tab, select the “General” section and your main window should have the general C/C++ settings. Add in the extra include directory in the “Additional Include Directories” section. You may use the SIMBAENGINE_DIR environment variable:

$(SIMBAENGINE_DIR)\Include\SQLEngine\Executor\ETree

Addional Include Directories

Fig 1: The Additional Include Directories Dialog in Visual Studio 2013

For a driver that will be built on Linux, you will have to modify Makefile_SRCS.mak and Makefile_FLAGS.mak to include the newly added files. Makefile_SRCS.mak and Makefile_FLAGS.mak will be located in the “Source” directory of the driver project.

For Makefile_SRCS.mak, add in the file location of QSScalarFNContainsE.cpp in the COMMON_SRCS section. If you placed the file in DataEngine folder then you would add DataEngine/QSScalarFnContainsE.cpp in the COMMON_SRCS list.

For Makefile_FLAGS.mak, Add in the include directory of the ETree under the COMMON_CFLAGS section, you should enter:

-I$(SIMBAENGINE_DIR)/Include/SQLEngine/Executor/ETree

After you have added the additional include directory, you can build the driver under any configuration. The SQLEngine will now recognize your custom scalar function and will be able to execute it within any standard SQL query.

For even more optimization (if your data source supports it), you can use Simba’s Collaborative Query Execution (CQE) capability to pass down the custom scalar function to be executed by your data source. For example, with the query SELECT * FROM ADDR WHERE ContainsE(LAST_NAME) = 1 and a data source that can handle the custom scalar function in the WHERE clause, we can implement a Boolean Expression handler to pass it down. If you were to implement the pass-down, ContainsE would be a node type of AE_NT_VX_CUSTOM_SCALAR_FN. (For more information about CQE and how to implement more advance features, please refer to our SimbaEngine SDK Developer Guide or Collaborative Query Execution (CQE) for Filters on our Knowledge Base. )

Congratulations (again)! You now have a custom scalar function in a custom ODBC driver (with very little effort). Get the full sample code here