How To Execute a Stored Procedure in a VFP Database with the VFP OLE DB Provider (299820)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 3.0b
  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
  • Microsoft Visual FoxPro for Windows 6.0
  • Microsoft Visual FoxPro for Windows 7.0
This article was previously published under Q299820

SUMMARY

In versions of Visual FoxPro (VFP) earlier than version 7.0, you cannot directly execute a stored procedure in a VFP database from outside VFP. Instead, you must use the Microsoft Visual FoxPro (MSVFP) ODBC driver and table triggers. This is because only ODBC Level 2 compliant ODBC drivers support this kind of functionality, and the MSVFP ODBC driver is ODBC Level 1 compliant, with some Level 2 features.

VFP 7.0 ships with an OLE DB provider that can be used to directly execute a stored procedure in a VFP database. This article describes how to do this.

NOTE: Purchasing VFP 7.0 is the only way to gain access to this OLE DB Provider; it is not posted on the Microsoft Web site, nor does it ship with any version of Microsoft Data Access Components (MDAC).

MORE INFORMATION

NOTE: You must have a version of Microsoft Visual FoxPro, the VFP OLE DB provider, and ActiveX Data Objects (ADO) on your system for this code to work.

To use the VFP OLE DB provider to run a stored procedure in a VFP database, follow these steps in any version of VFP:
  1. Open VFP and create a new database. To do this, on the File menu, click New and then click Database. Do not use the wizard.
  2. Name the database StoredProcDBC and save it, noting the folder you save it in. The database designer is now shown.
  3. On the database menu, select Edit Stored Procedures.
  4. Paste the following code in the code window that appears:
    PROCEDURE AddValues(lpVar1, lpVar2)
IF PARAMETERS() <2 OR VARTYPE(lpVar1) # 'N' OR ;
		VARTYPE(lpVar2) # 'N'
	RETURN .F.
ENDIF
RETURN lpVar1 + lpVar2
ENDPROC
  5. Save the code and close the code window. Close the database designer.
  6. Create a new program. To do this, on the File menu, click New and then click Database.
  7. Paste the following code in the code window:
    *-----------------------------------
* AUTHOR: Trevor Hancock, Microsoft (trevorh@microsoft.com)
* CREATED: 5/23/2001 2:31:06 PM
* ABSTRACT: This program runs a stored procedure named
*           AddValues() and returns a result. This procedure
*           is in a VFP database called StoredProcDBC.DBC
*           Taken from Microsoft Knowledge Base article Q299820.
*-----------------------------------
CLEAR
#DEFINE THIS_DIR	SUBSTR(SYS(16),1, ATC("\",SYS(16),OCCURS("\",sys(16)))) 
#DEFINE VFP_DBC	 	THIS_DIR + "StoredProcDBC.dbc"

PUBLIC goRS, goConn
LOCAL lcSQLCMD

CLOSE DATABASES ALL
CD (THIS_DIR)

goConn = CREATEOBJECT("ADODB.Connection")
lcSQLCMD = "AddValues(22,9)"

goConn.OPEN("Provider=vfpoledb;Data Source=" + VFP_DBC)

? "Connection State:", goConn.State
goRS = goConn.Execute(lcSQLCMD)
? "Stored Procedure Results:", goRS.FIELDS(0).VALUE

goRS.CLOSE()
goConn.CLOSE()

RELEASE goRS, goConn
  8. Save the program to the same folder as the database that you just created. The program name is not important.
  9. Run the program.
When this code runs, it calls the stored procedure in the database (.dbc) file that you created. The result of the stored procedure is stored to an ADO recordset. That value is then printed to _SCREEN. The program passes in two parameters, 22 and 9, which the stored procedure sums and returns.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

148865 PRB: Can't Access Stored Procedures in Visual FoxPro Database

Modification Type:MinorLast Reviewed:6/30/2004
Keywords:kbCodeSnippet kbhowto KB299820
©2004 Microsoft Corporation. All rights reserved.