Overview

Recital Database Gateways are available to MySQL, PostgreSQL, Informix, Ingres and Oracle, to OLEDB and ODBC data sources, such as Microsoft Access or SQL Server, to JDBC data sources via third party JDBC drivers, and to Recital itself.

Recital 9 saw the introduction of the Visual FoxPro compatible Remote Data Connectivity Functions. These can be used to provide a framework for using Recital Database Gateway technology. The Remote Data Connectivity Functions combine all the power and flexibility of SQL Pass-Through with automatic cursor creation and fetching of results.

Connecting

The SQLCONNECT() and SQLSTRINGCONNECT() functions are used to make the connection to the Recital Database Gateway.

Both of these functions can make use of a Recital Database Gateway definition file.

SQLCONNECT():

// SQLCONNECT() to an existing Gateway definition file
nStatHand = SQLCONNECT("connect1.gtw")

SQLSTRINGCONNECT():

// When cConnectString is omitted, ‘Select a Gateway’ dialog is displayed
nStatHand = SQLSTRINGCONNECT()

Click image to display full size

Fig 1: Recital: SELECT GATEWAY dialog.

These '.gtw' Database Gateway definition files can be created using the Recital/SQL CREATE CONNECTION command or the Recital CREATE GATEWAY worksurface.

CREATE CONNECTION:

CREATE CONNECTION connect1 AS "type=mysql;node=mysserv.recital.com;" +;
  "userid=user1;password=password1;database=demo"

CREATE GATEWAY:

Click image to display full size

Fig 2: Recital: CREATE GATEWAY.

Alternatively, the connection details can be specified as an argument to the SQLSTRINGCONNECT() function:

// Including cConnectString makes the connection
nStatHand = SQLSTRINGCONNECT("mysql@linux1:user1/pass1-database1.tcpip",.T.)

Both the SQLCONNECT() and SQLSTRINGCONNECT() functions support the driver:datasource connection strings for ODBC, OLEDB and JDBC connections:

// SQLSTRINGCONNECT() to ODBC datasource 'southwind'
nStatHand = SQLSTRINGCONNECT("odbc:southwind",.T.)
// SQLCONNECT() to OLEDB Visual FoxPro datasource
nStatHand = SQLCONNECT("oledb:Provider=vfpoledb.1;" + ;
  "Data Source=C:\Program Files\Microsoft Visual FoxPro 9" + ;
  "\Samples\Data\;Collating Sequence=general")

In all cases the return value, nStatHand in the examples, should be stored to use as the Statement Handle for subsequent function calls.

Disconnecting

The SQLDISCONNECT() function is used to disconnect from the Database Gateway:

nStatHand = SQLSTRINGCONNECT("rec@recital2:user2/pass2"+;
  "-/usr/recital/data/southwind.tcpip",.T.)
SQLTABLES(nStatHand)
select sqlresult
browse
SQLDISCONNECT(nStatHand)

The table information returned by the SQLTABLES() function is automatically fetched into a cursor. The cursor operates as a temporary table with the alias 'sqlresult' allowing the data to be processed and manipulated like any other active table.

Click image to display full size

Fig 3: Recital Mirage .NET Client: BROWSE of SQLTABLES() result set.

SQLCOLUMNS():

nStatHand = SQLSTRINGCONNECT("rec@recital2:user2/pass2"+;
  "-/usr/recital/data/southwind.tcpip",.T.)
SQLCOLUMNS(nStatHand, "accounts", "NATIVE")
select sqlresult 
browse

The column information returned by the SQLCOLUMNS() function is automatically fetched into a cursor. The cursor operates as a temporary table with the alias 'sqlresult' allowing the data to be processed and manipulated like any other active table.

Click image to display full size

Fig 4: Recital Mirage .NET Client: BROWSE of SQLCOLUMNS() result set.

Properties

Environment or current connection properties can be queried using the SQLGETPROP() function and changed using the SQLSETPROP() function. The example below obtains the current user id for the active connection.

nStatHand = SQLSTRINGCONNECT("rec@recital2:user2/pass2"+;
  "-/usr/recital/data/southwind.tcpip",.T.)
cUser = SQLGETPROP(nStatHand,"UserId")
if type("cUser") = "C"
    dialog box "Welcome " + cUser + "!"
endif
SQLDISCONNECT(nStatHand)

Note: The return value from the SQLGETPROP() can be of Numeric, Character or Logical data type, depending on the setting queried and whether the query is successful. The TYPE() function allows the data type of the return value to be checked.

Settings such as the user ID, connection string and password are Read Only, but other settings can be modified to suit the connection and application. In the section below, the “Transactions” setting is examined in more detail.

Executing SQL Statements

The SQLEXEC() function is used to send an SQL statement to be executed by the back-end Database.

nRet = SQLEXEC(nStatHand,"select * from example")
dialog box iif(nRet > 0, "Execute Succeeded", "Execute Failed")

By default, any results set are automatically loaded into a cursor. As before, with SQLTABLES() and SQLCOLUMNS(), the cursor operates as a temporary table with the alias 'sqlresult' allowing the data to be processed and manipulated like any other active table. An alternative cursor alias can be specified, by including the third parameter to the SQLEXEC() function.

nRet = SQLEXEC(nStatHand,"select * from example","myalias")
select myalias
browse

The return value from SQLEXEC() can be checked to determine the success or failure of the statement execution.

nRet = SQLEXEC(nStatHand,"select * from example")
dialog box iif(nRet > 0, "Execute Succeeded", "Execute Failed")

Statements can also be prepared prior to execution, allowing the syntax to be checked and parameters to be loaded before the statement is actually sent to the back-end Database. In this case, the SQLEXEC() just refers to the connection and the previously prepared statement is executed.

mAccountNo = "00081"
nRet = SQLPREPARE(nStatHand,"select account_no,title,;
  last_name,first_name,initial,street,;
  city,state,zip,limit,balance,available,;
  start_date from example where account_no=?mAccountNo ")
if nRet > 0
    nRet = SQLEXEC(nStatHand)
else
    dialog box "Error in Prepared Statement"
endif

Transactions

A connection's “Transactions” setting can be set to Automatic or Manual. When “Transactions” is set to Automatic mode, updates are handled individually and saved automatically. In Manual mode, the SQLCOMMIT() and SQLROLLBACK() functions can be used to control when and if an update is saved. The default “Transactions” setting is Automatic (1); the SQLSETPROP() function is used to change the setting to Manual (2).

nStatHand = SQLCONNECT("connect1.gtw",.T.)
nSetEnd = SQLSETPROP(nStatHand,"Transactions",2)
if nSetEnd = 1
    dialog box [Manual Transactions enabled]
else
    dialog message [Unable to enable Manual Transactions.  Continue?]
    /* continued... */
endif

The setting of Manual Transaction mode starts the transaction. The transaction must be terminated with an SQLCOMMIT() or an SQLROLLBACK(). Both of these functions will operate on all updates issued for the connection since the start of the transaction.

SQLCOMMIT():

/* connection and updates... */
nRet = SQLCOMMIT(nStatHand)
dialog box iif(nRet > 0, "Commit Succeeded", "Commit Failed")

SQLROLLBACK():

/* connection and updates... */
nRet = SQLROLLBACK(nStatHand)
dialog box iif(nRet > 0, "Rollback Succeeded", "Rollback Failed")
/* continued... */

Additional Information

For complete syntax and additional usage information on the Remote Data Connectivity Functions, please refer to the Recital/SQL Documentation distributed with Recital software or online.