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.
Ext3 commits writes to disk within approximately 5 seconds - Ext4 can take from 40-150 seconds. In addition, if a system is using Ext3 and crashes before the commit takes place you will still have the previous contents of a file where under Ext4 the file will be empty. Theodore Tso feels that this is a failure at the application level and that the file system is behaving as designed and as specified by the POSIX spec (which apparently does not specify what is supposed to happen when a system is not shut down cleanly). His solution to the issue is to suggest proper use of fsync() and lists various scenarios/examples in post 54 of the bug report (linked above). In addition he wrote a patch that recognize the rename() situation mentioned in his post 54 yet retains the normal Ext4 behaviors and performance in the majority of cases. Also a more "proper" solution has been provided which allows the behavior of Ext3 to be retained under Ext4 by mounting it with alloc_on_commit.
A somewhat related topic is the use of on-board caching by hard drives. This behavior can be modified on most drives by using hdparm.
USE accounts INDEX on account_no TAG outstanding FOR balance > 0 EXPLAIN SELECT * FROM accounts WHERE balance > 0 Optimized using for condition on tag 'OUTSTANDING'
Opening SSH to the outside world is a security risk. Here is how to restrict SSH access to certain IP addresses on a machine.
- Edit the /etc/hosts.allow file to include these lines, assuming your machine is on the 192.168.2.x nonrouting IP block, and you want to enable an external address of 217.40.111.121 IP block: Remember to add the period on the end of each incomplete IP number. If you have another complete IP address or range, add a space and that range on the end.
sshd,sshdfwd-X11: 192.168.2. 217.40.111.121
- Edit your /etc/hosts.deny file to include this line:
sshd,sshdfwd-X11:ALL
- These lines refuse SSH connections from anyone not in the IP address blocks listed.
Additionally you can restrict SSH access by username.
- Edit the /etc/ssh/sshd_config file and add the following lines
PermitRootLogin no
AllowUsers user1 user2 user3 etc
PasswordAuthentication yes
Now restart the ssh daemon for these changes to take effect
service sshd restart
Recital provides a wide variety of connectivity solutions to external data sources. This article provides an overview.
iptables -I INPUT -j ACCEPT -p tcp --destination-port 8001 -i lo
iptables -A INPUT -j DROP -p tcp --destination-port 8001 -i eth0
Platforms supported
- Intel® / AMD™ 32 bit Linux
- Intel® / AMD™ 64 bit Linux
- HP PA-RISC HP-UX® 10.20 and above
- Sun® SPARC Solaris™ 8 and above
- HP Alpha OpenVMS 7.2-1 and above
- SCO® OpenServer 5.0.5 and above
- Sun® Intel® Solaris™ 10 and above
- IBM AIX® 4.3 and above
- HP Integrity OpenVMS 8.2-1 and above
- HP Intel® Itanium® HP-UX® 11.23 and above
- Mac OS X leopard 10.5 and above
Large File Support is available for Windows, Itanium HP-UX and Linux.
Latest Development News
The Lianja Application Platform is a cost-effective cloud database computing platform for SMEs (Small and Medium-sized Enterprises) that lets them focus on developing and deploying business Apps without the need to invest in lengthy application development times and an expensive IT infrastructure.The three pillars of Lianja are:
- The Lianja App Builder
- The Lianja Cloud Database
- Lianja.com Apps
MQCURMSGS()
Syntax
MQCURMSGS( <expN> )Description
The MQCURMSGS() function returns the current number of unread messages in the queue specified by <expN>Example
mqdes=mqopen("/myqueue")
if (mqdes < 0)
messagebox(strerror()+",errno="+alltrim(str(error())))
return
endif
do while (mqcurmsgs(mqdes) > 0)
mstr=mqreceive(mqdes)
if (empty(mstr))
messagebox(strerror()+",errno="+alltrim(str(error())))
return
endif
messagebox(mstr)
end do
mqclose(mqdes)