NOTE: This document was originally written for Visual dBASE 7.0/7.01, it has been updated for dB2K (release 1) to include information about new properties, events, etc., and any new controls since the document was first written. If a control is new it will be noted. In updating this document, the images were left "as is" unless it was felt to be absolutely necessary to change them ...
In addition, this document refers to dB2K a lot, but unless it is about a dB2K specific aspect, the text can be used for Visual dBASE 7.0 through Visual dBASE 7.5.
However and very importantly, this is not a suggestion that you should use all of these SQL equivalents in your dB2K development. "Why not?", you might ask. Because many of these will return read-only queries, which in most applications will serve no purpose. Instead, you will want to use the OODML of dB2K for most situations (see other HOW TOs in the Knowledgebase).
Special Note: some of these commands will work fine with Visual dBASE 5.5 and 5.6, but there are no guarantees -- the SQL capabilites of the BDE were greatly enhanced in the 32-bit version of the BDE (4.5 and later releases), so if you try many of the commands shown here with VdBASE 5.x, you may find yourself getting a lot of "Capability not supported" errors.
There are times when using the local SQL commands may be a faster or more efficient way to do things (not many, but still ...).
NOTE: my primary source of information here is a really good book on SQL: Understanding the New SQL: A Complete Guide, by Jim Melton and Alan R. Simon, Morgan Kaufmann Publishers, San Francisco, 1993, ISBN: 1-55860-245-3. This book uses an excellent approach to teaching SQL and is well worth the rather high price. (This is in addition to online help ... but if you need to spend much time working with SQL, I heartily recommend this particular book -- I have no experience with any others that are out there ...)
In addition to the book mentioned above, there is a "SQL Help" icon in your dB2K folder -- this discusses local SQL commands in greater depth than is discussed here. Most importantly, it discusses these as implemented by the BDE.
Various Explanations
...
SQL commands, like XBase commands, are not generally case sensitive. In this
document they are usually shown in all caps to differentiate them from the data,
fields, etc., but you can use them in mixed case, all caps, all lower
case, etc.
In the text of this document I usually give "Syntax" listings. When I do, I use brackets (< and >) around the name of a value. For example, I might say: SELECT * FROM <tablename> -- if you wanted to use the "FISH" table in the samples folder, you would replace "<tablename>" with "fish" (without the quotes) -- the resulting command would look like: SELECT * FROM fish
When referring to Paradox tables, you may need to include the .DB table extension, and in some cases you may want to, or need to include the extension anyway.
When executing most of these commands, the table does not need to be opened. The examples should be fairly clear, and most of them have been tested against the samples tables that ship with dB2K.
Many of the SELECT statement options return a result query, rather than opening the table directly as you might expect. This query is opened in dBASE as a temporary table, in the form of an alias with the name of SQL_<num>. Any table opened this way is not updatable (it is read-only). If you are not sure, you can use code along these lines to find out:
//issue your select statement and then:
if ( <tablename> $ DBF() )
// do whatever you want to do -- it's updateable
else
// it is read-only
endif
The term columns is used to reference the fields in a record, and the term rows is used to reference records in a table (these are standard SQL terms).
If you see a pipe (|), it means you have an option for what to use (for example: "* | <fieldlist>" will mean that you can use either the wildcard for "all fields" or a list of specific fieldnames separated by commas).
If you see a command option in square brackets ( [] ), it means that this is optional, and is not required. This is often shown with the "WHERE" clause, i.e.,
SELECT * FROM <tablename> [WHERE <condition>]
In many of the examples below, in order to actually use the samples tables, their BDE aliases are used. The BDE alias is a pointer to a "database", and preceeds the table (for details on BDE Aliases see other HOW TO documents in the Knowledgebase). As an example, you may want to manipulate the "FISH" table in the DB2KSAMPLE database. To reference this table, it must be preceeded with the database reference: ":db2ksample:", i.e.,
SELECT * FROM :db2ksample:fish
Some columns in tables may have spaces or use SQL reserved words as the field names -- in those cases, you need to reference them with the table name in front of them with a dot separator, and quotes around the field name. Example:
SELECT name, fish."length cm" FROM :db2ksample:fish
Finally, a last mention -- for the most part, you will need to know very little SQL in dB2K, but for those who want to know more, it's here ... this document does not cover all aspects of SQL. The purpose of this document is to be a comparison of XBase commands to local SQL commands and to give a developer a basic understanding of how SQL works in dB2K with local tables. (Many 'SQL Server' software packages, such as Interbase, Oracle, and others have much more advanced SQL engines, the Borland Database Engine will pass along any SQL it does not directly understand to these engines and return the proper results ... if you are using one of these servers you may need to reference the manuals for more details on what is possible ....)
Before You Start Reading
...
If you are one of those who likes to try things as they are shown to you, you
will want to make sure that the two database aliases referenced here are open.
These are from the SAMPLES that are installed (by default) with dB2K, although
some of the samples shown here use sample tables from aliases that do not exist
(MUGS, from Visual dBASE 7.x) if you have only got dB2K on your computer, and
not an earlier version of Visual dBASE 7.x. In the Command Window, type:
open database mugs
open database db2ksample
You can then enter the commands in the Command Window ...
You may wish to back up the tables in the sample directories before you tinker with any examples shown in this HOW TO document, or these tables may get deleted, indexes wiped, etc.
If a command is shown split over multiple lines you will want to enter it as a single statement (no break), and remove the semicolins (;) shown at the end of the breaks ...
If you wish to see the results of a command you have entered, you will want to enter the SELECT command as shown, and then type: BROWSE -- this will bring the table up in the standard browse mode so you can see the results.
When you issue a SQL SELECT statement, dBASE automatically opens the result (whether it is a result query, or the actual table in question) in a new work area. This means that if you do a lot of working with local SQL you may have a lot of open work areas. In your code, if you wish to use a SQL select to get a result query and then do something with it, you will want to close that result and return to your previous work area. You might want to do something along the following lines:
cArea = alias() // current work area SELECT * FROM whatever WHERE whateveroptions ... // do what you need to retrieve results you are looking for use // close the query select ( cArea ) // back to previous work area
When done, you may want to close your tables and result queries by typing:
CLOSE TABLES
When you are completely done, you may also wish to issue the command:
CLOSE DATABASES
to make sure you don't leave those open (the next time you start dBASE it will open these for you ...).
APPEND/APPEND
FROM
You can add rows to a table using the SQL INSERT command.
APPEND
SQL Syntax:
INSERT INTO <tablename> [( <column[s]> )] ;
VALUES ( <value list> )
If you leave out the column listing, there must be a value for each field, and fields are filled in the sequence the fields are defined in the table structure (i.e., first value is placed in the first field ...).
If you use the column listing, you must specify at least one column and the associated value. If you leave columns out of the list, they will be empty (null - this means that the empty() function will return true, but isblank() may not).
Example:
// add a new row to fish table in "dB2KSample" database:
INSERT INTO :dB2KSample:fish ( name, species, fish."length cm" ) ;
VALUES ("George", "Human", 10 )
You can then browse the table after opening it and you will see the new row:
SELECT * FROM :dB2KSample:fish BROWSE
APPEND FROM
SQL Syntax:
INSERT INTO <tablename> SELECT * | <fieldlist> ;
FROM <tablename> [WHERE <condition>]
The columns in the select statement must match the columns in the table being inserted into (although the fieldnames do not have to match, the number of columns, the types and the sizes must match). If you use the field list, the fields must match as noted ...
Example:
INSERT INTO :dB2KSample:fish SELECT * FROM :dB2KSample:fish2
The above would assume you had made a copy of the fish table in the dB2KSample directory ( "copy table fish to fish2" ) ... and perhaps had changed the data in the second table.
If you use the optional "WHERE" clause (this is discussed later in this document in some detail) only the rows in the table being appended from that meet the condition would be appended. Example:
INSERT INTO :dB2KSample:fish SELECT * FROM :dB2KSample:fish2 WHERE fish2."length cm" <= 10
COUNT
The XBase DML command COUNT is used to count the records in a table. You can
add qualifiers (COUNT FOR ...) and so on. And you can use a COUNT ... TO ...
to save the value to a memory variable.
The SQL equivalent is to generate a read-only query that contains a single row with a single column with the number of rows, optionally it will count just the rows that that match a condition:
SQL Syntax:
SELECT COUNT(*) FROM <tablename> [WHERE <condition>]
Examples:
SELECT COUNT(*) FROM :dB2KSample:fish // or: SELECT COUNT(*) FROM :dB2KSample:fish WHERE fish."length cm" <= 10
This will return a result table with a name like "SQL_1" or "SQL_2". You can browse it and see the results -- you will end up with a single column (or field) named "COUNT___" (note -- that is three underscore characters -- it may not look like it depending on what fonts are being used and such). If you then close all tables or issue "USE" the temporary result table will be closed and gone ...
If you wish to save the value to a memory variable, you could do something like:
cArea = alias() SELECT COUNT(*) FROM :dB2KSample:fish WHERE fish."length cm" <= 10 // save to a memory variable: nCount = COUNT___ // close the temporary table: use select (cArea)
There are more 'calculations' toward the end of this document.
DELETE/ZAP
The dBASE table format (.DBF) uses a "soft delete". The only advantage to using
this is that it means that the records are left in the table in case you need
to recall them. The XBase DML is designed to work with this feature, and all
local SQL and OODML commands that work with .DBFs actually map to the soft delete
for .DBF tables. NOTE that this only occurs for .DBFs -- this is the only table
format that uses the soft delete -- all other table formats actually delete
a row when you specify that you really want to delete it.
Local SQL respects the "SET DELETED" setting, which determines whether or not to display rows that have been "flagged" as deleted.
SQL Syntax:
DELETE FROM <tablename> [WHERE <condition>]
WARNING: If you leave off the condition for this command you will empty your table! This is the equivalent of ZAP, except that DELETE FROM ignores the setting of SET SAFETY and will not ask if you really wish to delete the rows in the table ...
Examples:
NOTE: Just like the ZAP command, "DELETE FROM" requires exclusive use of the table. If you are not sure if you can obtain exclusive use of the table, you may wish to use the following function (by Peter Rorlick, found in the dUFLP library ...):
function UseExclusive( cTableName, lInNewArea ) local lSuccess // if user wants it in a new work area: if lInNewArea select select() endif // open the table with the exclusive parameter: use (cTableName) exclusive // default: lSuccess = false // next we need to see if we can actually do this: try delete tag DummyXYZ // nonexistant index tag // the above line will throw one of three // errors: // 110: "Operation requires exclusive use of table" // 53: "Tag not found: DUMMYXYZ" or // 35: "Table is not indexed" catch( exception e ) if e.code == 53 // message = "Error: Tag not found" // we have succeeded in opening the table exclusively lSuccess = true endif endtry // table is now open one way or another ... return lSuccessNote that if you do this, you will then want to close the table before executing the SQL DELETE command. Example:
if UseExclusive( ":dB2KSample:fish" ) use DELETE FROM :dB2KSample:fish WHERE name IS NULL else use endif
// delete empty rows: DELETE FROM :dB2KSample:fish WHERE name IS NULL // delete specific row: DELETE FROM :dB2KSample:fish WHERE name = "George" // empty the table (only do this if you have backed up the table): DELETE FROM :dB2KSample:fish
As another caveat -- if you are unfamiliar with the "soft delete" feature of .DBF tables, the data is still there, but to retrieve it you would need to use the XBase RECALL command (details in online help). To completely remove deleted rows of a table you would need to use the XBase PACK command, or the OODML database object's packTable() method. To view rows that have been deleted, issue the command SET DELETED OFF, or use the Properties dialog to set this option. To not see deleted rows, SET DELETED ON (this is the default in dB2K).
DELETE
TABLE
The XBase DML command DELETE TABLE is used to remove a table and all it's associated
files (.MDX, .DBT in the case of a .DBF, in the case of a .DB table, all the
myriad assorted files that may be created).
The SQL command for this is DROP TABLE.
SQL Syntax:
DROP TABLE <tablename>
Example (only do this if you have backed up the table ...):
DROP TABLE :dB2KSample:fish
DELETE
TAG
The XBase DML command DELETE Tag is used to remove an index tag from a table.
The SQL command for this is DROP INDEX. NOTE: If you "drop" all index tags, the .MDX file will automatically be deleted.
SQL Syntax:
DROP INDEX <tablename>.<indexname> | PRIMARY
Note that the word "PRIMARY" is used for .DB tables with a primary index in place of the <indexname> shown above (the .DB table format can create a primary index with no index name, so this keyword is vital). For .DBFs with a primary index, use the index name ...
Examples:
// for a .DBF: DROP INDEX :dB2KSample:fish.species // or for a .DB (note that this is an example but the // table does not exist ... this will return an error // if you try it right now): DROP INDEX "customer.db".PRIMARY
INDEX
The XBase DML command INDEX, with it's various options, is used to create indexes
for your tables. With .DBF tables, it is used to create either .NDX files or
.MDX tags.
The SQL command for this is CREATE INDEX. Note that with .DBFs this command will create .MDX tags, but no .NDX files -- there is no option for this.
SQL Syntax:
CREATE INDEX <indexname> ON <tablename> ;
(<column1> [, <column2> ...] )
There are some serious drawbacks here if you are used to creating expression indexes -- you cannot do this in SQL. You can create indexes on multiple columns, but not on expressions.
Further, you cannot create PRIMARY indexes with this command -- the only way to create a PRIMARY index using SQL is with the CREATE TABLE command (i.e., when you create the table originally) and to the best of my knowledge this only works for Paradox tables.
Examples:
CREATE INDEX name2 ON :dB2KSample:fish ( name ) CREATE INDEX namespecies ON :dB2KSample:fish ( name, species )
REPLACE
The REPLACE command is the XBase DML method of programmatically assigning a
value to a field, or by using the "ALL" option, of replacing the data for a
specific field (or fields) in all rows of a table.
SQL Syntax:
UPDATE <tablename> ;
SET <fieldname>=<somevalue> [, <fieldname2>=<someothervalue> ...] ;
[ WHERE <somecondition> ]
The "SET" is where you list the fields and what their new values are to be when the UPDATE has done it's job.
Examples:
One example might be a need to replace a logical field for all rows in a rowset with "false":
UPDATE MyTable SET myLogicalField = false
Note: In my non-scientific testing this is faster than the XBase REPLACE ALL command, and substantially faster than the OODML method (which actually requires creating a loop to process a table row by row and storing a new value for each row ...).
If you needed to have a condition, let's say a date field based on some value:
d = date()
UPDATE MyTable SET myLogicalField = false;
WHERE myDateField <= :d
SET
FIELDS
The XBase DML SET FIELDS command is used to limit the fields displayed or accessed
in a table.
The SQL version of this is to use a field list instead of the wildcard for the field names. In addition you can create calculated fields "on the fly" ... see "USE" below.
SET FILTER
The XBase DML SET FILTER statement was how a lot of filtering got done.
Filters are done in SQL by using the optional WHERE or HAVING clauses which shows up in many of the commands in this document. You may find that the WHERE clause is substantially faster than the XBase filter.
The operators are important, as the syntax is not exactly the same between XDML and SQL:
| XBase | SQL |
|---|---|
| and/.and. | and |
| or/.or. | or |
| not/.not. | not |
| = | = |
| == | = |
| =>/>= | >= |
| =</<= | <= |
| #/<> | <> |
| empty()/isBlank() | is null |
| not empty/not isBlank() | is not null |
| string1+string2 (concatenation of strings) |
string1 || string2 |
Note that the sequence of the less than or greater than symbols combined with the equal signs is quite important in SQL (in other words, >= works, => does not).
You can stack conditions up by combining them with and and/or or operators just like you can in XBase.
The Where clause is the standard filter, and is used as below. The Having clause is used with aggregate calculations (calculated fields), where these fields have specific values. The Having clause can only be used when there is a calculated field and there is a Group By clause.
SQL Syntax -- Where clause:
SELECT * | <fieldlist> FROM <tablename> WHERE ;
<condition> [ORDER BY <fieldname(s)>]
Example:
SELECT * FROM :dB2KSamples:fish WHERE fish."length cm" <= 10
SQL Syntax -- Having clause:
SELECT <fieldlist>, <CalculatedField(s)> FROM <tablename> ;
GROUP BY <fieldname(s)> ;
HAVING ( <condition> ) [ORDER BY <fieldname(s)>]
Example:
SELECT lineitem."invoice id", sum( qty ) AS totalqty FROM :mugs:lineitem ;
GROUP BY lineitem."invoice id" ;
HAVING ( sum(qty) <= 3 )
Note that you can combine a WHERE and a HAVING clause ... In addition (from LocalSQL Help): "A HAVING clause filters data after the aggregation of a GROUP BY clause. For filtering based on row values prior to aggregation, use a WHERE clause."
Special Options -- "$"
-- Item List
In XBase, there is the "$" operator, which is used to see if a string is contained
in a list of strings (or in an individual string -- see below). In SQL, this
is done with the "IN" clause:
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename> WHERE ;
<fieldname> IN ( <item1>, <item2> ... )
Example:
SELECT * FROM :mugs:customer WHERE customer."state id" IN ("CA", "NY" )
Note that the result query that is returned is editable ...
Special Options -- "$"
-- String1 In String2
This is different from the previous in that here we are looking for a match
in a single string. In XBase, you could check to see if a string was contained
anywhere in the string (i.e., "somestring" $ fieldname ).
In SQL this takes a bit more work, but with the use of some special wildcard characters, you can get there. The resulting rowset may be read-only. (NOTE: SQL is case sensitive ...)
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename> WHERE ;
<fieldname> LIKE "string-with-wildcard(s)"
The two wildcard characters are "%" and "_" (percent and underscore).
The percent sign refers to anything on that side of the character string (see examples), the underscore refers to a single character. If you are familiar with DOS wildcards for filenames, the percent sign is similar to the asterisk (*), and the underscore works identically to the question mark (?).
Examples:
// return any row where the name has the letter 'a' in it
SELECT * FROM :dB2KSample:fish WHERE name LIKE "%a%"
// return any row where the name begins with 'B'
SELECT * FROM :dB2KSample:fish WHERE name LIKE "B%"
// return any row where the name ENDS with "fish"
SELECT * FROM :dB2KSample:fish WHERE name LIKE "%fish"
// return any row where the name starts with any two
// characters, and then has the letters 'ue' and any
// other characters after ...
SELECT * FROM :dB2KSample:fish WHERE name LIKE "__ue%"
And so on. Using a combination of these two characters you can do pretty much anything you need ... see also below where string functions are discussed -- you can use the string functions on a field name to make these case insensitive searches ...
SET INDEX/SET
ORDER
The XBase DML has SET INDEX and SET ORDER, which have similar uses. However,
while SET INDEX can open .NDX files, SQL does not use these at all, and frankly
doesn't know what they are.
Using an index in SQL is pretty simple, but you can also bypass the index tags and simply tell dBASE to order the data in whatever fashion you wish.
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename> ;
ORDER BY <fieldname> [,<fieldname> ...] [ASC|DESC]
Note that you can use more than one fieldname in the list, and you can refer to a calculated field (see below).
The "ASC" and "DESC" operators are optional -- the default sequence is to order a table in ascending sequence (ASC), but you can order it in descending if you wish (by adding "DESC" to the end of the ORDER BY clause or to the end of the individual field reference). Interestingly you can sort with a combination of ascending and descending fields using SQL in one command.
Note that if an index tag exists, you can use the index tag name in place of the field name. If the index tag has the same name as the field name, dBASE will use the index tag. If the index tag name is the same as a field name, SQL will use the index tag as well.
Examples:
// order the customer table by last name:
SELECT * FROM :mugs:customer ORDER BY customer."last name"
// order the customer table by last name in descending sequence:
SELECT * FROM :mugs:customer ORDER BY customer."last name" DESC
// case insensitive:
SELECT * FROM :mugs:customer ORDER BY UPPER( customer."last name" )
// multiple fields (descending state, ascending last name):
SELECT * FROM :mugs:customer ORDER BY customer."state id" DESC, customer."last name" ASC
// calculated field:
SELECT customer."last name" || ', ' || customer."first name" AS fullname, ;
street1, street2 FROM :mugs:customer ORDER BY fullname
Note that you cannot use the wildcard in conjunction with a calculated field, and the rowset will be read-only because of the calculated field.
SET RELATION
The XBase DML uses SET RELATION to determine relationships between tables.
SQL can do related tables, but you will always get, with local tables, a read-only rowset. This can get quite complex. You need to use the SQL JOIN clause to relate tables, and there are a lot of options (INNER JOIN, OUTER JOIN ...).
The only place that this makes a lot of sense is if you are creating a report in dB2K (since the resulting query would be read-only anyway -- reports don't generally write to the tables). The report engine actually doesn't handle some sets of tables very well (such as parent/child/grandchild or a master table with two (or more) detail tables). Using a SQL JOIN for these situations makes sense.
For any forms that use multiple tables in dB2K you will want to use the OODML data objects (see other HOW TO documents in the Knowledgebase).
If you must use SQL Joins, I recommend (and this is the only time I recommend this) that you use the SQL Designer to model your joins, and then extract the output SQL and simplify it as much as you can (the SQL Designer generates some fairly lengthy code, some of which may be unnecessary).
An example of an outer join used with three tables -- a master table with two detail tables is (don't try this at home unless you actually have tables with these names and fields ...):
SELECT * FROM "testprim.dbf" Testprim ;
FULL OUTER JOIN "testdet1.dbf" Testdet1 ;
ON (Testprim.LinkField = Testdet1.LinkField) ;
FULL OUTER JOIN "testdet2.dbf" Testdet2 ;
ON (Testprim.LinkField = Testdet2.LinkField )
The tables would be linked
using a field called "LinkField", and the table names are "TestPrim" for the primary
(parent) table, and "TestDet1" and "TestDet2" for "Test Detail 1" and "Test Detail
2".
TOTAL
The XBase DML TOTAL command creates a new table that contains totals for numeric
fields in the original table you are totalling from.
See the section below on calculations and aggregate functions. The local SQL command for this returns similar results ...
USE
In XBASE DML, to open a table, you use the "USE" command. In SQL the SELECT
statement is how you open a table. The SELECT statement is the one SQL statement
you must know if you wish to use dB2K and the OODML properly.
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename> ;
[ORDER BY <fieldname> [, <fieldname> ...]] ;
[WHERE <condition>] ;
[HAVING (<condition>)] ;
[GROUP BY <fieldlist>] ;
[SAVE TO <tablename>]
The "*" is a wildcard for the fields.
Rather than selecting all fields in the table, you may wish to limit the field list. This can be done by listing each field you wish separated by commas.
Examples:
SELECT * FROM :dB2KSample:fish SELECT name, species FROM :dB2KSample:fish
If a field uses a SQL reserved word as the fieldname, or the field has spaces in the fieldname itself, you need to precede the field with the tablename, a dot, and put the fieldname in quotes, i.e.,:
SELECT name, fish."length cm" FROM :dB2KSample:fish
Calculated Fields
You can create calculated fields with your SQL SELECT statement but as soon
as you do this, your resulting table will be read-only. In addition, you cannot
use the wildcard for "all fields" ...
SQL Syntax:
SELECT <fieldlist>, <calculation> AS <calcfieldname> FROM <tablename>
A simple example of this might be to multiply the amount of items ordered in the Mugs example's lineitem table by 10 (the cost of each mug):
SELECT qty, qty*10 AS ordertotal FROM :mugs:lineitem
There are other calculations discussed later in this document that are "built-in-to-SQL" calculations ...
NOTE: You cannot use the fieldlist wildcard with a calculated field in the fieldlist (i.e., you cannot use "*, calcfield").
Save To
It is possible to save a result query to another table using the optional SAVE
TO clause. This can be useful with JOINs and other complex SELECTs.
Using the calculation above as an example:
SELECT lineitem."invoice id", qty, qty*10 AS ordertotal FROM :mugs:lineitem ;
SAVE TO ordertotals
NOTE: This will save the table to the local directory, unless you add the BDE Alias to the output tablename.
Once you have the output table, you can open it in an updateable way, but remember that the original. table(s) that this data has been pulled from will not be updated ...
Other Options
The SQL statement has many other options, some of which are discussed elsewhere
throughout this document ...
Very importantly, and one of the most powerful aspects of SQL, you can combine many of the options shown throughout this document into a single SELECT statement. Building such a statement can be a bit tricky, but you can do some pretty complex things with one command using SQL.
ZAP
See the "DELETE" command earlier in this document.
In SQL you can do this much easier, with a couple of drawbacks ...
SQL Syntax:
SELECT DISTINCT <fieldname> [, <fieldname> ...] FROM <tablename>
Problems: this will not allow you to select any fields except those you are checking for "uniqueness". You can use multiple fields in the SELECT statement, but the DISTINCT operator will find a combination of unique values for the different fields listed.
Example:
// you might want to use this to find all unique rows // based on the last names of your customers: SELECT DISTINCT customer."last name" FROM :mugs:customer
However, using a subquery, you could use this to actually get all of the fields (columns) in the customer table where the unique value you wanted was found:
SELECT * FROM :mugs:customer WHERE customer."last name" IN ;
(SELECT DISTINCT customer."last name" FROM :mugs:customer) ;
ORDER BY customer."last name"
Sum/Group
By
You may wish to total a field in a table. By itself this will generate a single-row,
single column table that is read-only. You may also wish to group that total
by some other field -- this would be used to generate the total for all rows
of that "group by" field. This calculation will also generate a read-only query.
SQL Syntax:
SELECT SUM( <fieldname> ) FROM <tablename>
OR
SELECT <fieldlist>, SUM( <fieldname> ) FROM <tablename> ;
GROUP BY <fieldname>
This will create a field called: SUM OF <fieldname> where <fieldname> is the name of the field you tell it to "sum".
Examples (using the Mugs database's lineitem table):
// total of the qty field ... -- one row, one field query SELECT SUM( qty ) FROM :mugs:lineitem // return a total for each invoice: SELECT lineitem."invoice id", SUM( qty ) FROM :mugs:lineitem GROUP BY lineitem."invoice id" // return a total for each item: SELECT lineitem."item id", SUM( qty ) FROM :mugs:lineitem GROUP BY lineitem."item id"
Other
Calculations
In XBase, you have the CALCULATE command, in SQL you can simply add the following
into a SELECT statement. The following include examples ...
MAX()
SQL Syntax:
SELECT MAX( <fieldname> ) FROM <tablename>
This will return the value of the field in question from the row with the largest value in that field. This will create a field called: MAX OF <fieldname> where <fieldname> is the name of the field you tell it to return the "max" of.
Example:
SELECT MAX( fish."length cm" ) FROM :dB2KSample:fish
If you want to see all of the fields in row that match this (and if you have multiple rows with the same value, all rows that match this), you could use a subquery (some details on these later in this document):
SELECT * FROM :dB2KSample:fish WHERE fish."length cm" = ;
( SELECT MAX( fish."length cm" ) FROM :dB2KSample:fish )
MIN()
SQL Syntax:
SELECT MIN( <fieldname> ) FROM <tablename>
This will return the value of the field in question from the row with the smallest value in that field. This will create a field called: MIN OF <fieldname> where <fieldname> is the name of the field you tell it to return the "min" of.
Example:
SELECT MIN( fish."length cm" ) FROM :dB2KSample:fish
If you want to see all of the fields in row that match this (and if you have multiple rows with the same value, all rows that match this), you could use a subquery (some details on these later in this document):
SELECT * FROM :dB2KSample:fish WHERE fish."length cm" = ;
( SELECT MIN( fish."length cm" ) FROM :dB2KSample:fish )
AVG()
SQL Syntax:
SELECT AVG( <fieldname> ) FROM <tablename>
This will return the average of the values in the field. This will create a field called: AVG OF <fieldname> where <fieldname> is the name of the field you tell it to return the average of.
Example:
SELECT AVG( fish."length cm" ) FROM :dB2KSample:fish
COUNT()
COUNT was discussed previously in this document under the COUNT heading.
Subqueries
Subqueries can be used to limit the data returned in a query by comparing values
against another table (or even in the same table -- see the calculatiions above).
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename> WHERE (<subquery>)
The subquery would be another SQL Select statement.
The subquery can use a special operator "EXISTS" to see if the value of the subquery exists in the main query created by the SELECT statement.
Example (note that this uses the MUGS database):
SELECT * FROM :mugs:customer WHERE EXISTS ;
(SELECT * FROM :mugs:invoice WHERE invoice."customer id" = customer."customer id" )
Note that this is not getting data from the Invoice table, it is just comparing the customer table to find any rows that have a matching row in the invoice table. If a customer does not have any invoices, they will not appear in the result set.
Multiple
Tables
In addition to the discussion of JOIN under the "SET RELATION" heading previously
in this document, it is possible to open multiple tables in a single query.
The resulting query from this may be read-only, and it can produce some very
unusual results.
SQL Syntax:
SELECT * | <fieldlist> FROM <tablename1>, ;
FROM <tablename2>
or
SELECT <tablename1>.<field>, <tablename2>.<field> FROM;
<tablename1>, <tablename2>
or
SELECT <alias1>.<field>, <alias2>.<field> FROM;
<tablename1> <alias1>, <tablename2> <alias2>
The last statement shown in the syntax above is useful when working with a lot of fields from different tables -- you assign an alias to the table by putting a space after the tablename (after the FROM clause) and the name of the alias.
Examples:
SELECT * FROM :dB2KSample:fish, :dB2KSample:sample
If you browse this, you will see that for every record in FISH there is a record from SAMPLE. This can give you some very odd results ...
SELECT fish.name, sample.sample FROM :dB2KSample:fish, :dB2KSample:sample
This should give the exact same results as above except that it will only display two fields, one from each of the tables ...
SELECT a.name, b.sample FROM :dB2KSample:fish a, :dB2KSample:sample b
And once again, this should give the same results as above, but the syntax is a bit easier to type, because of the use of the aliases. Note that the alias can be more than a single letter ... (we could have used "TableA" and "TableB" or anything we wanted ...).
Creating
Tables
One of the most useful features of local SQL is the ability to create tables
on the fly and to modify their structures (see below).
You can create tables using the CREATE TABLE command in SQL:
SQL Syntax:
CREATE TABLE <tablename> ( <fieldname> <type>(<size>) ... )
This can get tricky ... The SQL types don't always map exactly to the field types in the table. The following two listings show all of the field types you can use, one for .DBF and the other for .DB (Paradox) tables, and how they map to the appropriate table type:
SQL Field Maps for .DBFs
| SQL | .DBF |
|---|---|
| SMALLINT | Long |
| INTEGER | Long |
| DECIMAL(X,Y) | Numeric(X,Y) |
| NUMERIC(X,Y) | Numeric(X,Y) |
| FLOAT(X,Y) | Double |
| CHAR(X) | Character(X) |
| VARCHAR(X) | Character(X) |
| DATE | Date |
| BOOLEAN | Logical |
| BLOB(x,1) | Memo |
| BLOB(x,2) | Binary |
| BLOB(x,4) | OLE |
| TIMESTAMP | TimeStamp |
| MONEY | Numeric(20,4) |
| AUTOINC | Autoincrement |
SQL Field Maps for .DBs
| SQL | .DB |
|---|---|
| SMALLINT | Short |
| INTEGER | Long |
| DECIMAL(X,Y) | BCD |
| NUMERIC(X,Y) | Number |
| FLOAT(X,Y) | Number |
| CHAR(X) | Alpha |
| VARCHAR(X) | Alpha |
| DATE | Date |
| BOOLEAN | Logical |
| BLOB(x,1) | Memo |
| BLOB(x,2) | Binary |
| BLOB(x,3) | Formatted Memo |
| BLOB(x,4) | OLE |
| BLOB(x,5) | Graphic |
| TIME | Time |
| TIMESTAMP | TimeStamp |
| MONEY | Money |
| AUTOINC | Autoincrement |
| BYTES(x) | Bytes |
For a .DB table, if you need a primary index, you must add to the end of the CREATE TABLE command: PRIMARY( <keyfield> ), and note that the keyfield must be the first field in the table!
A simple example of the CREATE TABLE command:
CREATE TABLE TEST (;
"First Name" Char( 20 );
"Last Name" Char( 20 );
"Birthdate" Date ;
"Notes" Blob( 10, 1 ) )
(The last field created would be a memo.)
Modifying
Table Structures
The local SQL commands, in addition to allowing you to create tables on the
fly via code, allow you to alter the structure of a table. The only modifications
that can be done are by adding or deleting columns (fields).
SQL Syntax:
ALTER TABLE <tablename> ;
ADD <fieldname>(<type>) | DROP <fieldname> ;
[ ADD <fieldname>(<type>) | DROP <fieldname> ... ]
You can stack the add/drop commands and you can mix them in the same SQL statement. The types are from the listings above.
There appears to be a problem with .DB tables and Autoincrement tables -- you can create a new table with an autoincrement field, but you cannot add one to an already existing table. This limitation does not apply to .DBF tables.
Date Formats
According to the local SQL Help file provided by dBASE, Inc., Local SQL expects
date literals to be in a U.S. date format of month/day/year (either two or four
digit -- see below). To prevent date literals from being mistaken by the SQL
parser for math (division) calculations, enclose your dates in quotation marks
-- this keeps "1/23/1998" from being mistaken for 1 divided by 23 divided by
1998.
Leading zeros for the month and day fields are optional.
If the century is not specified for the year, the BDE setting FOURDIGITYEAR controls the century. If FOURDIGITYEAR is set to FALSE and the year is specified with only two digits, years 49 and less will be prefixed with 20 (i.e., 2049) and years of 50 or higher will be prefixed with 19. (This is much like using the SET EPOCH setting, set to 1950, in standard dBASE commands ...) To use a date outside of those bounds, specify the year in the date with all four digits.
More information from the Visual dBASE newsgroups from those in countries other than the US, who have done some testing, show the following ...:
q.sql = [select * from 'mytable.dbf' where datum = '17.01.1999']
q.sql = [select * from 'mytable.dbf' where datum = '01/17/1999']
Basically it appears that the separator marks (the slash or the dot) for dates tell the local SQL engine which version of the date to expect. If it sees the dot as the separator, it will assume that the date is European, and that the first characters (to the left of the dot) are the day of the month, and the next set of characters (between the two dots) are the month. If you use a slash, local SQL assumes that the date is in US date format, and that the first characters (to the left of the slash) are the month, and the next set of characters (between the slashes) are the day of the month. In either case, the characters to the right of the second mark (dot or slash) are always the year, and can be either in two or four digit year format (see above ...).
Thanks to Ivar B. Jessen for detailing this out and hopefully shedding a bit more light on the subject ...
String
Functions
These functions can be used in WHERE clauses, field lists, ORDER BY clauses,
and so on ...
UPPER(), LOWER()
These two functions work exactly as the XDML/dBASE functions work. They
convert the values to all upper or all lower case ...
TRIM()
The TRIM() function has some parameters, the syntax is a bit different from
the dBASE version:
SQL Syntax:
TRIM( <whattotrim> ["<c>"] FROM <fieldname> )
The <whattotrim> part is one of the following:
| LEADING | Characters at the beginning of the field |
| TRAILING | Characters at the end of the field |
| BOTH | Characters at both ends of the field |
The default character to trim is a space, but in the syntax listing above there is an optional character you can ask to have removed. An example of this might be a field where you had leading asterisks on the left of a number:
SELECT TRIM( LEADING "*" FROM MyField ), NAME, otherfields FROM MyTable
The use of the TRIM() function will return a read-only query.
SUBSTRING()
This is similar to the dBASE SUBSTR() function in that you must give a starting
place and an ending place, but in SQL the ending location is not optional.
SQL Syntax:
SUBSTRING( <fieldname> FROM <nStart> FOR <nEnd> )
If you wanted to get the first three characters of the "NAME" field, you could use:
// this will return the name field in one column, and // the first three characters in the next, with the // second column name being: "name substring 1" SELECT name, SUBSTRING( name FROM 1 FOR 3 ) from :dB2KSample:fish
As above, this will return a read-only query, as we are creating a calculated field.
Date Function
In dBASE we have the day(), month(), year() and various other functions that
can be used both on fields and other date values.
SQL uses the EXTRACT() function with some specific options.
SQL Syntax:
EXTRACT( <part> FROM <fieldname> )
Where <part> is one of the following: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
Example:
// this will return the 'customer id' field, and // a second field named: "EXTRACT DAY FROM order date" SELECT invoice."customer id", EXTRACT( DAY FROM invoice."order date" ) FROM :mugs:invoice
As with other calculated fields, this will generate a read-only query.
CAST Function
This function is a SQL function with no equivalent in dBASE XML (although VdBASE
7.x will automatically cast some field types, depending on what you are doing).
The CAST function is used to convert the value in the specified field to the data type specified. CAST can be applied to literal and calculated values as well. It can be used in a WHERE clause, a columns list for the SELECT statement, or as part of an UPDATE statement.
SQL Syntax:
CAST( <fieldname> AS <datatype> )
The datatype parameter may be one of the standard types used by local SQL (CHAR, INTEGER, NUMERIC, and so on). You cannot use BLOB, MEMO or BYTEs fields as types to cast to or from.
Converting a column value with CAST allows use of other functions on an otherwise incompatible type, such as using the SUBSTRING function on a DATE field.
When applied to the data retrieved through a SELECT statement the effect is temporary, and does not affect the actual "stored" data. When applied to an UPDATE statement the effect is permanent.
Memory
Variable Substitution
It is often necessary to insert a variable into your SQL Select statements,
either in WHERE clauses, or what have you.
There are a couple of ways of doing this. Examples:
cVar = "Blue Angelfish" SELECT * FROM :dB2KSample:fish WHERE name = :cVar
Another possibility is to use quotes around the variable (for string or date comparisons):
cVar = "Blue Angelfish" SELECT * FROM :dB2KSample:fish WHERE name = "&cVar."
The reason for the macro above (see & in online help) is that local SQL needs to see a valid SQL statement and cannot evaluate the dBASE variable itself (inserting the macro causes dBASE to evaluate it before it gets passed along to the SQL parser).
For dates, I recommend you use the parameter -- it appears to work with less problems than trying to get a combination of quotes and the value to be evaluated properly.
In the examples above, you can add wildcards and change the statements to use the LIKE clause, rather than the "=" operator.
NOTE: If you are using a SQL Select in a query object's sql property, you can set the value in the following manner:
cVar = "Blue Angelfish" q = new query() q.sql = [SELECT * FROM :dB2KSample:fish WHERE name = "]+cVar+["] q.active := true // OR -- note: do not combine the two of these!! q = new query() q.sql = [SELECT * FROM :dB2KSample:fish WHERE name = :cVar] q.params[ "cVar" ] = "Blue Angelfish" q.active := true
The use of the square brackets as delimiters is to make it easier to read the code above. A combination of single and double quotes is much harder to read than one with brackets and quotes.
Back to the Command Window. You can also use:
cVar = 10 SELECT * FROM :dB2KSample:fish WHERE fish."length cm" >= :cVar
Dates
Note that SQL understands dates only as character strings.
An example of this is:
SELECT * FROM :mugs:invoice WHERE invoice."order date" <= "12/15/1997"
See the section on date formats above.
Logicals
Use the words "true" or "false", and local SQL will understand a test for a
logical value ...
SELECT * FROM mytable WHERE mylogical = TRUE
The statements discussed here can be used very similarly to the way they are shown, however, if the SQL statement would produce a read-only query as shown in the text above, then it will do so for the OODML.
Query Object's SQL Property
The Query object is the data object seen first by most dBASE developers, as
it is the way to open or access a table.
At it's most basic, the query object must have three statements to work, and they must be in the sequence shown:
queryName = new Query() // create an instance of a query queryName.sql = "some SQL statement" // the sql select or what-have-you queryName.active = true // activate the query object
There is a lot more detail on the query object and other data objects in the HOW TO document comparing XBase to OODML in the Knowledgebase, but let's take a look ...
You can use the WHERE clause here, if you desire to, but you may want to use the filter property of the rowset object (which is created when your query is activated).
You may decide to use the WHERE clause here, and perhaps use the filter property for something else ...
Very importantly when using the WHERE clause with a SQL select in a query object's SQL property, is getting the delimiters "just right". The SQL statement itself must be a character string. This means that you need to use nested delimiters, which can be hard to read.
queryName.sql = "select * from customer where lastname = 'Jones'"
// is harder to read than:
queryName.sql = [select * from customer where lastname = 'Jones']
The second statement is easier to read, because the quotes don't get mixed up -- a single quote next to a double quote is very hard to determine what is what ...
If you need to use a variable, rather than a literal value as shown above, the syntax for the above will change ...
cName = "Jones" queryName.sql = [select * from customer where lastname = "]+cName+["]
Note that this embeds the quotes in the string and when the query's active statement is set to true, the string is evaluated properly.
As noted elsewhere, date values require delimiters.
Also as noted, numeric and logical values are handled as literals, so they do NOT need the quotes ...
nAge = 21 queryName.sql = [select * from customer where CustAge > ]+nAge // or: bUpdated = false queryName.sql = [select * from customer where Updated = ]+bUpdated
Filters
You can use the filter property of the rowset object to filter your data. This
filter cannot be a dBASE expression, but instead must be a sql expression, as
we've been examining them here (i.e., use the WHERE clause examples for it).
However, one thing you should note, the SQL SELECT statement recognizes all of the LIKE options for the WHERE clause, which allows a lot of flexibility to extract data, using wildcards. The filter property of the rowset does not recognize these options.
Using the example shown above of a variable and the custAge field of the table:
queryName = new query() queryName.sql = "select * from customer" queryName.active = true // somewhere in your code: nAge = 21 queryName.rowset.filter = "CustAge > "+nAge
(Note that you can use the findKey() method, and more, in the rowset -- details in other HOW TO documents in the Knowledgebase).
Indexing/Ordering Data
The biggest stickler when generating a query in local SQL is that the ORDER
BY clause, unless you are ordering the data by only one field, nearly always
creates a read-only query.
Instead, you should use an index tag in your table, and then use the rowset's indexName property:
queryName = new query() queryName.sql = "select * from customer" queryName.active = true // this must be after the active property is set to true: queryName.rowset.indexName = "someindexTag"
Joins
Joins are used to combine tables into a "virtual" table -- these create, using
local SQL, a read-only query.
If you need to set up your master/detail or parent/child relationships, you need to use the masterRowset/masterFields properties of the rowset, or if you are using tables that are not local (.DBF or .DB), then you would want to use the query object's masterSource property.
The only reason you might need a join in the OODML is for a report ...
Details on these are in other documents in the Knowledgebase.
Other Details
There is probably a lot more that can be discussed, but this is aimed at getting
you started ... check out the local SQL online help file, and if all else fails,
please use the newsgroups provided by dBASE, Inc. for assistance.
The only real drawback to using this is that the code it generates can be a bit lengthy:
On the positive side, the SQL designer is visual to an extent, and you can "run" the SQL generated and view the results in a browse window ...
My personal opinion is that unless you are doing reports that require more than two related/linked tables, you probably never need to use the SQL Designer, and even then, use it to model the SQL, and then clean up the generated code.
Unfortunately there are places in the OODML of dB2K where you are supposed to use SQL syntax for things, but the developers of dBASE didn't get all of the options set (filters are an area that comes to mind quickly -- it would be nice to be able to use the LIKE clause with the wildcard options ...). Oh well. With some experimentation you should be able to come up with what you need ...
Once again, thanks to Gary White, who provided me with an updated copy of the LOCALSQL help file produced by Inprise, and did some editing/review of the document as well.
DISCLAIMER: the author is an employee of dBASE, Inc., but has written this on his own time. If you have questions regarding this .HOW document, or about dB2K you can communicate directly with the author and dBVIPS in the appropriate newsgroups on the internet..HOW files are created as a free service by members of dBVIPS and dBASE, Inc. employees to help users learn to use dB2K more effectively. They are edited by both dBVIPS members and dBASE, Inc. Technical Support (to ensure quality). This .HOW file MAY NOT BE POSTED ELSEWHERE without the explicit permission of the author, who retains all rights to the document.
Copyright 2001, Kenneth J. Mayer. All rights reserved.
Information about dBASE, Inc. can be found at:
http://www.dbase.com
EoHT: X2SQL.HTM -- January 31, 2001 -- KJM