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.

Chapter Guide

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:

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" 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:

Problems that can arise with this scenario include:

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 drivers

Query optimization

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.

For example:

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", 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" 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:

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.

For example:

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.

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.

For example:

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.