Whats New with Data and SQL in VFP9 - dFPUG
Download
Report
Transcript Whats New with Data and SQL in VFP9 - dFPUG
Whats New with Data and
SQL in Visual FoxPro 9.0
Data Language, Datatypes,
CursorAdapter & XMLAdapter
Overview: What’s New with Data?
SQL
MORE!
Enhanced sub-query
support
Enhanced correlation
support
Enhanced UNION support
Performance
Commands & Functions
Datatypes
Remote Data
CursorAdapter
XMLAdapter
SQL Pass-through (SPT)
OLEDB Provider
Enhancements
SQL - More!
Amount of JOINs and amount of subqueries in SQL statement. .
Amount of UNIONs in SQL SELECT
statement.
Amount of tables referenced by SQL
statement.
More than 24 items in IN list.
Multiple sub-query nesting.
* No hard coded limit!
SQL - Enhanced sub-query support
Sub-query in SELECT list (projection).
Sub-query in FROM clause (derived).
Sub-query in UPDATE SET list.
ORDER BY in conjunction with TOP N
inside of non-correlated sub-query
Support for more complex expressions on
the left side in comparison with sub-query
SQL - Enhanced correlation support
Correlated UPDATE … FROM .
Correlated DELETE … FROM .
GROUP BY in correlated sub-query
SELECT * ;
FROM foo1 T1 ;
WHERE f1 IN (SELECT
FROM
WHERE
GROUP
MAX(f1);
foo2 T2 ;
T2.f2 = T1.f2;
BY f3)
SQL - Enhanced UNION support
UNION in INSERT INTO ... SELECT ...
FROM ...
ORDER BY <field name> when using
UNION
Referenced fields must be present in SELECT
list for the last SELECT in the UNION.
Performance
TOP N performance improvement
MIN()/MAX() optimization
Rushmore optimization changes
LIKE
INDEX ON DELETED() / NOT DELETED()
INDEX ON…FOR DELETED() / NOT DELETED()
Rushmore Optimization
INDEX ON !DELETED() will be used to optimize
!DELETED() or DELETED() conditions when
INDEX ON DELETED() is not present.
INDEX ON <…> FOR !DELETED() will be used to
optimize !DELETED() or DELETED() when
neither INDEX ON DELETED() or INDEX ON
!DELETED() is present.
Rushmore Optimization
When ever it is possible to determine that a
Query should filter on DELETED() or !DELETED()
a filtered index FOR DELETED() or FOR
!DELETED() will be used accordingly if no nonfiltered index exists.
If only indexes filtered FOR !DELETED() were
used for Rushmore optimization and SET
DELETED is ON, additional !DELETED()
optimization is not done as it is unnecessary.
Language Enhancements
BINARY index type …
SET REFRESH changes
CursorSetProp("Refresh") .
SYS(1104,[cAlias | nWorkarea])
FLUSH [{ IN<nWorkArea>|<cTableAlias>}|<cFileSpec>] [ FORCE ]
SYS(3092) output to a file
SET SQLBUFFERING / SELECT … WITH (Buffering = <lexpr>)
CAST()
ICASE()
New Datatypes
VarChar
Similar to char data type except filled value is
not padded (or trimmed) with spaces if value is
assigned explicitly or by means of default value.
Fields from external sources that do not fit into
Varchar limits will map to Memo.
VarChar has priority over Character
Index has same structure as Character
Uses:
Reduce size of indexes
With SQL Server VarChar (when < 254 bytes)
New Datatypes
VarBinary
Similar to VarChar except used to store
binary data.
NO CPTRANS
Uses:
TimeStamp (native)
GUID
Map to VarBinary in SQL Server
New Datatypes
BLOB
Similar to Memo data type except it’s
treated as a true binary data type.
NO CPTRANS
MODI MEMO displays HEX dump
SCATTER/GATHER MEMO
Uses:
Image.PictureVal (Goodbye General!)
Map to BLOB in SQL Server
New Datatypes
SET EXACT & Binary Data
SET COLLATE TO MACHINE
SET EXACT OFF
expression
--------------"abc" = "abc"
"abc " = "abc"
"abc" = "abc "
"abc" = "ABC"
"ABC" = "abc"
C=C
--.T.
.T.
.F.
.F.
.F.
Q=C
--.T.
.T.
.F.
.F.
.F.
C=Q
--.T.
.T.
.F.
.F.
.F.
Q=Q
--.T.
.T.
.F.
.F.
.F.
SET COLLATE TO MACHINE
SET EXACT ON
expression
--------------"abc" = "abc"
"abc " = "abc"
"abc" = "abc "
"abc" = "ABC"
"ABC" = "abc"
C=C
--.T.
.T.
.T.
.F.
.F.
expression
--------------"abc" = "abc"
"abc " = "abc"
"abc" = "abc "
"abc" = "ABC"
"ABC" = "abc"
Q=C
--.T.
.F.
.F.
.F.
.F.
C=Q
--.T.
.T.
.T.
.F.
.F.
SET COLLATE TO GENERAL
SET EXACT OFF
Q=C
--.T.
.T.
.F.
.F.
.F.
C=Q
--.T.
.T.
.F.
.T.
.T.
Q=Q
--.T.
.T.
.F.
.F.
.F.
C=Q
--.T.
.T.
.T.
.T.
.T.
Q=Q
--.T.
.F.
.F.
.F.
.F.
SET COLLATE TO GENERAL
SET EXACT ON
Q=Q
--.T.
.F.
.F.
.F.
.F.
expression
--------------"abc" = "abc"
"abc " = "abc"
"abc" = "abc "
"abc" = "ABC"
"ABC" = "abc"
Padding
--------CHR(0)
CHR(32)
Case Sensitive
--------------Always
SET COLLATE dependent
Comparison rules
Type on Left
------------Binary
Character
C=C
--.T.
.T.
.F.
.T.
.T.
C=C
--.T.
.T.
.T.
.T.
.T.
Q=C
--.T.
.F.
.F.
.F.
.F.
Remote Data
DisconnectRollback
RecordsFetched
Determines whether pending transaction has to be
rolled back when connection is being disconnected.
default (.F.)
Returns amount of records currently fetched from the
back end for ODBC/ADO based cursor.
FetchIsComplete
Returns .T. if fetch process for the ODBC/ADO based
cursor is complete.
SPT
Ability to determine amount of records affected
by an SPT execution.
Via additional parameter for SQLEXEC and
SQLMORERESULTS functions:
aCountInfo – Provides name of the array to populate with
row count information. Array contains two columns: 1 –
Alias, 2 –Count.
SQLIDLEDISCONNECT function
Similar behavior to implicit disconnect based on
IdleTimeOut property, now it can be done explicitly.
The function fails if statement handle is busy or
connection is in manual commit mode.
OLEDB Provider
Return Rowset from a stored procedure.
SetResultSet / GetResultSet / ClearResultSet
When used in native VFP, return value is
the alias of the returned rowset.
CursorAdapter
Support for TIMESTAMP fields
Auto-refresh support
On demand record refresh
TimestampFieldList
ca.RecordRefresh([nRecords],[nRecordOffset])
Delayed Memo fetch
DEFAULT and CHECK constraints
MapVarChar / MapBinary
XMLAdapter
Support for hierarchical XML
Support for XPath expressions.
XML encoding/decoding enhancements
Recommendation
Taming Visual FoxPro‘s SQL
Real World Data Solutions For VFP
Tamar E. Granor with Della Martin
Hentzenwerke Publishing
Special offer 20 EURO for mass order
but only if you are interested…