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
You can also extend the Recital Database Server with C Extension Libraries and use the functions defined within that library also.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.
Key features of the Recital scripting language include:
What are the key feature of the Recital database?
- High performance database application scripting language
- Modern object-oriented language features
- Easy to learn, easy to use
- Fast, just-in-time compiled
- Develop desktop or web applications
- Cross-platform support
- Extensive built-in functions
- Superb built-in SQL command integration
- Navigational data access for the most demanding applications
System Requirements:
- Minimum memory: 4MB
- Minimum Diskspace: ~20MB
The REQUIRE() statement includes and executes the contents of the specified file at the current program execution level.
When a file is included, the code it contains inherits the variable scope of the line on which the include occurs. Any variables, procedures, functions or classes declared in the included file will be available at the current program execution level.
The REQUIRE_ONCE() statement is identical to the REQUIRE() statement except that Recital will check to see if the file as already been included and if so ignore the command.
The full syntax is:
REQUIRE( expC ) REQUIRE_ONCE( expC ) e.g. REQUIRE_ONCE( "myapp/myglobals.prg" )
The getUIComponentBitmapData method can create bitmapdata for a given IUIComponent. Pass any UIcomponent to get its respective bitmapdata.
public static function getUIComponentBitmapData(target:IUIComponent):BitmapData {
var resultBitmapData:BitmapData = new BitmapData(target.width, target.height);
var m:Matrix = new Matrix();
resultBitmapData.draw(target, m);
return resultBitmapData;
}
Now convert the bitmapdata to a jpeg bytearray.
private static function encodeToJPEG(data:BitmapData, quality:Number = 75):ByteArray {
var encoder:JPGEncoder = new JPGEncoder(quality);
return encoder.encode(data);
}
Now encode the ByteArray into Base64.
public static function base64Encode(data:ByteArray):String {
var encoder:Base64Encoder = new Base64Encoder();
encoder.encodeBytes(data);
return encoder.flush();
}
Upload the base64 encoded ByteArray to the server.
public static uploadData():void {
var url:String = "saveFile.php";
var urlRequest:URLRequest = new URLRequest(url);
urlRequest.method = URLRequestMethod.POST;
var urlLoader:URLLoader = new URLLoader();
var urlVariables:URLVariables = new URLVariables();
urlVariables.file = jpgEncodedFile; // as returned from base64Encode()
urlLoader.data = urlVariables;
urlLoader.load(urlRequest);
}
The saveFile.php file on the server.
If you are using the Oracle Gateway in Recital, make sure the Oracle environment (ORACLE_HOME, ORACLE_SID etc.) is set up before starting the Recital Server. If not, you will see the error ORA-01019. A call to the Oracle environment setup script can be added to the /etc/init.d/recital script if your Recital Server is set to run on startup.
iptables -I INPUT -j ACCEPT -p tcp --destination-port 8001 -i lo
iptables -A INPUT -j DROP -p tcp --destination-port 8001 -i eth0