NUMBER : 2718 PRODUCT : dBASE VERSION : 5.0 OS : WINDOWS DATE : October 9, 1995 TITLE : Using Local SQL in dBASE for Windows Local SQL allows SQL (Structure Query Language) to be used on "local" tables. The term "local" in this context refers to any database tables which do not reside on a database server, specifically any Paradox or dBASE tables. The SQL statements are broken down into two different categories: DDL(Data Definition Language) and DML (Data Manipulation Language). The DML syntax is limited to what QBE can execute. The following clauses are supported: SELECT, WHERE, ORDER BY, GROUP BY and HAVING. The following aggregates: SUM, AVG, MIN, MAX, and COUNT as well as operators +,-,*,/,=,<>,and 'IS NULL' are supported. UPDATE, INSERT and DELETE are allowed. The DDL syntax is restricted to CREATE (DELETE) table and index. Views are not supported. Local SQL supports IDAPI aliases when referencing table names. With this feature SQL can be used to execute heterogeneous joins, joins of more than one table type. For example, a Paradox, an InterBase and a Sybase table could all participate in a SQL query. An example is provided below in the Sample Queries section. GENERAL RULES ------------- Table Names 1. Names which include the '.' character must be placed in either single or double quotes. a) mVar=SQLEXEC("SELECT * FROM 'c:\sample.dat\table' ") b) mVar=SQLEXEC('SELECT * FROM "table.dbf" ') Note: The space after the last single quote and before the final double quote in example a) is not necessary and is only for clarification. Likewise, the space after the last single quote and before the final double quote in example b) is not necessary. 2. Table names can include IDAPI style aliases. a) mVar=SQLEXEC("SELECT * FROM :Data:Table") Where data can be any alias defined in the IDAPI configuration utility. 3. A name which is a keyword must be placed in quotes. a) mVar=SQLEXEC(' SELECT PassId FROM "PassWord" ') Note: Not all keywords in the list below produce an error if not contained in quotes. Field Names 1. Field names which are placed in quotes must have the table reference associated with the field. a) mVar=SQLEXEC('SELECT t."Ship Date" FROM Inventory t) b) mVar=SQLEXEC("SELECT Animals.'Size' FROM Animals") c) mVar=SQLEXEC("SELECT Aa.'Size' FROM Animals aa") d) mVar=SQLEXEC("SELECT Aa.'Size', AVG(Weight); FROM Animals Aa GROUP BY Aa.'Size' ") 2. Field names which have spaces, such as Paradox tables, must be placed in quotes. a) mVar=SQLEXEC('SELECT EMPLOYEE."Emp Id" FROM Employee') 3. Field names which are keywords must be placed in quotes. a) mVar=SQLEXEC('SELECT Table."Date" FROM Table') b) mVar=SQLEXEC("SELECT AVG(Animals.'Size') AS Avg_Size FROM Animals") Note: Not all keywords listed in table at the end of this document produce an error if not contained in quotes. SAMPLE QUERIES -------------- DML SQLEXEC(" SELECT Part_No FROM Parts ; WHERE Part_No > 543") SQLEXEC(" SELECT Customer_No FROM 'c:\data\customer.dbf' ; WHERE Last_Name = 'Johnson'") SQLEXEC("SELECT DISTINCT Customer_No ; FROM c:\Data\Customer ; ORDER BY Customer_No DESCENDING") Note: Currently, when using the descending keyword in the order by clause, distinct must also be specified. This is an anomaly and will be corrected in future releases. mVar=SQLEXEC("SELECT Part_No, SUM( Quantity ) AS PQTY ; FROM Parts ; GROUP BY Part_No") Note: Each column selected with an aggregate must exist in the group by clause. mVar=SQLEXEC("SELECT Country FROM Customer; GROUP BY Country ; HAVING SUM(Produce) > 5500000") mVar=SQLEXEC("SELECT DISTINCT P Part_No, P.Quantity, ; G.City FROM Parts P, Goods G ; WHERE P.Part_No = G.Part_No ; AND P.Quantity > 20 ; ORDER BY P.Quantity, G.City, P.Part_No") Join using IDAPI Alias: mVar=SQLEXEC(" SELECT DISTINCT C.Cust_No, C.State, ; O.Order_No, L.Price FROM ':Local_Alias:Customer.db' C, ; :IB_Alias:Order O, ; :SYB_Alias:LineItem L; Where O.Cust_No = C.Cust_No AND ; O.Order_No = L.Order_No") mVar=SQLEXEC("UPDATE Goods ; SET City = 'Santa Cruz' ; WHERE Goods.City = 'Scotts Valley'") mVar=SQLEXEC("INSERT INTO GOODS ( Part_No, City ); VALUES ( 'aa0094', 'San Jose' )") mVar=SQLEXEC("DELETE FROM Goods; WHERE Part_No = 'aa0093'") DDL - See Below for data type mappings mVar=SQLEXEC("CREATE TABLE Parts ( Part_No CHAR(6), ; Part_Name CHAR(20) )") mVar=SQLEXEC("DROP INDEX Parts.Part_No") Note: Syntax for drop index is tablename.indexname, also tablename.primary indicates the primary index (Paradox Only). MEMORY VARIABLES ---------------- It is important to understand that SQLEXEC() passes a character string. Memory variables must be used cautiously since often quotes must also be passed as part of the character string. Dynamic SQL is supported as illustrated in the sample program. && space between double-quote and quote is for clarification mVar1 = " 'A8513' " mVar2 = 'Clients' mVar3 = "A8513" * --- a trailing blank in an SQL statement is not recommended, * --- the trailing blank in the following example is to show the * --- single-quote double-quote single-quote pattern. mVar = SQLEXEC("SELECT * FROM CLIENTS WHERE Client_Id = " + ; mVar1 + ' " ') * --- ...Client_Id = single-quote double-quote + mvar3 + * --- double-quote single-quote... mVar = SQLEXEC("SELECT * FROM CLIENTS WHERE Client_Id = '" + ; mVar3 + " ' " + ' " ') mVar = SQLEXEC("SELECT * FROM " + mVar2 + " WHERE Client_Id = ; 'A8513' ") SYNTAX NOT SUPPORTED -------------------- o Cursors o Views o UNION clause in the SELECT statement o Sub-select queries (inner-select; inner-query select within a select; correlated sub-queries; self-joins; et al.) o Cartesian products (joins without a where clause) o The JOIN keyword o GRANT and REVOKE o dBASE functions DATA TYPE MAPPINGS (for CREATE) -------------------------------- SQL: dBASE: SmallInt numeric 6 Int numeric 11 Character(X) character X VarChar(X) character X Date date 8 Boolean logical 1 Money numeric 20,4 Blob memo 10 Blob(,4) ole 10 Blob(,2) binary 10 see Note Float(X,Y) float X,Y see Note Numeric(X,Y) numeric X,Y mVar = SQLEXEC("CREATE TABLE 'Dat_Type.dbf' ; (Sm_Int SMALLINT, ; Dt_Int INT, ; Char20 CHARACTER(20), ; VarCH12 VARCHAR(12),; Dte DATE,; Blean BOOLEAN,; Mny MONEY,; dbMemo BLOB,; dbOle BLOB(,4),; dbBin BLOB(,2),; Flt FLOAT(10,4), ; Dt_Num92 NUMERIC(9,2))") Creates the following .dbf file: Structure for table C:\DBW\SAMPLES\DAT_TYPE.DBF Table type DBASE Number of records 0 Last update 09/15/94 ----------------------------------------------------------------- Field Field Name Type Length Dec Index 1 SM_INT NUMERIC 6 N 2 DT_INT NUMERIC 11 N 3 CHAR20 CHARACTER 20 N 4 VARCH12 CHARACTER 12 N 5 DTE DATE 8 N 6 BLEAN LOGICAL 1 N 7 MNY NUMERIC 20 4 N 8 DBMEMO MEMO 10 N 9 DBOLE OLE 10 N 10 DBBIN MEMO 10 N Note: Anomaly 11 FLT FLOAT 10 4 N 12 DT_NUM92 NUMERIC 9 2 N ----------------------------------------------------------------- ** Total ** 138 Note: At the time of this writing Blob type 2 ( blob(,2) ) creates a memo field. This is an anomaly and will be fixed in a future release. SAMPLE PROGRAM -------------- * --- This program uses SQLEXEC() to pass a memory * --- variable to IDAPI. SET TALK OFF USE CLEAR DBTYPE='DBASE' IF TYPE("Sql_String")='U' PUBLIC Sql_String Sql_String=SPACE(120) ELSE Sql_String=Sql_String+SPACE(120-LEN(Sql_String)) ENDIF TempVarA=SUBSTR(Sql_String,1,60) TempVarB=SUBSTR(Sql_String,61) @ 10,10 GET TempVarA @ 11,10 GET TempVarB READ Sql_String=TRIM(TempVarA+TempVarB) IF LEN(TRIM(Sql_String)) = 0 .or. LASTKEY() = 27 RETURN ENDIF Sql_Result = SQLEXEC(Sql_String) IF Sql_Result <> 0 DO Error_Rtn RETURN ENDIF USE Answer BROWSE RETURN PROCEDURE Error_Rtn ? "IDAPI ERROR # =" ?? DBERROR() ? "IDAPI MESSAGE = " ?? DBMESSAGE() ? "dBASE ERROR # = " ?? ERROR() ? "dBASE ERROR MESSAGE = " ?? MESSAGE() ? "REMOTE SQL SERVER ERROR # = " ?? SQLERROR() ? "REMOTE SQL SERVER ERROR MESSAGE = " ?? SQLMESSAGE() RETURN * LOCAL SQL RESERVED WORDS ------------------------ ACTIVE ADD AFTER ALL ALTER AND ANY AS ASC ASCENDING AT AUTO AUTODDL AVG BASED BASENAME BASE_NAME BEFORE BEGIN BETWEEN BLOB BLOBEDIT BUFFER BY CACHE CAST CHAR CHARACTER CHECK COLLATE COLLATION COLUMN COMMIT COMMITTED COMPUTED CLOSE CONNECT CONSTRAINT CONTAINING CONTINUE COUNT CREATE CSTRING CURRENT CURSOR DATABASE DATE DB_KEY DEBUG DEC DECIMAL DECLARE DEFAULT DELETE DESC DESCRIBE DESCRIPTOR DESCENDING DISCONNECT DISTINCT DO DOMAIN DOUBLE DROP ECHO EDIT ELSE END ESCAPE EVENT EXCEPTION EXECUTE EXISTS EXIT EXTERN EXTRACT FETCH FILE FILTER FLOAT FOR FOREIGN FOUND FROM FULL GDSCODE GENERATOR GEN_ID GLOBAL GOTO GRANT GROUP HAVING HELP IF IMMEDIATE IN INACTIVE INDICATOR INDEX INIT INNER INPUT INPUT_TYPE INSERT INT INTEGER INTO IS ISQL ISOLATION JOIN KEY LC_TYPE LEFT LENGTH LEV LEVEL LIKE LOGFILE LONG MANUAL MAX MAXIMUM MERGE MESSAGE MIN MINIMUM NAMES NATIONAL NATURAL NCHAR NO NOAUTO NOT NULL NUMERIC OF ON ONLY OPEN OPTION OR ORDER OUTER OUTPUT OVERFLOW PAGE PAGE_SIZE PAGELENGTH PAGES PARAMETER PASSWORD PLAN POSITION POST_EVENT PRECISION PREPARE PROCEDURE PROTECTED PRIMARY PRIVILEGES PUBLIC QUIT RDB&DB_KEY READ REAL REFERENCES RELEASE RESERV RESERVING RETAIN RETURN RETURNS REVOKE RIGHT ROLLBACK RUNTIME SCHEMA SEGMENT SELECT SET SHADOW SHARED SHELL SHOW SINGULAR SIZE SMALLINT SNAPSHOT SOME SORT SQL SQLCODE SQLERROR SQLWARNING STABILITY STARTING STARTS STATEMENT STATIC STATISTICS SUB_TYPE SUM SUSPEND TABLE TERMINATOR THEN TO TRANSLATE TRIGGER TRIM UNCOMMITED UNION UNIQUE UPDATE UPPER USER USING VALUE VALUES VARCHAR VARIABLE VARYING VERSION VIEW WAIT WHEN WHENEVER WHERE WHILE WITH WORK WRITE DISCLAIMER: You have the right to use this technical information subject to the terms of the No-Nonsense License Statement that you received with the Borland product to which this information pertains.