🚀 go-pugleaf

On Wed, 22 Jul 1998 15:34:49 GMT, ncbuyukmihci@ucdavis.edu (Nedim
Buyukmihci) wrote:

>On Tue, 21 Jul 1998 17:57:23 GMT, koterski@borland.com (Steve
>Koterski) wrote:
>
>Steve,
>
>Thanks for your quick and detailed response!
>
>The field is of Char type and is 5 characters in size and the fields
>are filled completely so justification should not be an issue. I had
>already tried all your suggestions except trimming, all with the same
>discrepancy problem.
>
>But, neither should trim be an issue, should it? Yet, when I tried
>your suggestion for trimming the field, it worked fine. Can you
>explain that? I think this is rather a serious problem, don't you?

A serious problem with what? dBASE? Your table file?

Try your same SQL against another table or, better yet, one of the sample
tables (I believe one of them has a CAHR type zip code field). My bet is
that the SQL statement works, without the TRIM function. This would tend to
indicate that there may be a problem in your table file.

dBASE tables are essentially SDF files. Beyond the table file header, data
is stored in fixed-length ASCII and padded with spaces (ASCII decimal 32)
where data values are shorter than the field length. If you look at a table
file in a hexidecimal file viewer, you should see nothing but data and
spaces. If you see any other characters -- formatting characters or
characters with an ASCII decimal value below 32 or greater than 127 --
suspect them as the culprit. You should especially never see a NULL
character (ASCII decimal 0) embedded between field values in the table
file. NULL characters can often be negated with the dBASE or SQL TRIM
functions, giving the (incorrect) impression it might be an extra space
character instead of an offending NULL character.

What would be really great is if you could identify one (or a couple) of
the 344 records your original SQL statement is having a problem with. Given
this knowledge, you could look at those records in specific while
inspecting the table file in a hexidecimal viewer and potentially see the
source of the problem.

Try copying the contents of your table out to an ASCII file,
comma-delimited or SDF. How many records get copied out? All 543 or just
199? Is the zip code field for any of those copied records blank? Are any
of the zip code field values longer than five (including invisible
characters)?

If all of your data is successfully copied to the text file, create a new
table file using the same structure as the original and copy the data into
it from the ASCII file. Does *this* copy of your data successfully work
with your original SQL statement? If so, you will have eradicated some data
corruption that was interfering.

Another thing you can try is a REPLACE ALL, replacing the field's data with
the current contents of the field using the dBASE TRIM function. (I suggest
only doing this on a back-up copy of the original data file, for safety.)
You could also do this with an SQL statement:

  UPDATE "YourTable.dbf"
  SET Zip = TRIM(BOTH " " FROM Zip)

You might also consider obtaining one of a number of data corruption
utilities available.

I have concentrated on the table itself being the problem. Why? Because
this problem does not manifest itself elsewhere. Local SQL is a facility of
the Borland Database Engine (BDE). Visual dBASE, Delphi, and C++Builder all
use the BDE as the default data engine for local tables. If there were some
fundamental problem in the BDE that resulted in this sort of situation, we
would be seeing people complaining of it. Instead, programmer's (myself
included) are successfully using the very basic sort of SQL your original
statement represents. This would tend to eliminate the BDE as the sole or
primary cause.

Unlike other BDE-using programming tools, though, Visual dBASE does perform
some amount of sleight-of-hand with the SQL. So there is still a slight
chance that the problem is either caused by or (more likely) exacerbated by
the interactions of Visual dBASE with the BDE SQL operation. However, I
still feel the most likely culprit is the table file itself.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "Knowledge comes, but wisdom lingers."
Technical Publications                -- Alfred, Lord Tennyson (1809-1914)
INPRISE Corporation
http://www.inprise.com/delphi