Unspecified error when truncating large dbf

Question

I'm using a OleDbCommand to truncate DBF-files. It works fine for most files, but if the file size is e.g. 400 MB I get an "Unspecified error". I've read somewhere that the size limit of a dbf-file is 2 GB, so I hope there is a way to work with files that large...

System.Data.OleDb.OleDbException: Unspecified error
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
    at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
    at OleDbTruncateTest.Program.Main(String[] args) in C:\Users\henjoh\Visual Studio 2008\Projects\OleDbTruncateTest\OleDbTruncateTest\Program.cs:line 22

Below is the essential code for the operation:

using System;
using System.Data.OleDb;
using System.IO;

namespace OleDbTruncateTest
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string file = @"C:\Temp\largefile.DBF";
                string pathName = Path.GetDirectoryName(file);
                string fileName = Path.GetFileName(file);

                using (OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + pathName + "; Extended Properties=dBase III"))
                {
                    connection.Open();
                    using (OleDbCommand comm = new OleDbCommand("DELETE FROM " + fileName, connection))
                    {
                        comm.ExecuteNonQuery();
                    }
                }
                Console.WriteLine("Done");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            Console.WriteLine("ENTER to exit...");
            Console.ReadLine();
        }
    }
}

Any ideas on how to be able to truncate large dbf-files?

score 1 · Accepted Answer · answered Apr 16 '12 at 11:08

With .dbf files, typically originated with dBASE, Clipper and Foxpro (and Visual FoxPro), they were all designed 32-bit and thus a cap of 2 gig for any single file size. No choice, that's it. If the file is OVER the 2 gig file limit, it must be a .DBF file that is handled by some other product that can read .dbf files, such as Sybase's Advantage Database Server which can directly read/write/support .DBF files and go beyond the 2 gig limit.

If you want to truly truncate (ie: remove all records), delete from will only mark the records for deletion and leave the records there until you "PACK" the table. That said, and since I don't use the Microsoft JET OleDB provider, but use Microsoft Visual FoxPro OleDbProvider download.

Then, I would build a string containing VFP commands to explicitly open the table exclusively and ZAP it (which deletes all records and packs and rebuilds indexes too)... something like

string VFPScript  = "ExecScript( "
      + "[USE " + fileName + " EXCLUSIVE] +chr(13)+chr(10) + "
      + "[IF USED( '" + fileName + "')] + chr(13)+chr(10) + "
         + "[ZAP] +chr(13)+chr(10)+ "
      + "[ENDIF] +chr(13)+chr(10)+ "
      + "[USE]  )";

// put this script into command object, then execute it...
using (OleDbCommand comm = new OleDbCommand( VFPScript, connection))
{
    comm.ExecuteNonQuery();
}

NOTE. The only command here that I don't know if JET recognizes is the "ExecScript()" function which in VFP, allows you to pass a string as a block of commands and execute it as if it was a .prg. So you can do things like loops and IF/ENDIF blocks (with some limitations). However, this example is building the string as

USE YourFile EXCLUSIVE
if used( "YourFile" )
   ZAP
ENDIF
USE

FINAL NOTE. When dealing with the table names. The .DBF extension is IMPLIED when going through the OleDB provider, so you'll want to NOT use the .dbf extension as part of the string. BOTH OleDbProviders will still find the table as long as it is in the path pointed to by the connection string.

Good luck.

Thanks for the tip on the VFP OleDb Provider! It solved the issue with truncating large dbf-files. Unfortunately there were too many other syntax differences between it and the OleDb Provider for just replacing the latter so I ended up using the VFP OleDb Provider just for the 'delete from' command. But it works so I'm happy! ;-) — Henrik, Apr 17 '12 at 05:58
The VFP OleDb Provider together with this solution was spot on! I was trying to achieve the exact same thing, thank you so much. — Saul Delgado, Apr 17 '13 at 15:17

Unspecified error when truncating large dbf

1 Answers1