Lesson 16 - SQL, ODBC and ADO application support

Top  Previous  Next

The Clarion IDE provides powerful support for applications connecting to SQL, ODBC, and ADO based data sources, through an easy to use Dictionary Synchronizer, and Language and Template support. This lesson examines these areas, and gives you a summary of all of the tools available.

Guidelines for selecting a driver

To a new developer, selecting the right database driver to use in your applications can be a perplexing decision. Here are some general guidelines to follow:

 

Getting Started

Choosing a file system is an important decision, and we encourage you to gather as much information from as many good sources as you can to support your decision. Although the choice of file systems is important, with Clarion, it is not irrevocable. If the file system you choose does not live up to your expectations, you can change to one that does. For example, some developers use the TopSpeed file system for project development, then switch to an SQL file system during project implementation in order to postpone the expense of the SQL software and server hardware until late in the development cycle.

 

ISAM Native Drivers

AN ISAM (Indexed Sequential Access Method) file is best used with programs accessed by a single, or limited amount of users. It is normally stored on a local drive, or a network server if accessed by multiple users. In this configuration, the access time to read and write data is adequate. The cost of implementing this type of database is also minimal. However, as the number of users increases, and the size of the data stored grows, your access times to read and write your data may decrease, and an SQL based database should be considered.

Native ISAM file types supported by Clarion:

ASCII, BASIC, Btrieve, Clarion 2.1, Clipper, dbaseIII, dbaseIV, DOS (Binary), FoxPro 2.6, and TopSpeed. For more information, see the main help section on ISAM Database Drivers.

 

SQL Native Drivers

SQL is actually a declarative language that has become the standard query language for many database systems. Advantages are numerous, including more concurrent users, ability to access data from remote servers (sometimes on different platforms), numerous security and backup/restore features, and expanded query abilities. The disadvantages are cost and a learning curve with its configuration and language familiarization. However, with the rise applications deployed on the Internet, SQL databases’ long term cost savings in development time and maintenance are significant.

Native SQL file types supported by Clarion:

Microsoft SQL, Oracle, Pervasive.SQL, and SQL Anywhere. For more information, see the main help section on Database Drivers.

 

Application support of ISAM and SQL databases

With both ISAM and SQL dictionary definitions, Clarion’s Application Generator provides two basic template sets (Clarion and ABC) to use in your application design.

Behind the scenes, the SQL drivers convert standard Clarion I/O statements and function calls into optimized SQL statements, which they send to the back end SQL servers for processing.

Although both sets support the SQL drivers defined in the data dictionary, the ABC template chain offers extended options for SQL databases.

 

Global Support

In the Global Properties Classes tab control, the Browser Class (the library that supports the retrieval of data rows to a selected list box) has additional configuration options that optimize database performance based on the file system you are using.

See the Browser Class configuration topic for more information.

SQL Advanced Support

The ability to return a row of records from an SQL database is provided by the BrowseBox control template, and is included by default in all default Browse procedures.

 

When an SQL driver is detected as the primary table used in the Browse Box, the template registry provides an additional SQL Advanced tab control. This control allows you to extend the optimized SQL statements generated by the template. For more detailed information, see the SQL Advanced Tab topic in the core help.

 

Language Support

Finally, don’t forget the Application Generator’s built-in template embeds that allow you to extend the functionality and performance of the basic template defaults. Each SQL driver (or accelerator) contains a number of Driver String and Properties that allow you to extend the SQL application beyond its default behavior. Refer to the core help and Database Drivers manual for a more detailed look at the rich support that is offered.

 

The ODBC Driver

A good rule of thumb is to always use a native driver if you have the opportunity to do so (namely, it is on the list of drivers provided by Clarion). But what do you do when your database it not on the list of drivers?

First, check to see that the database vendor supplies an ODBC driver. Once you have installed the vendor’s ODBC driver support, use the Clarion ODBC driver to attach to the data source. (See Lesson 7 in the Dictionary section to review how this is done).

See the ODBC:Overview and other topics in the core help for a more detailed examination.

Finally, ADO Support

ADO (ActiveX Data Objects) is a relatively new interface for establishing a connection to virtually any database. Think of ADO as a higher layer of abstraction over the ODBC connectivity layer. They are objects used to connect to various data sources. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

Again, like ODBC, your database vendor should provide you with the proper ADO libraries. When they are installed they will look like other ODBC data sources that are registered.

The next step is to import the ADO data source into the Clarion dictionary. Although it is registered and accessed like all other drivers, keep in mind that the Clarion ADO "driver" is merely holding the connection string information that can be used by the ADO Connection Object in the Application Generator. The ADO "file structure" imported in the dictionary is present to satisfy some template requirements in the application generator and to assist you in your application design sessions.

To access ADO data sources in your applications, a complete set of ADO support templates are shipped with Clarion. Here is a complete list of these templates:

 

ADO Extension Templates

ADOSupport

A required global extension for all ADO based applications

QcenterSupport

Support to modify BrowseQBEList default expressions.

ADORecordsetObject

Support to access methods in RecordSet object

ADOCommandObject

Support to access methods in Command object

AddADOFile

Add an ADO file not associated directly with procedure

ADOloginExtension

Add support to create a Login procedure.

 

ADO Procedure Templates

ADOErrorsProc

ADOLoginProc

Browse

Form

Process

Report

 

ADO Control Templates

ADOBrwLocator

ADOErrList

ADOLoginControl

BrowseBox

BrowseBoxSelectButton

BrowseProcessButton

BrowseQBEList

BrowseUpdateButtons

PauseProcessControl

ProcessControl

SaveButton

 

ADO Code Templates

ADOCommandExecute

ADORecordsetGetBOF

ADORecordsetGetCacheSize

ADORecordsetGetCursorLocation

ADORecordsetGetEOF

ADORecordsetGetMaxRecords

ADORecordsetGetPageCount

ADORecordsetGetPageSize

ADORecordsetOpen

ADORecordsetPutCacheSize

ADORecordsetPutCursorLocation

ADORecordsetPutMaxRecords

ADORecordsetPutPageSize

ADORecordsetToXML

BrowseRefresh

BrowsetoXML