User-Defined Functions: White Paper
Overview
With the advent of Version 2.6 of the NuVu Query suite, the tool is now able to execute user-defined functions within a given library (a compiled Progress program called ‘udf.r’) which MUST reside in the same folder as the running instances of the NuVu socketserver programs (eg: socketserver_v10.r). This now enables the queries to derive information directly into a given column of the query, based on running external business logic that resides within the functions contained within the UDF library.
How it works
- Client requests the running of a query which contains one or more defined UDF functions.
- NuVu Query connects directly from that machine to the NuVu Repository Server which in turns connects through to an available NuVu Socket Server.
- The NuVu Socket Server interprets the query, and determines that there is at least one UDF function within it that is required to be invoked for each row that is read from the database. If this is the case, then the UDF library is invoked persistently (udf.r).
- Once the data has been fetched for a row, the actual UDF function is called, passing any optional parameters and the results are stored into the appropriate column in the data before the row is returned back to the client.
- Once the query is finished retrieving rows from the database, the UDF library is unloaded from memory. If your UDF library contains a FUNCTION ‘udf_finalise’ then that function is called from the socketserver_x.r. This is the ideal place to unload any of your own persistent procedures launched by your version of udf.r.
Things to remember
- The udf.p supplied with the NuVu Query Suite is maintained thereafter by you – any changes made to it your responsibility. Any changes to the udf.p MUST be compiled and saved to a filename called udf.r which MUST reside in the same folder as the running socketserver.r programs (eg: socketserver_v10.r).
- All databases used by any function within the udf.p library MUST be defined in any query that is going to call any function within that library. In addition, the ‘schemaholder’ name must match the logical database name that was used for each connected database when the udf.p library was compiled.
- Calculated fields CANNOT be used as parameters to any UDF function. Database fields and defined prompts/variables are allowed.
- Ensure you have no locking of any tables inside your defined UDF functions – or if you do, ensure that you release the locks before exiting the function. Any remaining locks could prevent the closing of the database within the calling ‘socketserver_x.r’ program and will hang up the NuVu Broker Service ultimately.
- Ensure you unload any persistent procedures started. When the query completes, the udf.r is unloaded automatically by NuVu, but if there are persistent procedures started remotely inside udf.p that remain running (and reference the database) then the database is not able to be disconnected by the socketserver_x.r program, and that will result in unavailable databases the next time a query is run.
Configuration
In order to enable the calling of UDF functions, the following steps must be followed:
- The udf.p supplied with the NuVu Query Suite, once changed, MUST be compiled with all the required logical database connections in place.
- The compiled udf.r library of functions MUST be copied to the same folder that contains the socketserver.r (eg: socketserver_v10.r) in order for the socketserver program to locate this and instantiate the library persistently.
- Appropriate UDF functions must be defined within any query – the syntax (function name and any parameters) are NOT checked when the query is defined, so it is up to you to ensure the correctness of these.
Documentation
The functions that are written into your custom udf.p can be documented (optionally) by creating a udf.iniin a specified format as per the documentation below. If this udf.ini is dropped into the NuVuQuery install folder then the calculated fields and expression windows will open a new section (called UDF’s) in the formula section of those windows, which will allow pasting into the expression area by simply double-clicking on the required function.
For sake of example, NuVu has created 2 simple UDF functions, and documented them in the supplied udf.ini file as part of your install. So when you invoke the Calculated fields function (for example), you should see the following inside the ‘Formula’ section of the window:
This information is extracted from the supplied udf.ini file which contains this:
[Functions]
test_month
test_string
[test_month]
Syntax=UDF("test_month",{date})
Details=Expects 1 parameter\nReturns the month of the passed {date} field\n\nExample: UDF("test_month",`mydb.mytable.myfield`) will return the month portion (integer between 1 and 12) of the supplied date field
[test_string]
Syntax=UDF("test_string",{field})
Details=Expects 1 parameter\nReturns the passed {field} as a character (string) field\n\nExample: UDF("test_string",`mydb.mytable.myfield`) will return the same field passed to the function, in the raw (native) format available from the server
The [Functions] setting will contain a list of each function that you wish to document. Each function is then described in sections below, with the section name containing the function name (in square brackets), followed by the Syntax and Details for each function. The details section can contain ‘\n’ characters which will transpose to be newlines in the help text that is displayed when hovering over any of the defined UDF functions.
An Example of writing a new UDF function:
The following is an example of how to create a new function within the supplied udf.p library. This example will return the difference between the Credit Limit and the Balance of each row of any query interrogating the Customer table within the Sports2000 database.
The NuVu Query tool ALWAYS passes the user ID of the user that is running the query tool as the first parameter to any function. The second parameter (also compulsory) contains a packed list of passed parameters, each parameter delimited with a CHR(1) character. It is up to you to extract the various parameters in order to use them within the function. There are helper functions within the library that can be used to extract parameters and convert them into variables that you can use.
The result of ALL udf functions MUST return a string, and depending on the intended column datatype, you must format date and logical values as follows:
Date: YYYYMMDD
Logical: 1 = True, 0 = False
Other values can just be returned as a string – for example: STRING(ws_difference).
/* get_difference function:
Returns the difference between CreditLimit and Balance for a customer */
FUNCTION get_difference RETURNS CHAR (parm_user AS CHAR,parm_str AS CHAR):
DEF VAR MyCustNum LIKE Customer.CustNum NO-UNDO.
MyCustNum = AsInteger(GetParam(1,parm_str)).
FIND Customer WHERE Customer.CustNum = MyCustNum NO-LOCK NO-ERROR.
IF NOT AVAILABLE Customer THEN
RETURN "0".
ELSE
RETURN STRING(Customer.CreditLimit – Customer.Balance).
END FUNCTION.
Calling the sample UDF function:
The following describes what is required within the NuVu Query tool in order to invoke this sample function:
- Create a calculated field and add a UDF formula as shown below
UDF("get_difference",`Customer_CustNum`)will invoke the ‘get_difference’ function that MUST exist within the UDF library (udf.p) which will calculate the difference between the CreditLimit and the current Balance of the customer and return it as a string field to be inserted into the calculated field results row. If the function does not exist within the udf.p library (or the incorrect number/type of parameters are passed), then the results of this column will be blank.
Supplied base udf.p library as supplied by NuVu Pty Ltd.
/*------
udf.p - Contains User-Defined functions that can be called from the
socketserver program while extracting rows from the database.
Each function MUST have 2 parameters defined:
1) The user id of the NuVu user that is running the query. This
is passed in from the query.
2) A chr(1) delimited set of optional parameters. These parameters
can be converted to any datatype via the built in functions GetDate,
GetInteger,GetDecimal,GetLogical that are included in this library.
Also the parameters must conform to this format:
date - YYYYMMDD
logical - 0 = False, 1 = True
IMPORTANT NOTE: All databases that are referenced inside this
procedure MUST be connected when any function within
the udf is called. Unfortunately if this procedure
is called without all the required databases it will
result in an unhandled stop condition. This is however
checked inside the socketserver program before this
procedure is loaded.
B Culverwell - NuVu Pty Ltd.
------*/
/*------Predefined functions - never remove these */
/* AsDate function: translates a string date in format YYYYMMDD into a true
date and returns it. */
FUNCTION AsDate RETURNS DATE (parm_str AS CHAR):
DEF VAR MyDate AS DATE NO-UNDO INITIAL ?.
IF parm_str = "" THEN
RETURN ?.
MyDate = DATE(INT(SUBSTR(parm_str,5,2)),
INT(SUBSTR(parm_str,7,2)),
INT(SUBSTR(parm_str,1,4))) NO-ERROR.
RETURN MyDate.
END.
/* AsLogical function: translates a string logical (0/1 or blank) into a
logical and returns it */
FUNCTION AsLogical RETURNS LOGICAL (parm_str AS CHAR):
IF parm_str = "" THEN
RETURN ?.
ELSE
IF parm_str = "1" THEN
RETURN TRUE.
ELSE
RETURN FALSE.
END.
/* AsInteger function: translates a string into an integer and returns it */
FUNCTION AsInteger RETURNS INTEGER (parm_str AS CHAR):
DEF VAR MyInteger AS INTEGER NO-UNDO INITIAL ?.
IF parm_str = "" THEN
RETURN ?.
ELSE DO:
MyInteger = INTEGER(parm_str) NO-ERROR.
RETURN MyInteger.
END.
END.
/* AsDecimal function: translates a string into a decimal and returns it */
FUNCTION AsDecimal RETURNS DECIMAL (parm_str AS CHAR):
DEF VAR MyDecimal AS DECIMAL NO-UNDO INITIAL ?.
IF parm_str = "" THEN
RETURN ?.
ELSE DO:
MyDecimal = DECIMAL(parm_str) NO-ERROR.
RETURN MyDecimal.
END.
END.
/* GetParam function: returns the xth parameter from the passed string */
FUNCTION GetParam RETURNS CHAR (parm_num AS INTEGER,parm_str AS CHAR):
DEF VAR RetVal AS CHAR NO-UNDO.
RetVal = ENTRY(parm_num,parm_str,CHR(1)) NO-ERROR.
RETURN RetVal.
END.
/* test_string function: just returns the passed string
Note as this is a "string" there is no conversion necessary */
FUNCTION test_string RETURNS CHAR (parm_user AS CHAR,parm_str AS CHAR):
IF parm_str = ? THEN
RETURN "".
ELSE
RETURN parm_str.
END.
/* test_month function: returns the month of the passed string date */
FUNCTION test_month RETURNS CHAR (parm_user AS CHAR,parm_str AS CHAR):
DEF VAR MyDate AS DATE NO-UNDO.
MyDate = AsDate(GetParam(1,parm_str)).
IF MyDate = ? THEN
RETURN "0".
ELSE
RETURN STRING(MONTH(MyDate)).
END.
/* test_crash function: will crash if a parameter (integer) is passed in
over 4000 or so - this is to test the Progress logging */
FUNCTION test_crash RETURNS CHAR (parm_user AS CHAR,parm_str AS CHAR):
DEF VAR i AS INT NO-UNDO.
DEF VAR MaxCnt AS INT NO-UNDO.
DEF VAR Str AS CHAR NO-UNDO.
MaxCnt = AsInteger(GetParam(1,parm_str)).
DO i = 1 TO MaxCnt:
Str = Str + "this will eventually crash (>32K string)".
END.
RETURN "Success".
END.
/* udf_initialise function - if exists, is called automatically when the udf.p
is loaded into memory. This is the place to insert any temp-tables or other
logic to be shared by functions in this library. */
FUNCTION udf_initialise RETURNS INTEGER:
END.
/* udf_finalise function - if exists, is called automatically when the udf.p
is about to be unloaded from memory. This is the place to unload any of
your persistent proceduresinsert any temp-tables or other
your own persistent procedures. */
FUNCTION udf_finalise RETURNS INTEGER:
END.
/*------Your user defined functions to be specified below */
/* EG:
FUNCTION deal_profit RETURNS CHAR (parm_user AS CHAR,parm_str AS CHAR):
RETURN STRING(RANDOM(100,20000)).
END.
*/