In this article Barry Mavin, CEO and Chief Software Architect for Recital, gives details on Working with user-defined Functions in the Recital Database Server.

Overview

User-defined functions (UDFs) are collections of statements written in the Recital 4GL (compatible with Visual FoxPro) stored under a name and saved in a Database. User-defined functions are just-in-time compiled by the Recital database engine. User-defined functions can be used in SQL statements to extend the power and flexibility of the inbuilt functions. Using the Database Administrator in Recital Enterprise Studio, you can easily create, view, modify, and test Stored Procedures, Triggers, and user-defined functions.

Tip

Creating and Editing user-defined functions

To create a new User-defined function,  right-click the Procedures node in the Databases tree of the Project Explorer and choose Create. To modify an existing User-defined function select the User-defined function in the Databases Tree in the Project Explorer by double-clicking on it or selecting Modify from the context menu. By convertion we recommend that you name your User-defined functions beginning with "f_xxx_", where xxx is the name of the table that they are associated with.

Testing the user-defined function

To test run the user-defined function, select it in the Databases Tree in the Project Explorer by double-clicking on it. Once the Database Administrator is displayed, click the Run button to run it.

Example

Example: user-defined function "f_order_details_total".

////////////////////////////////////////////////////////////////////////
// example user-defined function
function f_order_details_total(pUnitprice, pQuantity, pDiscount)
    return (pUnitprice + pQuantity + pDiscount) > 0
endfunc

Example: using the user-defined function in a SQL SELECT statement.

////////////////////////////////////////////////////////////////////////
// sample code to use a user-defined function in a SQL SELECT statement
select * from customers where f_order_details_total(Unitprice, Quantity, Discount)

Using user-defined function libraries with the Recital Database Server

You can place all of the user-defined functions associated with a particular table into a procedure library. You then define an Open Trigger for the table that opens up the procedure library whenever the table is accessed. This is a much faster way of using user-defined functions as it reduces the amount of file open/close operations during a query and also simplifies development and maintenance.

By convertion we recommend that you should name the library using the convention "lib_xxx", where xxx is the name of the table that the library is associated with.

Example: procedure library in lib_order_details.

////////////////////////////////////////////////////////////////////////
// example user-defined functions
function f_order_details_total(pUnitprice, pQuantity, pDiscount)
    return (pUnitprice * pQuantity - pDiscount) > 0
endfunc

function f_order_details_diff(pUnitprice, pQuantity, pDiscount, pValue)
    return f_order_details_total(pUnitprice, pQuantity, pDiscount) - pValue
endfunc

Example: Open Trigger in dt_order_details_open.

////////////////////////////////////////////////////////////////////////
// This trigger will open up the procedure library when the table is opened
set procedure to lib_order_details additive

Example: Close Trigger in dt_order_details_close.

////////////////////////////////////////////////////////////////////////
// This trigger will close the procedure library when the table is closed
close procedure lib_order_details

Example: using the user-defined function in a SQL SELECT statement.

////////////////////////////////////////////////////////////////////////
// sample code to use a user-defined function in a SQL SELECT statement
select * from customers where f_order_details_total(Unitprice, Quantity, Discount)

User-defined functions can also be used with any of the Client Drivers that work with the Recital Database Server.

We are pleased to announce the release of Recital 10.0.3.

Here is a brief list of features and functionality that you will find in the 10.0.3 release.

• New Commands:
  - SET TMPNAMPATH ON|OFF
  - REMOVE TABLE
• New Functions:
  - CURSORGETPROP()
  - CURSORSETPROP()
  - CURVAL()
  - GETFLDSTATE()
  - OLDVAL()
  - TABLEREVERT()
  - TABLEUPDATE()
  - SETFLDSTATE()
• Enhanced Functions:
  - TMPNAM() - additional parameter to specify the return of basename only
  - MAILATTACH() - parameter changed from array to filename to allow directory and file extension to be specified
• Enhancements:
  - DO level increased from 32 to 64.
• Fixes:
  - Delay exiting Recital after SYS(3) or SYS(2015)
  - SET SOFTSEEK issue when search key above first record in index
  - Compilation error with REPLACE command after UDF call
  - FETCH INTO memvars error
  - END TRANSACTION at command prompt error
  - ROLLBACK locking error
  - Linux ODBC Driver undefined symbol error
  - RELEASE variable with same name as variable in calling program issue
  - SQLCODE() issue on non-gateway data access
  - Issuing two SQLEXEC() calls error
  - LASTSEQNO() in workareas > 1 error
  - SET RELATION to detail table in workarea 1 issue
  - LIST STATUS on empty table delay
  - SET AUTOCATALOG alias entries error
  - ADD OBJECT in DEFINE CLASS error
  - DEACTIVATE WINDOW error
  - SORT error
  - Other reported bugs

1. New MD5( expC ) function to calculate an MD5 crypto key from any character expression
2. New MD5FILE( expC ) function calculates an MD5 crypto key for a given filename. If the filename is a pattern e.g *.* it will calculate the key across all files matching the pattern
3. New mod_recital.so available for using Recital Web on linux x86_32
4. New mod_recital64.so available for using Recital Web on linux x86_64