Application development > Querying databases > Querying Paradox and dBASE tables

The common query engine enables BDE application developers to access tables in standard databases using either the SQL or QBE languages. Two categories of SQL statements ("Local SQL") are supported for tables in standard databases:

•Data Definition Language (DDL)

•Data Manipulation Language (DML)

For more specific information about the BDE implementation of the SQL-92 specification, see the Local SQL Guide.

Naming conventions

When writing SQL statements to be used with dBASE, FoxPro, Access, and Paradox tables, observe the following naming conventions:

Table names

Table names that include a period (.) must be placed in either single or double quotation marks. For example:

select * from 'c:\sample.dat\table'

select * from "table.dbf"

Table names can include BDE style aliases. For example,

select * from ":data:table"

Names that are keywords must be placed in quotation marks. For example,

select passid from "password"

Names that have spaces must be placed in quotation marks. For example,

select * from "old table"

Field names

Field names that have spaces must be placed in quotation marks. For example,

select e."Emp Id" from Employee e

Field names that are keywords must be placed in quotation marks. For example,

select t."date" from Table t

Field names that are placed in quotation marks must have a table reference.

Data Manipulation Language

The following DML clauses are supported:

SELECT, WHERE, ORDER BY, GROUP BY, UNION, and HAVING

The following aggregates are supported:

SUM, AVG, MIN, MAX, COUNT

Note: The field type returned by aggregator functions is type DOUBLE.

The following operators are supported:

+, -, *, /, =, <, >, <>, <=, >=, NOT

UPDATE, INSERT, DELETE operations are fully supported to SQL 92 entry level.

For example:

DELETE FROM "Current Cust.db" C

WHERE C."CustID" IN

(SELECT O."CustID"

FROM "Old Cust.db" O)

Also supported

•Subqueries are supported in SELECT, WHERE, and HAVING clauses. In addition to scalar comparison operators ( =, <, > ... ), additional predicates IN, ANY, SOME, ALL, and EXISTS are supported.

•Complex aggregate expressions are supported, including scalar expressions with both aggregation and arithmetic. For example:
SUM( Field * 10 )
SUM( Field ) * 10
SUM( Field1 + Field2 )

•Constructs such as SUM( MIN(Field) ) are supported in projections.

•You can constrain any updateable query by setting the query statement property stmtCONSTRAINED to TRUE before execution. A error will then be returned whenever a modify or insert would cause the new record to disappear from the result set. Refer also to record integrity constraints.

Data Definition Language

The DDL syntax for Paradox, dBASE, FoxPro, and Access tables is restricted to CREATE TABLE (or INDEX), DROP TABLE (or INDEX). For example:

create table parts ( part_no char(6), part_name char(20) )

The following example demonstrates how SQL DDL can be executed through BDE:

hDBICur hCur;
pBYTE szQuery = "create table 'c:\\example\\test.dbf' "
"( fld1 int, fld2 date)";
rslt = DbiQExecDirect(hDb, langSQL, szQuery, &hCur);

For data mappings used by CREATE TABLE and more examples, see the Local SQL Guide.

Querying Paradox, dBASE, FoxPro, and Access tables

Querying Paradox, dBASE, FoxPro, and Access tables