|
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.
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 Help section explains 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.
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.
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:
|
|
To read and return data | |
|
|
To alter existing data records | |
|
|
To add records | |
|
|
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.
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:
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.