Wiki Home

SQL


Namespace: VFP
Structured Query Language

SQL was supposed to be a standard for accessing data, but has been affected by differing proprietary functions supported by each ?RDBMS

This fragmentation makes designing a front-end or middle-tier that functions with a variety of back-end RDBMS data stores difficult.

List here any references you know of that show how to craft back-end agnostic SQL: This table is an SQL language comparison between VFP, Sql Server, My Sql and Oracle Rdbms. If you want to write SQL that will run on any of these platforms, follow the statments made in the "consensus" rows:
Functionality VFP Sql Server My Sql Firebird RDBMS Oracle RDBMS SQL-92
SELECT querystandardsamesamesame
ConsensusYou can use the SELECT statment, with restrictions on its clauses
SELECT .. INTO
Supports: INTO TABLE (tablename), INTO CURSOR (cursorname), INTO ARRAY (arrayname)
INTO TABLE results in a new table on the server
Always (?) returns a cursor as the locally usable result set.
????
????
????
ConsensusData Layer must manage converting the result set to a usable form unless a cursor is desired.
WHERE clause
Supports any VFP expression ( numeric operators, built in functions, user defined functions, etc)
Also supports SQL: LIKE operator, BETWEEN lit1 AND lit2
Supports SQL: LIKE operator, BETWEEN lit1 AND lit2
Supports SQL: LIKE operator, Between?
????
Supports SQL: LIKE operator (does not support [] wildcards), Between?
ConsensusUse the SQL LIKE and BETWEEN operators for strings, never VFP UDFs.
WHERE clause
Case Insensitive Comparisons
Use UPPER(fieldName)
????
????
????
????
????
Aggregate functions
use in SELECT...GROUP BY statements
AVG(nExpr) CNT( ) MAX(eExpr) MIN(eExpr) NPV(nExpr1, nExpr2 [, nExpr3]) STD(nExpr) SUM(nExpr) VAR(nExpr)
AVG MAX BINARY_CHECKSUM MIN CHECKSUM SUM CHECKSUM_AGG STDEV COUNT STDEVP COUNT_BIG VAR GROUPING VARP
????
????
????
????
ConsensusAVG, MAX, MIN, SUM, COUNT (but VFP returns AVG as integer for AVG( integer)
Stored Procedures
Supported (what syntax calls them?)
Supported
SQLEXEC(nHnd, "MyStoredProc({parameters})", "MyCursorName")
(Does it support the FireBird syntax?)
Not supported in older versions
cValue1 = "whatever"
cValue2 = "whatever"
SQLEXEC(MyConnectionHandle, [EXECUTE MyStoredProc( @Param1 = cValue1, @Param2 = cValue2 )], "MyCursorName" )
Supported
SQLEXEC(nHnd, [SELECT * FROM MyStoredProc({parameters})], "MyCursorName" )
(Does it support the Sql Server syntax?)
Supported (what syntax calls them?)
????
ConsensusStored procedures might have been a way to "fudge" differences between RDBMS's, but since My Sql dosn't support them: avoid stored procedures.
Numeric Data Types
N {character number}, N(len,dec), F {binary float?}, F(len,dec), I {binary integer}, B {binary double}, Y {currency}
decimal/dec, float/double precision, real/float(1..7), float/float(8..15), int/integer
TINYINT {1 byte}, SMALLINT {2 bytes}, MEDIUMINT {3 bytes}, INT {4 bytes}, BIGINT {8 bytes}
????
char varying, character varying(len), character, character(len), binary varying, national character(len)/national char(len), national character varying(len)/national char varying(len), national text
Character Data Types
C {character}, C(len), M {memo}, G {general} ... How do you do a Binary-Char, or Binary-Memo???
varchar/char varying, varchar(len)/character varying(len), char/character, char(len)/character(len), varbinary/binary varying, nchar(len)/national character(len)/national char(len), nvarchar(len)/national character varying(len)/national char varying(len), ntext/national text
CHAR(nLen) {like vfp:C(nLen)}, VARCHAR(nLen) {like vfp:M}, TINYTEXT/TINYBLOB, TEXT/BLOB, MEDIUMTEXT/MEDIUMBLOB, LONGBLOB
????
CHAR?, VARCHAR?, See Oracle Data Types
dec, double precision, float(1..15), integer
ConsensusCHAR(nLen) is the only type that works across all, though using Memo in VFP and VARCHAR in others could be very useful.
Date Data Types
D {date}, T {date-time}
timestamp/rowversion -- Are there any others??
DATETIME { '0000-00-00 00:00:00' }, DATE {'0000-00-00'}, TIMESTAMP(nLen) {nLen=2,4,6,8,10,12 or 14}, TIME {'00:00:00'}, YEAR { 0000 }
????
????
rowversion
Logical Data Types
L {logical}
ENUM( "opt1", "opt2",... ) { one byte selects from defined options }, BYTE {just use a 0 or 1 }
????
ConsensusAvoid Logical fields since a BYTE really is just as good, and not all RDBMS support a boolean. There is no one type across all.

See Also: Personal Oracle
Category SQL Language Category Open QUestions Category System Databases Category Methodologies Category Distributed Data
( Topic last updated: 2007.11.02 12:21:51 PM )