We call the feature "SQL extension scripting" and it allows you to dynamically create, load and use functions for use in normal NexusDB SQL.
As usual, let's start with an
Example
First we create a script file called sqlExtensionTest.nxscript:
begin
RegisteredSQLFunctions.AddObject('scrEcho', TnxSQLFunctionInfo.Create(
'scrEcho', 1 , rttAsArg1x, true, '', ''));
RegisteredSQLFunctions.AddObject('scrUpper', TnxSQLFunctionInfo.Create(
'scrUpper', 1 , rttAsArg1x, true, '', ''));
end;
function scrEcho(aInput: variant): variant;
begin
result:=aInput;
end;
function scrUpper(aInput: variant): variant;
begin
result:=UpperCase(aInput);
end;
begin
end.
The "magic" in this script is the predefined RegisterSQLFunctions function. When the script gets loaded by the server, this function is executed. The function can register any number of named functions into the SQL engine by simply adding them to the RegisteredSQLFunctions list. The function is defined by passing an instance of a simple TnxSQLFunctionInfo class. It's constructor looks like this:
aReturnType: TnxSqlReturnTypeType; aEnabled: Boolean = true;
aExample: WideString = ''; aSyntax: WideString = '');
aReturnType at this point in time needs to be rttAsArg1x meaning the function returns one variant argument. It is (just like aExample and aSyntax) included for future extensions, specifically for returning cursors. aArgCount is obviously the number of arguments that the function takes and aFunctionName is clear anyways.
Once the function is registered all you need to do is to implement the function, so we simply create a pascal function called aFunctionName with the matching arguments and return type variant. That's all there is to it.
How to use it???
Very simple actually. First step is to place the file into a SQLScripts subdirectory of the server (this will obviously be customizable in the release version via a server setting). Then we need to load the script from SQL and call the functions. It goes like this:
SELECT scrUpper(1) FROM myTable;"
drop assembly abc;
That's all. So we create an assembly with the SQL name abc and call the functions declared in it. An assembly is always loaded for a database instance, that means that you can of course share it between multiple queries. If an assembly is not dropped explicitly it is free on closing the database.
The future?
Several extensions are planned. First and foremost we plan to add #meta support for functions in assemblies. Further we will try to extend the return type to support cursors and of course we will keep adding scripts to the Code Depot.
Summary
With SQL Extension scripting we give you, the developer, the possibility to easily create and maintain your own library of SQL functions, without ever needing to recompile the server. This will make it very easy to create custom functionality for SQL and will allow you to move practically all your business logic to a server. Not only is a pascal script much more powerful for solving most tasks than SQL but it is, thanks to on-the-fly compilation and caching also significantly faster than interpreted standard SQL scripts.