PxPlus SQL ODBC

Using the PxPlus SQL ODBC Driver

Primarily, ODBC is used to provide read access to data files from other products such as Crystal reports, Excel or Word on Windows and OpenOffice/LibreOffice Calc and Base on UNIX/Linux. Most programming languages have an ODBC access facility to allow files to be read or updated as well. The process of bringing PxPlus data to your application begins with making a data connection the steps involved will vary according to your application and your server technology.

For specifics on PxPlus ODBC DSNs and connection information, see Configuration Procedures.

Once a data connection is established, the PxPlus data itself can be accessed from the database using SQL commands. SQL (Structured Query Language) is the standard interactive programming language for accessing and manipulating databases.

This section describes the specific elements of SQL that can be used with the PxPlus SQL ODBC Driver.

See Example SQL for several examples of different SQL queries.

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

Statements

An SQL statement is used to perform various operations on the database. The PxPlus SQL ODBC Driver supports four types of SQL statements:

 

SELECT

Retrieves data from the database

 

INSERT

Adds new data to the database

 

DELETE

Removes data from the database

 

UPDATE

Changes data in the database

Joining

SQL statements operate with logical sets of data - they declare what data is required, not how the data is to be retrieved. When data is required from two or more tables, the statement can establish a relationship between the tables. In SQL, this concept is called joining. The join operation selects rows from two different tables such that the value in one column of Table 1 also appears in a column of Table 2.

Example:

A customer table includes a code for sales representatives called SALESREP and the sales representative table includes a SALESREP code among other information about sales representatives (names, addresses, etc.). A join relationship between the customer table and sales representative table can be established because they each have a SALESREP column.

To join three or more tables, you can nest a join operation within another join operation.

Types of Joins

The PxPlus SQL ODBC Driver supports four types of joins:

Type

Description

CROSS JOIN

Returns all the records in Table 2 for each common record in Table 1. To create a cross join, a comma is used to separate the declared tables. The keywords CROSS JOIN can be used in place of the comma.

Example:

SELECT * FROM Customer, SalesReps

JOIN / INNER JOIN

Discards unmatched rows in either table. The keyword INNER is optional.

The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00)

Example:

SELECT * FROM {oj Customer INNER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}

LEFT JOIN / LEFT OUTER JOIN

LEFT [OUTER] JOIN will, for each record in Table 1, join the matching record in Table 2, if any. The keyword OUTER is optional.

The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00)

Example:

SELECT * FROM {oj Customer LEFT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}

RIGHT JOIN / RIGHT OUTER JOIN
(as of PxPlus SQL ODBC Driver version 6.00)

RIGHT [OUTER] JOIN will, for each record in Table 2, join the matching record in Table 1, if any. The keyword OUTER is optional.

The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00)

Example:

SELECT * FROM {oj Customer RIGHT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}

FULL JOIN / FULL OUTER JOIN
(as of PxPlus SQL ODBC Driver version 6.10)

FULL [OUTER] JOIN will do a LEFT JOIN and then a RIGHT JOIN. The keyword OUTER is optional.

The outer join escape sequence is optional. (as of PxPlus SQL ODBC Driver version 6.00)

Example:

SELECT * FROM {oj Customer FULL OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP}

Three or More Table Joins

The PxPlus SQL ODBC Driver supports nesting join operations, which allow for joining three or more tables:

Join

Description

JOIN NESTED IN TABLE 1 POSITION
(as of PxPlus SQL ODBC Driver version 6.00)

This will perform a left outer join with Table 1 and Table 2, take the results of that join and perform a left join with Table 3. The parentheses around the nested join are optional.

Example:

SELECT * FROM {oj (Customer LEFT OUTER JOIN SalesReps ON Customer.SALESREP = SalesReps.SALESREP) LEFT OUTER JOIN Shipping ON Customer.CUSTID = Shipping.CUSTID}

JOIN NESTED IN TABLE 2 POSITION

This will perform a left outer join with Table 2 and Table 3, take the results of that join and use them as the right side and left join them with Table 1.

Optional parentheses around the nested join are allowed. (as of PxPlus SQL ODBC Driver version 6.00)

Example:

SELECT * FROM {oj Customer LEFT OUTER JOIN SalesReps LEFT OUTER JOIN Commission ON SalesReps.SALESREP = Commission.SALESREP ON Customer.SALESREP = SalesRep.SALESREP}

Mixing different types of joins is supported; i.e. you can nest an inner join within a left outer join. It is also possible to join more than three tables by nesting more than once. Caution must be used when joining three or more tables, as the more tables you join, the longer the SQL query will take to complete. This is proportional to the sizes of the tables being joined.

Unions (as of PxPlus SQL ODBC Driver version 6.00)

Another method for getting data from two or more tables is to union select statements together. A union combines the results of two or more select statements. The select statements within the union must have the same number of columns. The columns must also have similar data types. In addition, the columns in each select statement must be in the same order.

To union three or more tables together, you can chain unions together one after another.

Example:

If you had an invoice2014 table and an invoice2015 table each containing invoices for their respective years and you wanted to query the invoices for the last two years, you could simply union the results of a select on each table. Using an ORDER BY,it is also possible to sort the combined results from the two tables.

Types of Unions

The two types of unions, UNION and UNION ALL, are explained below:

Type

Description

UNION

A UNION will combine the results of the select statements, remove duplicate records and sort the results based on the first column. This is equivalent to a DISTINCT on the combined results.

Example:

SELECT * FROM invoice2014 UNION SELECT * FROM invoice2015

UNION ALL

A UNION ALL will simply combine the results of the select statements.

Example:

SELECT * FROM invoice2014 UNION ALL SELECT * FROM invoice2015