OLE DB Provider for VFP9: Three Ways To Change Session Settings, Useful When One Way Seems Not To Work
by Carl Warner
In answering many online posters asking how to increase the performance of the OLE DB Provider for VFP when using VB.Net and C# to get to data that resides within a VFP database, I have recommended that these non-VFP developers change the default value for TABLEVALIDATE to zero to avoid the performance penalty that the default value of three introduces into their data sessions. Just that one little change seems to help them and they become happy campers.
I have always recommended using the Config.fpw method to get that task done as I outlined back in two past issues of VFUG online newsletters. Well, since that time, I have been informed that sometimes the Config.fpw method doesn't work for some developers, even though I haven't seen or heard of it directly from any non-VFP developer myself. So, to cover all of the bases in case one method doesn't in fact seem to work, I am offering up two other ways to get any user of the OLE DB Provider for VFP to to change certain session settings in trying to grab VFP data from outside of VFP.
Buried in the Help for VFP9, it is given that there are four (yes, only four) settings that can be set at startup time and even a bit later depending on what method you use.
| MVCOUNT | which sets the maximum number of variables that Visual FoxPro can maintain. |
| ENGINEBEHAVIOR | which specifies the Engine compatibility level. |
| TABLEVALIDATE | which specifies the level of table validation to perform. |
| REFRESH | which determines how frequently to refresh local memory buffers with changes from other users on the network.. |
METHOD I-- use of Config.fpw file.
As Fox developers, we are familiar with the Config.fpw file since it can also be used by both a VFP development session or a VFP runtime session. So, naturally, it is easy to relate to it and recommend using it by non-VFP developers as they try to change settings for an OLE DB Provider session.
Let's review the method that I covered in my previous online articles (details under References section below) on the use of this VFP configuration file.
An OLE DB Provider for VFP user simply needs to use an ASCII file editor to create a file called Config.fpw and then to place it in the same folder location as the vfpoledb.dll file. In Windows XP, that is located at C:\Program Files\Common Files\System\Ole DB .
Contents of Config.fpw can be laid out as follows if all four are used (use only what is required and understand what they do):
| TABLEVALIDATE = 0 ENGINEBEHAVIOR = 90 MVCOUNT = 32000 REFRESH = 5 |
After a session is initiated, those of us who are VFP developers can interactively check that these OLE DB provider for VFP work even within a development copy of VFP9 all from a Command Window. As I am not a VB or C# developer, I will show the commands in a VFP9 session. Those of you who want to approach the VFP9 data via those other programming environments will see it will be easy to customize them for your specific environment from the straightforward VFP9 syntax.
To initiate an ADO session using the OLE DB Provider for VFP9 and then to check that the Config.fpw setting worked as far as the TABLEVALIDATE override of the default value of three:
|
oConn=NEWOBJECT('adodb.connection') oConn.Open('provider=vfpoledb;data source=c:\') oRS = oConn.Execute("EXECSCRIPT([RETURN SET('TableValidate')])") ? oRS.Fields(0).Value && Shows that it was set to 0 if the Config.fpw was successful-- the default value is 3 |
METHOD II-- use of commands in ADO Session.
|
oConn=NEWOBJECT('adodb.connection') oConn.Open('Provider=VFPOLEDB;Data Source=C:\') oConn.Execute([SET TableValidate TO 0]) |
| oRS = oConn.Execute("EXECSCRIPT([RETURN SET('TableValidate')])")
&& this is the extra command in this method since the value wasn't
pulled from the Config.fpw that we didn't use here ? oRS.Fields(0).Value && Shows that it was (re)set to 0 from the default value of 3 |
METHOD III-- use of Data Link Properties dialog.
This last method depends on simply taking advantage of
the extra possibilities for settings that can be built into the connection
string for the OLE DB Provider for VFP9.
Besides the four settings available to the Config.fpw file at startup, the OLE
DB Provider for VFP connection string adds two more in the way of a DELETED
setting and another one for a REPROCESS value. The beauty of this method
is that no external file is required and these are built into the connection
string which you have to have anyway to initiate the OLE DB Provider session.
Let's take a look at how to fire this up under Windows XP. [Windows Vista
seems to behave differently as far as getting to view the Data Link Properties
dialog and I don't have to time to fool with Vista's quirks to show you from
there. :) ] Once we have the ability to get to the proper Provider, we will then look at the results of filling in the blanks for not
only the four settings, but also the other two available to us. Again,
only use what you need. We're only doing all six now to show you a
complete connection string with all of them in place and that it in fact can
easily be done.
Also, let's quickly clear up what to do to even get to the Data Link Properties
dialog for all OLE DB Providers on a given PC. I've seen too many
developers who aren't yet familiar with OLE DB Providers look in the same
location as ODBC drivers expecting to see them there. I guess that's a
logical assumption. But, OLE DB Providers are a different animal and they
will not show up there. OLE DB Providers have more in common with DSN-less
ODBC connection strings than the DSN setup stuff you see via the ODBC
Administrator easily accessible via Windows Control Panel.
We will setup two connection strings to the MS supplied TestData.dbc VFP9 database container that is sample data shipped with VFP9.
How Do We Know Quickly What OLE DB Providers, if Any, Are On A PC?
An easy way to get to the Data Link Properties is to go ahead and make a new shortcut available on your desktop. This will get you a way to see all OLE DB Providers installed on a given PC, including the OLE DB Provider for VFP9. When you see the Provider you want to use, you can then use the other tabs of the Data Link Properties dialog to construct a valid connection string for use in your programs.
|
Right-click the desktop.
Select New>Text
Document
Change the name to
LookatOLEDB.UDL (note the extension).
Tell Windows "YES!"
when it warns about changing the file extension.
Now, DblClick that on
the new desktop shortcut and check out your Providers tab.
|
As
you can see below, you can use this new shortcut to build an OLE DB
connection string.
NOTE:
To actually see the connection string AFTER you have constructed it,
rather than the Data Link Properties dialog, just right-click on that
desktop shortcut and choose "Open with" and choose the program NotePad which
will then allow you to view the UDL file that holds the connection string
data that we show examples of below.
| SETTING UP AN OLE DB PROVIDER CONNECTION STRING TO THE VFP DATABASE, TESTDATA.DBC, LEAVING ALL DEFAULT SETTINGS AS-IS | |
 |
Here is the resultant connection string that leaves
everything as is with all defaults in place:
[oledb]
|
| SETTING UP AN OLE DB PROVIDER CONNECTION STRING TO THE VFP DATABASE, TESTDATA.DBC, CHANGING SIX SETTINGS TO OVERRIDE THE DEFAULT VALUES | |
 |
Now we have the new connection string with six
defaults changed: [oledb] ; Everything after this line is an OLE DB initstring Provider=VFPOLEDB.1;Data Source=C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\testdata.dbc;Password="";Collating Sequence=MACHINE;DELETED=True;MVCOUNT=32000;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;REPROCESS=5 |
As you can clearly see, just setup the connection string using the Data Link Properties tabs and use the result in your programs to override defaults and tweak for better performance.
You can use the same ADO commands we used above in the other methods to test that your active sessions did in fact have those settings revised. If they don't work as expected, you can review your connection string or use one of the other methods instead to get what you want.
SUMMARY OF METHODS CAPABLE OF CHANGING SESSION SETTINGS FROM DEFAULTS--
|
I . Config.fpw II. ADO Session Commands III. Enhanced OLE DB Provider Connection String |
I hope this article gives you enough information and insight
where you can now see you can get at least one good way to get to any VFP data
you want without VFP and using the latest OLE DB Provider for VFP.
Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
References:
SET TABLEVALIDATE Command
http://msdn.microsoft.com/en-us/library/a368y56z(VS.80).aspx
SET ENGINEBEHAVIOR Command
http://msdn.microsoft.com/en-us/library/ax9aye37(VS.80).aspx
SET REFRESH Command
http://msdn.microsoft.com/en-us/library/bc65b24b(VS.80).aspx
Special Terms for Configuration Files (MVCOUNT specifically)
http://msdn.microsoft.com/en-us/library/6eks5f35(VS.80).aspx
MVCOUNT |
Sets the maximum number of variables that Visual FoxPro can
maintain. This value can range from 128 to 65,000.
Default: 16,384 |
EXECSCRIPT( ) Function
http://msdn.microsoft.com/en-us/library/9ssewd6y(VS.80).aspx
SET( ) Function
http://msdn.microsoft.com/en-us/library/w8f9b8fa(VS.80).aspx
SET DELETED Command
http://msdn.microsoft.com/en-us/library/87tc2sc3(VS.80).aspx
SET REPROCESS Command
http://msdn.microsoft.com/en-us/library/sb288te2(VS.80).aspx
VFUG Newsletter March 2005 Final 03/22/05:
Talk to yourself [Talk To Your VFP Data via the OLE DB Provider for VFP]
|
|
Overview of ODBC and OLE DB Providers |
|
|
How Do We Know Quickly What OLE DB Providers, if Any, Are On A PC? |
|
|
How Can We Quickly See What Properties Are Are Associated With the OLE DB Provider for VFP? |
|
|
How Can You Use the OLE DB Provider from MS Word? |
|
|
How Can You Establish an OLE DB Provider for VFP connection within VFP and Check The Version of the Provider? |
|
|
How can VB.Net Use the OLE DB Provider for VFP Even Though You Don't Know VB.Net? |
|
|
And Why Do I Want To Be Able To Talk To Myself with the OLE DB Provider for VFP? |
VFUG Newsletter April-May 2005 Final 05/24/05:
Talk To Your VFP Data via the OLE DB Provider for VFP
(Addendum)