dBase udf and dll

[pixelsearch]

9 hours ago, Holmesware said:

Is there a way to tell if a record is marked as deleted?

It's not easy, because OP wrote above, in this link :

* the GetSubRecord function skips the first byte of the record, that is the record delete mark.
* IsRecordDeleted() missing function.

I just can't understand why OP didn't give access to this crucial information. What's the use of the dll if you can't distinguish valid records from deleted ones when (or before) they are displayed ?

Anyway, knowing the structure of a dbf/foxpro file, it's doable as you'll see in this reworked script :

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <GuiScrollBars.au3>
#include <dBase.au3>

Global $hwnd, $g_idListView, $hDbf
Example()

Func Example()
    $sDbfName = "Test.dbf"
    $hwnd = GUICreate($sDbfName, 1000, 600, 250, 100, -1, $WS_EX_ACCEPTFILES)
    $hDbf = OpenDBF($sDbfName)
    $fldCnt = GetFieldCount($hDbf)
    $g_idListView = GUICtrlCreateListView("", 0, 0, 1000, 600)
    _GUIScrollBars_Init($g_idListView)
    _GUICtrlListView_AddColumn($g_idListView, "Del", 35)
    GUISetState(@SW_SHOW)

    Local $cnt = 0
    Do
        $fld = GetFieldName($hDbf, $cnt)
        $len = GetFieldLenght($hDbf, $cnt)
        $len = $len*5
        _GUICtrlListView_AddColumn($g_idListView, $fld, 60+$len)
        $cnt +=1
    Until $cnt = $fldCnt

    Local $hFileOpen = FileOpen($sDbfName, 0 + 512) ; 0 = Read, 512 = ANSI
    If $hFileOpen = - 1 Then Exit MsgBox(0,"FileOpen", "opening error")
    ; Pos 8-9       Position of first data record
    ; Pos 10-11     Length of one data record, including delete flag
    FileSetPos($hFileOpen, 8, $FILE_BEGIN) ; on byte 8
    $sRead = FileRead($hFileOpen, 4) ; read 4 bytes (8-9-10-11)
    $iPosFirstRecord = Asc(StringMid($sRead, 1, 1)) + 256 * Asc(StringMid($sRead, 2, 1))
    $iRecordLength   = Asc(StringMid($sRead, 3, 1)) + 256 * Asc(StringMid($sRead, 4, 1))
    Local $sData, $iIndex = - 1 ; keep it always - 1

    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    Local $fldNr = 36 ; 1st column = 0
    Local $vSearch = 4.90
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

    For $i = 0 To GetRecordCount($hDbf) - 1
        If StringInStr(GetSubRecord($hDbf, $i, $fldNr), $vSearch) > 0 Then
            $iIndex += 1 ; 0+
            If $iIndex = 0 Then _GUICtrlListView_BeginUpdate($g_idListView)
            Local $iPosDeleted = $iPosFirstRecord + $i * $iRecordLength
            FileSetPos($hFileOpen, $iPosDeleted, $FILE_BEGIN)
            $sData = FileRead($hFileOpen, 1) = "*" ? " X|" : "|"
            For $j = 0 To $fldCnt - 1
                $sData &= GetSubRecord($hDbf, $i, $j) & "|"
            Next
            GUICtrlCreateListViewItem(StringTrimRight($sData, 1), $g_idListView)
        EndIf
    Next

    If $iIndex > -1 Then _GUICtrlListView_EndUpdate($g_idListView)
    WinSetTitle($hWnd, "", WinGetTitle($hWnd) & " : " & $iIndex + 1 & " match")

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                FileClose($hFileOpen)
                CloseDBF($hDbf)
                ExitLoop

            Case $g_idListView

        EndSwitch
    WEnd

    GUIDelete($hwnd)
EndFunc

The deleted records are flagged with "X" in an extra 1st column. In case you don't want them to appear at all, it should be easy for you to move some lines in the script.

The only issue (hopefully) you may encounter could appear with this line :

Local $hFileOpen = FileOpen($sDbfName, 0 + 512) ; 0 = Read, 512 = ANSI

My foxpro file is ANSI, so I had to open it with 512
If your foxpro file needs another value for opening, then you'll find the different values in AutoIt help file, topic FileOpen()

Please tell us if it worked for you, ok ?
Good luck

[RTFC]

10 hours ago, Holmesware said:

Is there a way to tell is a record is marked as deleted?

If you were to use function _Xbase_ReadToArray() from here, you'll note the comment in this line that populates the array:

$array[$currow][$curcol]=DllStructGetData($XbaseRecord,$fc+1)   ; first entry = deletion flag (ignored here)

All you'd have to do is adapt this, i.e., DllStructGetData($XbaseRecord,1) would retieve the deleted-flag for the currently read record.

Edited March 24, 2022 by RTFC

[Holmesware]

Again Thank you, great info. I found a shortcut. Since I'm already working off a copy of the DB file and not the live one. I just packed the DB before searching, but this info may prove useful for the next issue I run into. Also, it may not be helpful if I need the record number. Right now I just need the data.

[Holmesware]

Yes, This works. I had to open my Foxpro file with 512 as well. Thanks again.

[pixelsearch]

@Holmesware: that's great news, glad it worked 
I didn't want to mention OP's pack function, because my foxpro file became totally corrupted after I tried it yesterday, with a more terrible issue than user "jwurmz" described in this link (precedent page). Here are some of his words :

Pack($hDbf) just locks the program up. All other file operations seem to operate as needed; I can create a DBF from scratch, add records, read records, and mark records as deleted without any major issue. However, a Pack operation doesn't succeed and I need this big time.

OP replying  him this, in his very last comment, before leaving the Forum :

Pack function was tested many times, but maybe there is still a bug in the code

Gladly I tried OP's Pack function on a copy of the foxpro file, so no harm done. As Pack worked for you (using OP's function ?) then I'll retry it one of these days, in case I didn't use it correctly the 1st time.

@RTFC: thanks for your input, sure it's worth a try.
 

[Holmesware]

With regards to the pack function. I found that it'll corrupt the FPT file (memo block file). Copy the origional FPT file out of the folder and copy it back in after the pack and it'll be fine or it'll be fine after you re-index the DB with an actual FoxPro program.

I'm not needing the information in the memo fields so if you dont' copy the FPT file, the pack function works.

I'm working with an old ERP system that uses a FoxPro database and found that after importing a bunch of data programmactically, even to the memo file using a non-autoit progam, I had to pack and reindex the database files with the ERP system before the DB's with memo fields could be read properly.

Thanks for the response. The WHY is just as important as the IT'S WORKING.

I'll have more on this in a bit.

 

[Holmesware]

Wow, didn't expect this.

If you open a DBF file, Pack it, Read from it. It works.

The second you loose that handle ($hDbf), the DB is corrupted.

With your example. Adding the Pack($hDbf) line. Gets me what I need. A search without any deleted entries. But the DB is unusable aftwards.

Adding the highlighted lines, gets me a corrupt database only.

Using Beyond Compare on a before and after Pack version of the file. I was able to restore the database by editing the header back to the Before version.

The Before is what is should look like. I don't have the knowledge to figure out WHY it does this. I'm past the "I need something to work" stage and headed down a rabbit hole of a broken / unfinished .dll file.

 

[jpam]

well.....better late than never!

The bug in the pack function was fixed years ago, but I totally forgot to upload the new version.
have now uploaded the new version to the forum.

jpam

dbasedll.zip