|
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();
}
|
