5

I am trying to convert data from Act 2000 to a MySQL database. I have successfully imported the DBF files into individual MySQL tables. However I am having issues with the *.BLB file, which seems to be a non-standard memo file.

The DBF files, identifies themselves as dbase III Plus, No memo format. There is a single *.BLB which is a memo file for multiple DBFs to share BLOB data.

If you read this document: http://cicorp.com/act/sdk/ACT6-SDK-ChapterA.htm#_Toc483994053)

You can see that the REGARDING column is a 6 character one. The description is: This 6-byte field is supplied by the system and contains a reference to a field in the Binary Large Object (BLOB) Database.

Now upon opening the *.BLB I can see that the block size is 64 bytes. All the blocks of text are NULL padded out to that size.

Where I am stumbling is trying to convert the values stored in the REGARDING column to blocks location in the BLB file. My assumption is that 6 character field is an offset.

For example, one value for REGARDING is, (ignoring the square brackets): [ ",J$]

In my Googling, I found this: http://ulisse.elettra.trieste.it/services/doc/dbase/DBFstruct.htm#C1.5

It explains that in memo fields (in normal DBF files at least) the space value is ignore (i.e. it's padding out the column).

Therefore if I'm correct (again, square brackets) [",J$] should be the offset in my BLB file. Luckily I've still got access to the original ACT2000 software, so I can compare the full text in the program / MySQL and BLB file.

Using my example value, I know that the DB row with REGARDING value of [ ",J$] corresponds to a 1024 byte offset (or 16 blocks, assuming my guess of a 64 byte sized block).

I've tried reading some Python code for open source projects that read DBF files - but I'm in over my head.

I think what I need to do is unpack the characters to binary, but am not sure.

How can I find the 64-block based spot to read from based on what's found in the DBF files?

EDIT by Jerry Dodge

I've attempted to reverse-engineer the strings in this field to hexadecimal values, and then to an integer value using StrToInt64, but the result still does not match up with the blob file. I've also tried multiplying this integer value by 64 and not multiplying, but the result keeps winding up outside of the size of the blob file, not actually finding any data.

For example, a value of ___/BD (_ = space) translates to $2f4244 hexidecimal, which in turn translates to the integer value of 3097156, but does not correspond with any relevant portion of data in the blob file, even when multiplied or divided by 64.

Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
Rowan Parker
  • 794
  • 2
  • 7
  • 18
  • I've followed your "assumption" of 64 byte blocks, and have managed to read the `.blb` file with legible data :-) Now, I'm trying to figure out the same, referencing this block based on the "REGARDING" field. – Jerry Dodge Apr 25 '14 at 21:44
  • By the way, my `REGARDING` field contains almost all values different from yours, no brackets, just 1, 2, or 3 textual characters (such as `/ " ' [ } ` as well as numbers and letters. It's my guess that these `6 bytes` (as the documentation states) is translated unwillingly. I've tried a combination of decoding them, with no luck. – Jerry Dodge Apr 26 '14 at 00:33
  • I'm starting to wonder if ACT! has some sort of encryption here, because no matter what combination I try, nothing matches up. – Jerry Dodge Apr 27 '14 at 23:24
  • 1
    I seem to recall that ACT used Codebase to manage their dbf files and codebase most definitely applied compression to the blob files. http://www.codebase.com/ – Nathaniel Johnson May 02 '14 at 18:22
  • @NathanielJohnson I believe these 6 character strings are somehow encoded on a different level. – Jerry Dodge May 03 '14 at 01:29
  • The memo/blob offset you are getting should be a block offset, not a byte offset. So if the number is really 1024, you should look at block 1024 in the blob file. – infused Aug 30 '19 at 18:13

1 Answers1

0

According to the SDK you linked, the following happens as I understand:

There is a TYPE field (right behing REGARDING) that encodes what REGARDING is used for (see the second table of the linked chapter). So I'd assume that if type=6 (meeting not held) the REGARDING is either irrelevant or only contains a meeting ID reference from some other table. On that line of thought I would only expect REGARDING to be a BLB offset if type=101 (or possibly 100). I'd also not abandon the thought that in these relevant cases TYPE might be a concatenation of BLB file index and offset (because there is a mention that each file must not be longer than 30K chars and I really expect to be able to store much more data even in one table).

Levente Pánczél
  • 1,895
  • 2
  • 14
  • 16
  • I'm more than aware of this TYPE field and am only looking at the ones marked as notes. There's still nothing here explaining how to convert the 6 character string to the correspondent BLOB address. – Jerry Dodge May 03 '14 at 01:31
  • Sorry that, did not see it mentioned. Then my best guess is that not all bits of the field are used for the offset (e.g. other bits are file-index, or unused/random). I would try to locate 2-3 strings that are unique, try to calculate their byte-offsets and block-offsets as binary values, and then see whether there is a close match in the 48-bit string. – Levente Pánczél May 03 '14 at 10:35