About DBase string fields

 dBase  Comments Off on About DBase string fields
Dec 142016
 

Today I got a Dbase table that was odd:
When read using tdbf, some string field contained NULL in one record, but an empty string in another record. I was unable to produce such a file with Personal Dbase 8 as well as our internal tool DBaseTableViewer (which despite its name can also change tables, it started out as a simple viewer and uses tdbf to access the files): Deleting the contents of a string field always resulted in NULL. Nonetheless the dbf file looked like this:

null-and-empty-string-fields

As you can see the BEARBEITET field has three different values:
ERROR
[empty string]
[NULL]

Looking at it with a Hex editor I found that a string field is treated as NULL, if it contains all spaces ($20), it is treated as an empty string, if it contains at least one NUL ($00) (the rest can also be spaces), which was the case for the second record. I can recreate the effect by changing the file with said Hex editor.

The source code in tdbf for reading a string field does the following:

  1. Get the field offset and length.
  2. Copy its content to a buffer.
  3. Starting from the end, decrement the length as long as length is > 0 and the lengthth character is a space
  4. If length is now 0, treat the value als NULL, if not, copy it to a string by typecasting the buffer to a PChar

So if the field’s content is:

00 00 00 00 00

it will converted to an empty string.

If the content is:

20 20 20 20 20

it will converted to NULL.

If the content is:

20 00 20 20 20

it will converted to ‘ ‘ (a string containing a single space).

I still have no idea how this file was created though.

 Posted by on 2016-12-14 at 19:34

Specifying a date in dbase

 dBase  Comments Off on Specifying a date in dbase
Jan 312014
 

One of the recurring confusions with databases is how to specify a date value in SQL statements. In the case of dbase, it’s not just SQL but also the builtin language.

There you specify a date as

ctod("<date>")

where <date> is the date in the format currently used for output.

e.g. if you are using the standard German date format:

replace all DATEFIELD to ctod("31.01.2014")

 Posted by on 2014-01-31 at 13:01

dBASE: When an empty string field does not match “”

 dBase  Comments Off on dBASE: When an empty string field does not match “”
Jul 292013
 

Filtering the current table on a field in dBASE is done like this:

set filter to [condition]

So if you want all records where ANUMBER > 10 you just type this command:

set filter to ANUMBER > 10

It’s as easy as this… unless your field ANAME is a character field and you want all records where it isn’t empty. You are probably tempted to use:

set filter to ANAME <> ""

Oddly enough for my table it did return an empty result, even though I knew there must be records where that field is not empty. After perusing Google (Please stop trying to guess what I am searching for, just search for what I tell you!), giving up and asking a colleague, it turned out the correct filter is this:

set filter to .not. isblank(ANAME)

Or alternatively:

set filter to .not. empty(ANAME)

 Posted by on 2013-07-29 at 16:33

Using dBase

 dBase  Comments Off on Using dBase
Jun 052013
 

I recently needed to do some table manipulation with Visual dBase. Not being an expert with this program (I usually write tools for that purpose in Delphi using the BDE or tdbf), I had to look up quite a few commands.

Here are the links that proved useful:

But what I was actually looking for did not show up in a Google search:

How do I replace a field in all records with the corresponding record number multiplied with 10? And this only for those records that do not have a value >= 99999 in that field already.

The answer turned out to be rather simple:

goto top
replace lfdnummer with recno()*10 for lfdnummer < 99999

and just in case you want to replace a value for all records:

replace all lfdnummer with recno()*10
 Posted by on 2013-06-05 at 15:26