Visual FoxPro contains many enhancements for SQL capabilities. For more information, see SQL Language Improvements.

Visual FoxPro includes the following new field and data types:

• VarcharВ 
  
  To store alphanumeric text without including padding by additional spaces at the end of the field or truncating trailing spaces, use the new Varchar field type.
  
  If you do not want Varchar fields translated across code pages, use the Varchar (Binary) field type. For more information, see Varchar Field Type.
  
  You can specify Varchar type mapping between ODBC, ADO, and XML data source types and CursorAdapter and XMLAdapter objects using the MapVarchar Property. You can also specify Varchar mapping for SQL pass-through technology and remote views using the MapVarchar setting in the CURSORSETPROP(В ) function. For more information, see CURSORSETPROP( ) Function and CURSORGETPROP( ) Function.
  
  
• VarbinaryВ 
  
  To store binary values and literals of fixed length in fields and variables without padding the field with additional zero (0) bytes or truncating any trailing zero bytes that are entered by the user, use the Varbinary data type. Internally, Visual FoxPro binary literals contain a prefix, 0h, followed by a string of hexadecimal numbers and are not enclosed with quotation marks (""), unlike character strings. For more information, see Varbinary Data Type.
  
  You can specify binary type mapping between ODBC, ADO, and XML data source types and CursorAdapter and XMLAdapter objects using the MapBinary Property. You can also specify binary mapping for SQL pass-through technology and remote views using the MapBinary setting in the CURSORSETPROP(В ) function. For more information, see CURSORSETPROP( ) Function and CURSORGETPROP( ) Function.
  
  
• Blob
  
  To store binary data with indeterminate length, use the Blob data type. For more information, see Blob Data Type.
  
  

Many of the Visual FoxPro language elements affected by these new data types are listed in the topics for the new data types.

Visual FoxPro includes a new binary, or bitmap, index for creating indexes based on logical expressions, for example, indexes based on deleted records. A binary index can be significantly smaller than a non-binary index and can improve the speed of maintaining indexes. You can create binary indexes using the Table Designer or INDEX command. Visual FoxPro also includes Rushmore optimization enhancements in the SQL engine for deleted records.

For more information, see Visual FoxPro Index Types, INDEX Command, ALTER TABLE - SQL Command, and Indexes Based on Deleted Records.

You can convert expressions from one data type to another by using the new CAST(В ) function. Using CAST(В ) makes it possible for you to create SQL statements more compatible with SQL Server.

For more information, see CAST( ) Function.

By using the aCountInfo parameter of the SQLEXEC(В ) and SQLMORERESULTS(В ) functions, you can get the name of the cursor created and a count of the records affected by the execution of a SQL pass-through statement.

For more information, see SQLEXEC( ) Function) and SQLMORERESULTS( ) Function.

Visual FoxPro now supports the DisconnectRollback property for use with the SQLSETPROP(В ), SQLGETPROP(В ), DBSETPROP(В ), and DBGETPROP(В ) functions. DisconnectRollback is a connection-level property that causes a transaction to be either rolled back or committed when the SQLDISCONNECT(В ) function is called for the last connection handle associated with the connection.

The DisconnectRollback property accepts a logical value.

• False (.F.) - (Default) The transaction will be committed when the SQLDISCONNECT(В ) function is called for the last statement handle associated with the connection.
  
  
• True (.T.) - The transaction is rolled back when the SQLDISCONNECT(В ) function is called for the last statement handle associated with the connection.
  
  

The following example shows the DisconnectRollback property set in the DBSETPROP(В ) and SQLSETPROP(В ) functions.

В	Copy Code
DBSETPROP("testConnection","CONNECTION","DisconnectRollback",.T.) SQLSETPROP(con,"DisconnectRollback",.T.)

For more information, see DisconnectRollback property in SQLSETPROP( ) Function.

You can use the new SQLIDLEDISCONNECT(В ) function to allow a SQL Pass-Through connection to be temporarily disconnected. Use the following syntax.

В	Copy Code
SQLIDLEDISCONNECT( nStatementHandle )

The nStatementHandle parameter is set to the statement handle to be disconnected or 0 if all statement handles should be disconnected.

The SQLIDLEDISCONNECT(В ) function returns the value 1 if it is successful; otherwise, it returns -1.

The function fails if the specified statement handle is busy or the connection is in manual commit mode. The AERROR(В ) function can be used to obtain error information.

The disconnected connection handle is automatically restored if it is needed for an operation. The original connection data source name is used.

If a statement handle is temporarily released, the OBDChstmt property returns 0; the OBDChdbc returns 0 if the connection is temporarily disconnected. A shared connection is temporarily disconnected as soon as all of its statement handles are temporarily released.

For more information, see SQLIDLEDISCONNECT( ) Function.

You can retrieve information for all active SQL connection statement handles using the new ASQLHANDLES(В ) function. ASQLHANDLES(В ) creates and uses the specified array to store numeric statement handle references that you can use in other Visual FoxPro SQL functions, such as SQLEXEC(В ) and SQLDISCONNECT(В ). ASQLHANDLES(В ) returns the number of active statement handles in use or zero (0) if none are available. For more information, see ASQLHANDLES( ) Function.

The ADOBookmark property is now supported by the CURSORGETPROP(В ) function. Use this property to obtain the ActiveXВ® Data Objects (ADO) bookmark for the current record in an ADO-based cursor.

For more information, see ADOBookmark Property in CURSORGETPROP( ) Function.

If a table is not selected and an alias is not specified, Error 52, "No table is open in the current work area," is generated. If the cursor selected is not valid, Error 1467, "Property is invalid for local cursors," is generated.

You can obtain the number of fetched records during SQL Pass-Through execution by using the new RecordsFetched cursor property with the CURSORGETPROP(В ) function.

Specifying the RecordsFetched cursor property will return the number of fetched records from an OBDC/ADO-based cursor.

If records have been deleted or appended locally, the RecordsFetched cursor property may not return the current number of records in the OBDC/ADO-based cursor. In addition, filter conditions are ignored.

For more information, see RecordsFetched Property in CURSORGETPROP( ) Function.

You can determine if a fetch process is complete for an OBDC/ADO-based cursor by using the new FetchIsComplete cursor property with the CURSORGETPROP(В ) function. Read-only at design time and run time.

This property is not supported on environment level (work area 0) cursors, tables, and local views.

The FetchIsComplete cursor property returns a logical expression True (.T.) if the fetch process is complete; otherwise False (.F.) is returned.

For more information, see FetchIsComplete Property in CURSORGETPROP( ) Function.

You can use the ISMEMOFETCHED(В ) function to determine whether a Memo field or General field is fetched when you are using delayed memo fetching. For more information about delayed memo fetching, see Speeding Up Data Retrieval.

The syntax for this function is:

ISMEMOFETCHED(cFieldName | nFieldNumber [, nWorkArea | cTableAlias ])

The ISMEMOFETCHED(В ) function returns True (.T.) when the Memo field is fetched or if local data is used. ISMEMOFETCHED() returns NULL if the record pointer is positioned at the beginning of the cursor or past the last record.

For more information, see ISMEMOFETCHED( ) Function.

In Visual FoxPro, you can now cancel a lengthy ADO fetch by pressing the ESC key.

Visual FoxPro supports using long type names with the following functions, commands, and properties.

• CAST( ) Function
  
  
• ALTER TABLE - SQL Command
  
  
• CREATE CURSOR - SQL Command
  
  
• CREATE TABLE - SQL Command
  
  
• CREATE FROM Command
  
  
• CursorSchema Property
  
  
• DataType Property
  
  

The following table lists the data types along with their long type names and short type names.

Visual FoxPro allows ambiguous long type names to be used with the ALTER TABLE, CREATE CURSOR, CREATE TABLE, and CREATE FROM commands. If the specified long type name is not a recognized long type name, Visual FoxPro will truncate the specified name to the first character.

In prior versions of Visual FoxPro, transactions using the BEGIN TRANSACTION Command were only supported for local and remote data from databases. Transactions involving free tables and cursors are now supported through use of the MAKETRANSACTABLE( ) and ISTRANSACTABLE( ) functions. For more information, see MAKETRANSACTABLE( ) Function and ISTRANSACTABLE( ) Function.

You can specify a code page by including the CODEPAGE clause with the CREATE CURSOR or CREATE TABLE commands.

When the CODEPAGE clause is specified, the new table or cursor has a code page specified by nCodePage. An error, 1914, "Code page number is invalid", is generated if an invalid code page is specified.

The following example creates a table and displays its code page:

В	Copy Code
CREATE TABLE Sales CODEPAGE=1251 (OrderID I, CustID I, OrderAmt Y(4))

? CPDBF(В )

For more information, see CREATE CURSOR - SQL Command, CREATE TABLE - SQL Command and Code Pages Supported by Visual FoxPro.

Visual FoxPro now supports automatic conversion from character data type to memo data type without loss of data when using the ALTER TABLE command along with the ALTER COLUMN clause. This conversion is also supported when making structural changes using the Table Designer. For more information, see ALTER TABLE - SQL Command.

You can initialize fields in the current record to their default values as stored in the table's database container (DBC) by using the DEFAULT [AUTOINC] option when clearing the record with the BLANK command. For more information, see BLANK Command.

Visual FoxPro now includes options and parameters for the FLUSH command and FFLUSH function so you can explicitly save all changes you make to all open tables and indexes. You can also save changes to a specific table by specifying a work area, table alias, or a path and file name. For more information, see FLUSH Command and FFLUSH( ) Function.

The new cTableName parameter for the AUSED(В ) function makes it possible to filter the created array to contain only the aliases being used for a specified table.

AUSED(ArrayName [, nDataSessionNumber [, cTableName ]])

The cTableName parameter accepts the following formats to specify a table, from highest to lowest in priority.

• DatabaseName!TableName or DatabaseName!ViewName
  
  
• Path\DatabaseName!TableName or Path\DatabaseName!ViewName
  
  
• DBC-defined table name or view in the current DBC in the current data session
  
  
• Simple or full file name
  
  

For more information, see AUSED( ) Function.

You can use the new GETAUTOINCVALUE( ) function to return the last value generated for an autoincremented field within a data session. For more information, see GETAUTOINCVALUE( ) Function.

The new SET TABLEPROMPT command controls whether Visual FoxPro prompts the user with the Open Dialog Box (Visual FoxPro) to select a table when one specified cannot be found, such as in SELECT - SQL Command. For more information, see SET TABLEPROMPT Command.

For queries such as SELECT - SQL Command, character data is often manipulated using Visual FoxPro functions and expressions. Since the length of the resulting field value may be important for certain application uses, it is valuable to have this Character data mapped to Varchar data in the result set. The SET VARCHARMAPPING command controls whether Character data is mapped to a Character or Varchar data type. For more information, see SET VARCHARMAPPING Command.

When a table header is locked during validation, attempts to open the table, for example, with the USE command, generate the message "File is in use (Error 3)." If the table header cannot be locked for a table open operation, you can suppress this message by setting the third bit for the SET TABLEVALIDATE command. You must also set the first bit to validate the record count when the table opens. Therefore, you need to set the SET TABLEVALIDATE command to a value of 5. Also, a fourth bit option (value of 8) is available for Insert operations which checks the table header before the appended record is saved to disk and the table header is modified.

For more information, see SET TABLEVALIDATE Command.

You can specify fractions of a second for the nSeconds2 parameter to a minimum of 0.001 seconds. You can also specify the following values for the optional second parameter:

• -1 - Always read data from a disk.
  
  
• 0 - Always use data in memory buffer but do not refresh buffer.
  
  

The Table refresh interval check box on the Data tab of the Options dialog box now also accepts fractional values.

For more information, see SET REFRESH Command and Data Tab, Options Dialog Box.

You can use the new Refresh property with the CURSORGETPROP(В ) function to differentiate the SET REFRESH values for individual cursors. The default setting is -2, which is a global value. This value is not available with the SET REFRESH command.

The Refresh property is available at the Data Session and Cursor level. The default setting for a Data Session level is -2 and the default value for a Cursor level is the current session's level setting. If the global level setting is set to 0, the Cursor level setting is ignored.

If a table is not currently selected and an alias is not specified, Error 52, "No table is open in the current work area," is generated.

For more information, see Refresh Property in CURSORGETPROP( ) Function.

You can now use the following syntax with the SET(В ) function to determine how the SET REPROCESS command was declared.

For more information, see SET( ) Function and SET REPROCESS Command.

You can use the new SYS(3092) function in conjunction with SYS(3054) to record the resulting output to a file.

SYS( 3092 [, cFileName [, lAdditive ]])

The cFileName parameter specifies the file to echo the SYS(3054) output to. Sending an empty string to cFileName will deactivate output recording to the file.

The default value for lAdditive is False (.F.). This specifies that new output will overwrite the previous contents of the specified file. To append new output to the specified file, set lAdditive to True (.T.).

SYS(3092) returns the name of the current echo file if it is active; otherwise, it returns an empty string.

SYS(3054) and SYS(3092) are global settings — in a multithreaded runtime they are scoped to a thread. Each function can be changed independently from each other.

These functions are not available in the Visual FoxPro OLE DB Provider.

For more information, see SYS(3054) - Rushmore Query Optimization Level and SYS(3092) - Output Rushmore Query Optimization Level.

You can optionally specify the alias or work area of a specified table or cursor for which cached memory is purged. For more information, see SYS(1104) - Purge Memory Cache.

The SYS(2029) function returns new values for tables that contain Autoinc, Varchar, Varbinary or Blob fields. For more information, see SYS(2029) - Table Type.

Use SYS(987) to map remote Unicode data retrieved through SQL pass-through or remote views to ANSI. This function can be used to retrieve remote Varchar data as ANSI for use with Memo fields. This setting is a global setting across all data sessions so should be used with care. For more information, see SYS(987) - Map Remote Data to ANSI.

When the mouse pointer is positioned over a Memo field cell in a Browse window or Grid control, a Memo Tip window displays the contents of the Memo field.

For other field types, positioning the mouse pointer over the field displays the field contents in a Field Tip window when the field is sized smaller than its contents.

Memo Tip windows display no more than 4 kilobytes of text, and are not displayed for binary data. A Memo Tip window is displayed until the mouse pointer is moved from the Memo field. The _TOOLTIPTIMEOUT System Variable determines how long a Field Tip window is displayed.

You can disable Memo Tips by setting the _SCREEN ShowTips Property to False (.F.).

Memo and Field Tips will also be displayed for Grid controls if both _SCREEN and the form's ShowTips property are set to True (.T.). Additionally, the ToolTipText Property for the field's grid column Textbox control must contain an empty string.

You can specify the code page used to decode data when XML is being parsed and to encode data when UTF-8 encoded XML is generated. The following language changes are available:

• nCodePage Parameter
  
  To specify code pages, you can use the nCodePage parameter for the following XMLToTable methods:
  
  
  • ToCursor Method
    
    
  • ChangesToCursor Method
    
    
  • ApplyDiffgram Method
    
    

  В	Copy Code
  XMLTable.ToCursor ( [ lAppend [, cAlias [, nCodePage ]]] ) XMLTable.ChangesToCursor( [ cAlias [, lIncludeUnchangedData [, nCodePage ]]] ) XMLTable.ApplyDiffgram( [ cAlias [, oCursorAdapter [, lPreserveChanges [, nCodePage ]]]] )

• CodePage and UseCodePage Properties
  
  Use the CodePage Property and UseCodePage Property to specify code pages when you use the following classes:
  
  
  • XMLAdapter Class
    
    
  • XMLTable Class
    
    
  • XMLField Class
    
    

  В	Copy Code
  XMLAdapter.CodePage = nValue XMLTable.CodePage = nValue XMLField.CodePage = nValue

• Flag 32768
  
  The flag 32768 is available for the following functions and class:
  
  
  • CursorAdapter Class
    
    
  • XMLTOCURSOR( ) Function
    
    
  • CURSORTOXML( ) Function
    
    
  • XMLUPDATEGRAM( ) Function
    
    

  В	Copy Code
  CursorAdaptor.Flags = nCodePage XMLTOCURSOR( eExpression | cXMLFile [, cCursorName [, nFlags ]]) CURSORTOXML(nWorkArea | cTableAlias, cOutput [, nOutputFormat [, nFlags [, nRecords [, cSchemaName [, cSchemaLocation [, cNameSpace ]]]]]]) XMLUPDATEGRAM( [ cAliasList [, nFlags [, cSchemaLocation]]])

  The nCodePage parameter must match a recognized Visual FoxPro code page.
  
  

For more information, see Code Pages Supported by Visual FoxPro.

For CursorAdapter and XMLAdapter classes, you can use the MapVarchar property to map to Varchar data types. To map to Varbinary and Blob data types, you can use the MapBinary property.

The XMLTOCURSOR( ) Function contains several new flags to support mapping of Char and base64Binary XML field types to new Fox data types.

For more information, see the MapVarchar Property and MapBinary Property.

You can better handle conflicts when performing update and delete operations using the commands specified by the UpdateCmd and DeleteCmd properties for CursorAdapter objects by using the new ConflictCheckType and ConflictCheckCmd properties for CursorAdapter objects.

You can use ConflictCheckType to specify how to handle a conflict check during an update or delete operation. When ConflictCheckType is set to 4, you can use ConflictCheckCmd to specify a custom command to append to the end of the commands in the UpdateCmd and DeleteCmd properties.

Note:
Visual FoxPro 8.0 Service Pack 1 includes the ConflictCheckType and ConflictCheckCmd properties.

For more information, see ConflictCheckType Property and ConflictCheckCmd Property.

You can specify default settings for CursorFill Method calls made without the first two parameters by setting these properties. For more information, see UseCursorSchema Property and NoData Property.

The new TimestampFieldList property lets you specify a list of timestamp fields for the cursor created by the CursorAdapter. For more information see TimestampFieldList Property.

There are a number of scenarios where you might want to have cursor data refreshed from a remote data source after an Insert/Update operation has occurred. These include following scenarios:

• A table has an auto-increment field that also acts as a primary key.
  
  
• A table has a timestamp field, and that field must be refreshed from the database after each Insert/Update in order to allow successful subsequent updates to the record when WhereType=4 (key and timestamp).
  
  
• A table contains some fields which have DEFAULT values or triggers defined that will cause changes to occur.
  
  

The following new properties have been added to the CursorAdapter class for Auto-Refresh support:

For more information about how Visual FoxPro updates remote data using a CursorAdapter, see Data Access Management Using CursorAdapters. Also, see InsertCmdRefreshCmd Property, InsertCmdRefreshFieldList Property, InsertCmdRefreshKeyFieldList Property, UpdateCmdRefreshCmd Property, UpdateCmdRefreshFieldList Property, UpdateCmdRefreshKeyFieldList Property and RefreshTimeStamp Property.

In Visual FoxPro 8.0, the REFRESH( ) Function provides on demand record refresh functionality for local and remote views, however, it does not support this for the CursorAdapter. Visual FoxPro 9.0 extends REFRESH(В ) support to the CursorAdapter and provides some additional capabilities:

For more information, see RecordRefresh Method, BeforeRecordRefresh Event, AfterRecordRefresh Event, RefreshCmdDataSourceType Property, RefreshCmdDataSource Property, RefreshIgnoreFieldList Property, RefreshCmd Property and RefreshAlias Property.

The CursorAdapter class has a FetchMemo Property, which when set to False (.F.) in Visual FoxPro 9.0 places the cursor in Delayed Memo Fetch mode similar to Remote Views. Delayed Memo Fetch Mode prevents the contents of Memo fields from being fetched using CursorFill Method or CursorRefresh Method. An attempt to fetch content for a Memo field is done when the application attempts to access the value. The following CursorAdapter enhancements provide support for Delayed Memo Fetch:

For more information, see DelayedMemoFetch Method, FetchMemoDataSourceType Property, FetchMemoDataSource Property and FetchMemoCmdList Property.

The new UseTransactions property specifies whether the CursorAdapter should use transactions when sending Insert, Update or Delete commands through ADO or ODBC. For more information, see UseTransactions Property.

In Visual FoxPro 9.0, DEFAULT values and table and field level CHECK constraints are supported for XML, Native, ADO and ODBC data sources. In Visual FoxPro 8.0, DEFAULT values and table and field level CHECK constraints are only supported for an XML data source. For the DEFAULT values and CHECK constraints to be applied to a cursor, call the CursorFill Method with the lUseSchema parameter set to True (.T.). For more information, see CursorSchema Property.

When you move data between a remote server and Visual FoxPro, Visual FoxPro uses ODBC or ADO data types to map remote data types to local Visual FoxPro data types. In Visual FoxPro 9.0, certain ODBC and ADO data types can now be mapped to a logical data type in remote views and the CursorAdapter object. For more information, see Data Type Conversion Control.

When working with an ADO data source for your CursorAdapter, you may want to specify a code page to use for character data translation. The new ADOCodePage property allows you to specify this code page. For more information, see ADOCodePage Property.

You can read to and write from your relational database into XML documents using nesting to handle the relationships between tables. You accomplish this using the RespectNesting Property of the XMLAdapter class. The XMLTable class has the Nest Method, Unnest Method and the following properties to handle nesting.

• FirstNestedTable Property
  
  
• NestedInto Property
  
  
• NextSiblingTable Property
  
  

For more information, see the XMLAdapter Class and the XMLTable Class.

The LoadXML method accepts any XML document with a valid schema. Previously, the method required that the schema follow the format of a Visual Studio generated dataset. When you use the LoadXML method to read an XML document with a schema different from a Visual Studio generated dataset, the properties for the XMLAdapter, XMLName, and XMLPrefix properties are set to empty (""). The XMLAdapterXMLNamespace property becomes equal to the target Namespace attribute value for the schema node and each XML element becomes a complexType and is mapped to an XMLTable object. The XMLNamespace property is set to namespaceURI for the element.

If you set the XMLAdapterRespectNesting property to True (.T.), the top level element declaration is ignored if it is referenced from some other complex element. For that case, the XMLTable object for the referenced element is nested into the XMLTable for the element that references it.

For more information, see LoadXML Method.

You can use XPath expressions to access complex XML documents and the new properties for reading the nodes within the document. For example, you might want to filter record nodes, restore relationships based on foreign key fields, use an element's text as data for a field, or access XML that uses multiple XML namespaces. The following properties provide you with the ability to read the XML at the XMLAdapter level, XMLTable level, or the XMLField level.

• DeclareXMLPrefix Property
  
  
• SelectionNamespaces Property
  
  
• XMLNameIsXPath Property
  
  

You can use the following table to determine the node within the XML document that you want to start reading.

For example, if you use an XPath expression in the XMLName property for an XMLAdapter, reading begins at the first node

The following methods do not support the use of XPath expressions in the XMLName property:

• The ApplyDiffgram and ChangesToCursor methods do not support XPath expressions for XMLAdapter and XMLTable objects.
  
  
• The ToCursor method does not support an XPath expression for XMLAdapter when the IsDiffgram property is set to True (.T.).
  
  
• The ToXML method does not support XPath expressions for XMLAdapter and XMLTable objects and ignores XMLField objects that use XPath expressions.
  
  

For more information about XPath expressions, see the XPath Reference in the Microsoft Core XML Services (MSXML) 4.0 SDK in the MSDN library at http://msdn.microsoft.com/library.

Support for the following functions has been added to the OLE DB Provider for Visual FoxPro:

• CURSORTOXML( ) Function
  
  
• XMLTOCURSOR( ) Function
  
  
• XMLUPDATEGRAM( ) Function
  
  

When used in the OLE DB Provider for Visual FoxPro, the _VFPВ VFPXMLProg property is not supported for the CURSORTOXML(В ), XMLTOCURSOR(В ) and XMLUPDATEGRAM(В ) functions because the _VFP system variable is not supported in the OLE DB Provider.

You can use the EXECSCRIPT( ) function with the Visual FoxPro OLE DB Provider. For more information, see EXECSCRIPT( ) Function.

You can use the new SETRESULTSET(В ), GETRESULTSET(В ), and CLEARRESULTSET(В ) functions to mark a cursor or table that has been opened by the Visual FoxPro OLE DB Provider, retrieve the work area of the marked cursor, and clear the marker flag from a marked cursor. By marking a cursor or table, you can retrieve a rowset that is created from the marked cursor or table from a database container (DBC) stored procedure when the OLE DB Provider completes command execution.

For more information, see SETRESULTSET( ) Function, GETRESULTSET( ) Function, and CLEARRESULTSET( ) Function.