Microsoft KB Archive/299820

= How To Execute a Stored Procedure in a VFP Database with the VFP OLE DB Provider =

Article ID: 299820

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition

-



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:  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. Name the database StoredProcDBC and save it, noting the folder you save it in. The database designer is now shown. On the database menu, select Edit Stored Procedures.  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  Save the code and close the code window. Close the database designer. Create a new program. To do this, on the File menu, click New and then click Database.  Paste the following code in the code window: *--- CLEAR
 * 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.
 * 1) DEFINE THIS_DIR   SUBSTR(SYS(16),1, ATC(&quot;\&quot;,SYS(16),OCCURS(&quot;\&quot;,sys(16))))
 * 2) DEFINE VFP_DBC    THIS_DIR + &quot;StoredProcDBC.dbc&quot;

PUBLIC goRS, goConn LOCAL lcSQLCMD

CLOSE DATABASES ALL CD (THIS_DIR)

goConn = CREATEOBJECT(&quot;ADODB.Connection&quot;) lcSQLCMD = &quot;AddValues(22,9)&quot;

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

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

goRS.CLOSE goConn.CLOSE

RELEASE goRS, goConn </li> Save the program to the same folder as the database that you just created. The program name is not important.</li> Run the program.</li></ol>

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.

<div class="references_section">