Home arrow Articles arrow Interactive Paradox arrow Local SQL Part 1: Introduction to SQL
24 July 2014
 
 
Local SQL Part 1: Introduction to SQL Print E-mail
Written by Larry DiGiovanni   
23 May 2001
This article is the first in a series of articles on using SQL in Paradox applications.Local SQL Part 1
Introduction to SQL
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com

Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox® (as part of the Borland® Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox® developers as well as interactive end-users.

This is the first in a planned series of articles on using Local SQL with Paradox®/BDE™ applications. This article will focus on describing what SQL and Local SQL are (and what they are not).


What Is SQL?

SQL is a data sublanguage that provides a means of defining, querying, and updating database tables, similar to Query By Example (QBE). The functions and operations supported by SQL are predicated on the relational model of databases introduced by Dr. E. F. Codd in the 1970s. The acronym SQL is commonly represented as standing for Structured Query Language.

SQL is text-based and non-procedural, and is geared towards set-based operations. The language consists of a fairly small number of statement types, each with a set of optional and required clauses, predicates, and argument lists.

SQL is also an acronym which has found its way into the names and trademarks of an assortment of RDBMS (Relational Database Management System) packages, database tools, reporting packages, utilities, etc. This widespread use of the term probably contributes to a general sense of confusion regarding what SQL is and how and when it may be used.


What SQL Is Not

SQL is not a database program. The SQL sublanguage is supported by many different RDBMS platforms, including desktop database applications, like Paradox and MS Access, as well as client/server database systems, like Oracle, MS SQL Server, and Interbase.

SQL is not a programming language. SQL does not provide syntax elements for program control/branching or procedure definition. SQL also does not provide for local variable declaration and use. SQL can be utilized within most contemporary programming languages to perform data access and manipulation. Also, most RDBMS vendors include a procedural extension to SQL with their software. For instance, Oracle supports PL/SQL, and MS SQL Server supports Transact-SQL.

SQL is not limited to client/server database environments. As discussed below, SQL is available within most current desktop database environments as well.


What Is Local SQL?

From the LocalSQL.hlp file: "Local SQL is the subset of the SQL-92 specification used to access dBASE, Paradox, and FoxPro tables. On receving [sic]local SQL statements from front-end applications, the Borland Database Engine (BDE) translates the statements into BDE API functions."

As such, Local SQL is a part of Paradox, Delphi, C++ Builder, and any other application which uses the Borland Database Engine to access data.


What Local SQL Is Not

Local SQL is not SQL Links. SQL Links is a set of drivers used to make Paradox a client to a client/server RDBMS, like Oracle or MS SQL Server.

Local SQL is not Local Interbase.
Local SQL is not a database server.


How Do You Use Local SQL?

Local SQL can be used in the same way as QBE, either interactively or within code. Paradox provides a SQL script editor that allows a developer or end user to create and execute interactive SQL statements.

SQL queries can also be used in ObjectPAL (for Paradox), ObjectPascal (for Delphi) C++ Builder, and other Borland/Inprise development tools.
Also, the Borland Database Engine typically installs with a product called Database Desktop. DBD can be used to create tables and query tables using either Local SQL or QBE.


Why Learn Local SQL?

The simple answer is that Local SQL provides users and developers with yet another tool that can be used in data analysis and software development.
There are times when you’ll find that Local SQL provides a simpler and/or faster solution to a given problem than QBE or ObjectPAL.

Additionally, SQL can be used to do things that QBE cannot, like creating and restructuring tables. Also, there are several functions that can be used in SQL queries that are not available within QBE queries, such as SUBSTRING, which extracts a portion of a string, or EXTRACT, which extracts the day, month, or year from a date.

Last (but not least), SQL has become the lingua franca of databases. Knowledge of Local SQL can be directly applied to working with the SQL dialect of just about any other RDBMS available.


How Do I Learn Local SQL?

The easiest approach is to first familiarize yourself with the basic syntax and rules of SQL, then apply that knowledge and begin writing SQL queries.

If you have existing procedural code or QBE queries that produce result sets, you can use the comparison of the result sets to gauge the accuracy of your SQL queries. Plus, in many cases, an existing QBE query can be converted to SQL by using the "Show SQL" option.


Summary

SQL is a data sublanguage developed for conducting set-based operations on relational databases. It is available for use across the full range of current desktop and client/server RDBMS packages, including Paradox through the use of Local SQL, which is part of the Borland Database Engine, which in turn, is part of Paradox. It can be used with new and existing Paradox applications to perform queries and updates on table data as well as to restructure tables and add/remove indexes. Using Local SQL in Paradox (or other BDE) applications does not require a database server or SQL Links.
In future articles, we will present the major parts of the syntax and rules of SQL, and work through some examples.


Local SQL Part 2
< Prev   Next >
 
Top! Top!