TOPIC:

I'm trying to open 1GB DBF table with 255 fields on it. X# is complaining and exception is thrown with following message:
"Exception of type 'XSharp.RDD.RddError' was thrown."

The actual description is : "Field 'DAYSCMPL' is not valid"

The field in this table is N(3,2) with index on it. There are no rows with **** on it and the table is opened without issues through VFP.

Any thoughts?
Dimitar

Hi Dimitar,

Please create a copy of the file and then delete all its records except just a couple of them. Does the error still happen? If yes, can you please post it here or send it directly to us to have a look? If not, it means there's something specific about the original data, in which case would it be possible to send the complete file? Of course it will be handled with privacy if it contains sensitive data.

Hey Chris,

I grabbed just first 10 records into a separate table with same struct. Got same error I send this example to you.

Thanks
Dimitar

Hi Dimitar,

Thanks, I see where the problem is, it's because this numeric field has a length of 2 for decimals, with a total length of 3, while a total length of at least 4 is expected (decimals + 2).

So I guess VFP allows you to create such fields with no issue? What kind of values can you store in this field, I assume a field value of 1,23 cannot be stored correctly, or can it?

Chris,

Foxpro allows this and then only stores the decimal point and 2 decimals. So the allowed values are between .01 and .99.

I opened the table and executed:
This shows that the value 999 was written.
A value > 999 throws a "Numeric Overflow" exception:

Btw I do not think we should support it in the same way.

Robert

Hi Robert,

Its a tricky on I admit. For VFP decimals are not strict. The fact that you have N(3,2) doesn't mean each number must have 2 decimals. In this case you can safely add 100 or 999 into the field. Of course attempting to put 1000 should end up with exception (in contrast with what VFP is doing).

I'm afraid there might be a lot of cases coming from VFP which does not respect the decimals the way you think they should do.

Kind Regards
Dimitar

Dimitar

It is not the way "we" think, but the way it has been handled by all other Xbase dialects (Xbase++, Clipper, Visual Objects, Advantage, Six driver etc).
FoxPro is the only DBF implementation that allows a value of 999 to be written to a N 3,2 field.
I do not think it was intentional.

Open the DBF in FoxPro and type:
and then try to enter the value 999 in a blank field: it won't work.
I think this is not a feature, but a bug.

Robert

I agree and I disagree.

Here is the DBF specs for Advantage server - devzone.advantagedatabase.com/dz/webhelp...d_specifications.htm

If you look at that file, you will find standard and non-standard implementations as well as VFP-specifics etc. And I know this is probably a huge pain to deal with all but if X# claims it works with one or another dialect, why should VFP dialect suffer from compatibility issues just because the DBF created with VFP are not compatible with DBFs created with Advantage for example (or vice versa)?

If I'm using VFP dialect I don't expect to be able to open DBFs created with Advantage if they contain advantage-specific dbf schema or field types. But I expect the dbf to be fully compatible, non-breakable if being used with X#. Not really expecting you try to fix some "bugs" within the schema.

Speaking about compatibilities - is NULL an option and recognizable? Its probably not for all dialects but if its not standard does it mean X# would throw an exception if trying to open a table with nullable field type? Or how do you cope with the memo field length since memo in VFP is 4 bytes while as it seems the standard is 10?

Dimitar

Hi Dimitar,

In the specifications you posted, it is clearly stated that for field type "Numeric", the size of decimals can be "0 to Length-2"

So there should not exist a Numeric ("N") field with total length 3 and length for decimals 2.
The other field types that are specific to VFP should be already supported in X#. If there is some that it is not, it's either a bug or work in progress.

Yes I saw that

But here is the case - all VFP community is used with this "bug" as its always being like that in Microsoft's Visual FoxPro. So you can imagine how many "validations" and how many tables exists out there with this bug utilized as a feature. This is quite of a problem if you ask me. Its not only about N(3,2) but also for N(5,2). You are free to enter values like 10000 in such field. For VFP this is legal value and I suppose for X# + VFP dialect it should be the same.

Dimitar.

Hi Dimitar,

I agree, if it is a widely used "feature", then we need to look into it. Can some more VFP develoeprs step in here please, so we can hear some more opinions?

Dimitar,

We do support Nullable fields in the DBFVFP driver (our VFP compatible DBF driver) as well as the other VFP types such as Integer (with autoincrement), DateTime, VarBinary etc.
And it is not impossible to implement this strange behavior for numeric columns as well.
But I would really like to hear from other VFP developers if they think we should support this.

Robert

I've worked in VFP for decades and have never relied on this odd behavior regarding overflows for anything and I can't imagine a situation in which I would. Of course, that doesn't mean no situation exists in which someone does rely on it.

VFP is not consistent in handling overflows because if you use REPLACE to try to put 10000 in a N(3,2) field you will get a numeric overflow error but if you use UPDATE or INSERT you won't. That doesn't make any sense to me but perfect compatibility would require duplicating that behavior as well I guess (assuming inline SQL is supported at some point).

So it comes back to the overall question of exactly how much should X# recreate things which arguably don't make sense. I don't have an answer for that.

I've never had or seen a table that needed a N(3,2) field, and I would never think to rely on this "bug" in production.

Eric

I second that.
Never saw it and i'll never use it in this way.

About 5 years ago when upsizing my data from DBF to Sql Server, I discovered that I had a few records here-and-there in a few tables where this situation existed, but it was not intentional that I had allowed for it in my code base; it just happened somehow.

I believe my fix was to increase the field width in Sql Server on the left side of the decimal so I would not lose data values.

So, my point is that people may have this situation in some DBF's and not even know about it, nor was it intentional, as that's what happened to me.

The following sentence is copied from VFP help regarding Numeric field types:

"In fields with Numeric data type, the length of the decimal portion is determined at design time when you create the field. The length of the decimal portion is part of the total length. For example, if you specify a length of 6 for a numeric field and a decimal setting of 4, the field can store values up to 9.9999."

This clearly states what has been found is clearly a bug. The problem is what do you do when you find such case in X#? Why the DBF is considered corrupt and what would happen if you have N(5,2) having a value that does not exactly match the structure (i.e 10000)?

Dimitar

Dimitar,

The DBF is considered "corrupt" for the N 3,2 field because our driver validates the DBF header and sees this as an invalid field definition.
If you change the field definition to N 4,2 and assign large values (> 9.99) in VFP and then open the DBF inside X# then the value will be read as you would expect.
When you write a value > 9.99 in X# then we will format the number with STR(newValue,4,2) and this will generate a string "****"

Robert

Thanks Robert,

That make sense. Its not unmanageable our side for sure (I mean determining fields with bad length) and update accordingly.

I'm surprised you keep entering **** for overflowed values. I know its STR who produces it but this cost more troubles than benefits. Do you consider throwing exception in this scenario?

Dimitar

Dimitar,
I have to correct myself. Str() will generate the "****". Writing to the DBF will generate a runtime error: Data width error.
Robert