1 / 20

Whats New with Data and SQL in Visual FoxPro 9.0

Whats New with Data and SQL in Visual FoxPro 9.0. Data Language, Datatypes, CursorAdapter & XMLAdapter. SQL MORE! Enhanced sub-query support Enhanced correlation support Enhanced UNION support Performance Commands & Functions Datatypes. Remote Data CursorAdapter XMLAdapter

kylie-alvarado
Télécharger la présentation

Whats New with Data and SQL in Visual FoxPro 9.0

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Whats New with Data and SQL in Visual FoxPro 9.0 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

  20. Recommendation • Taming Visual FoxPro‘s SQLReal World Data Solutions For VFPTamar E. Granor with Della MartinHentzenwerke Publishing • Special offer 20 EURO for mass orderbut only if you are interested…

More Related