Microsoft OLE DB Provider for Visual Foxpro 8.0 Readme

Microsoft OLE DB Provider for Visual FoxPro 8.0 Readme

This document contains the most recent notes from the Microsoft Visual FoxPro product team and has more updated information than the online help documentation. For information about new features since previous versions of the Provider, see "What's New in Microsoft OLE DB Provider for Visual FoxPro 8.0”.

For the latest information about the Microsoft OLE DB Provider for Visual FoxPro, please visit the Visual FoxPro home page at http://msdn.microsoft.com/vfoxpro.

1.1 Important Pre-Installation Information

Installing the Microsoft OLE DB Provider for Visual FoxPro 8.0 on a computer that has a previous version of the Provider causes this version to replace the previous version. If you wish to preserve the previous version of the Provider, you must rename it before proceeding with this installation. To rename the Provider using Windows Explorer:

· Shut down any applications that might be using the provider, such as IIS, or any desktop applications that use the provider.

· Open the ...\Program Files\Common Files\System\Ole DB folder

· Right click on vfpoledb.dll, select Rename, and change the name to vfpoledb_save.dll.

· To restore the saved version, delete or rename this release version, and rename vfpoledb_save.dll to vfpoledb.dll. It should not be necessary to re-register the provider, provided that you have not uninstalled the previous version.

1.2. System Requirements

The Visual FoxPro Ole DB Provider has the following minimum system requirements:

· Processor Pentium class

· RAM 64 MB (recommended: 128 MB or higher)

· Available Hard Disk Space 2 MB for minimum install, 4 MB for full install

· Operating System Microsoft Windows XP, Microsoft Windows 2000 Service Pack 2 and later, Windows Me, or Windows 98

· Software Microsoft Data Access Components (MDAC) version 2.6 or higher.

Note Installation on Windows NT 4.0 is not supported.

2. Known Issues

This section lists known issues in this release of the Microsoft OLE DB Provider for Visual FoxPro.

2.1. New or Altered Functionality

The following items involve new behavior or behavior changes that might affect the way your applications behave.

· SYS(3099) – For this version, several changes have been made to the Visual FoxPro SQL engine. The effect of these changes is that certain SQL syntax that was accepted in version 7.0 will no longer work in 8.0. In those cases where you wish to continue using the discontinued syntax, the SYS(3099) function was added:

· SYS(3099, 70) && Version 7 compatibility

· SYS(3099, 80) && Version 8 compatibility (default)

· To set this value in the provider, execute the function with the desired parameter. You may also use this function in stored procedures to set the SQL compatibility level as appropriate. A summary of changes follows.

· Using the DISTINCT clause in a SELECT SQL statement containing Memo and/or General fields is no longer supported. Instead, you can wrap a Memo field expression inside a function such as PADR() or ALLTRIM(). Additionally, you can no longer use a UNION clause with a Memo field unless the ALL clause is also included. An error is now generated if a Memo or General field is used in this scenario.

ex. SELECT DISTINCT * FROM Labels && now fails since the Labels.dbf contains a Memo field

· The handling of the GROUP BY clause (and related HAVING clause) has changed. For example, version 7.0 allows for the following syntax:

ex. SELECT cust_id, country FROM Customer GROUP BY country

This statement can lead to ambiguous results since one of the fields (cust_id) is not specified in the GROUP BY clause. In Version 8, we will now raise an error like SQL Server. Note: you can include an Aggregate function in the field list without having it in the GROUP BY clause.

ex. SELECT COUNT(cust_id), country FROM Customer GROUP BY country

In addition, Memo and General fields are not supported in GROUP BY unless used in an expression:

ex. SELECT last_name,LEFT(notes,100) AS notes2 from employee GROUP BY last_name, notes2

· The following new functionality may be set in the Config.fpw FoxPro configuration file (the Config.fpw file placed must be placed in the same directory as the Provider):

· ENGINEBEHAVIOR – Specifies the Engine compatibility level. Note that this command is the same as SYS(3099), but is settable in the Config.fpw configuration file.

· Example: Specifies that Visual FoxPro treats SQL SELECT commands as it did in versions prior to Visual FoxPro 8.0.

· ENGINEBEHAVIOR=70

· Specifies that Visual FoxPro treats SQL SELECT commands as follows. (Default)

· ENGINEBEHAVIOR=80

· TABLEVALIDATE - Specifies the level of table validation to perform. You can use TABLEVALIDATE when you want a lower level of table integrity checking than the default setting.

· Example: TABLEVALIDATE=<nLevel>

· nLevel specifies the table validation level. If omitted, the default value of nLevel is 3. The following lists the values for nLevel:

· 0 - Does not perform table validation.

· 1 - Validate record count when opening a table. Note: The table (.dbf) file header is locked during validation. This can result in decreased scalability when tables are opened as shared, for example, by calling USE tableName SHARED, or when calling a SQL SELECT, INSERT, UPDATE, or DELETE command for an unopened table, and SET EXCLUSIVE is OFF.

· 2 - Validate record count when appending or inserting records and writing them to disk.

· 3 - Validate as described by nLevel=1 and nLevel=2. (Default)

· The following configuration setting is also now supported in the Config.fpw file:

· MVCOUNT - sets the maximum number of variables that Visual FoxPro Provider can maintain (in stored procedures). For more information, see "Special Terms for Configuration Files" topic on MSDN. Note that the default MVCOUNT has been increased to 16384.

· Example: MVCOUNT=32768

· Note that only the above three FoxPro configuration file settings are recognized by the Provider when reading the Config.fpw file.

· When setting the connection string for the Visual FoxPro OLE DB Provider, you can specify an existing Visual FoxPro ODBC Data Source Name (DSN) instead of a data source. The Provider uses the data source indicated in the DSN and expands it to an appropriate Provider connection string. For example, if there is an existing DSN named "VFP_Northwind", you can connect using the following connection string: "Provider=VFPOLEDB.1; DSN=VFP_Northwind;"

· The GETENV( ) function is no longer supported in the OLE DB Provider.

· Stored Procedures may be called in two ways:

· myProc( param1, param2, …) && This is the conventional Visual FoxPro calling syntax.

· Exec myProc param1, param2, … && Alternate syntax. Note that when using ADO or ADO .NET, and setting the CommandType to StoredProc, this form is required.

o When using the CommandType of StoredProcedure in ADO.NET, you must do one of the following or it will fail:

1. Set the CommandText to the name of the stored procedure without any parentheses. Then add the parameters to the Parameters collection.

2. Set the CommandText to the name of the stored procedure with the parameters included in the parentheses like the conventional Visual FoxPro calling syntax. You then can not use the Parameters collection to store the parameter values.

o When using the CommandType of StoredProc in ADO:

1. Set the CommandText to the name of the stored procedure with parentheses and place holders (?) for each parameter sent. Then create parameters objects for each one used in the place holders.

2. Set the CommandText using the conventional Visual FoxPro calling syntax.

· Also note that that the "EXEC" key word is automatically supplied by ADO and ADO .NET and is not required unless the CommandType is other than StoredProc.

2.2. Documentation Issues

Please refer to the "What's New in Visual FoxPro 8.0" topic for a complete list of all new features.

2.3. Feature Issues

This section describes the most recent information about feature issues included in this release of Visual FoxPro.

· When you "drag-and-drop" tables from the Visual Studio .NET Server Explorer to the VS IDE Design Surface, you may have to make changes to the generated SelectCommand – i.e.; remove the double quotes, or enter the appropriate VFP SQL statement via ConfigureDataAdapter. The DataWizard will not currently create the Update/Insert/Delete statements – you must configure these yourself.

· When used with SQL Server as a "linked server", the Provider supports query operations only. The Provider does not support update operations (Update/Insert/Delete).

· Using the DISTINCT clause in a SELECT SQL statement containing Memo and/or General fields is no longer supported. Instead, you can wrap a Memo field expression inside a function such as PADR() or ALLTRIM(). Additionally, you can no longer use a UNION clause with a Memo field unless the ALL clause is also included. An error is now generated if a Memo or General field is used in this scenario. See the SYS(3099) topic in "New or Altered Functionality" in this readme for additional details.

· When passing a table name only to the provider via ADO, you must set the CommandType to adCmdTable.

2.4. Resolved Issues

Following is a list of articles from the Microsoft knowledge base listing problems with the current, released version of the VFP provider (versions 7.0.0.9262 and 7.0.0.9465). These problems are resolved in this web release version of the provider:

BUG: VFP OLE DB Provider Only Works with CHAR Command Parameters

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q321631

BUG: Error Selecting Empty VFP Memo w/ .NET OleDbDataAdapter

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q326544

.