In this article Barry Mavin, CEO and Chief Software Architect for Recital, details how to work with Triggers in the Recital Database Server.
Overview
A trigger is a special kind of stored procedure that runs when you modify data in a specified table using one or more of the data modification operations: UPDATE, INSERT, or DELETE.
Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you can control whether to allow a new order to be inserted based on a customer's current account status.
Triggers are also useful for enforcing referential and data integrity.
Triggers can be used with any data source that is handled natively by the Recital Database Engine. This includes Recital, FoxPro, FoxBASE, Clipper, dBase, CISAM, and RMS data,
Creating and Editing Triggers
To create a new Trigger, right-click the Procedures node in the Databases tree of the Project Explorer and choose Create. To modify an existing Trigger select the Trigger in the Databases Tree in the Project Explorer by double-clicking on it, or select Modify from the context menu. By convertion we recommend that you name your Stored Procedures beginning with "sp_xxx_", user-defined functions with "f_xxx_", and Triggers with "dt_xxx_", where xxx is the name of the table that they are associated with.
Associating Triggers with a Table
Once you have written your Triggers as detailed above you can associate them with the operations performed on a Table by selecting the Table tab.
The Tables tab allows you to select a Trigger procedure by clicking on the small button at the right of the Text field.
Types of Triggers
As can be seen from the Tables tab detailed below, The Recital Database Server handles 6 distinct types of Triggers.
Open Trigger
The Open Trigger is called after is a table is opened but before any operations are performed on it. You can use this trigger to record a log of table usage or provide a programmable means of checing security. If the Trigger procedure returns .F. (false), then the table is not opened. You can use a TRY...CATCH block around the associated command to inform the user.
Close Trigger
The Close Trigger is called just prior to a table being closed. In this trigger you may find it useful to get transaction counts by using the IOSTATS() built-in 4GL function, and record these values in a transaction log.
Update Trigger
The Update Trigger is called prior to a record update operation being performed. You can use this trigger to perform complex application or data specific validation. If the Trigger procedure returns .F. (false), then the record is not updated. You can use inform the user from within the Trigger procedure the reason that the data cannot be updated.
Delete Trigger
The Delete Trigger is called prior to a record delete operation being performed. You can use this trigger to perform complex application or data specific validation such as cross-table lookups e.g. attempting to delete a customer recortd when there are still open orders for that specific customer. If the Trigger procedure returns .F. (false), then the record is not deleted.
Insert Trigger
The Insert Trigger is called prior to a record insert (append) operation being performed. You can use this trigger to perform such tasks as setting up default values of columns within the record. If the Trigger procedure returns .F. (false), then the record is not inserted.
Rollback Trigger
The RollbackTrigger is called prior to a rollback operation being performed from within a form. If the Trigger procedure returns .F. (false), then the record is not rolled back to its original state.
Testing the Trigger
To test run the Trigger, select the Trigger 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 the Trigger.
We are pleased to announce the immediate availability of Recital 10.0.1.
Included in this version are:
- Improved SQL query optimizer
- *New* apache plugin for building Recital web apps on x86_64 (mod_recital64.so)
- Performance improvements in connections to Recital Server and Recital Web.
- Recital ODBC driver performance improvements and bug fixes
- Miscellaneous bug fixes
Existing Recital 10 users can download the patch file and apply it to an existing installation.
Enjoy!
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.
auth sufficient pam_krb5.so try_first_pass
auth sufficient pam_unix.so shadow nullok try_first_pass
account required pam_unix.so broken_shadow
account [default=bad success=ok user_unknown=ignore] pam_krb5.so
This article looks at After Image Journaling and audit trails in Recital using SET JOURNAL and associated commands.
Overview
After Image Journaling, used in conjunction with a structured backup policy is an effective disaster recovery solution. Any transaction that takes place on a table that is being journaled is logged in the journal file. In the event of a disk crash or similar event in which the table is lost, the journaled transactions can be reapplied to the latest backup copy of the table. Alternatively or additionally, the journal can be used to provide an audit trail to all modifications made to the table data.
NOTE: Recital also provides Before Image Journaling via BEGIN TRANSACTION / END TRANSACTION blocks, allowing unsuccessful transactions to be rolled back to a set saved state.
SET JOURNAL and RECOVER
Regular backups are an essential routine for any system, but in high-transaction environments restoration of the latest backup can still mean a major loss of data. After image journaling can successfully be used as part of your disaster recovery strategy to minimize data loss and down time. Recital after image journaling functionality is based on the use of the SET JOURNAL and RECOVER commands.
SET JOURNAL
SET JOURNAL TO [<.dbj filename> | ()] SET JOURNAL ON | OFF | ( )
The SET JOURNAL command is used to enable the After Image Journaling and audit trail for the active table. The TO <.dbj filename> clause associates the specified transaction journal file with the active table. If the journal file does not exist, it will be created. The filename can be substituted with a <expC>, enclosed in round brackets, which returns a valid filename. If no file extension is specified, ‘.dbj’ is used. When specifying a journal file, it is recommended that the journal file is stored on a different disk than that which the table is stored on, so that if a fatal disk error occurs, then the journal file will not be lost along with the table.
//Enable journaling for the southwind!orders table open database southwind use orders set journal to /journals/ord_journ
The <.dbj filename> is a standard table. It contains seven
fields that are specific to a journal file, followed by the first
249 fields of the associated table.
The first seven fields in the journal are:
|
Field |
Type |
Display |
Storage |
Description |
|
AUD_DATE |
Date |
8 | 10 * |
4 |
The date on which the transaction was performed. |
|
AUD_TIME |
Character |
8 |
8 |
The time at which the transaction was performed, in the format HH:MM:SS. |
|
AUD_TERM |
Character |
12 |
12 |
The name of the terminal from which the transaction was performed |
|
AUD_UID |
Short |
5 |
2 |
The ID of the user who performed the transaction. |
|
AUD_GID |
Short |
5 |
2 |
The group ID of the user who performed the transaction. |
|
AUD_CMD |
Short |
4 |
2 |
The command number of the transaction performed from the command table below |
|
AUD_RECNO |
Integer |
7 |
4 |
The record number in the associated table which the transaction was performed on. |
* Dependent on SET CENTURY setting.
The AUD_CMD Command Reference Numbers are as follows:
|
Command |
Number |
|
DELETE |
14 |
|
RECALL |
36 |
|
REPLACE |
41 |
|
BROWSE |
6 |
|
CHANGE |
8 |
|
EDIT |
17 |
|
INSERT |
26 |
|
APPEND |
5 |
|
READ |
35 |
Since journal files are standard Recital tables, you can use standard Recital commands such as the REPORT command to print audit trails, transaction logs, etc.
//Enable journaling for the southwind!orders table open database southwind use orders set journal to /journals/ord_journ //.. transactions close data //View journaled records use /journals/ord_journ.dbj
Click image to display full size
Fig 1: Journal Record Example.
The SET JOURNAL TO command without a <.dbj filename> specified closes the active journal file and no further journaling will take place on the active table until the SET JOURNAL TO <.dbj filename> is reissued.
The journaling features are mainly used with shared tables. It should be noted that there is an overhead in enabling transaction journaling, as records updated in a table are also written to the journal file. When records are appended into a journal file, locking is automatically performed so that multiple users can update the journal concurrently. The associated table must be opened shareable for this to occur. Each table can have a journal file associated with it.
The SET JOURNAL ON | OFF command enables or disables transaction journaling. This command is primarily used in applications where journaling can be disabled for a certain class of operations. By default, SET JOURNAL is ON, but no journal files are set.
NOTE: Only the first 249 fields of a table can be journaled: subsequent fields are ignored. The maximum number of fields in a Recital table is 256.
RECOVER
RECOVER FROM <.dbj filename> | ()
The RECOVER command uses the journal file to reapply lost transactions to a previous backup of the data after a fatal error such as a disk head crash. The FROM clause specifies the journal file to use. The file name can be substituted with an <expC>, enclosed in round brackets, which returns a valid filename. If no file extension is specified, then ‘.dbj’ is assumed.
Regular backups are essential to the successful use of After Image Journaling. It is also very important to reinitialize the journal file after each backup: either open the journal file as you would a normal table and use the ZAP command, or delete the file completely. If a fatal error occurs, such as a disk head crash, the table and index files must be restored from a backup, then the RECOVER command executed. RECOVER will reapply' all of the transactions in the journal file to the table, and update the indexes. After the RECOVER command has completed, you can continue with normal processing.
//Create a backup of the southwind!orders table //...backup table and associated files //Reinitialize the journal file erase /journals/ord_journ.dbj //Enable journaling for the southwind!orders table open database southwind use orders set journal to /journals/ord_journ //.. transactions //Restore the backup of the southwind!orders table //...restore //Open the restored backup open database southwind use orders //Reapply the transactions using the journal recover from /journals/ord_journ.dbj //Now, enable the journal file again or //restart with a new backup
Journaling Memo Fields
By default, memo fields - variable length text fields - are not journaled due to the possible storage overhead of multiple copies of potentially large blocks of text. But, if memo journaling is required, the SET MEMOJOURNAL ON command can be used to enable this.
SET MEMOJOURNAL
SET MEMOJOURNAL ON | OFF | ()
The SET MEMOJOURNAL command causes memo fields to be journaled when journaling is set on a table. This command allows the optional logical expression <expL> to be evaluated. If a value of .T. is returned, MEMOJOURNAL is set ON. If a value of .F. is returned, MEMOJOURNAL is set OFF. By default SET MEMOJOURNAL is OFF.
Like a normal Recital table, the journal holds only a pointer to a data block in an associated memo file, not the actual memo data itself. The journal's memo file has a file extension of .dbm rather than the standard Recital .dbt. Therefore, if the journal is being opened as a table, in order to view the journal's memo data, the SET MEMOEXT command should be used.
//Enable journaling for the southwind!suppliers table open database southwind use suppliers set journal to /journals/sup_journ //.. transactions close data //Set filename extension for memo file set memoext to '.dbm' //View journaled records use /journals/sup_journ.dbj
Summary
The After Image Journaling enabled by the SET JOURNAL and RECOVER commands can be used in conjunction with a strict backup regime to minimize data loss in cases where tables become damaged or irretrievable. Journal files can be accessed like standard Recital tables and provide detailed information about the transactions applied to a table, so can be used for auditing purposes.

If you have software packages which you wish to share with others or simply between your own personal machines, a neat and easy solution is to create your own YUM repository and provide your .repo file for download.
YUM is by far the easiest method of installing software on Red hat, Centos and Fedora. Not only does it mean you don't need to trawl the web looking for somewhere to download the packages, YUM does a great job of satisfying any package dependencies. As long as the required packages are available in the enabled repositories on your system, YUM will go out and get everything you need.
To create your own YUM repository, you will need to install the yum-utils and createrepo packages:
yum install yum-utils createrepo
yum-utils contains the tools you will need to manage your soon to be created repository, and createrepo is used to create the xml based rpm metadata you will require for your repository.
Once you have installed these required tools, create a directory in your chosen web server's document root e.g:
mkdir -p /var/www/html/repo/recital/updates
Copy the rpm's you wish to host into this newly created directory.
The next step is to create the xml based rpm metadata. To create this use the createrepo program we installed earlier.
At the shell type the following command:
createrepo -v -s md5 /var/www/html/repo/recital/updates
This will create the required metadata in the repodata directory of your /var/www/html/repo/recital/updates directory.
root@test repodata]# ls -l rwotal 44 -rw-r--r-- 1 root root 28996 Jan 13 21:42 filelists.xml.gz -rw-r--r-- 1 root root 284 Jan 13 21:42 other.xml.gz -rw-r--r-- 1 root root 1082 Jan 13 21:42 primary.xml.gz -rw-r--r-- 1 root root 951 Jan 13 21:42 repomd.xml
To do a final consistency check on your repository run the following command:
verifytree /var/www/html/repo/recital/updates
We now have a fully functioning YUM repository for our hosted rpm packages.
The next process is to create a .repo file in the client systems /etc/yum.repos.d directory.
Navigate to the /etc/yum.repos.d directory on your system as root.
Using your preferred text editor to create the .repo file. In this example I will call it recital.repo.
Now paste in the following lines:
[Recital] name=Recital Update Server baseurl=http://ftp.recitalsoftware.com/repo/recital/updates enabled=1 gpgcheck=1
Once that is saved, at the shell prompt on the same machine (YUM client system).
$ yum repolist Loaded plugins: presto, refresh-packagekit repo id repo name status Recital Recital Update Server enabled: 1 adobe-linux-i386 Adobe Systems Incorporated enabled: 17 fedora Fedora 12 - i386 enabled: 15,366
As you can see the Recital repo is now being picked up and we have access to all the packages it is hosting.
See how easy that was!
DB_SAMBA=YES ;export DB_SAMBA
preserve case = nodefault case = lowermangle case = yes
oplocks = False
share modes = no
In this article Barry Mavin, CEO and Chief Software Architect for Recital, details on how to use the Client Drivers provided with the Recital Database Server to work with local or remote server-side JDBC data sources.
Overview
The Recital Universal .NET Data Provider provides connectivity to the Recital Database Server running on any supported platform (Windows, Linux, Unix, OpenVMS) using the RecitalConnection object.
The Recital Universal JDBC Driver provides the same functionality for java applications.
The Recital Universal ODBC Driver provides the same functionality for applications that use ODBC.
Each of the above Client Drivers use a connection string to describe connections parameters.
The basic format of a connection string consists of a series of keyword/value pairs separated by semicolons. The equals sign (=) connects each keyword and its value.
The following table lists the valid names for keyword/values.
| Name | Default | Description |
|---|---|---|
|
Data Source |
The name or network address of the instance of the Recital Database Server which to connect to. | |
| Directory | The target directory on the remote server where data to be accessed resides. This is ignored when a Database is specified. | |
|
Encrypt |
false | When true, DES3 encryption is used for all data sent between the client and server. |
| Initial Catalog -or- Database |
The name of the database on the remote server. | |
| Password -or- Pwd |
The password used to authenticate access to the remote server. | |
| User ID | The user name used to authenticate access to the remote server. | |
|
Connection Pooling |
false | Enable connection pooling to the server. This provides for one connection to be shared. |
| Logging | false | Provides for the ability to log all server requests for debugging purposes |
| Rowid | true | When Rowid is true (the default) a column will be post-fixed to each SELECT query that is a unique row identifier. This is used to provide optimised UPDATE and DELETE operations. If you use the RecitalSqlGrid, RecitalSqlForm, or RecitalSqlGridForm components then this column is not visible but is used to handle updates to the underlying data source. |
| Logfile | The name of the logfile for logging | |
| Gateway |
Opens an SQL gateway(Connection) to a foreign SQL data source on
the remote server.
servertype@nodename:username/password-database e.g. oracle@nodename:username/password-database mysql@nodename:username/password-database postgresql@nodename:username/password-database -or- odbc:odbc_data_source_name_on_server oledb:oledb_connection_string_on_server jdbc:jdbc_driver_path_on_server;jdbc:Recital:args |
To connect to a server-side JDBC data source, you ue the gateway=value key/value pair in the following way.
gateway=jdbc:jdbc_driver_path_on_server;jdbc:Recital:args
You can find examples of connection strings for most ODBC and OLE DB data sources by clicking here.
Example in C# using the Recital Universal .NET Data Provider:
////////////////////////////////////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;
////////////////////////////////////////////////////////////////////////
// The following code example creates an instance of a DataAdapter that
// uses a Connection to the Recital Database Server, and a gateway to
// Recital Southwind database. It then populates a DataTable
// in a DataSet with the list of customers via the JDBC driver.
// The SQL statement and Connection arguments passed to the DataAdapter
// constructor are used to create the SelectCommand property of the
// DataAdapter.
public DataSet SelectCustomers()
{
string gateway = "jdbc:/usr/java/lib/RecitalJDBC/Recital/sql/RecitalDriver;"+
"jdbc:Recital:Data Source=localhost;database=southwind";
RecitalConnection swindConn = new
RecitalConnection("Data Source=localhost;gateway=\""+gateway+"\");
RecitalCommand selectCMD = new
RecitalCommand("SELECT CustomerID, CompanyName FROM Customers", swindConn);
selectCMD.CommandTimeout = 30;
RecitalDataAdapter custDA = new RecitalDataAdapter();
custDA.SelectCommand = selectCMD;
swindConn.Open();
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
swindConn.Close();
return custDS;
}
Example in Java using the Recital Universal JDBC Driver:
//////////////////////////////////////////////////////////////////////// // standard imports required by the JDBC driver import java.sql.*; import java.io.*; import java.net.URL; import java.math.BigDecimal; import Recital.sql.*; ////////////////////////////////////////////////////////////////////////
// The following code example creates a Connection to the Recital // Database Server, and a gateway to the Recital Southwind database. // It then retrieves all the customers via the JDBC driver. public void SelectCustomers() { // setup the Connection URL for JDBC String gateway = "jdbc:/usr/java/lib/RecitalJDBC/Recital/sql/RecitalDriver;"+ "jdbc:Recital:Data Source=localhost;database=southwind"; String url = "jdbc:Recital:Data Source=localhost;gateway=\""+gateway+"\";
// load the Recital Universal JDBC Driver new RecitalDriver(); // create the connection Connection con = DriverManager.getConnection(url); // create the statement Statement stmt = con.createStatement(); // perform the SQL query ResultSet rs = stmt.executeQuery("SELECT CustomerID, CompanyName FROM Customers"); // fetch the data while (rs.next()) { String CompanyID = rs.getString("CustomerID"); String CompanyName = rs.getString("CompanyName"); // do something with the data... } // Release the statement stmt.close(); // Disconnect from the server con.close(); }
There is a good article on the gluster website here which gives some good information regarding file system optimization suitable for a HA Recital cluster solution.