Simulating NULL characters in dBaseIII/IV

[RuthHB]

I'm trying to figure out how to make number column values in dBaseIII/IV appear to be blank. I understand that dBaseIII/IV does not support NULL per se. However, I have used some products, namely MSExcel and ESRI products (Environmental Systems Research Institute), that allow the user to leave number values blank. However, when I import these dBase files into some software, the blank values show up as a default value, such as 0. I am working in one of these softwares right now that shows them all as 0 (ESRI software ArcMap). For obvious reasons, this is not acceptable. ArcMap comes with a collection of COM objects (called ArcObjects) that can be used for customization. So, I am building a VB6 application that reads data via an ADO connection/Oracle or SQLSvr into a dBase file. My problem is how to get the values that should be NULL to just stay blank in the dBase file. VBNULL or NULL converts to 0 and VBEMPTY converts to 1 (as I'm sure many of you already know). I tried to just not touch the fields, but they still change end up with a default 0 in them. Does anybody know what this is all about or have any ideas?

Thanks in advance!

Ruth

 

[tviman]

Numeric fields in DBF format can be left blank, or empty. As such, blank numeric fields are not equal to " " or "". there just "empty". What you're describing is not a DBF problem but rather a problem with the software you're using to import the DBF table data. It sees the field as blank and is changing it to 0 because it thinks that a blank field is the same as a 0 (zero). What you could do is to create a temporary copy of the DBF file and convert any numeric fields to character data. Or you could build a "filter" application that does it on the fly, assuming that your import application doesn't care what the incoming data looks like.

There's always a better way. The fun is trying to find it!

 

[dbMark]

I base my statements on the specific behavior of dBase 5 for DOS but should apply to most other versions as well.

Actually when a new record is added to a table, the numeric fields are empty. Not even a zero is there. Remember, the table's record is really just a long string of character data. So by default the portion of that record or string is really just empty spaces. If you do a BROWSE, EDIT, DISPLAY or LIST you will see nothing, just blanks.

Background... the database language interprets and writes to that file based upon its rules as contained in its commands and functions. When you open a table with USE <tablename>, it is similar to telling the program to make a call to FOPEN(). When you BROWSE or EDIT, it is similar to the program using FREAD() and display the contents. If you change anything, then when you go to the next ecord it will use the equivalent of FWRITE() to save those changes to the table. When you close the table with USE, it is similar to telling the program to make a call to FCLOSE(). In other words, many of the commands in dBase and other database programming languages are simply an added layer or interface to aid us (and protect us from ourselves and silly mistakes) in manipulating data.

That said, this added but user-friendly layer of functionality comes at a price. Certain behaviors are built in. If you BROWSE or EDIT, you can go to the field using the arrow, tab or enter keys and nothing gets changed. But type in something into a numeric field such as a number, period, minus or plus that the editing mask lets through and you will forevermore have some value in that field, even if it is only a zero. If you remove the number it will by default leave a zero there. And that zero is also present in the literal data string which comprises that file itself.

How to remove those pesky zeros? Within dBase you could write code to use FOPEN() and replce the zero values (if you had set it to have decimals, then you'd have to also search for those, such that 2 decimals would look like "0.00") with blanks or spaces. You would have to be extraordinarily careful that you only looked at the numeric fields and that code would have to be hand coded, quite tedious and very prone to mistakes you may not catch. FREAD() and FWRITE() know nothing about records and fields, as that is the layer added for us humans to use.

In short, dbase will never remove a zero from a numeric field which had been edited previously unless you access it in low-level mode which is hard and possibly dangerous for most programmers to tackle.

 

[dbMark]

Try this command to blank specific fields, including numeric fields:

BLANK FIELDS {field1, field2, ...}

To clear the entire record:

BLANK

This works in dBase 5 for DOS as well as FoxPro 2.6 and Visual FoxPro. I do not know if earlier versions have this command.

dbMark

 

[MiggyD]

I know this is late but...

I know when I create a shapefile or poloyline, a zero is automatically inserted in the blank numeric fields if I export the DBF. BUT, if I open that layer's database (whether "modify feature" or "identify button"), it still shows up as a blank.

I'm going to agree with tviman and say that it's the gis program.

I'm not sure but I do recall (once long ago) reading up on MS's JetEngine being revamped. This may be the cause. Try using ODBC instead of ADO (I know it's more work but...). I've c/p and conatated some of the help file (from VB4's InfoView) for your review. You should check out the highlights.

-------------------------------
When you install the Microsoft ODBC database driver, the set-up program writes a set of default values to the Windows registry in the Engines and ISAM Formats subkeys for Jet 3.0. For Jet 2.5, the initialization settings are in the VB.INI (or <APPNAME>.INI file). The following sections describe initialization and ISAM Format settings for both the 16-bit (Jet 2.5) and 32-bit (Jet 3.0) Microsoft ODBC database drivers.

Microsoft ODBC Driver Initialization Settings (Jet 3.0)

The JET\3.0\ENGINES\ODBC folder contains initialization settings for the Microsoft Jet database engine. Typical settings for the entries in this folder are shown in the following example.

win32=<pathname>\ODBC32.DLL
LoginTimeout=20
. . .
. . .
. . .
[highlight]PreparedInsert=0
PreparedUpdate=0[/highlight]
FastRequery=0


The Jet database engine uses the ODBC entries as follows:

Entry Description
. . .
. . .
. . .
[highlight]PreparedInsert[/highlight] = An indicator of whether to use a prepared INSERT statement that inserts data in all columns. Values are 0 (use a custom INSERT statement that inserts only non-Null values) and 1 (use a prepared INSERT statement). The default is 0.Using prepared INSERT statements can cause Nulls to overwrite server defaults and can cause triggers to execute on columns that weren't inserted explicitly.

[highlight]PreparedUpdate[/highlight] = An indicator of whether to use a prepared UPDATE statement that updates data in all columns. Values are 0 (use a custom UPDATE statement that sets only columns that have changed) and 1 (use a prepared UPDATE statement). The default is 0.Using prepared UPDATE statements can cause triggers to execute on unchanged columns.
-------------------------------

Hope this info helps,
--MiggyD


PS -- We're currenlty using ARC 8.3. We've received the version 9 disks, but after the headaches of upgrade from 8.2 to 8.3...we're not installing ver 9 any time soon. So, good luck with your program(s).