?
Solved

Invalid Field Length in dBase III file?

Posted on 2005-03-17
11
Medium Priority
?
662 Views
Last Modified: 2013-11-24
Hello,

I am parsing what I believe is a "dBASE III, index present" file using Java.  At least that is what DBF Viewer tells me it is.

As I read in the field descriptors, the first 15 or so read in normally, parsing the field name, type, and length.  But suddenly when I come to a field of type character named "Notes" that DBF Viewer is telling me should be length 250, I am reading length "-6" .

Does anyone have an idea what this might be?  As I said, all the other fields to that point seem to be normal.

Thanks in advance!

John
0
Comment
Question by:2djohn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:2djohn
ID: 13570720
BTW, if this question belongs in a more appropriate area of the Database forums, please let me know.  I haven't used this area of EE much.  Thanks.
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 450 total points
ID: 13570867
The maximum number of characters per field is 254.  So, those numbers surely aren't corresponding with this limit.

Since it says "Notes", I'm guessing that field may be a memo field which has yet another file where the actual "Notes" data resides.  If it is true dBASE, a file name with the extension .dbt should be hanging around.  If it's really FoxPro data, a similar file name with the .fpt extension should be hanging around.  If a memo file is missing and a .dbf is referencing it, you could easily get odd results.
0
 

Author Comment

by:2djohn
ID: 13571055
Hi Carl,

Thanks for your comment.  2 things though:

1) as I mentioned, the field length is 250, under the 254 limit
2) there is a .fpt file in the same dir

The data is showing up correctly in the DBF Viewer software, notes and all.  The data type byte is saying it's char data- would it be very different if it was a memo?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 33

Assisted Solution

by:CarlWarner
CarlWarner earned 450 total points
ID: 13571088
Well, now we know it's actually FoxPro data and not pure dBASE.  While they used to be very compatible in the old days, they are not very compatible any more.

If "Notes" shows as Char data, that's fine.  But, if you have an .fpt file, that says there must be a memo field referenced somewhere in the .dbf file.  If you see another field that returns a Memo type, then "Notes" may not be of Memo type.  I sure can't tell from here what it really is.  But, as I initially posted, memo fields can give odd results in these so-called viewers.  The environment in which they were created probably reads that data without issue, as well as showing the true data types.  A memo field can hold any kind of data, ASCII or binary, and is not limited to just character data, but can be just character data if that was the intent of what the system was supposed to hold.

Here is a reference that lays out the bytes of the header (ignore the article title):

ERR: "Not a Database File"; How to Modify Database Header
http://support.microsoft.com/default.aspx?scid=kb;en-us;98743
0
 

Author Comment

by:2djohn
ID: 13571205
Carl,

The strange thing is that it is the field descriptor that is giving the strange results?  I could see if the data was bogus, but strange that the descriptor would have a negative number.

Thanks,

John
0
 
LVL 33

Assisted Solution

by:CarlWarner
CarlWarner earned 450 total points
ID: 13571420
If the data were a memo field, you wouldn't see the data if the field descriptor didn't somehow know it was a memo field and then extract the data out of the appropriate location in the .fpt file.  So, if you actually see data in the .dbf, the field type shouldn't be of the memo type.  But, I don't know how that viewer you have displays memo data.  Again, if you have an .fpt file, there should be one field in that table/.dbf that actually is a Memo field and that is what the viewer should return if the viewer is working properly.

But, I can't figure that out from here. <s>
0
 
LVL 22

Assisted Solution

by:JesterToo
JesterToo earned 300 total points
ID: 13571871
If the fpt file goes with this dbf they will both have the same base filename... i.e.  abc.dbf and abc.fpt

This may be a problem with the JDBC driver not supporting xbase memo files... there have been several "flavors" of them over the past 20 years and they weren't very compatible with each other.  Which JDBC driver are you using?  Also, what "dbf viewer" are you using?  (Extended Systems has/had one available for free that worked with their Advantage Database Server product that, I believe, can work with stand-alone files when used in "local server" mode... I'm failry sure this version handles Clipper/Foxpro(2.6) style FPT files).

The field designator for a memo field in all the xbase flavors I've used has been "M".  The memo field in each row of the dbf stores a short binary value that is the offset into the fpt file where the text of the memo begins... any given memo not all be contiguous so there can be another pointer in this data that points to the next chunk... and so on and so forth.  Some versions of memo files also stored "back pointers" so all the blocks were chained backwards and forwards.  This gave a bit more stability to these versions of memo files.  Many developers avoided using memo files... they were notorious for becoming corrupted and losing all the content.  The double pointers made them slightly less prone to breaking.

HTH,
Lynn
0
 
LVL 22

Assisted Solution

by:JesterToo
JesterToo earned 300 total points
ID: 13571932
There are free utilities available that can examine your dbf's and report the data structure if you need...

Go to this site:  http://www.the-oasis.net/

and navigate the following links...
   Source Code Archive
   The Reef FTP Site
   Clipper Code

and scroll down the list of files.  Look for data_doc.zip (158kb) and dbinfo.zip (12kb).  I've used these years ago until I wrote my own version with a few more features... but they should be adequate to interrogate your dbf(s).  Since this is downloaded code, I'd run these programs against copies of the dbf(s) just to be safe(er).
0
 

Author Comment

by:2djohn
ID: 13573965
Thanks for all of your input everyone.  Here is more information:

1) I have to read the dbf data after uploading it from a web page, so there is no JDBC involved- I am reading in a byte array.  My code works on other .dbf files, just not this one

2) I have downloaded a few different DBF viewers, and they all show the field in questions as type "C", size 250, Precision "0", and offset "402".

3) To confirm that this is not a memo field, I tried deleting the associated .fpt file, and I was still able to view the file in the different viewers.  So maybe there was a memo field with this database once, but not now?

Thanks,

John
0
 

Author Comment

by:2djohn
ID: 13574052
By the way I would be happy to e-mail anyone the files in question if any experts think it would help.

I guess the main thing that confuses me is how all the different DBF viewers are able to read the field and database correctly, but for some reason my parsing code is picking that one field up as size -6...
0
 

Author Comment

by:2djohn
ID: 13574800
Experts- I have solved the problem- it was as simple as reading in an unsigned byte rather than a signed one.  I apologise for taking your time, and appreciate your feedback.  I will split the points up.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question