[News] [FAQ] [Examples] [Presentations] [Opinions] [About me] [Links] [Perm] [Statistics] [Search]

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

The contents:

• How call SP
• How to use parameters in VFP 8.0 and later
• Solution Code Page problem
• OLEDB VFP 8.0 access problem for IIS 6.0

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:

• First, in a procedural file of VFP-base it is not necessary to use definitions of classes, if it has a place to be, on attempt of connection we receive E_UNEXPECTED error.
• Secondly, it is not necessary to apply a mode adCmdStoredProc in ADODB (or CommandType.StoredProcedure in ADO.NET), in this case we receive: Unrecognized command verb (Error 16).
• Thirdly, for VFP 7.0 it is not necessary to use a collection Command.Parameters, anyway I have not anything good from this. The transfer of values from parameters should be carried out obviously by use cammand string as command to call
• At last, it is necessary to have in view of, that the value returned through RETURN from SP, is located in resulting recordset, containing unique record and one column with the name: 'return_value'.

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

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 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:

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.

In all respects to this web-site, visit page: � Michael Drozdov, 2000-2005. All rights reserved. Last change of page: 09.21.2005