Welcome, Guest
Username: Password: Remember me
This public forum is meant for questions and discussions about Visual FoxPro
  • Page:
  • 1

TOPIC:

Can't open DBF table 02 Dec 2020 13:44 #16849

  • dexter
  • dexter's Avatar
  • Topic Author


  • Posts: 11
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 13:50 #16850

    • Chris
    • Chris's Avatar


  • Posts: 3751
  • 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.
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 13:56 #16851

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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
    Attachments:

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 14:29 #16853

    • Chris
    • Chris's Avatar


  • Posts: 3751
  • 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?
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 14:40 #16855

    • robert
    • robert's Avatar


  • Posts: 3289
  • 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:
    REPLACE dayscmpl WITH 999
    ? dayscmpl
    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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Last edit: by robert.

    Can't open DBF table 02 Dec 2020 15:07 #16857

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 16:09 #16861

    • robert
    • robert's Avatar


  • Posts: 3289
  • 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:
    BROWSE FIELDS dayscmpl

    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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 18:16 #16863

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 18:39 #16864

    • Chris
    • Chris's Avatar


  • Posts: 3751
  • 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.
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 19:12 #16865

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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.

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 20:08 #16866

    • Chris
    • Chris's Avatar


  • Posts: 3751
  • 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?
    XSharp Development Team
    chris(at)xsharp.eu

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 20:32 #16867

    • robert
    • robert's Avatar


  • Posts: 3289
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 02 Dec 2020 22:39 #16868

    • kevclark64
    • kevclark64's Avatar


  • Posts: 123
  • 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.

    Please Log in or Create an account to join the conversation.

    Last edit: by kevclark64.

    Can't open DBF table 03 Dec 2020 00:51 #16870

    • Eric Selje
    • Eric Selje's Avatar


  • Posts: 31
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 03 Dec 2020 11:10 #16872

    • pluisje
    • pluisje's Avatar


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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 03 Dec 2020 13:14 #16874

    • FoxProMatt
    • FoxProMatt's Avatar



    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.

    Please Log in or Create an account to join the conversation.

    Last edit: by FoxProMatt.

    Can't open DBF table 03 Dec 2020 14:04 #16875

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 03 Dec 2020 14:49 #16876

    • robert
    • robert's Avatar


  • Posts: 3289
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Last edit: by robert.

    Can't open DBF table 03 Dec 2020 18:32 #16879

    • dexter
    • dexter's Avatar
    • Topic Author


  • Posts: 11
  • 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

    Please Log in or Create an account to join the conversation.

    Can't open DBF table 03 Dec 2020 19:09 #16880

    • robert
    • robert's Avatar


  • Posts: 3289
  • Dimitar,
    I have to correct myself. Str() will generate the "****". Writing to the DBF will generate a runtime error: Data width error.
    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1