KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q299820: HOWTO: Run a DBC Stored Proc with the VFP OLE DB Provider

Article: Q299820
Product(s): Microsoft FoxPro
Version(s): 3.0,3.0b,5.0,5.0a,6.0,7.0
Operating System(s): 
Keyword(s): kbActiveX kbATM kbCOMt kbvfp300 kbvfp300b kbvfp500 kbvfp600 kbGrpDSFox kbCodeSnippet kb
Last Modified: 17-JAN-2002

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0, 7.0 
-------------------------------------------------------------------------------

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:

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

Additional query words: OLEDB

======================================================================
Keywords          : kbActiveX kbATM kbCOMt kbvfp300 kbvfp300b kbvfp500 kbvfp600 kbGrpDSFox kbCodeSnippet kbvfp700 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP700 kbVFP500a
Version           : :3.0,3.0b,5.0,5.0a,6.0,7.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.