public static void showTrace(String msg)
{
	if (msg.length() > 0) System.out.println(msg);
	System.out.println("Trace: " + 
				   "file " + new Throwable().getStackTrace()[1].getFileName() +
				   " class " + new Throwable().getStackTrace()[1].getClassName() +
				   " method " + new Throwable().getStackTrace()[1].getMethodName() +
				   " line " + new Throwable().getStackTrace()[1].getLineNumber());
}

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

In this article Barry Mavin, CEO and Chief Software Architect for Recital provides details on how to use the Recital Universal .NET Data Provider with the Recital Database Server.

Overview

A data provider in the .NET Framework serves as a bridge between an application and a data source. A data provider is used to retrieve data from a data source and to reconcile changes to that data back to the data source.

Each .NET Framework data provider has a DataAdapter object: the .NET Framework Data Provider for OLE DB is the OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server is the SqlDataAdapter object, the .NET Framework Data Provider for ODBC is the OdbcDataAdapter object, and the .NET Framework Data Provider for the Recital Database Server is the RecitalDataAdapter object.

The Recital Universal .NET Data Provider can access any data sources supported by the Recital Database Server. It is not restricted to only access Recital data. It can be used to access server-side ODBC, JDBC and OLE DB data sources also.

Core classes of the Data Provider

The Connection, Command, DataReader, and DataAdapter objects represent the core elements of the .NET Framework data provider model. The Recital Universal .NET Data Provider is plug compatible with the .NET Framework Data Provider for SQL Server. All SQL Server classes are prefixed with "Sql" e.g. SqlDataAdaptor. To use the Recital Universal Data Adaptor, simply change the "Sql" prefix to "Recital" e.g. RecitalDataAdaptor.

The following table describes these objects.

Along with the core classes listed in the preceding table, a .NET Framework data provider also contains the classes listed in the following table.

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 .NET Data Provider supports a connection string format that is similar to the SQL Server connection string format.

The basic format of a connection string consists of a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value.

The following table lists the valid names for keyword values within the ConnectionString property of the RecitalConnection class.

Populating a DataSet from a DataAdaptor

The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.

A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.

The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet.

The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema.

Examples in C#:

////////////////////////////////////////////////////////////////////////
// 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 Southwind database 
// and populates a DataTable in a DataSet with the list of customers. 
// The SQL statement and Connection arguments passed to the DataAdapter 
// constructor are used to create the SelectCommand property of the DataAdapter.
public DataSet SelectCustomers()
{
	RecitalConnection swindConn = new
		RecitalConnection("Data Source=localhost;Initial Catalog=southwind");
	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;
}

////////////////////////////////////////////////////////////////////////
// The following example uses the RecitalCommand, RecitalDataAdapter and 
// RecitalConnection, to select records from a database, and populate a 
// DataSet with the selected rows. The filled DataSet is then returned. 
// To accomplish this, the method is passed an initialized DataSet, a 
// connection string, and a query string that is a SQL SELECT statement
public DataSet SelectRecitalRows(DataSet dataset, string connection, string query) 
{
	RecitalConnection conn = new RecitalConnection(connection);
	SqlDataAdapter adapter = new RecitalDataAdapter();
	adapter.SelectCommand = new RecitalCommand(query, conn);
	adapter.Fill(dataset);
	return dataset;
}

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