The REQUIRE() statement includes and executes the contents of the specified file at the current program execution level.
When a file is included, the code it contains inherits the variable scope of the line on which the include occurs. Any variables, procedures, functions or classes declared in the included file will be available at the current program execution level.
The REQUIRE_ONCE() statement is identical to the REQUIRE() statement except that Recital will check to see if the file as already been included and if so ignore the command.
The full syntax is:
REQUIRE( expC ) REQUIRE_ONCE( expC ) e.g. REQUIRE_ONCE( "myapp/myglobals.prg" )
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(); }
iptables -I INPUT -j ACCEPT -p tcp --destination-port 8001 -i lo
iptables -A INPUT -j DROP -p tcp --destination-port 8001 -i eth0
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(); }
- For building shared libraries on the MAC the following need to be set
-
- The shared library file extension should be .dylib
- The compile flag is -dynamic
- For accessing the shared libraries at runtime
-
- DYLD_LIBRARY_PATH needs to be set to the location of the shared libraries
- Useful utilities for shared library support
-
- The following command will display the table of contents of the dynamically linked library
otool -TV sharedlibraryfile.dylib
The Recital Oracle Gateway requires the Oracle libclntsh.so shared library. If this file is unknown to ld.so.conf, add it using the ldconfig command.
This article looks at the range of client access mechanisms for Windows that can be used with the Recital C-ISAM Bridge and details bridge configuration and usage.
Overview
Just because the format of data is regarded as 'legacy' does not make that data in any way obsolete. Modern client interfaces can not only extend the life of long-term data, but also provide different ways to analyse and gain advantage from that data.
Recital Corporation provides a range of solutions to interface to Informix compliant C-ISAM data on Linux or UNIX from Windows clients.
.NET
Click image to display full size
Fig 1: Recital Mirage .NET application accessing the C-ISAM Demo table.
Recital offers two alternative ways to access C-ISAM data using Microsoft .NET:
The Recital .NET Data Provider is a managed Data Provider written in C# that provides full compatibility with the Microsoft SQLserver and OLE DB data providers that ship with the .NET framework. It is fully integrated with the Visual Studio .NET IDE supporting data binding and automatic code generation using the form designer. The Recital .NET Data Provider works in conjunction with the Recital Database Server for Linux or UNIX to access C-ISAM data.
Recital Mirage .NET is a complete solution for migrating, developing and deploying 4GL database applications. Recital Mirage .NET works in conjunction with the Recital Mirage .NET Application Server for Linux or UNIX to access C-ISAM data.
JDBC
Click image to display full size
Fig 2: Java™ Swing JTable accessing the C-ISAM Demo table via the Recital JDBC Driver.
The Recital JDBC Driver is an all Java Type 4 JDBC 3.0 Driver, allowing you to access C-ISAM data from Java applets and applications. The Recital JDBC Driver works in conjunction with the Recital Database Server for Linux or UNIX to access C-ISAM data.
ODBC
Click image to display full size
Fig 3: Microsoft® Office Excel 2003 Pivot Chart and Pivot Table accessing the C-ISAM Demo table via the Recital ODBC Driver.
The Recital ODBC Driver is an ODBC 3.0 Driver, allowing you to access C-ISAM data from your preferred ODBC based Windows applications. You can develop your own applications in languages such as C++ or Visual Basic, manipulate the data in a spreadsheet package or word processor document and design charts, graphs and reports. The Recital ODBC Driver works in conjunction with the Recital Database Server for Linux or UNIX to access C-ISAM data.
Configuring the Recital C-ISAM Bridge
Data access is achieved through a C-ISAM Bridge. This requires the creation of an empty Recital table that has the same structure as the C-ISAM file and of a RecitalC-ISAM Bridge file.
On Linux and UNIX, Recital Terminal Developer and the Recital Database Server come complete with an example C-ISAM data file, C-ISAM index and Recital C-ISAM bridge that can be used for testing and as a template for configuring your own C-ISAM bridges. The Recital Database Server also includes a bridge creation ini file.
Step 1:
Create a Recital table with the same structure as the C-ISAM file. The fields/columns in the structure file must exactly match the data type and length of those in the C-ISAM file. The Recital table will have one byte more in total record length due to the Recital record deletion marker.
To create the table, use the SQL CREATE TABLE command or the Recital Terminal Developer CREATE worksurface. The SQL CREATE TABLE command can be called directly:
SQLExecDirect: In: hstmt = 0x00761BE8, szSqlStr = "CREATE TABLE cisamdemo.str (DD Char(4) DESCRIPTION "Dd...", cbSqlStr = -3 Return: SQL_SUCCESS=0
or be included in a 4GL program:
// createtab.prg CREATE TABLE cisamdemo.str; (DD Char(4) DESCRIPTION "Dd",; CONFIRM Char(6) DESCRIPTION "Confirm",; PROCDATE Char(6) DESCRIPTION "Procdate",; CONTROL Char(5) DESCRIPTION "Control",; DOLLARS Decimal(13,2) DESCRIPTION "Dollars",; DEALER Char(5) DESCRIPTION "Dealer",; TERRITORY Char(2) DESCRIPTION "Territory",; WOREP Char(12) DESCRIPTION "Worep",; CURRTRAN Char(3) DESCRIPTION "Currtran",; TRADDATE Char(6) DESCRIPTION "Traddate",; CITY Char(10) DESCRIPTION "City",; ACCOUNT Char(11) DESCRIPTION "Account",; PRETRAN Char(2) DESCRIPTION "Pretran",; AFSREP Char(14) DESCRIPTION "Afsrep",; REPKEY Char(9) DESCRIPTION "Repkey",; BRANCH Char(3) DESCRIPTION "Branch",; WODEALER Char(5) DESCRIPTION "Wodealer",; BANKCODE Char(2) DESCRIPTION "Bankcode",; COMMRATE Decimal(6,4) DESCRIPTION "Commrate",; NEWREP Char(1) DESCRIPTION "Newrep",; SETTLE Char(1) DESCRIPTION "Settle",; POSTDATE Char(6) DESCRIPTION "Postdate") if file("cisamdemo.str") return .T. else return .F. endif // end of createtab.prg
Server-side 4GL programs can be called by all clients, e.g. from a Java class with a JDBC connection:
//--------------------------------- //-- create_str.java -- //--------------------------------- import java.sql.*; import java.io.*; import Recital.sql.*; public class create_str { public static void main(String argv[]) { try { new RecitalDriver(); String url = "jdbc:Recital:" + "SERVERNAME=cisamserver;" + "DIRECTORY=/usr/recital/data/southwind;" + "USERNAME=user;" + "PASSWORD=password"; Connection con = DriverManager.getConnection(url, "user", "pass"); Statement stmt = con.createStatement(); CallableStatement sp = con.prepareCall("{call createtab}"); boolean res = sp.execute(); String outParam = sp.getString(1); System.out.println("Returned "+outParam); sp.close(); con.close(); } catch (Exception e) { System.out.flush(); System.err.flush(); DriverManager.println("Driver exception: " + e.getMessage()); e.printStackTrace(); } try { System.out.println("Press any key to continue..."); System.in.read(); } catch(IOException ie) { ; } } }
The table should be given a ‘.str’ file extension (rather than the default ‘.dbf’) to signify that this is a structure file only.
Please see the end of this article for information on matching Informix and Recital data types
Fig 4: Recital CREATE/MODIFY STRUCTURE worksurface for character mode table creation.
Step 2: Creating the Bridge File
If you have Recital installed on the server platform, the Bridge File can be created using the CREATE BRIDGE worksurface. The corresponding command to modify the bridge file is MODIFY BRIDGE <bridge file>. This is the cisamdemo.dbf bridge file in the CREATE/MODIFY BRIDGE WORKSURFACE:
> modify bridge cisamdemo.dbf
Fig 5: Recital CREATE BRIDGE/MODIFY BRIDGE worksurface for bridge creation.
For Recital Database Server clients, the Bridge File can be created using the Recital/SQL CREATE BRIDGE command:
Recital/SQL CREATE BRIDGE:
CREATE BRIDGE cisamdemo.dbf; TYPE "CISAM"; EXTERNAL "cisamdemo.dat"; METADATA "cisamdemo.str"; ALIAS "cisamdemo"
or:
CREATE BRIDGE cisamdemo.dbf; AS "type=CISAM;external=cisamdemo.dat;metadata=cisamdemo.str;alias=cisamdemo"
The examples above assume that the C-ISAM file, the bridge file and the Recital structure file are all in the current working directory. Full path information can be specified for the <externalname> and the <databasename>. For added flexibility, environment variables can be used to determine the path at the time the bridge is opened. Environment variables can be included for either or both the <externalname> and the <databasename>. A colon should be specified between the environment variable and the file name.
e.g.
CREATE BRIDGE cisamdemo.dbf; TYPE "CISAM"; EXTERNAL "DB_DATADIR:cisamdemo"; METADATA "DB_MIRAGE_PATH:cisamdemo.str"; ALIAS "cisamdemo"
Recital CREATE BRIDGE/MODIFY BRIDGE worksurface:
Fig 6: Recital CREATE BRIDGE/MODIFY BRIDGE worksurface - using environment variables.
Using the Bridge
The Bridge can now be used. To access the C-ISAM file, use the ‘alias’ specified in the Bridge definition.
SQL:
SELECT * FROM cisamdemo
Recital/4GL:
use cisamdemo
Indexes
The cisamdemo.dat file included in the Recital distributions for Linux and UNIX has three associated index keys in the cisamdemo.idx file:
Select area: 1 Database in use: cisamdemo Alias: cisamdemo Number of records: 4 Current record: 2 File Type: CISAM (C-ISAM) Master Index: [cisamdemo.idx key #1] Key: DD+CONFIRM+PROCDATE+CONTROL Type: Character Len: 21 (Unique) Index: [cisamdemo.idx key #2]
Key: DD+SUBSTR(CONFIRM,2,5)+TRADDATE+STR(DOLLARS,13,2) +CURRTRAN+ACCOUNT Type: Character Len: 42 Index: [cisamdemo.idx key #3] Key: DEALER+BRANCH+AFSREP+SUBSTR(PROCDATE,5,2) +SUBSTR(CONTROL,2,4) Type: Character Len: 28
The Recital C-ISAM bridge makes full use of the C-ISAM indexes. SQL SELECT statements with WHERE clauses are optimized based on any of the existing indexes when possible. The following ODBC SELECT call makes use of key #3 rather than sequentially searching through the data file.
SQLExecDirect: In: hstmt = 0x00761BE8, szSqlStr = "select * from cisamdemo where dealer+branch+afsrep=' 00161 595-7912", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "DD", "CONFIRM", "PROCDATE", "CONTROL", "DOLLARS", "DEALER", "TERRITORY", "WOREP", "CURRTRAN", "TRADDATE", "CITY", "ACCOUNT", "PRETRAN", "AFSREP", "REPKEY", "BRANCH", "WODEALER", "BANKCODE", "COMMRATE", "NEWREP", "SETTLE", "POSTDATE" "0159", " 15522", "930312", "13356", 4992.60, "00161", "19", "", "210", "930305", "", "70000100009", "", "595-7912", "930315791", "", "", "59", 0.0000, "1", "", "930315" 1 row fetched from 22 columns.
Using the Recital/4GL, the primary index is set as the master index when the bridge is first opened. Any secondary indexes can be selected using the SET ORDER TO <expN> command. The Recital/4GL SEEK or FIND commands and SEEK() function can be used to search in the current master index.
> SET ORDER TO 3 Master index: [cisamdemo.idx key #3] > SEEK “00161 595-7912”
Appendix 1: Data Types
Informix |
Recital |
Byte |
Numeric |
Char |
Character |
Character |
Character |
Date |
Date |
Datetime |
Character |
Decimal |
Numeric |
Double Precision |
Float |
Float |
Real |
16 Bit Integer |
Short |
Integer |
Numeric |
Interval |
Character |
32 Bit Long |
Integer |
Money |
Numeric |
Numeric |
Numeric |
Real |
Numeric |
Smallfloat |
Numeric |
Smallint |
Numeric |
Text |
Unsupported |
Varchar |
Character |
Appendix 2: C-ISAM RDD Error Messages
The following errors relate to the use of the Recital CISAM Replaceable Database Driver (RDD). They can be received as an ‘errno <expN>’ on Recital error messages:
ERRNO() |
Error Description |
100 |
Duplicate record |
101 |
File not open |
102 |
Invalid argument |
103 |
Invalid key description |
104 |
Out of file descriptors |
105 |
Invalid ISAM file format |
106 |
Exclusive lock required |
107 |
Record claimed by another user |
108 |
Key already exists |
109 |
Primary key may not be used |
110 |
Beginning or end of file reached |
111 |
No match was found |
112 |
There is no “current” established |
113 |
Entire file locked by another user |
114 |
File name too long |
115 |
Cannot create lock file |
116 |
Memory allocation request failed |
117 |
Bad custom collating |
118 |
Duplicate primary key allowed |
119 |
Invalid transaction identifier |
120 |
Exclusively locked in a transaction |
121 |
Internal error in journaling |
122 |
Object not locked |
When installing nomachine on redhat 5.3 64-bit be sure to:
- Make sure you have installed the 64-bit packages as the 32-bit ones will not work.
- add the hostname to /etc/hosts
- Check "Disable encryption of all traffic" (in configuration / advanced tab)
- add the hostname to /etc/hosts
- make sure the host IP is not specified as 127.0.0.1 line
- Uncheck "Disable encryption of all traffic" (in configuration / advanced tab)