5656
How call Stored Procedure of a VFP-database in VFP 7.0 and later through OLE DB

 

The contents:

How call SP
Go top

> Has tried through OLE DB call SP of a VFP-database.
> However I receive errors... Is it possible to overcome it?

I have to confess it has turned out not at once :-) ... and I have come across three problems at least:

Pay attention also that as the function together with values of parameters is transferred as a command and as one string, for parameters type of string there is a problem of a choice of terminators of a string, depending on values in such string-parameters...: - (

Below two examples of code showing as it is possible to call procedure RemainingCredit() from the demonstration example TasTrade.

1. The code example on JScript:

//////////// Save as file: SP_test.wsf 
<package>
<job>
<script id="main" language="jscript">

var gcCntStr = "Provider=VFPOLEDB.1;Data Source=c:\\program files\\microsoft visual foxpro 7\\samples\\tastrade\\data\\tastrade.dbc;";

var goCnt = null; // As ADODB.Connection
var goCmd = null; // As ADODB.Command
var goRS = null;  // As ADODB.Recordset
var gvRetVal = 0; // As Variant
var gErrCode = 1;

var adStateOpen = 1;
var adStateClosed = 0;
var adCmdText = 1;

try
{
	goCnt = WScript.CreateObject("ADODB.Connection");
	goCnt.ConnectionString = gcCntStr;
	goCnt.Open();
	if (goCnt.State == adStateOpen)
	{
		goCmd = WScript.CreateObject("ADODB.Command");
		goCmd.CommandText = "RemainingCredit('ALFKI')";
		goCmd.CommandType = adCmdText;
		goCmd.ActiveConnection = goCnt;
		goRS = goCmd.Execute;
		gvRetVal = goRS.Fields(0).Value;
		goCnt.Close();
		gErrCode = 0;
	}

}
catch(err)
{
	WScript.Echo("Error occurred"
		+ "\nCode: " + hex(err.number) 
		+ "\nDescription: " + err.description); 
	gErrCode = 1;
}
finally
{
	if (goCnt != null && goCnt.State == adStateOpen)
	{
		goCnt.Close();
	}
	goRS = null;
	goCmd = null;
	goCnt = null;
	if (gErrCode == 0)
	{
		WScript.Echo("RemainingCredit('ALFKI') = " + gvRetVal);
	}
	WScript.Quit(gErrCode);
}

//////////////////////////

function hex(nmb)
{
    if (nmb > 0)
        return nmb.toString(16);
    else
        return (nmb + 0x100000000).toString(16);
}
</script>
</job>
</package>
//////////// The end of file: SP_test.wsf 

2. The code example on CSharp

//////////// File: SP_test.cs
using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApp
{
	/// 
	/// Summary description for MyCls.
	/// 
	class MyCls
	{
		/// 
		/// The main entry point for the application.
		/// 
		
		[STAThread]
		static void Main(string[] args)
		{
			const string strCntStr = @"Provider=VFPOLEDB.1;Data Source=c:\program files\microsoft visual foxpro 7\samples\tastrade\data\tastrade.dbc;";
			const string strCmd = @"RemainingCredit('ALFKI')";
			OleDbConnection oleDbConnection = null;
			OleDbCommand oleDbCommand = null;
			object objRetVal = null;
			try
			{
				oleDbConnection = new OleDbConnection();
				oleDbConnection.ConnectionString = strCntStr;
				oleDbConnection.Open();
			 
				if (oleDbConnection.State == ConnectionState.Open)
				{
					oleDbCommand = oleDbConnection.CreateCommand();
					oleDbCommand.CommandType =  CommandType.Text;  
					oleDbCommand.CommandText = strCmd;
					objRetVal = oleDbCommand.ExecuteScalar();
					oleDbCommand.Cancel();
					if (objRetVal != null)
					{
						System.Console.WriteLine(strCmd + " = " + objRetVal);  
					}
				}
			}
			catch(OleDbException e)
			{
				string Msg = e.Message;
				System.Console.WriteLine(Msg); 
			}
			finally
			{
				if (oleDbCommand != null)
				{
					oleDbCommand.Dispose();
					oleDbCommand = null;
				}
				if (oleDbConnection != null)
				{
					if (oleDbConnection.State != ConnectionState.Closed)
					{
						oleDbConnection.Close();
					}
					oleDbCommand = null;
				}
			}
		}
	}
}
//////////// The end of file: SP_test.cs 

See also: Q299820 Knowledge Base Articles HOWTO: Execute a Stored Procedure in a VFP Database with the VFP OLE DB Provider

How to use parameters in VFP 8.0 and later
Go top

Since the version 8.0 usage of a collection of parameters is possible, so is below reduced C#-code, indicating as it is possible to fulfill the parameterized view 'Employee listing' from VFP-database Tastrade.dbc. To receive the SQL-SELECT definition of parameterized View, it is necessary to add in VFP-database Tastrade.dbc the stored procedure __spDbGetProp(), being a wrapper for call to VFP-function BDGETPROP().

FUNCTION __spDbGetProp 
	LPARAMETERS tcName, tcType, tcProperty
	RETURN DBGETPROP(tcName, tcType, tcProperty)
ENDFUNC
further code demonstrating execution this parametrized View, can be such:
using System;
using System.Data;  
using System.Data.OleDb;  

namespace VfpPrmView
{
	/// 
	/// Summary description for VfpPrmView
	/// 
	class testVfpOleDb
	{
		/// 
		/// The main entry point for the application.
		/// 
		[STAThread]
		static void Main(string[] args)
		{
			VfpOleDb vfpOleDb = new VfpOleDb();
			if (vfpOleDb.GetVfpViewData())
			{
				System.Console.WriteLine("Ok!");
			}
			else
			{
				System.Console.WriteLine("Error");
			}
		}
	}

	class VfpOleDb
	{
		static string m_strCntStr = "Provider=VFPOLEDB.1;Data Source=C:\\PROGRAM FILES\\MICROSOFT VISUAL FOXPRO 8\\SAMPLES\\TASTRADE\\DATA\\TASTRADE.DBC;Mode=Share Deny None;Extended Properties=\"\";User ID=\"\";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN=\"\"";
		static string m_strCmdProp = "__spDbGetProp(?,?,?)";                                 // SP to call VFP-function DBGETPROP()
		static string [] m_asPrmName  = new string [] {"tcName", "tcType", "tcProperty"};    // args names in __spDbGetProp()
		static string [] m_asPrmValues  = new string [] {"Employee listing", "view", "SQL"}; // args values to call __spDbGetProp()
		
		static string m_strPrmView = "?cTitle"; // parameter in VFP-view: 'Employee listing'
		static string m_strPrmValue = "Sales";  // parameter value 
		
		System.Data.OleDb.OleDbConnection m_cnt;  	
		
		public VfpOleDb(){}
		
		public bool GetVfpViewData()
		{
			bool bRetVal = false;
			if (!CreateConnect())
			{
				return bRetVal;
			}
			try
			{
				/////////////////////////////////////////////////
				// (1) Call SP to get SQL-SELECT from VFP-database
				using (OleDbCommand cmdProp = m_cnt.CreateCommand())
				{
					cmdProp.CommandText = m_strCmdProp;
					cmdProp.CommandType = CommandType.Text;
					for (int nInd = 0; nInd < m_asPrmName.Length; nInd++)
					{
						cmdProp.Parameters.Add(m_asPrmName[nInd], OleDbType.Char, m_asPrmValues[nInd].Length);
						cmdProp.Parameters[m_asPrmName[nInd]].Value = m_asPrmValues[nInd];
					}
					if (OpenConnect())
					{
						object objView = cmdProp.ExecuteScalar(); 
						if (objView != null)
						{
							// Ok! SET ANSI OFF
							using (OleDbCommand cmdSet = m_cnt.CreateCommand())
							{
								cmdSet.CommandText = "SET ANSI OFF";
								if(OpenConnect())
								{
									cmdSet.ExecuteNonQuery();
								}
							}
							/////////////////////////////////////////////////
							// (2) Replace parameter '?MyParamName' to '?'
							string strSrcSelectView = objView.ToString();
							int nPrmPos = strSrcSelectView.IndexOf(m_strPrmView);
							if (nPrmPos != (-1))
							{
								string strCmdSelectView = strSrcSelectView.Substring(0, nPrmPos)
									+ "?"
									+ strSrcSelectView.Substring(nPrmPos + m_strPrmView.Length);
								/////////////////////////////////////////////////
								// (3) Execute SQL-SELECT
								using (OleDbCommand cmdView = m_cnt.CreateCommand())
								{
									cmdView.CommandText = strCmdSelectView;
									cmdView.CommandType = CommandType.Text;
									// Set value to parameter
									cmdView.Parameters.Add("0", OleDbType.Char, m_strPrmValue.Length);
									cmdView.Parameters["0"].Value = m_strPrmValue;
									if (OpenConnect())
									{
										// Execute SQL-SELECT
										OleDbDataReader oRdr = cmdView.ExecuteReader(CommandBehavior.CloseConnection);
										if (oRdr != null)
										{
											/////////////////////////////////////////////////
											// (4) Show results
											int nRowCount = 0;
											int nFld = 0;
											string sFldName = null;
											while (oRdr.Read())
											{
												System.Console.Write("\n--- row: {0}", nRowCount.ToString());
												for (nFld = 0; nFld < oRdr.FieldCount; nFld++)
												{
													sFldName = oRdr.GetName(nFld);
													System.Console.Write("\n{0}", sFldName);
													System.Console.Write(" = {0}",oRdr.GetValue(oRdr.GetOrdinal(sFldName)));
												}
												nRowCount++;
											}
											oRdr.Close();
											bRetVal = (nRowCount > 0);
											if (bRetVal)
											{
												System.Console.Write("\n===\n");
											}
										}
									}
								}
							}
						}
					}
				}
			}
			catch (Exception e)
			{
				ShowException(e);
			}
			finally
			{
				CloseData();
			}
			return bRetVal;
		}

		bool CreateConnect()
		{
			CloseData();
			try
			{
				m_cnt = new OleDbConnection(m_strCntStr);
			}
			catch (Exception e)
			{
				ShowException(e);
			}
			return (m_cnt != null);
		}

		bool OpenConnect()
		{
			bool bRetVal = false;
			if (m_cnt != null)
			{
				if (m_cnt.State != System.Data.ConnectionState.Open)
				{
					m_cnt.Open();
				}
				bRetVal = (m_cnt.State == System.Data.ConnectionState.Open);
			}
			return bRetVal;
		}

		void CloseData()
		{
			if (m_cnt != null)
			{
				try
				{
					if (m_cnt.State != System.Data.ConnectionState.Closed)
					{
						m_cnt.Close(); 
					}
				}
				catch (NullReferenceException e)
				{
					string strSkipMsg = e.Message; 
				}
				finally
				{
					m_cnt = null;
				}
			}
		}

		~VfpOleDb()
		{
			CloseData();
		}

		void ShowException(Exception e)
		{
			System.Console.WriteLine("Message: {0}", e.Message);   
			System.Console.WriteLine("Source: {0}", e.Source);   
		}
	}
}
As you can see, solution of a type here is applied: "if the mountain does not go to Magomet, Magomet goes to a mountain"... :-) here collection of parameters (OleDbCommand.Parameters) is used doubly too: first time - for definition of values of parameters for the stored procedure __spDbGetProp(tcName, tcType, tcProperty), second - for the definition value (m_strPrmValue = "Sales") of parameter (m_strPrmView = "?cTitle") in SQL-SELECT expression for View 'Employee listing'. Pay attention, that if for you the nonconventional installation of examples delivered with VFP 8 (and/or not 8), before attempt of execution than code reduced above, is necessary for you change constant m_strCntStr in appropriate way.

As example of C#-application operating datas from VFP-database, it is possible to see TestUrls-application in csurltst.zip (151KB) (MS VS.NET 2003 MS Framework 1.1 SP1) for testing of the remote html-links, and code ViewResult-application for review of results obtained through VFP-SP gettestresult().

Solution Code Page problem
Go top

Code Page problem also was obtained at manipulation with stored procedures through MS VFP OLE DB Provider (not from under the VFP-applicatons). In the sense that on second and subsequent call to stored procedure the value return by function CPCURRENT() ceases to be 1251, and changes on 1252. Any location of the file Config.fpw, containing a line CODEPAGE = 1251, which could be thought up, do not help. The situation can be corrected by direct change of the vfpoledb.dll file (see in the folder C:\Program Files\Common Files\System\Ole DB\). The information where and what necessary to be changed are in the table below:

File Address What change
vfpoledb.dll (8.0.0.3006) 0004af7e E4->E3
vfpoledb.dll (8.0.0.3117) 0004b43c E4->E3

At vfpoledb.dll versions: 8.0.0.2521 and 9.0.0.2412 similar problems was not obtained. See also addresses for corrections of a similar problem in vfpXt.dll on How multi-threaded COM component under MS MTS/Component Services "works"?.

OLEDB VFP 8.0 access problem for IIS 6.0
Go top

At attempt to use VFP 8.0 OLEDB Provider from under MS IIS 6.0 (OS Windows 2003 Server) I receive: System.Data.OleDb.OleDbException: No error information available: REGDB_E_CLASSNOTREG (0x80040154). If to create the usual Windows-application (for example on C#) with access through System.Data.OleDb), all works without questions.

It means, that you try to use version 8.0.0.3006 and there are three variants of the decision of a problem, known for me:

  1. Instead of VFP 8.0 OLEDB Provider 8.0.0.3006 use VFP OLEDB Provider for version 7.0
  2. In the machine.config file in section processmodel change UserName with MACHINE on SYSTEM
  3. Instead of VFP 8.0 OLEDB Provider 8.0.0.3006 use VFP OLEDB Provider 8.0.0.3117 (SP1VFP8) or later from http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx Pay attention, that the rights of the local administrator (NOT the domain admin or network admin account) of a server are necessary for correct registration of OLE-component to you.
 
 
Hosted by uCoz