This series of articles is intended to discuss the Paradox system from the
point-of-view of the programmer whose primary experience is grounded in
Microsoft Access.

This forum is duplicated on our site at http://www.sundialservices.com/forum
in the "General" section, forum "TechTips," and we invite you to also
comment there.

====
DATA MODEL AND CHARACTERISTICS:

Microsoft Access was founded on the SQL-92 data model, which pervades the
entire system. All of the information in an MDB file is fundamentally "an
SQL table," and the only mechanism that the entire system uses to access
that file is through the JET{tm} database engine. Access also supports
ODBC and allows "attached tables" which do not necessarily conform to the
SQL standard, but operates with them much more poorly. This basic
architecture is shared with other systems, including Microsoft SQL Server,
SyBase, and to a certain extent, Oracle.

The SQL system is designed from the seminal research done by Codd and
others, and MS-Access/JET conforms to it quite closely. Tables consist of
a flat structure of rows and columns: width is limited (but large);
length, essentially, is not. Like other SQL systems, data is stored in a
"page-oriented" physical layout and information is somewhat compressed.
For example, if a character field allows up to 255 characters but the value
stored in a cell is only 20 characters long, only 20 characters (more or
less) are stored. SQL supports the use of indexes to reduce access-time to
a particular value, but in SQL the =only= means of access to the data is by
means of a query, written in the SQL data access language. An SQL query
says "what, not how." The query describes what you want to see in the
result, but it does not tell the system how to obtain the information.
Even programmatic actions -- recordsets and so-forth -- use queries.

Paradox, on the other hand, is built on a much earlier model in which the
SQL-92 standard either did not exist yet(!), or could not be supported by
the computers of the era. The DOS environment, where a machine holding a
"whopping 640 kilobytes" of RAM was really too much to expect, and a "2
gigabyte" hard-drive was beyond comprehension, is the world in which the
original Paradox system (like dBase and FoxBase) was meant to run. It was
a big leap forward from dBase in the sense that it did support real queries
"at all," but it simply could not support SQL. No one could. Procedural
programming was required to do most things, and the relationship of that
language (PAL) to the rest of the system was, particularly at first,
essentially a macro system.

Paradox tables, as we have said before, exist in a group of files in a
single directory. Referential integrity is supported within tables, and
between tables to a certain extent. Indexes are usable when processing
queries and are automatically detected, but you can define explicit indexes
and use them explicitly, either in programs or in the user-interface, in
ways that are not contemplated in the SQL standard. Tables can also be
accessed programmatically without the use (expressed or implied) of
queries.

The data-storage format uses fixed-length fields. If a character field is
defined to hold up to 255 characters, (slightly more than) that amount of
storage is reserved on-disk for every value, NULL or not, regardless of
actual length stored. There is no compression. This data-storage format
is what is commonly known as VSAM (Virtual Sequential Access Method).

The Paradox system, unlike Access, provides explicit, user-controlled
locking .. of records, tables, or directories. Record-locks are imposed on
a per-record basis; not per-page as in Microsoft Access. Here the VSAM
oriented architecture of Paradox tables pays back handsomely in terms of
speed in multi-user situations.

The query system of Paradox, which we'll discuss more in a later TechTip, is
based fundamentally on QBE (Query By Example). Some queries can be
transformed into SQL syntax, and this is done when interacting with
SQL-only external tables (such as Access or SQL Server), but there are many
useful types of queries that can be expressed in QBE which cannot be
expressed in SQL. Whereas Microsoft Access provides a QBE window and
translates the QBE inputs into SQL, the Paradox engine is fundamentally
built on QBE and actually translates SQL-style queries into that format for
execution.

----
ChimneySweep(R): F-A-A-ST table repair at a click of the mouse!
http://www.sundialservices.com/products/chimneysweep

Sundial Services wrote:

AFAIK, page-oriented is only used in MS/Sybase SQL Server, and nowadays only
as a throwback to lock escalation.

You know this for sure? I sort of doubt it.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry DiGiovanni wrote:
I'm sure that storage formats -do- differ, and in an intentionally
simplified article I didn't want to muddy the water too much.

There are, basically, two major storage formats that you will see. One is
as is used by Paradox, where each record occupies a strictly fixed amount
of storage and (usually) leftover space in each block is not used.

The second, which I attributed to Access, is also like the one used within
the Paradox .MB file: multiple storage records per block, each of variable
length, sometimes with basic compression such as run-length encoding (RLE).
Short values take up less space than longer ones do.


It is true! If you are passing the query straight through to an SQL server
then it will not be processed by QBE, but if it's Paradox tables, it's QBE.

----
ChimneySweep(R): F-A-A-ST table repair at a click of the mouse!
http://www.sundialservices.com/products/chimneysweep

Côme wrote:

Agreed. Or CAST or EXTRACT.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry DiGiovanni wrote:
Yay!

----
ChimneySweep(R): F-A-A-ST table repair at a click of the mouse!
http://www.sundialservices.com/products/chimneysweep