Accessing FoxPro Data Without FoxPro

This small VFP DLL can help bring the power of Rushmore into non-Rushmore applications.

FoxTalk February 1999

Copyright Pinnacle Publishing, Inc. All rights reserved.Accessing FoxPro Data without a FoxPro Front-End
Steve Zimmelman

There are times when FoxPro data access is necessary from applications other than FoxPro. When that happens, the best tool for the job still might be FoxPro. This small VFP DLL can help bring the power of Rushmore into non-Rushmore applications.

Last year, I developed an application for our company in Delphi 3 that reads the tables in our FoxPro DOS legacy system. Everything went fine in beta, so we started to distribute the application to our clients. There was, however, one oversight: We never tested the system against tables that were highly populated. When we installed the application at one of our largest client sites, the application fell to its knees and died. The problem: The Borland Database Engine was attempting some complex queries using Local SQL against a table that had more than two million records in it. Our client informed us that queries were taking as long as 72 hours (yes, that's hours, not inutes) to complete. This, of course, wasn't acceptable, so I started to investigate alternate ways of running the queries. The result was a VFP OLE object in the form of a DLL that runs the queries (or almost any FoxPro command) from within Delphi (or any other 32-bit language) transparently and with the speed of Rushmore. Using this technology, the query time dropped from hours to seconds.

The code for the VFP OLE DLL is actually very simple, and it can contain as little as a single procedure or function. I chose to write a few procedures that were specific to the application, but I also included some generic ones that might be used by any application. For the sake of simplicity, I've included only the generic procedures and functions in the following code:


 **************************************
 * Program: VFP_OLE.PRG
 * Visual FoxPro 5 OLE DLL
 **************************************
 DEFINE CLASS VFP_OLE_Server AS CONTAINER OLEPUBLIC

    Procedure Init
       * The Procedure INIT is automatically
       * executed when the DLL is loaded.
       Set Talk Off
       Set Safe Off
       On Error Do Ole_Err With Error(),Lineno(),;
                                Message(),Program()
       Set Exclusive Off
       Set Null On
       *****************************************
       *-- If CPDIALOG is ON and a DBF that was
       *-- created without a CodePage is opened,
       *-- the CodePage dialog box will confront
       *-- the user.
       *****************************************
       SET CPDIALOG OFF
       *
       Set Reprocess To 1
       *
       * Move FoxPro main screen way off to the
       * bit-bucket so it won't be seen if
       * it's made visible.
       Move Window Screen To -1000,-1000
       Modify Window Screen Title "VFP OLE"
       Hide Window Screen
    EndProc

    Procedure SetDir
    Parameter cDir
       Set Default to (m.cDir)
    EndProc

    Function ExeSql
       Parameter cSql
       Private nRecs,i,cFile,cFileSrc,cFullPath,;
               cDestpath,IsVFPFile,;
               cDbfFileName,nHandle
       lIsVFPFile = .F.
       cFullPath = Set('FullPath')
       *
       * Show main VFP window so File
       * dialog box will be visible
       * if VFP can't find a file that's
       * needed for the SQL command.
       *
       Show Window Screen
       *
       *-- Execute SQL Statement --*
       *
       cSql = AllTrim(m.cSql)
       &cSql
       *
       Hide Window Screen
       *
       nRecs = _Tally
       *
       Set FullPath On
       cFileSrc = DBF()
       Use
       **************************************
       *-- Check TableType.
       *-- If Type Is Visual FoxPro,
       *-- convert to Fox2x.
       **************************************
       nHandle = FOpen(m.cFileSrc)
       If nHandle <> -1
          lIsVFPFile = (FGets(m.nHandle,1)=Chr(48))
          =FClose(m.nHandle)
       Endif
       Use (m.cFileSrc) Exclusive
       cDestPath = left(dbf(),rat('\',dbf()))
       If m.lIsVFPFile
          *-- Convert result To Fox2x format --*
          cFile = 'T'+right(sys(3),7)
          Copy To (m.cDestPath+m.cFile) Type Fox2x
          Use
          Erase (m.cFileSrc)
          If File(Left(m.cFileSrc,;
                  Len(m.cFileSrc)-4)+'.FTP')
             Erase (Left(m.cFileSrc,;
                    Len(m.cFileSrc)-4)+'.FTP')
          Endif
          Rename (m.cDestPath+m.cFile+'.DBF') ;
                 TO (m.cFileSrc)
          If File(m.cDestPath+m.cFile+'.FPT')
             Rename (m.cDestPath+m.cFile+'.FPT');
                    TO (Left(m.cFileSrc,;
                        Len(m.cFileSrc)-4)+'.FTP')
          Endif


          Use (m.cFileSrc) Exclusive
       Endif
       *-- Restore FullPath setting --*
       Set FullPath &cFullPath

       **-- Return Result Record Count --**
       Return (m.nRecs)
    EndFunc

    Procedure IndexOn
       **-- Create Index Tags --*
       Parameter cDBF,cKey,cTag,lDeleteTags
       Use (m.cDBF) EXCLUSIVE In 0 Alias IndexDBF
       Select IndexDBF
       If m.lDeleteTags
          DELETE TAG ALL
       Endif
       INDEX ON &cKey TAG &cTag
       USE
    EndProc

    Procedure SetPath
       Parameter cPath
       Set Path To (m.cPath)
    EndProc

    Procedure FoxCommand
       Parameter cCMD
       &cCMD
    EndProc

    Function FoxFunction
       Parameter cFunc
       Private Rtn
       Rtn = Eval(m.cFunc)
       Return (m.Rtn)
    EndFunc

 ENDDEFINE

 Procedure Ole_Err
    **-- Handle DLL internal errors --**
    Parameter nErr,nLine,cMessage,cPRG
    IF (m.nErr=1707)
       *-- CDX not present, OK to Retry --*
       Retry
    Else
       MessageBox( m.cMessage+Chr(13)+Chr(13)+;
                  'Error# '+str(m.nErr,5)+Chr(13)+;
                  'At Line#'+Str(m.nLine,5)+Chr(13)+;
                  'In '+m.cPrg+chr(13)+Chr(13)+;
                  'See File:OLE_ERR.TXT for details.';
                  ,16,'ERROR in VFP_OLE.DLL Module')

       *
       *-- Dump memory and file Status to text file.
       *
       Create Cursor OleError (ErrText M(10))
       List Status NoConsole To File OLE_STAT.TMP
       List Memory Like * NoConsole To File OLE_MEM.TMP

       Append Blank
       Replace ErrText With ;
               Replicate('*',80)+Chr(13)+Chr(10)+;
               DTOC(Date())+' '+Time()+;
               Chr(13)+Chr(10)+;
               PadC(' STATUS ',80,'*')+;
               Chr(13)+Chr(10)

       Append Memo ErrText From OLE_STAT.TMP
       Replace ErrText With Chr(13)+Chr(10)+;
               PadC(' MEMORY ',80,'*')+;
               Chr(13)+Chr(10) Addi

       Append Memo ErrText From OLE_MEM.TMP
       Replace ErrText With Chr(13)+Chr(10)+;
               PadC('-- End Error --',80,'*')+;
               Chr(13)+Chr(10) Addi

       If File('OLE_ERR.TXT')
          Copy Memo ErrText To OLE_ERR.TXT Addi
       Else
          Copy Memo ErrText To OLE_ERR.TXT
       Endif

       Erase OLE_STAT.TMP
       Erase OLE_MEM.TMP
       *
       Close Data
       *-- Cancel causes Delphi or VB to raise an
       *-- error.
       Hide Window Screen
       *-- The CANCEL command causes Delphi
       *-- to be able to trap the error.
       Cancel
    Endif
 EndProc

 *:EOF(VFP_OLE.PRG)

Creating the DLL is very easy in VFP. When you click on the Build button from the project, there's an option to build an OLE DLL. Just click the OLE DLL radio button, then the OK button

Once the DLL is built, the next step is to register it with Windows. This is easily done using REGSVR32.EXE, which is distributed with Windows 95/98, NT, and VFP. The syntax for registering the DLL is:

  REGSVR32 VFP_OLE.DLL

Let's get connected
Getting connected using the DLL is just about as easy as compiling it. In Delphi, you use the CreateOleObject() method, and in VB you use CreateObject(). Because I have a sneaking suspicion that most VFP developers are probably more familiar with VB than Delphi, I used only VB code to demonstrate this technique.

 Private Sub Command1_Click()
    '-- Declare variable to hold the reference
    Dim VFPOLE As Object

    '-- Assign object reference to variable
    Set VFPOLE = CreateObject("VFP_OLE.VFP_OLE_Server")

    VFPOLE.FoxCommand _
             ("Wait Window 'Executing Query' Nowait")

    VFPOLE.FoxCommand ("Select * From Invoice " & _
                       "Where (Date()-InvDate)>=60 "& _
                       "Into Table MyInvAge")

    VFPOLE.FoxCommand ("Wait Clear")

    If VFPOLE.FoxFunction ("Used('MyInvAge')") Then
       DoSomething
    End If

    '-- Release the reference
    Set VFPOLE = Nothing
 End Sub

You can use code similar to this, or, if you have more specific needs, you can write a series of PRGs, compile them into an APP or FXPs, and execute them from non-VFP applications using the FoxCommand method, like this:

   VFPOLE.FoxCommand("DO MyApp.APP")

With this DLL, you have the ability to do almost anything you can do from within FoxPro from non-VFP applications. When you need the power of Rushmore, nothing runs like a Fox!

 

Share this article!

Follow us!

Find more helpful articles: