As well as supporting Visual FoxPro SQL, the Recital Database Server incorporates a Visual FoxPro compatible 4GL that provides developers with the ability to write Stored Procedures, Triggers and User-Defined Functions in a language that they are familiar with.
The Recital Database Server runs on Linux, UNIX, and OpenVMS, providing Visual FoxPro developers with an open solution and the ability to choose the backend server that they want their data to reside on. Building Visual FoxPro client applications that leverage the power of backend Linux and UNIX servers is a very compelling reason for Visual FoxPro developers to consider using the Recital Database Server as their server of choice for application deployment.
The Recital Client Drivers that are included with the Recital Database Server are all internet enabled and connect with the server using TCP/IP. This allows these drivers to be used for both local and remote data access.
Tip
You can also extend the Recital Database Server with C Extension Libraries and use the functions defined within that library also.
Some of the Benefits of the Recital Database Server to Visual FoxPro Developers
- Use Windows, Linux, UNIX or OpenVMS backend database servers
- Internet enabled data access
- Develop Stored Procedures, Triggers and User-Defined functions in a language that you already know; Visual FoxPro
- Supports SETRESULTSET( ) allowing Stored Procedures to return Result Sets.
- Visual FoxPro compatible SQL Pass Through Functions (SPT)
- Navigational and SQL data access
- Supports Very Large Databases (tables larger than 2GB) up to 2^63 bytes
- Superb multi-user performance with fine granularity row-level locking
- Supports joins across multiple databases using the database!table command syntax
- Supports both optimistic and pessimistic row-level locking
- Flexible security system down to column-level protection
- Extensible using dynamically loadable C function and class libraries
- Has integrated XML language support providing Microsoft ADO.NET XML compatible data import and export
- Data source independent Visual FoxPro compatible SQL (ANSI compliant with Oracle and MySQL extensions)
- Built-in Client/Server capabilities with universal data access using open system Gateways and Bridges
- Visual FoxPro client applications can work with data from various geographic locations
- Supports enhanced database security with built-in DES3 table encryption
- Supports structured error handling using TRY/CATCH/THROW...FINALLY
- World-wide support
Using the Recital Database Administrator
You can administer the Recital Database Server through the Database Administrator which is incorporated into Recital Enterprise Studio. Using the Database Administrator you can create and modify Databases, Tables, Indexes, Remote Connections, Stored Procedures, Triggers and User-Defined Functions in a language you are familiar with; Visual FoxPro. The language that you write in on the client using Visual FoxPro is the same language that you can use in the Recital Database Server.
Accessing Remote Data using Visual FoxPro
There are several ways in which Visual FoxPro can access remote data in the Recital Database Server.
- ODBC
- Remote Views
- Remote Connections
- SQL Pass Through Functions
- Cursor Adapters
ODBC
To use any of the above methods of accessing remote data in the Recital Database Server, you will need to download and install the Recital Universal ODBC Driver and setup a Data Source using the ODBC Administrator in Windows.
Remote Views
Once you have setup an ODBC Data Source, you can create a Remote View in Visual FoxPro. When you use a remote view, Visual FoxPro will make a connection to the Recital Database Server using the Recital Universal ODBC Driver. Remote Views are capable of everything that local views are, including updating remote data via the view.
Remote Connections
A Connection is a predefined remote data source that has been stored in a Visual FoxPro Database Container. When you use a connection, Visual FoxPro will make a connection to the Recital Database Server using the Recital Universal ODBC Driver. Remote Views are capable of everything that local views are, including updating remote data via the view.
SQL Pass Through Functions
In Visual FoxPro, SQL Pass Through (SPT) consists of a set of functions that allow you to send commands directly through ODBC. The Recital Database Server also supports these commands natively, so you can use them in Stored Procedures on the server also. SQL Pass Through enables you to take advantage of certain features of the Recital Database Server that Remote Views cannot access. This includes Data Definition, Table Creation, Server Administration (Alter Table, Create Indexes etc) and the ability to call stored procedures. These stored procedures can also return a Result Set if required using the SETRESULTSET( ) function. A Remote View is limited to only using the SQL SELECT command, whereas SQL Pass Through can execute any command supported by the Recital Database Server, including all the Visual FoxPro build-in commands such as PACK, ZAP, COPY etc. When you use the SQLExec( ) function, you can prefix the command that you specify as a argument to this function with the word "recital " and any 4GL command can be executed e.g.
dbHandle = SQLConnect("My Recital Database Server Connection")
if (handle >= 0)
SQLExec(dbHandle, "recital OPEN DATABASE southwind EXCLUSIVE")
SQLExec(dbHandle, "recital PACK DATABASE")
SQLExec(dbHandle, "recital OPEN DATABASE southwind")
SQLExec(dbHandle, "SELECT * FROM customers", "customers")
SELECT customers
BROWSE
endif
The following provides a summary of the SQL Pass Through Functions supported by both Visual FoxPro and the Recital Database Server.
| Function | Description |
|---|---|
| SQLConnect() |
Establishes a connection to a data source |
| SQLStringConnect() | Establishes a connection to a data source using a connection string |
| SQLDisconnect() | Terminates a connection to a data source |
| SQLCancel() | Requests cancellation of an executing SQL statement |
| SQLExec() | Sends a SQL statement to the data source, where the statement is processed |
| SQLMoreResults() | Copies another result set to a Visual FoxPro cursor if more result sets are available |
| SQLPrepare() |
Prepares a SQL statement for remote execution by SQLExec( ) |
| SQLCommit() | Commits a transaction |
| SQLRollback() | Cancels any changes made during the current transaction |
| SQLTables() | Stores the names of tables in a data source to a Visual FoxPro cursor |
| SQLColumns() |
Stores a list of column names and information about each column for the specified data source table to a Visual FoxPro cursor |
| SQLGetProp() | Retrieves the current or default settings for an active connection |
| SQLSetProp() | Specifies settings for an active connection. You can use SQLSETPROP( ) to specify settings at the connection level. To specify Visual FoxPro default settings at the environment level, include 0 as the statement handle |
Cursor Adapters
The CursorAdapter class which was introduced in Visual FoxPro 8 supports handling a wide range of data sources as native Visual FoxPro cursors. This includes the ability to connect to and work with data in the Recital Database Server on remote servers. There's a good article here that shows how to use the CursorAdapter class that was introduced in Visual FoxPro 8. By setting the DataSourceType to "ODBC", and then using the SQLConnect( ) or SQLConnectString( ) SQL Pass Through Functions (SPT) to set the DataSource property to the connection handle returned by these SPT functions, you can access data in the Recital Database Server on a remote Linux, UNIX or OpenVMS server. You can find other discussions concerning the CursorAdapter here.
* Example Visual FoxPro code
ca = CreateObject("CursorAdapter")
ca.DataSourceType = "ODBC"
ca.DataSource = SQLConnect("My Recital Database Server Connection")
ca.SelectCmd = "select * from customers"
ca.Alias = "Customers"
ca.FetchSize = 25
ca.FetchAsNeeded = .t.
ca.MaxRecords = -1
ca.CursorFill()
Browse