none
Manipulate the 'deleted record' flag on DBF files

    Question

  • Hi,

     

    I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files.  I need to migrate the content of these files to a SQL Server 2005 database.

     

    These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag.  When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

     

    It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

     

    What are the options available for me to be able to query and differentiate the 'deleted' records ?

     

    Thank you in advance,

     

    Bertrand Larsy

    Monday, May 7, 2007 10:06 AM

Answers

  • Hi Bertrand,

    Here's some example VB code to work with the deleted status of a row:

     

     

      Try

       Dim cn1 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn1.Open()
       '-- Make some VFP data to play with
       Dim cmd1 As New OleDbCommand( _
        "Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
       Dim cmd2 As New OleDbCommand( _
        "Insert Into TestDBF Values (1, 'Hello')", cn1)
       Dim cmd3 As New OleDbCommand( _
        "Insert Into TestDBF Values (2, 'World')", cn1)
       Dim cmd4 As New OleDbCommand( _
        "Delete From TestDBF Where Field1 = 1", cn1)
       cmd1.ExecuteNonQuery()
       cmd2.ExecuteNonQuery()
       cmd3.ExecuteNonQuery()
       cmd4.ExecuteNonQuery()
       cn1.Close()

       Dim cn2 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn2.Open()

       Dim cmd5 As New OleDbCommand( _
        "Select * From TestDBF", cn2)
       Dim da1 As New OleDbDataAdapter(cmd5)
       Dim ds1 As New DataSet
       Dim dr1 As DataRow
       da1.Fill(ds1)
       For Each dr1 In ds1.Tables(0).Rows
        Console.WriteLine( _
         dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
       Next
       Console.ReadLine()
       cn2.Close()

       Dim cn3 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn3.Open()

       Dim cmd6 As New OleDbCommand( _
        "Set Deleted Off", cn3)
       cmd6.ExecuteNonQuery()
       Dim cmd7 As New OleDbCommand( _
          "Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
       Dim da2 As New OleDbDataAdapter(cmd7)
       Dim ds2 As New DataSet
       Dim dr2 As DataRow
       da2.Fill(ds2)
       For Each dr2 In ds2.Tables(0).Rows
        Console.WriteLine( _
        dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
       Next
       Console.ReadLine()
       cn2.Close()

      Catch e As Exception
       MsgBox(e.ToString())
      End Try

    Monday, May 7, 2007 1:29 PM

All replies

  • Hi Bertrand,

    Here's some example VB code to work with the deleted status of a row:

     

     

      Try

       Dim cn1 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn1.Open()
       '-- Make some VFP data to play with
       Dim cmd1 As New OleDbCommand( _
        "Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
       Dim cmd2 As New OleDbCommand( _
        "Insert Into TestDBF Values (1, 'Hello')", cn1)
       Dim cmd3 As New OleDbCommand( _
        "Insert Into TestDBF Values (2, 'World')", cn1)
       Dim cmd4 As New OleDbCommand( _
        "Delete From TestDBF Where Field1 = 1", cn1)
       cmd1.ExecuteNonQuery()
       cmd2.ExecuteNonQuery()
       cmd3.ExecuteNonQuery()
       cmd4.ExecuteNonQuery()
       cn1.Close()

       Dim cn2 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn2.Open()

       Dim cmd5 As New OleDbCommand( _
        "Select * From TestDBF", cn2)
       Dim da1 As New OleDbDataAdapter(cmd5)
       Dim ds1 As New DataSet
       Dim dr1 As DataRow
       da1.Fill(ds1)
       For Each dr1 In ds1.Tables(0).Rows
        Console.WriteLine( _
         dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
       Next
       Console.ReadLine()
       cn2.Close()

       Dim cn3 As New OleDbConnection( _
        "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
       cn3.Open()

       Dim cmd6 As New OleDbCommand( _
        "Set Deleted Off", cn3)
       cmd6.ExecuteNonQuery()
       Dim cmd7 As New OleDbCommand( _
          "Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
       Dim da2 As New OleDbDataAdapter(cmd7)
       Dim ds2 As New DataSet
       Dim dr2 As DataRow
       da2.Fill(ds2)
       For Each dr2 In ds2.Tables(0).Rows
        Console.WriteLine( _
        dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
       Next
       Console.ReadLine()
       cn2.Close()

      Catch e As Exception
       MsgBox(e.ToString())
      End Try

    Monday, May 7, 2007 1:29 PM
  • Thank you,

     

    This is indeed a workable solution.

     

    Now, is there a way to perform the same work in a T-SQL script ?

     

    I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

     

    SELECT [name]

    FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

     

    This returns all records, but not the deleted ones

     

    SELECT [name]

    FROM OPENQUERY(linkedServer,'SET Deleted OFF;

    SELECT name FROM Members')

     

    This produces the error '

    Msg 7357, Level 16, State 2, Line 1

    Cannot process the object "SET Deleted OFF;

    SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

    '

    When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

     

    DECLARE @Query varchar(max)

    SET @Query='SELECT name FROM Members'

    EXECUTE (@Query) AT linkedServer

     

    This returns all records, but not the deleted ones

     

    DECLARE @Query varchar(max)

    SET @Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

    EXECUTE (@Query) AT linkedServer

     

    This produces the query output 'Command(s) completed successfully.', but does not return any result.

     

    Kind regards,

     

    Bertrand Larsy

    Tuesday, May 8, 2007 7:10 AM
  • Hi Bertrand,

    I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

    Tuesday, May 8, 2007 3:32 PM
  • Neither of the 2 following samples give any result:

     

    DECLARE @Query varchar(max)

    SET @Query='SET Deleted OFF;SELECT name FROM Members'

    EXECUTE (@Query) AT linkedServer

     

    DECLARE @Query varchar(max)

    SET @Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

    EXECUTE (@Query) AT linkedServer

     

    Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

     

    Kind regards,

     

    Bertrand Larsy

    Wednesday, May 9, 2007 4:49 AM
  • Hello from Munich

     

    I have a similiar problem with an external dbf-file inluding ".DBF", ".KEY", "DBF", which contains a lot of marked recordes for deletion. These are marked with the "deleted flag" of dbf. 

     

    I'am using OLE DB provider "Microsoft.ACE.OLEDB.12.0" to import the dbf-records with OleDbDataReader.

     

    My problem occurs during the import procedere, when the read-pointer is set to a marked record to delete. The problem is, that the index-information were still deleted, however the dbf-records still exist.

     

    The exception error of OleDbDataReader is: "record is deleted. Cannot find the searchkey"

     

    My aim during import is to ignore marked records for deletion, their are not of interest to me.

     

    How can I manipulate the import procedure or the "deleted flag"?

     

    Do a command code for OleDbCommand exist not to import marked dbf-records with deleted flags?

     

    Thank you very much for your kind help!

     

    Thank you in advance,

     

    Garbor

     

     

     

    Friday, December 5, 2008 9:14 AM