Dismantle Me

I CANNOT believe I am about to admit to having worked with FoxPro, but at the possible detriment of my career – here goes…

First, in the words of one of my previous coworkers, let me say “STUPID FOX!”.

I am NOT a FoxPro developer nor do I wish to ever see another line of FoxPro code or a DBF again as long as I ever live. I would honestly rather gargle tacks.

I was working on a project where we needed to keep some legacy DBF tables alive and updated while we were migrating to a new system. We designed an SSIS package to get our data out of SQL Server and then truncate the FoxPro tables and run the data back in.

Now here is where, if you were a FoxPro developer, you would say to me “Ha! Idiot. You can’t truncate a FoxPro table. You have to mark the rows for deletion and then pack it”.

Well now I know that – thanks.

The problem came when I tried to issue a ZAP command against the DBF from a SQL Command object and an OLE DB connection to the DBF.

Error: Unsupported verb

Here is a verb for FoxPro – FoxPro SUCKS.

or is that an adverb – whatever.

So obviously I couldn’t ZAP or PACK my DBF from the OLE DB object. I then needed to to drop a TAG and then recreate the index. I realized I was going to have to write more FoxPro code to do this and then call it from my package. The only problem with that is – the only thing worse than FoxPro code is MORE FoxPro code, which is essentially what I was facing.

So after much research and monkeying around, I found a solution for this. Using VB code, you can issue pretty much any command against a DBF if you do it in the right way. This allows me to ZAP, PACK and jack with table indexes from a script object inside of SSIS.

Here is how to execute pretty much any command that you want to against a FoxPro dbf from VB.NET. Here I create an index on one of the FoxPro tables…

Dim cn1 As New OleDbConnection(“Provider=VFPOLEDB.1;Data Source=E:\FoxPro\sales\master.dbf;”)

‘ Create the index on the storemaster table

Dim cmdCreateIndex As New OleDbCommand( _
“EXECSCRIPT([SELECT 0]+CHR(13)+CHR(10)+[USE master EXCLUSIVE]+CHR(13)+CHR(10)+[INDEX ON store_num TAG store_num])”, cn1)

Try

cn1.Open()

cmdCreateIndex.ExecuteNonQuery()

Catch ex As Exception

‘ An error gets thrown, but the operation succeeds

Finally

cn1.Close()

End Try

Notice that an error gets caught even though the operation succeeds. This is still a mystery to me, but I think it’s because FoxPro is looking for some variable to put it’s response in, and can’t find it. I have not been able to figure out how to define that variable in a way that FoxPro can see and write to yet. The error will look something like this…

“variable ‘ ‘ is undefined.”

Closing the connection in the “Finally” is uber important, because if you don’t, nothing will be able to touch that FoxPro table until your process is completely finished and is no longer running. This would include any other commands in your process that need to use the table.