Easysoft ODBC-SQI SQL Engine User Guide - Introduction
Introducing the Easysoft ODBC-SQI SQL Engine
The Easysoft ODBC-SQI SQL Engine is a relational database engine that enables heterogeneous access to multiple data sources.
To a client application it is an ODBC 3.5 driver that enhances the functionality of any existing ODBC driver to ODBC 3.5.
The Easysoft ODBC-SQI SQL Engine is a core component of the Easysoft Data Access suite of middleware products.
It can be used with the Easysoft ODBC-ODBC Bridge and the Easysoft JDBC-ODBC Bridge to enable heterogeneous access to multiple local or remote ODBC data sources from non-Windows platforms and Java based applications.
The Easysoft ODBC-SQI SQL Engine product component
The Easysoft ODBC-SQI SQL Engine is available both as a separate product and as a component of the following Easysoft Data Access solutions:
- Easysoft Data Access for ISAM
- Easysoft Data Access for Sage Tetra CS/3
- Easysoft Data Access for Zortec System Z
- Easysoft Data Access for Unisys LINC Developer
With these solutions, the Easysoft ODBC-SQI SQL Engine is usually used in conjunction with either the Easysoft ODBC-ODBC Bridge or the Easysoft JDBC-ODBC Bridge to enable cross-platform data access.
This manual explains the installation, configuration and technical aspects of the Easysoft ODBC-SQI SQL Engine when used as a separate product.
If you have obtained the Easysoft ODBC-SQI SQL Engine as a component of an Easysoft Data Access solution, the technical aspects covered in "Technical Reference" on page 61 are still relevant, but for installation and configuration details you should refer to the manual specific to that Easysoft Data Access solution.
Figure 5 shows how the Easysoft ODBC-SQI SQL Engine works in conjunction with other Easysoft Data Access products.
Figure 1: The Easysoft ODBC-SQI SQL Engine product component
Benefits of using the Easysoft ODBC-SQI SQL Engine
An application accesses a data source via an ODBC 'driver'.
For example, suppose that you are updating your employee records. In your ODBC application, you might choose an option to view all employees in the Sales department.
This command is passed to a Driver Manager which loads the appropriate ODBC driver and sends function calls to it.
The ODBC driver then accesses the data and retrieves the appropriate records which are displayed on the application screen:
Figure 2: The Easysoft ODBC-SQI SQL Engine architecture
Your work may involve the searching, updating or deleting of records on several different sets of data (or data sources), such as:
- employee records stored in a Microsoft Access database
- product data stored in a Microsoft Excel spreadsheet
- customer information stored in legacy C-ISAM files
Problems that can arise with this scenario include:
- your ODBC application may not be able to query more than one data source at a time
- when performing joins on multiple tables, all the data in those tables has to be brought across the network to your ODBC application, draining network resources
- the drivers that connect to the different data sources may have different functionality or may not support particular SQL constructs, such as outer joins
- your queries may take an unreasonably long time to execute
The following sections explain how the Easysoft ODBC-SQI SQL Engine overcomes these problems.
joining multiple data sources into one
The Easysoft ODBC-SQI SQL Engine enables the seamless joining of data from two or more ODBC data sources.
Applications can query, join and modify data from disparate data sources as though the tables were in a single local data source, known as a virtual data source:
Figure 3: Joining multiple data sources into one
Reducing network traffic
The Easysoft ODBC-SQI SQL Engine performs queries where the data is stored, not where your ODBC application is located.
If your data is stored on a remote machine (the server machine) then SQL queries are performed on that machine, so that only the data in the results set is transferred back across the network to your ODBC application, thus keeping network traffic to a minimum:
Figure 4: Reducing network traffic
Without the Easysoft ODBC-SQI SQL Engine, queries are performed on the local machine where your ODBC application is running, which can involve transferring large amounts of data across the network, consuming network resources.
Enhancing the functionality of existing ODBC drivers
The Easysoft ODBC-SQI SQL Engine enhances the functionality of existing ODBC drivers to ODBC 3.5, so that any differences in ODBC conformance between the drivers are irrelevant.
For example, if your existing drivers conform to ODBC 1 or ODBC 2, the Easysoft ODBC-SQI SQL Engine effectively upgrades them to conform to ODBC 3.5:
Figure 5: Enhancing the functionality of existing ODBC
The Easysoft ODBC-SQI SQL Engine optimizes the syntax of your SQL queries to reduce query times to as short a time as possible.
Understanding how your data is queried
Databases can store vast amounts of data and queries are used to search through the data and find exactly the information that you want to look at or edit.
If you had a database of employee records there would probably many ways in which you would want to edit your employee records.
- Add and delete employees
- Change an employee's salary
- Change an employee's address
- Record notes following an employee's annual appraisal
Depending on how the database has been implemented, you might be able to perform these edits via a user interface by selecting particular options or typing into certain fields and then clicking a button to commit the changes.
Behind the scenes, the user interface invokes ODBC function calls to connect to the data source and execute SQL statements to perform the edit.
ODBC is a programming interface that enables applications to connect to relational databases. SQL is a data sublanguage used for manipulating data in relational databases. SQL statements are embedded as arguments of ODBC function calls to perform operations on data.
You can write SQL queries manually, and some advanced users might prefer to do this rather than manipulate data via a user interface.
In some instances, it might not be possible to perform a complex query through the user interface available so the only alternative is to write the SQL query by hand.
A simple SQL query might be:
SELECT * FROM EMPLOYEE WHERE DEPT='SALES'
which would display the details of all employees in the Sales department.
There are different levels of ODBC and SQL conformance.
The Easysoft ODBC-SQI SQL Engine conforms to ODBC 3.5 and supports the SQL minimum grammar (which is a recognized standard) with the majority of SQL-92 extensions.
Conformance within the Easysoft ODBC-SQI SQL Engine is documented fully in "Technical Reference" on page 61, where you will also find details of standards and specifications for ODBC and SQL.
If you are a developer implementing bespoke ODBC applications or customizations for manipulating relational databases, refer to the "Technical Reference" on page 61 for full Easysoft ODBC-SQI SQL Engine technical details.
Reproducing SQL statements
In this documentation there are examples of SQL statements and the results you can expect from particular statements.
When reproducing these SQL statements on your own system, please note that:
- Your SQL tool might require you to end each SQL statement with a semi-colon ';'.
The semi-colon is a separator which allows you to separate one SQL statement from another in the same command, but some SQL tools expect you to include the semi-colon at the end of all statements.
select * from table1 ;
This trailing semi-colon is not included in the syntax in this documentation, as it is not required by the isql tool used as reference.
- For clarity, some sample SQL statements in this manual are set out on multiple lines.
Depending on the SQL tool that you are using, you might be able to enter statements over multiple lines or you might need to type them on one continuous line.
Your SQL tool will probably report an error if it cannot process statements extending over multiple lines.
- If you work with table names that contain spaces, you must place quotation marks around the table names in any SQL statements.
select "New Customers".CustID, "Existing Customers".CustID from "New Customers" and "Existing Customers"
will select the CustID data from both the "New Customers" table and the "Existing Customers" table.
Overview of installation and setup
The main stages in installing and setting up the Easysoft ODBC-SQI SQL Engine are:
1. Download the software from the Easysoft web site (www.easysoft.com).
2. Install the Easysoft ODBC-SQI SQL Engine on the machine from where you intend to access the data sources.
3. Set up your virtual data sources.
4. Once you have created your virtual data sources, you can connect to them via any ODBC-compliant application.
When installing the Easysoft ODBC-SQI SQL Engine for use with the Easysoft ODBC-ODBC Bridge or Easysoft JDBC-ODBC Bridge, there is no particular sequence in which the products should be installed, but you should configure the data sources for accessing your remote data and ensure that they are connecting to the data correctly before including them in a virtual data source.