|
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
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 |
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.
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. |
|
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) |
|
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. |
|
RIGHT JOIN / RIGHT OUTER JOIN |
RIGHT [OUTER] JOIN will, for each record in Table 2, join the matching record in Table 1, if any. The keyword OUTER is optional. |
|
FULL JOIN / FULL OUTER JOIN |
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: |
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 |
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. |
|
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. |
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.
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. |
|
UNION ALL |
A UNION ALL will simply combine the results of the select statements. |