Recent

Author Topic: The current value of AutoInc fields in a TDbf table  (Read 10233 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #15 on: October 30, 2021, 02:41:04 pm »
It looks great, korba812. I have run a few successful tests. I am going to do a full test on Monday.

I do have a question: I saw in the code that files of level $31 were recognized, although they weren't processed correctly. But it is called a "level 31 database file" (Visual FoxPro with AutoNums). $31 <> 31. But that's also what it says on the wiki. TDbf components have the properties Version and TableLevel. Do you know what that Version is used for? And to create those DBF files, should I specify a TableLevel of 31 or 49?

I'll fix the wiki when we're happy with it.

korba812

  • Sr. Member
  • ****
  • Posts: 391
Re: The current value of AutoInc fields in a TDbf table
« Reply #16 on: October 30, 2021, 03:27:41 pm »
Thanks for dbf file. It looks like my patch is correct.
The TableLevel property can only take values as specified in the table on the wiki page (3,4,7,25,30). Version number in the dbf header is determined based on TableLevel value and type of used fields. In this case you should use LableLevel = 30 and if you use autoinc then $31 will be written to dbf header. I know, It's a little bit confused.

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #17 on: November 01, 2021, 03:38:27 pm »
I have tested it extensively. It works. But I have a question.

How do you get the current value of the AutoInc field after you append a record? I do it like this:

Code: Pascal  [Select][+][-]
  1.       MyTable.Append;
  2.       MyTable.Edit;
  3.       MyTable.Post;
  4.       MyTable.Edit;
  5.       Autonum := MyTable.FieldByName('custnr').AsInteger;

but there's probably a simpler way.

korba812

  • Sr. Member
  • ****
  • Posts: 391
Re: The current value of AutoInc fields in a TDbf table
« Reply #18 on: November 01, 2021, 04:52:27 pm »
This is the best and surest way to get value of an autoinc field. However, if you do not intend to share the dbf file between applications (only one application uses the dbf file exclusively) then autoinc value can be obtained from DbfFieldDefs property of TDbf component:
Code: Pascal  [Select][+][-]
  1. Autonum := MyTable.DbfFieldDefs.Items[0].AutoInc;
  2.  
But if you use shared dbf files, in this case you have no guarantee that the value of the autoinc field has not been changed by another application.

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #19 on: November 02, 2021, 12:17:11 pm »
Ah, that is what I wanted to know. Thanks, korba812!

I have added it to the bugtracker.

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #20 on: November 02, 2021, 01:00:48 pm »
Another question: when looking at the wiki to see what has to be changed, I noticed that it states that Visual dBase VII, Foxpto and Visual FoxPro all support AutoInc fields. Yes to dBase VII, but I couldn't find if FoxPro supports them as well. And only Visual Foxpro supports a StepSize. I found this file format specification. but it doesn't cover dBase VII.

It is in the code:

Code: Pascal  [Select][+][-]
  1. // OH 2000-11-15 dBase7 support. Header Update (add fields like Next AutoInc Value)

But I'm not sure if it would work for the other two. I expect only partially, but it is not easy to test, as XBase++ doesn't support all those formats. I found the following table:

Code: [Select]
Mapping of FoxPro field types in the Xbase++ DDL

  Description           Field type  Length   Field type  Valtype()

  FoxPro                                     Xbase++

  Double                B           8        F           N
  Character (text)  *)  C           1-254    C           C
  Character (binary)    C           1-254    X           C
  Date                  D           8        D           D

  Float                 F           1-20     N           N
  Generic               G           4        O           M
  Long signed integer   I           4        I           N
  Logical               L           1        L           L
  Memo (text)       *)  M           4 or 10  M           M
  Memo (binary)         M           4 or 10  V           M
  Numeric               N           1-20     N           N

  Time stamp            T           8        T           C
  Currency              Y           8        Y           N
  VarChar *)            V           1-254    R           C     Don't use !
  VarBinary             Q           1-254    Z           C     Don't use !
  Integer (autoinc)     I           4        S           N

 *) Data is converted according to SET CHARSET

And there are some other things I'll have fix on the wiki, like how to use the table levels and the "field length limit" (which is actually the "field name length limit").

korba812

  • Sr. Member
  • ****
  • Posts: 391
Re: The current value of AutoInc fields in a TDbf table
« Reply #21 on: November 03, 2021, 03:31:18 pm »
I couldn't find any information about autoinc fields on FoxPro. All information available is for VisualFoxPro so I guess FoxPro doesn't support autoinc fields.
Moreover, TDbf component with TableLevel = 25 and autoinc fields produces invalid dbf file - header version is $30 (VFP without autoinc) and autoinc field is '+' instead of 'I'. Interestingly, such a file cannot be opened in TDbf. So TDbf definitely doesn't support AutoInc at TableLevel = 25.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: The current value of AutoInc fields in a TDbf table
« Reply #22 on: November 03, 2021, 03:43:40 pm »
Have you thought about doing it "sqlite-style"?
IIRC, the moment you declare a field in a sqlite-table to be autoincrement, sqlite creates (or appends to) a table called "sqlite_sequence", keeping track of the highest value of each AutoInc-Field of each Table (remember: a table can have more than one AutoInc-Field).
https://www.sqlite.org/autoinc.html
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #23 on: November 03, 2021, 05:29:50 pm »
Interesting approach, Zvoni. I'll keep it in mind if I need something like that.

But we're mostly trying to make the TDbf component more compatible with the existing DBF formats.

funlw65

  • Full Member
  • ***
  • Posts: 148
    • Visual Pin Configurator for Nucleo 64pin boards
FreePascal 3.2.2, C 10.2.1, D 1.24 under Linux(init,musl,glibc), DragonflyBSD, NetBSD
gui: gtk2, qt5, raylib4.x+raygui3.x, nanovg 
tui: freevision, tvision2, termbox2+widgets, finalcut
db: typhoon-1.11...

korba812

  • Sr. Member
  • ****
  • Posts: 391
Re: The current value of AutoInc fields in a TDbf table
« Reply #25 on: November 04, 2021, 02:38:12 pm »
Not only. Also dBase III, dBase IV, FoxPro, VisualFoxPro, Clipper, etc... Each of them is slightly different.

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: The current value of AutoInc fields in a TDbf table
« Reply #26 on: November 04, 2021, 06:19:38 pm »
Not to put too fine a point on it, but in dBase formats autoinc fields should be regenerated on import, which means you can never rely on the autoinc field value between different applications. The autoinc field is stored as an integer value, but afaik the last increment value is NOT stored.
I worked on PerfectView, a then well known dBase format database system in the Netherlands many moons ago. The behavior between dialects is always the same: on export/import you will loose the exact sync between the tables (the autoinc value itself should be considered opaque, on import autoinc will be rest,i.e. implementation detail, not for the user). dBase and family were never designed to be truly multi-user. It is a single user database, with some later bolt-on's to make it work somewhat multi-user. Apart from single user applications I would run away from that format and use something more modern.

To summarize: if you use ANY dBase format, don't rely on the exact value of the autoinc field for queries in a multi-user environment and local tables.
In the 2020's dBase and the likes should be considered an archaeological find.
« Last Edit: November 04, 2021, 06:36:11 pm by Thaddy »
Specialize a type, not a var.

korba812

  • Sr. Member
  • ****
  • Posts: 391
Re: The current value of AutoInc fields in a TDbf table
« Reply #27 on: November 04, 2021, 07:31:09 pm »
Typically, multi-user access is accomplished via file locks and has been used extensively in Clipper applications. It worked quite well (especially with NetWare or SMB1) and still works fine if applications are running on the same machine, e.g. Terminal Server.
You're right - there is no point in using dbf files in large, multi-user applications today. But dbf is still widely used for data interchange.

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: The current value of AutoInc fields in a TDbf table
« Reply #28 on: November 04, 2021, 09:48:55 pm »
Clipper applications
Yes. But that works indeed the same as I described. Once exported/imported there is NO sync between autoinc fields on other Applications using the same tables.
Specialize a type, not a var.

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: The current value of AutoInc fields in a TDbf table
« Reply #29 on: November 05, 2021, 09:11:59 am »
The first version of the application that is using these tables is more than 20 years old. Gradually stuff got added. There is work on a webapp. That started quite some time ago, but 4 years ago in earnest. Unfortunately, it is far easier for one man to add something to that old app, than for a whole SCRUM team to add that same change to a webapp. Feature parity is still some way off. Then again, of course the webapp does things the old one cannot, like a shared database between different locations and seamless integration of other online services. And those two things become increasingly important.

And of course, far more developers have a job building that webapp :)

The power of FPC/Lazarus is that you can build something that works very fast, especially database applications. The problem is that people who programmed in Delphi get scarce and it is seen as something old and depreciated. It's better to use the development tools and environment everyone knows than lobby for the use of a better one they don't. And it has to run in a browser, of course. Distributing and installing applications automatically never got off the ground.

So, in time the old, file based databases will slowly disappear. But in the mean time, it's much easier to roll out applications if you don't need to install an SQL server at each location. And as webplatforms assume a single connection to the database with admin access, you are required to build a backend as well. Which requires rolling your own (AJAX) communications, etc. It's far more work.

 

TinyPortal © 2005-2018