Home Products Services Documentation Blog Downloads Store

search...

Home Blogs Using the Recital Database Server with Visual FoxPro

Login

Username

Password

Remember Me

Forgot yourpassword?Forgot yourusername?Create an account

Main Menu

CompanyBlogsFAQWeblinksContact DetailsSales EnquirySubmit FeedbackPrivacy PolicyTerms of Service

Using the Recital Database Server with Visual FoxProIn this article Barry Mavin, CEO and Chief Software Architect for Recital, details how to use the Recital Database Server with Visual FoxPro.

Visual FoxPro has its own local database engine but this is Windows specific and is not client /server. The Recital Database Server is a completeData integration server that has ANSI SQL support as well as a Visual FoxPro compatible SQL engine. This SQL engine works against its ownnative database and a wide range of other supported data sources.

As well as supporting Visual FoxPro SQL, the Recital Database Server incorporates a Visual FoxPro compatible 4GL that provides developerswith 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 tochoose the backend server that they want their data to reside on. Building Visual FoxPro client applications that leverage the power of backendLinux and UNIX servers is a very compelling reason for Visual FoxPro developers to consider using the Recital Database Server as their server ofchoice 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 DevelopersUse Windows, Linux, UNIX or OpenVMS backend database serversInternet enabled data accessDevelop Stored Procedures, Triggers and User-Defined functions in a language that you already know; Visual FoxProSupports SETRESULTSET( ) allowing Stored Procedures to return Result Sets.Visual FoxPro compatible SQL Pass Through Functions (SPT)Navigational and SQL data accessSupports Very Large Databases (tables larger than 2GB) up to 2^63 bytesSuperb multi-user performance with fine granularity row-level lockingSupports joins across multiple databases using the database!table command syntaxSupports both optimistic and pessimistic row-level lockingFlexible security system down to column-level protectionExtensible using dynamically loadable C function and class librariesHas integrated XML language support providing Microsoft ADO.NET XML compatible data import and exportData 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 BridgesVisual FoxPro client applications can work with data from various geographic locationsSupports enhanced database security with built-in DES3 table encryptionSupports structured error handling using TRY/CATCH/THROW...FINALLYWorld-wide support

Using the Recital Database AdministratorYou 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 andUser-Defined Functions in a language you are familiar with; Visual FoxPro. The language that you write in on the client using Visual FoxPro is thesame language that you can use in the Recital Database Server.

Accessing Remote Data using Visual FoxProThere are several ways in which Visual FoxPro can access remote data in the Recital Database Server.

ODBCRemote ViewsRemote ConnectionsSQL Pass Through FunctionsCursor Adapters

ODBCTo use any of the above methods of accessing remote data in the Recital Database Server, you will need to download and install the RecitalUniversal ODBC Driver and setup a Data Source using the ODBC Administrator in Windows.Remote ViewsOnce you have setup an ODBC Data Source, you can create a Remote View in Visual FoxPro. When you use a remote view, Visual FoxPro willmake a connection to the Recital Database Server using the Recital Universal ODBC Driver. Remote Views are capable of everything that localviews are, including updating remote data via the view.

Using the Recital Database Server with Visual FoxPro http://www.recital.com/index.php?option=com_content&...

1 of 2 3/31/2014 2:59 PM

Copyright 2014 Recital Software Inc.

Remote ConnectionsA 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 ofeverything that local views are, including updating remote data via the view.SQL Pass Through FunctionsIn Visual FoxPro, SQL Pass Through (SPT) consists of a set of functions that allow you to send commands directly through ODBC. The RecitalDatabase Server also supports these commands natively, so you can use them in Stored Procedures on the server also. SQL Pass Throughenables you to take advantage of certain features of the Recital Database Server that Remote Views cannot access. This includes DataDefinition, Table Creation, Server Administration (Alter Table, Create Indexes etc) and the ability to call stored procedures. These storedprocedures can also return a Result Set if required using the SETRESULTSET( ) function. A Remote View is limited to only using the SQLSELECT command, whereas SQL Pass Through can execute any command supported by the Recital Database Server, including all the VisualFoxPro build-in commands such as PACK, ZAP, COPY etc. When you use the SQLExec( ) function, you can prefix the command that you specifyas 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 DescriptionSQLConnect() Establishes a connection to a data sourceSQLStringConnect() Establishes a connection to a data source using a connection stringSQLDisconnect() Terminates a connection to a data sourceSQLCancel() Requests cancellation of an executing SQL statementSQLExec() Sends a SQL statement to the data source, where the statement is processedSQLMoreResults() Copies another result set to a Visual FoxPro cursor if more result sets are availableSQLPrepare() Prepares a SQL statement for remote execution by SQLExec( )SQLCommit() Commits a transactionSQLRollback() Cancels any changes made during the current transactionSQLTables() Stores the names of tables in a data source to a Visual FoxPro cursorSQLColumns() 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 connectionSQLSetProp() 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 AdaptersThe CursorAdapter class which was introduced in Visual FoxPro 8 supports handling a wide range of data sources as native Visual FoxProcursors. This includes the ability to connect to and work with data in the Recital Database Server on remote servers. There's a good article herethat shows how to use the CursorAdapter class that was introduced in Visual FoxPro 8. By setting the DataSourceType to "ODBC", and thenusing the SQLConnect( ) or SQLConnectString( ) SQL Pass Through Functions (SPT) to set the DataSource property to the connection handlereturned by these SPT functions, you can access data in the Recital Database Server on a remote Linux, UNIX or OpenVMS server. You can findother 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

Using the Recital Database Server with Visual FoxPro http://www.recital.com/index.php?option=com_content&...

2 of 2 3/31/2014 2:59 PM