Wiki Home

VFP Ole DBProvider


Namespace: WIN_COM_API
As opposed to the VFP ODBC driver.

Note that OLEDB is a COM technology, and that means Windows-only. As opposed to ODBC, which is platform independent (not just Windows).

VFP OLEDB Provider, a updated version from the one included in Visual FoxPro 9.0, is available for download here: http://microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications. The Visual FoxPro OLE DB Provider is supported by OLE DB System Components as provided by MDAC 2.6 or later. The requirements to run the Visual FoxPro OLE DB Provider are the same as for Visual FoxPro 8.0.

Q: The VFP Ole DB Provider in the .NET beta does expose parameterized views, but I have not found a way to specify a parameter. I went through the "Database" project wizard in the .NET IDE, but when I selected a parameterized view, it just returned all data. The result set was as if there was no parameterized WHERE clause in the view. - William Fields

A: Yes, the provider supports DBC events. -- Mike Stewart

How do I redistribute the Ole DB provider with my applications?

This describes how to distribute the provider with your applications using InstallShield Express � Visual FoxPro Limited Edition and the merge module that is installed with Visual FoxPro 7.0.

To see an example of how to distribute the VFP 7.0 OLE DB provider with your applications, do the following:

1. Install VFP 7.0

The merge module for the OLE DB provider will be installed at the same time, by default, in C:\Program Files\Common Files\Microsoft Shared\Merge Modules\, and it is called VfpOleDB.MSM.

2. Install Installhield (IS) Express � Visual FoxPro Limited Edition from the VFP 7.0 Installation CD.

3. Start IS from the Start | Programs | Installshield program group.

4. Create a new IS project (or open an existing one) , by selecting File | New.

5. Navigate to the �Specify Application Data | Objects/Merge Modules� item.

6. Select the �Microsoft Visual FoxPro OLEDB Provider� checkbox.

That should be all there is to it.

References:

-MSDN KB article Q259658 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q259658 "INFO: Location of Merge Modules for Windows Installer Packages"

-VFP Installation CD under TECHNICAL ARTICLES\VFPDEPLOY.DOC written by Mike Stewart. Section "Selecting Objects/Merge Modules"

What is an Ole DB Connection String?

A string containing information on how to connect to a data source using an Ole DB Provider.

Argument Description:
Provider = Specifies the name of a provider to use for the connection.
File Name = Specifies the name of a provider-specific file (for example, a persisted data source object) containing preset connection information.
Remote Provider = Specifies the name of a provider to use when opening a client-side connection. (Remote Data Service only.)
Remote Server = Specifies the path name of the server to use when opening a client-side connection. (Remote Data Service only.)
URL = Specifies the connection string as an absolute URL identifying a resource, such as a file or directory.


How do I get the Connection String needed to use Ole DB?
  1. Create a dummy empty file
    • Using something like Notepad
    • Or on the desktop with New / Text File
  2. Rename the file, changing it's extension to ".UDL"
  3. Double-click on this new file
  4. Fill in the Ole DB dialog that appears
    • Select the Provider (you should see Microsoft OLEDB Provider for Visual FoxPro, among others)
    • Fill in the information in Connection
    • Test the connection
  5. Close and save
  6. Open the newly created UDL file and you will see the connection string that you can cut and paste into your programs
-- Alex Feldstein

Now, How do I use the Ole DB connection string? - ?wgcs

In VFP, using a VFP data source:
cConnectString = [Provider=VFPOLEDB.1;Data Source=] + HOME(1) + [Samples\Data\testdata.dbc;] + ;
  [Mode=ReadWrite|Share Deny None;Password="";Collating Sequence=MACHINE]
oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = cConnectString
oConn.ConnectionTimeout = 30
oConn.Open
cSQL = "select * from customer"
oRS = oConn.Execute (cSQL)
if oRS.EOF
   ? "No records found."
   oRS.Close
   oConn.CLose
   RELEASE oRS, oConn
   RETURN
endif

do while !oRS.EOF
   ? oRS.Fields("Cust_id").Value + " - " + ;
      oRS.Fields("company").Value
   oRS.MoveNext
enddo

oRS.Close
oConn.CLose
RELEASE oRS, oConn
RETURN

In ASP+VBS, using the same sample VFP data source:
dim cConnectString, oRS, oConn
cConnectString = "Provider=VFPOLEDB.1;Data Source=C:\vfp7\Samples\Data\testdata.dbc;" & _
  "Mode=ReadWrite|Share Deny None;Password='';Collating Sequence=MACHINE"
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = cConnectString
oConn.ConnectionTimeout = 30
oConn.Open
cSQL = "select * from customer"
Set oRS = oConn.Execute (cSQL)
If oRS.EOF Then
   oRS.Close
   oConn.Close
   Set oRS = Nothing
   Set oConn = Nothing
   Response.Write "No records found.< br >" & vbCR
   Response.End
End If

Do while not oRS.EOF
   Response.Write oRS.Fields("cust_id").Value & " - " & _
      oRS.Fields("company").Value & "< br >" & vbCR
   oRS.MoveNext
Loop

oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
Response.End

-- Alex Feldstein

I am having problems with the Ole Db provider with vfp in .net 2003.

The following code slowly leaks memory at about 4k/s. Can anybody tell me whats wrong?


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace OleDbMemoryLeaker
{
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Data.OleDb.OleDbConnection fox;
		private System.Data.OleDb.OleDbDataAdapter adapter;
		private System.Windows.Forms.Timer timer1;
		private System.ComponentModel.IContainer components;

		public Form1()
		{
			InitializeComponent();

			this.fox = new System.Data.OleDb.OleDbConnection();
			this.fox.ConnectionString = "User ID=;DSN=;Cache Authentication=False;Data Source=\"C:\\PathToData." +
				"DBC\";Provider=\"VFPOLEDB\";Collating Sequence=MACHINE;Mask Password=False;persis" +
				"t security info=False;Mode=Share Deny None;Extended Properties=;Encrypt Password" +
				"=False";
			this.fox.Open();
		}

		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		private void InitializeComponent()
		{
			this.components = new System.ComponentModel.Container();
			this.timer1 = new System.Windows.Forms.Timer(this.components);
			this.timer1.Enabled = true;
			this.timer1.Interval = 500;
			this.timer1.Tick += new System.EventHandler(this.timer1_Tick);
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(292, 266);
			this.Name = "Form1";
			this.Text = "Form1";

		}
		#endregion

		[STAThread]
		static void Main()
		{
			Application.Run(new Form1());
		}

		DataTable test = null;
		private void timer1_Tick(object sender, System.EventArgs e)
		{
			if(test != null)
			{
				foreach(DataColumn c in test.Columns)
				{
					c.Dispose();
				}
				for(int i = 0; i < test.Columns.Count; i++)
					test.Columns.RemoveAt(0);

				test.Clear();
				test.Dispose();
				test = null;
			}

			test = new DataTable();

			this.adapter = new System.Data.OleDb.OleDbDataAdapter("Select * from test", fox);

			this.adapter.Fill(test);
			this.adapter.SelectCommand.Dispose();
			this.adapter.SelectCommand = null;
			this.adapter.Dispose();
			this.adapter = null;

			GC.Collect();
		}
	}
}

Thanks,
Paul



Hi Paul, it looks like it may be your 'fox' connection object�with every timer iteration you're opening a new connection and never closing it.

Give that a try and see if it makes any difference.

Jeff Bowman
Another sample using the Ole DB connection string in an ASP (not .NET program)

  Source = "c:\mydatabasefolder\"
  'Source = "c:\mydatabasefolder\mydatabase.dbc"
  AccessConStr = "Provider=vfpoledb.1;Data Source=" & Source & ";Collating Sequence=general"
  Set vfpConn = Server.CreateObject("ADODB.connection")
  vfpConn.ConnectionString = AccessConStr
  vfpConn.open
  For i = 0 to vfpConn.Errors.Count-1
    Response.Write "Error number: " & vfpConn.Errors(i).Number & ", " & vfpConn.Errors(i).Description & "
" Next SQLQuery = "SELECT field FROM Table" Set rs = Server.CreateObject("ADODB.RecordSet") Set rs = vfpConn.Execute(SQLQuery) ... do something ... rs.Close vfpConn.Close


Some interesting points: you will get better error messages without "on error resume next" in your function. Also, there may be a difference between the Collating Sequence for dbc and free table directories. For DBC database use MACHINE for free table directories use GENERAL. In situations where you do not have a password do not include it in the connection string "Password=''" causes problems with some drivers.

VFP oleDB on PHP ( w2003 server+ xampp )


// w2003 server web edition + XAMPP( apache+php+mysql) + vfpoledb :D

$conn = new COM("ADODB.Connection");
$conn->Open("Provider=vfpoledb.1;Data Source=//server1/_databases/dbf1;Collating Sequence=Machine");

//insert some data
$filename="/myfolder/myfile.jpg";
$ma_sql='insert into archivo (arch_real) values ("'.$filename.'")';
$conn->Execute($ma_sql);

// lets display records...
$ma_sql="SELECT * from archivo";
$rs = $conn->Execute($ma_sql);
while (!$rs->EOF) {
	.... print data ....
$rs->MoveNext()

$rs->Close();
$conn->Close();
$rs = null;
$conn = null;

Hope it helps: vfp on php

Miguel Angel Hernaiz

what about if I have a database with a stored connection and loads of remote views in my foxpro app, how do I make that
stored connection use the oledb link instead of the old odbc one?




Contributors: William Fields Doug Hennig Mike Stewart Alex Feldstein Nancy Folsom
Category Data Category Code Samples Category VFP Tips And Tricks Category ADO Category 3 Star Topics
( Topic last updated: 2010.06.24 06:05:25 PM )