In this article Barry Mavin, CEO and Chief Software Architect for Recital, details Working with Stored Procedures in the Recital Database Server.

Overview

Stored procedures and user-defined functions are collections of SQL statements and optional control-of-flow statements written in the Recital 4GL (compatible with VFP) stored under a name and saved in a Database. Both stored procedures and user-defined functions are just-in-time compiled by the Recital database engine. Using the Database Administrator in Recital Enterprise Studio, you can easily create, view, modify, and test Stored Procedures, Triggers, and user-defined functions

Creating and Editing Stored Procedures

To create a new Stored Procedure,  right-click the Procedures node in the Databases tree of the Project Explorer and choose Create. To modify an existing stored procedure select the Stored Procedure 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 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.

Testing the Procedure

To test run the Stored Procedure, select the Stored Procedure 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 procedure.

Getting return values

Example Stored Procedure called "sp_myproc":

parameter arg1, arg2
return arg1 + arg2

Example calling the Stored Procedure from C# .NET:

////////////////////////////////////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;

////////////////////////////////////////////////////////////////////////
// sample code to call a Stored Procedure that adds to numeric values together
public int CallStoredProcedure()
{
	RecitalConnection conn = new 
		RecitalConnection("Data Source=localhost;Database=southwind;uid=?;pwd=?");
	RecitalCommand cmd = new RecitalCommand();
	cmd.Connection = conn;
	cmd.CommandText = "sp_myproc(@arg1, @arg2)";
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.Parameters["@arg1"].Value = 10;
	cmd.Parameters["@arg2"].Value = 20;
	conn.Open();
	cmd.ExecuteNonQuery();
	int result = (int)(cmd.Parameters["retvalue"].Value);    // get the return value from the sp
	conn.Close();
	return result;
	}

Writing Stored Procedures that return a Resultset

If you want to write a Stored Procedure that returns a ResultSet, you use the SETRESULTSET() function of the 4GL. Using the Universal .NET Data Provider, you can then execute the 4GL Stored Procedure and return the ResultSet to the client application for processing. ResultSets that are returned from Stored Procedures are read-only.

Example Stored Procedure called "sp_myproc":

parameter query
select * from customers &query into cursor "mydata"
return setresultset("mydata")

Example calling the Stored Procedure from C# .NET:

////////////////////////////////////////////////////////////////////////
// include the references below
using System.Data;
using Recital.Data;

////////////////////////////////////////////////////////////////////////
// sample code to call a stored procedure that returns a ResultSet
public void CallStoredProcedure()
{
	RecitalConnection conn = new 
		RecitalConnection("Data Source=localhost;Database=southwind;uid=?;pwd=?");
	RecitalCommand cmd = new RecitalCommand();
	cmd.Connection = conn;
	cmd.CommandText = "sp_myproc(@query)";
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.Parameters["@query"].Value = "where not deleted()";
	conn.Open();
	RecitalDataReader dreader = cmd.ExecuteReader();
	int sqlcnt = (int)(cmd.Parameters["sqlcnt"].Value);    // returns number of affected rows
	while (dreader.Read())
	{
		// read and process the data
	}
	dreader.Close();
	conn.Close();
}

If when your attempt to create device meta-data fails this is drbd preventing you from corrupting a file system present on the target partition.

$ drbdadm create-md drbd0

v08 Magic number not found
md_offset 30005817344
al_offset 30005784576
bm_offset 30004867072

Found ext2 filesystem which uses 190804004 kB
current configuration leaves usable 29301628 kB

Device size would be truncated, which
would corrupt data and result in
'access beyond end of device' errors.
You need to either
 * use external meta data (recommended)
 * shrink that filesystem first
 * zero out the device (destroy the filesystem)
Operation refused.

Command 'drbdmeta /dev/drbd0 v08 /dev/sda4 internal create-md' terminated with exit code 40
drbdadm aborting

Once you have confirmed that the file system present on the target partition is no longer required at the prompt type the following:

Replace /dev/sdaX with the block device you are targeting.

dd if=/dev/zero of=/dev/sdaX bs=1M count=128

Once this has completed the drbdadm create-md drbd0 command will complete with a "success."

$ drbdadm create-md drbd0
v08 Magic number not found
v07 Magic number not found
v07 Magic number not found
v08 Magic number not found
Writing meta data...
initialising activity log
NOT initialized bitmap
New drbd meta data block successfully created.
success
$

Opening SSH to the outside world is a security risk. Here is how to restrict SSH access to certain IP addresses on a machine.

1. 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

2. Edit your /etc/hosts.deny file to include this line:
   
   

   sshd,sshdfwd-X11:ALL 

3. These lines refuse SSH connections from anyone not in the IP address blocks listed.

Additionally you can restrict SSH access by username.

1. 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

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