Using Database Gateways with the Remote Data Connectivity Functions

Recital Developer Center / Technical Articles /Using Database Gateways with the Remote Data Connectivity Functions

Using Database Gateways with the Remote Data Connectivity Functions

Related Links
Recital Home Recital Product Family Recital Terminal Developer Recital Visual Developer Recital Database Server Recital Web Developer

	Email this article
	Print this article

The first in a series of articles examining the use of Recital Gateways and Bridges across different platforms. This article looks at the use of the Remote Data Connectivity Functions with Recital Database Gateways.

Yvonne Milne
August, 2004. Updated August 2005.

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 Terminal Developer: SELECT GATEWAY dialog.

These '.gtw' Database Gateway definition files can be created using the Recital/SQL CREATE CONNECTION command or the Recital Terminal Developer 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 Terminal Developer: 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:

SQLSTRINGCONNECT(nStatHand)

Metadata

The SQLTABLES() and SQLCOLUMNS() functions allow you to query the Database Gateway metadata, providing information about the data source's tables and the columns belonging to those tables respectively.

SQLTABLES():

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... */

Click image to display full size

Fig 5: Recital Mirage .NET Client: Manual Transactions.

Manual Transactions example code:

// Function to issue Update Statement
function update_it
  dialog message "Update with any changes?"
  if lastkey() = 89
    mAccno = sqlresult.account_no
    mLastName = sqlresult.last_name
    mFirstName = sqlresult.first_name
    mTitle = sqlresult.title
    mInitial = sqlresult.initial
    mStreet = sqlresult.street
    mCity = sqlresult.city
    mState = sqlresult.state
    mZip = sqlresult.zip
    mLimit = sqlresult.limit
    mBalance = sqlresult.balance
    mAvailable = sqlresult.available
    mStartDate = sqlresult.start_date
                     
    nRet = SQLEXEC(nStatHand,"update example set last_name=?mLastName,;
            first_name=?mFirstName,;
            title=?mTitle,;
            initial=?mInitial,;
            street=?mStreet,;
            city=?mCity,;
            state=?mState,;
            zip=?mZip,;
            limit=?mLimit,;
            balance=?mBalance,;
            available=?mAvailable,;
            start_date=?mStartDate where account_no=?mAccno")
    dialog box iif(nRet > 0, "Update Succeeded", "Update Failed")
  else
    dialog box "Update cancelled"                      
  endif 
return .t.

// Function to handle image button selection
function v_imagebtn
  do case
  // Update
  case mimagebtn = 1
    update_it()
  // Commit and Exit 
  case mimagebtn = 2
    nRet = SQLCOMMIT(nStatHand)
    dialog box iif(nRet > 0, "Commit Succeeded", "Commit Failed")
    SQLDISCONNECT(nStatHand) 
    show object "system" properties "method=exit"
  // Rollback and Exit 
  case mimagebtn = 3
    dialog message "Are you sure you want to rollback changes and exit?"
    if lastkey() = 89
      SQLROLLBACK(nStatHand)
      SQLDISCONNECT(nStatHand)                      
      show object "system" properties "method=exit"
    else 
      dialog box "Rollback cancelled"
    endif
  // Go Top
  case mimagebtn = 4 
    go top
    show gets
  // Go Previous
  case mimagebtn = 5 
    skip -1
    if bof()
      dialog box [No Previous Records]
      go top
    endif
    show gets
  // Go Next
  case mimagebtn = 6
    skip
    if eof()
      go bottom
      dialog box [No More Records]
    endif
    show gets
  // Go Bottom
  case mimagebtn = 7 
    go bottom
    show gets
  endcase
return .t.

// Main Program
  show object "region" ; 
  properties "command=define;id=region1;;
  region=2,4,74,22;title=Using Database Gateways"
                     
  // Connect
  nStatHand=SQLCONNECT("connect1.gtw",.T.)
  if nStatHand < 1
    dialog box [Could not connect]
  else
  // Enable Manual Transactions
  nSetEnd = SQLSETPROP(nStatHand,"Transactions",2)
  if nSetEnd <> 1
    dialog message [Unable to enable Manual Transactions. Continue?]
    if lastkey() <> 89
      SQLDISCONNECT(nStatHand)
      return
    endif
  endif
                     
  // Select records
  nRet = SQLPREPARE(nStatHand,"select account_no,title,;
    last_name,first_name,initial,street,;
    city,state,zip,limit,balance,available,;
    start_date from example")
  if nRet > 0
    nRet = SQLEXEC(nStatHand)
                     
    // Create screen to edit records from automatically created cursor
    select sqlresult
    @03,04 to 23,77 properties"borderStyle=etchedIn;;
      borderwidth=1;backcolor=lightsteelblue;fill=True"
    @05,14 say [Account Code:]
    @05,40 get account_no when .F.
    @06,14 say [Personal Title:]
    @06,40 get title
    @07,14 say [Last Name:]
    @07,40 get last_name
    @08,14 say [First name]
    @08,40 get first_name
    @09,14 say [Middle Initial:]
    @09,40 get initial
    @10,14 say [Street:]
    @10,40 get street
    @11,14 say [City:]
    @11,40 get city
    @12,14 say [State:]
    @12,40 get state
    @13,14 say [Zip:]
    @13,40 get zip
    @14,14 say [Credit Limit:]
    @14,40 get limit
    @15,14 say [Credit Balance:]
    @15,40 get balance
    @16,14 say [Credit Available:]
    @16,40 get available
    @17,14 say [Start Date:]
    @17,40 get start_date 
    @21,04 to 23,77 properties "borderStyle=etchedIn;borderwidth=1"
    store 1 to mimagebtn
    @22,25 get mimagebtn;
    function "*HNB *save22x22xp;*ok22x22xp;*cancel22x22xp;*gotopbtnXP;;
      *goprevbtnXP;*gonextbtnXP;*goendbtnXP";
    valid v_imagebtn();
    size 1,1,1                    
    READ
  else
    dialog box "Error in Prepared Statement"
  endif
  SQLDISCONNECT(nStatHand)
endif

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.