SQL Support
Chapter Updated 04/28/23
SQL Command Status
Development of SQL support is still underway and very preliminary. As of the 4.0.3 version, the following SQL commands
are available.
The SQL commands are modeled after industry standard SQL specifications and do what you would expect an SQL command to do.
Use of [brackets] in this chapter identifies optional components of a given command.
ALTER TABLE
Expected format:
ALTER TABLE tablename.DBF RENAME TO newtablename.DBF
CREATE INDEX
Expected format to create an Dbase 3, NDX index:
CREATE INDEX ixname.NDX ON tablename.dbf ( EXPRESSION ) [ASSOCIATE]
Expected format to create an Dbase 4, tag on an MDX index:
CREATE [UNIQUE] INDEX tagname ON tablename.DBF ( EXPRESSION ) [DESC] [FILTER .NOT. DELETED()]
The ASSOCIATE parameter is specific to Xbase64 library, it is used to associate non production (NDX) index file to a dbf
file so it will be automatically opened with the dbf file whenever the dbf file is opened by the xbase64 routines.
The [ASSOCIATE] parameter is not used with MDX production indices
Xbase first looks for ".NDX" in the file name to determine if an NDX index should be created.
If .NDX is not in the filename, it looks in the uda for "IXTYPE" for either NDX or MDX to
detmermine the index type to create. if IXTYPE is not found, it creates an MDX tag.
The optional DESC parameter defines an entire index key as descending. This is
different than other SQL implementations where specific fields can be descending.
The optional FILTER parameter is specific to the XBASE64 library, is it used to assign a filter to a tag in an
MDX style index. Everything to the right of the keyword FILTER is considered part of the filter.
The original DBASE indices used to '+' to create an index on more than one field
ie: FIELD1+FIELD2+FIELD3
The Xbase library supports either '+' or ',' when creating mutli field indices.
CREATE TABLE
Expected format:
CREATE TABLE tablename.dbf (Field1 CHAR(10), INTFLD1 INTEGER, ... )
| SQL TYPE | XBASE Field Type |
|---|
| SMALLINT | NUMERIC(6,0) |
| INTEGER | NUMERIC(11,0) |
| DECIMAL(x,y) | NUMERIC(x+1,y) |
| NUMERIC(x,y) | NUMERIC(x,y) |
| FLOAT(x,y) | FLOAT(x,y) |
| CHAR(n) | CHARACTER(n) |
| DATE | DATE |
| VARCHAR | MEMO |
| LOGICAL | LOGICAL |
DELETE
Expected format:
DELETE FROM tablename.DBF [WHERE expression]
DROP INDEX
Expected format:
DROP INDEX [IF EXISTS] ixname.NDX ON tablename.DBF
DROP INDEX [IF EXISTS] tagname ON tablename.DBF
DROP TABLE
Expected format:
DROP TABLE [IF EXISTS] tablename.DBF
INSERT
Expexted format:
INSERT INTO tablename (field1, field2, field3,...) VALUES ( 'charval', numval, 'what is the correct odbc date format to use? CCYYMMDD');
SET
Used to set a variable name
Expected format:
SET ATTRIBUTE = DATAVALUE
SET ATTRIBUTE = ^ (to delete an entry)
