PxPlus SQL ODBC

The PxPlus SQL ODBC Driver delivers third party access to PxPlus data. It enables any ODBC-compliant application on Windows or UNIX/Linux platforms to communicate with your PxPlus database from any location on the network.

Note:
As of 2019, the PxPlus SQL ODBC Driver is no longer sold as a stand-alone product.

Two PxPlus SQL ODBC Driver modes are available:

Mode

Description

Local

Accesses local PxPlus data directly

Client/Server

Accesses remote PxPlus data over a network

This section discusses the basic concepts and features of the PxPlus SQL ODBC Driver. It covers the installation and configuration procedures for the PxPlus SQL ODBC Driver and the PxPlus SQL Server, as well as the defining/accessing of data files, and the use of the PxPlus SQL ODBC Driver to access PxPlus data from other languages and applications.

See Frequently Asked Questions for answers to some commonly asked questions about the PxPlus SQL ODBC Driver and other PxPlus File IO products.

Note:
If you are trying to access third party data from PxPlus, see [ODB] Open Database.

ODBC Client/Server

For greater performance and security over the network (without the need for additional software), consider the Client/Server mode of the PxPlus SQL ODBC Driver. This interface performs optimization of query processing on the server side to ensure safe high-speed access to your data, particularly for implementing distributed multi-user applications.

The PxPlus SQL ODBC Driver, when used as a client, is freely distributable. However, to be operational, it must be connected to a fully installed and activated PxPlus SQL Server.

What is ODBC?

ODBC is the acronym for Open Data Base Connectivity, an interface standard that maintains a common access method for DBMS (Data Base Management Systems).

The ODBC interface provides a standard set of functions or APIs (Application Program Interfaces) that allow applications to access a variety of ODBC-compliant databases. It also administers the database names and drivers associated with the data files.

ODBC access is based on SQL (Structured Query Language), which is an English-like database access language designed to enable end-users to view and manipulate data files. Over the years, the SQL language has been standardized by ANSI and adopted by a large number of database manufacturers. SQL's original intent was to provide ad-hoc access to data - but not as a development language or as a database interface tool. With the advent of ODBC and other generic interfaces, SQL became the de-facto standard used to manipulate databases.

Because the SQL language is English-like in its structure, it is easy to learn and understand. The basic SQL directives are:

 

SELECT

To read and return data

 

UPDATE

To alter existing data records

 

INSERT

To add records

 

DELETE

To remove data records

Example:

SELECT cst_id, cst_name FROM Customer

This retrieves customer numbers and names from the Customer file.

For more information on the PxPlus SQL ODBC Driver, see Using the PxPlus SQL ODBC Driver.

ODBC Architecture

Typically, the standard ODBC architecture consists of four major components:

Component

Description

Application

Responsible for interacting with the user and for calling ODBC functions to submit SQL statements to, and retrieve results from, one or more data sources. Some examples of ODBC applications on Windows are Excel and MS SQL Server. Some examples on UNIX/Linux are OpenOffice Calc and isql.

Driver

Processes the ODBC function calls, submits SQL requests to a specific data source, and returns results to applications. In addition, the driver is responsible for interacting with the software needed to access a specific data source.

Driver Manager

Loads/calls drivers on behalf of an application. The Driver Manager processes ODBC function calls or passes them to the driver. The Driver Manager on Windows is odbcad32, while on UNIX/Linux, it is odbcinst.

Data Source

Represents the data to be accessed. It can be a flat-file or a particular database in a DBMS. It also refers to the actual location of the data, as well as any technical information needed to access the data (driver name, network address, user ID, password, etc.).

This architecture enables an application to access different ODBC data sources, in different locations, using the same function calls available in the ODBC API.

Components interact in the following chain of events:

  1. ODBC-compliant application uses API calls to submit SQL directives to the data source.
  2. Communication between the application and ODBC driver is handled by the driver manager, which loads the driver and passes along the API requests.
  3. The ODBC driver implements ODBC API functions for the selected DBMS data source.
  4. Requests are processed by the data source, and the results are sent back up the chain to be retrieved by the application.

Why Use the PxPlus SQL ODBC Driver?

The PxPlus SQL ODBC Driver allows your PxPlus data to be accessed by the most popular database managers, query applications, and report writers: MS SQL Server, Excel or Word with MSQUERY, Informix and Crystal Reports, just to name a few.

Most programming languages have an ODBC access facility to allow files to be read or updated as well. The PxPlus SQL ODBC Driver allows standardized access to PxPlus data via:

The PxPlus SQL ODBC Driver supports three basic types of data: strings, numerics and dates. 

The SELECT statement is used to establish logical relationships between data files (usually referred to as joining files). A typical JOIN would be:

     SELECT cst_id, cst_name, smn_name FROM Customer, Salesman 
          WHERE smn_id = cst_smn 

The statement reads the entire Customer file and, for each customer, reads the Salesman file for any records whose smn_id matches cst_smn. If the field smn_id is a Key field for the file, then the PxPlus SQL ODBC Driver reads the file directly by key; otherwise, the file is read in its entirety. The WHERE clause can be used to selectively filter out any unwanted data. 

The PxPlus SQL ODBC Driver can sort the data on any field using the ORDER BY clause of the SELECT statement. If the ORDER BY fields match any of the key fields of the primary file, then the primary file is accessed by this key. In addition, you can GROUP data BY common fields. 

SUM, COUNT, AVG, MAX and MIN functions can be used to provide statistical information on the data fields.

See Also

Frequently Asked Questions