Connecting to specific data sources Professional

Connection data for a specific data source is stored in a ODBC data source which is created/modified by the ODBC Data Source Administrator. The DSN defined on a client computer is sufficient to be included in a connection string so that a DacSession() object can establish a connection. A generic programming pattern for establishing a connection is given in the following code where the name of the ODBC data source is specified using the DSN keyword (see also: How to use a foreign DBMS):

cConnect := "DBE=ODBCDBE" 
cConnect += ";DSN=ODBC-data-source" 
cConnect += ";UID=User-ID" 
cConnect += ";PWD=Password" 

oSession := DacSession():new( cConnect ) 

However, connecting to a data source is possible without creating DSNs on a computer. In this case, all data required for a connection must be included in the connection string when creating a DacSession() object. The examples in this section demonstrate this approach for different data sources. All examples use the same DbeSys() procedure for loading the ODBCDBE:

// DBSESYS.PRG 
#pragma Library( "Adac20b.lib" ) 

PROCEDURE DbeSys 
   DbeLoad( "ODBCDBE" ) 
   DbeSetDefault( "ODBCDBE" ) 
RETURN 

One MDB file of MS Access contains multiple tables which are accessible by their names defined in MS Access. The connection string must contain the MDB file name. Xbase++ then connects to the MDB file and can open individual tables with USE.

// ACCESS.PRG 
#include "Odbcdbe.ch" 

PROCEDURE Main 
   LOCAL cConnect, oSession 

   cConnect := "DBE=ODBCDBE" 
   cConnect += ";DRIVER=Microsoft Access Driver (*.mdb)" 
   cConnect += ";FIL=MS Access" 
   cConnect += ";DBQ=testdb.mdb" 

   oSession := DacSession():new( cConnect ) 

   IF oSession:isConnected() 
      USE Customer 
      Browse() 
      USE 
      oSession:disconnect() 
   ENDIF 
RETURN 

If data in a spread sheet is organized in a continuous row/column scheme it can be accessed like a database table. The name of the table is the name of a cell range in the spread sheet (cell ranges are named using Insert->Names from Excel's menu). This means that one spread sheet may contain multiple tables from the ODBC point of view. The first row of the cell range contains character strings describing the name of each column. The column names are used as the field names of the table when it is opened with the USE command in Xbase++.

// EXCEL.PRG 
#include "Odbcdbe.ch" 

PROCEDURE Main 
   LOCAL cConnect, oSession 

   cConnect := "DBE=ODBCDBE" 
   cConnect += ";DRIVER=Microsoft Excel Driver (*.xls)" 
   cConnect += ";FIL=excel 8.0" 
   cConnect += ";UID=Admin" 
   cConnect += ";DBQ=testdb.xls" 

   oSession := DacSession():new( cConnect ) 

   IF oSession:isConnected() 
      USE Customer 
      Browse() 
      USE 
      oSession:disconnect() 
   ENDIF 
RETURN 

If the cell range's name is unknown or if it is not named, it can be addressed using Excel's naming convention for cell ranges:

USE "A1:E22" ALIAS Customer 
Browse() 

Note, however, that an alias name must be specified when opening a table in this way. Also, the first row of the specified cell range is used as field names. If the addressed cells don't contain data which can be used as valid field names, any further operation can fail.

If a cell range of another worksheet in the same document is to be accessed the cell range must be preceded by the name of the worksheet and the dollar sign. Also, the whole name must be enclosed by double quotas.

USE ('"Worksheet$A1:E22"') ALIAS Customer 
Browse() 

Note, however, that an alias name must be specified when opening a table in this way.

Use the following statement if a cell range of another worksheet in the same document is to be accessed but should not be limited by any cell range.

USE ('"Worksheet$"') ALIAS Customer 
Browse() 

DbDelete() may cause an error because there is no physical recoed concept, thus a physical deletion is not possible. The content of a cell can be deleted by assigning an empty string, for example.

Excel supports not all data types. For example, there is no character type, only the memo type.

The connection to an Oracle DBMS requires the Oracle Client to be installed on a client computer. This software must be configured using data that is also required for the ODBC data source. The following steps describe the configuration of the Oracle Client:

When the Oracle Client is correctly configured, the service name must be used for connecting to the server. The following code is an example using "MyOracle8" as the service name. It accesses the DEMO database and uses the Customer table contained in this database.

// ORACLE81.PRG 
#include "Odbcdbe.ch" 

PROCEDURE Main 
   LOCAL cConnect, oSession 

   cConnect := "DBE=ODBCDBE" 
   cConnect += ";DRIVER=ORACLE ODBC DRIVER" 
   cConnect += ";DBQ=MyOracle8" 
   cConnect += ";UID=John" 
   cConnect += ";PWD=Camelot" 

   oSession := DacSession():new( cConnect ) 

   IF oSession:isConnected() 
      USE DEMO.Customer 
      Browse() 
      USE 
      oSession:disconnect() 
   ENDIF 
RETURN 

This example shows a connection string required for connecting to the sample database of Sybase SQL Anywhere 5.0 running on a server computer. Connection data include in the Start= parameter the location of the SQL Anywhwere Client (DBCLIENT.EXE) that must be installed on a client computer.

// SQLANYW.PRG 
#include "Odbcdbe.ch" 

PROCEDURE Main 
   LOCAL cConnect, oSession 

   cConnect := "DBE=ODBCDBE" 
   cConnect += ";DRIVER=Sybase SQL Anywhere 5.0" 
   cConnect += ";START=d:\sqlany50\win32\dbclient" 
   cConnect += ";UID=dba" 
   cConnect += ";PWD=sql" 
   cConnect += ";DatabaseName=sademo" 
   cConnect += ";AutoStop=yes" 

   oSession := DacSession():new( cConnect ) 

   IF oSession:isConnected() 
      USE Customer 
      Browse() 
      USE 
      oSession:disconnect() 
   ENDIF 
RETURN 

This example shows a connection string required for connecting to MS SQL Server running on a server computer. Connection data include the names or IDs of the client and server computer, user login data and the database to connect to.

// SQLSRV.PRG 
#include "Odbcdbe.ch" 

PROCEDURE Main 
   LOCAL cConnect, oSession 

   cConnect := "DBE=ODBCDBE" 
   cConnect += ";DRIVER=SQL Server" 
   cConnect += ";SERVER=ALASKA02" 
   cConnect += ";UID=John" 
   cConnect += ";PWD=Camelot" 
   cConnect += ";DATABASE=SalesDepartment" 
   cConnect += ";WSID=WorkStationID" 
   cConnect += ";Trusted_Connection=Yes" 

   oSession := DacSession():new( cConnect ) 

   IF oSession:isConnected() 
      USE Customer 
      Browse() 
      USE 
      oSession:disconnect() 
   ENDIF 
RETURN 

Please pay attention to the regional setting. It should be turned off. This ensures proper conversion of data with regional dependency, such as numeric, date, timestamp etc. Alternatively, the setting can be specified in the connection string:

cConnect += ";Regional=no"