Enhancements in Europa Data Language & Datatypes

1. MVP Summit: Europa Data Language, Datatypes, CursorAdapter & XMLAdapter

2. 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 Overview: What’s New with Data?

3. SQL - More! • Amount of JOINs and amount of sub-queries 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!

4. 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

5. SQL - Enhanced correlation support • Correlated UPDATE … FROM     • Correlated DELETE  … FROM      • GROUP BY in correlated sub-querySELECT * ; FROM foo1 T1 ; WHERE f1 IN (SELECT MAX(f1); FROM foo2 T2 ; WHERE T2.f2 = T1.f2; GROUP BY f3)

6. 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.

7. Performance • TOP N performance improvement • MIN()/MAX() optimization • Rushmore optimization changes • LIKE • INDEX ON DELETED() / NOT DELETED()INDEX ON…FOR DELETED() / NOT DELETED()

8. 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.

9. 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 non-filtered 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.

10. 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()

11. New DatatypesVarChar • 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)

12. New DatatypesVarBinary • Similar to VarChar except used to store binary data. • NO CPTRANS • Uses: • TimeStamp (native) • GUID • Map to VarBinary in SQL Server

13. New DatatypesBLOB • 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

14. SET COLLATE TO MACHINESET EXACT OFF expression C=C Q=C C=Q Q=Q--------------- --- --- --- ---"abc" = "abc" .T. .T. .T. .T."abc " = "abc" .T. .T. .T. .T."abc" = "abc " .F. .F. .F. .F."abc" = "ABC" .F. .F. .F. .F."ABC" = "abc" .F. .F. .F. .F. SET COLLATE TO MACHINESET EXACT ON expression C=C Q=C C=Q Q=Q--------------- --- --- --- ---"abc" = "abc" .T. .T. .T. .T."abc " = "abc" .T. .F. .T. .F."abc" = "abc " .T. .F. .T. .F."abc" = "ABC" .F. .F. .F. .F."ABC" = "abc" .F. .F. .F. .F. SET COLLATE TO GENERAL SET EXACT OFF expression C=C Q=C C=Q Q=Q--------------- --- --- --- ---"abc" = "abc" .T. .T. .T. .T."abc " = "abc" .T. .T. .T. .T."abc" = "abc " .F. .F. .F. .F."abc" = "ABC" .T. .F. .T. .F."ABC" = "abc" .T. .F. .T. .F. SET COLLATE TO GENERALSET EXACT ON expression C=C Q=C C=Q Q=Q--------------- --- --- --- ---"abc" = "abc" .T. .T. .T. .T."abc " = "abc" .T. .F. .T. .F."abc" = "abc " .T. .F. .T. .F."abc" = "ABC" .T. .F. .T. .F."ABC" = "abc" .T. .F. .T. .F. New DatatypesSET EXACT & Binary Data • Comparison rulesType on Left Padding Case Sensitive------------- --------- ---------------Binary CHR(0) AlwaysCharacter CHR(32) SET COLLATE dependent

15. Remote Data • DisconnectRollback • Determines whether pending transaction has to be rolled back when connection is being disconnected. default (.F.) • RecordsFetched • 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.

16. 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.

17. 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.

18. CursorAdapter • Support for TIMESTAMP fields • TimestampFieldList • Auto-refresh support • On demand record refresh • ca.RecordRefresh([nRecords],[nRecordOffset]) • Delayed Memo fetch • DEFAULT and CHECK constraints • MapVarChar / MapBinary

19. XMLAdapter • Support for hierarchical XML • Support for XPath expressions. • XML encoding/decoding enhancements