Lazarus
Free Pascal => Windows => Topic started by: compispezi@web.de on August 09, 2022, 03:46:46 pm
-
Hi friends. I'm a beginner. That means, these are my first steps after a pause of 15 years. I would like to use simple databasestructures without client-Serverfunction.
A very good way was to use dBase in an older Delphi 7 version.
Is here a way to work with dbase structures in Lazarus (create database / add / edit / sql...) ?
Thank you very much
-
Do yourself a favour, and forget DBase.
Use SQLite.
And FWIW: There is a Sub-Forum called "Databases"...... ..oO(now what could this be for?)
-
Hi Zvoni, in the past i used dBase in all projects.
SQLite ist a single component without server needed?
-
Hi Zvoni, in the past i used dBase in all projects.
SQLite ist a single component without server needed?
Correct. The only thing you‘d need is the sqlite-library/dll placed in the same folder as your executable
-
Lazarus has TDBF component installed by default but you have to enable it first:
Lazarus main menu > Package > Install/uninstall packages > Available for installation > type: DBFLaz 0.1.1 > Save and rebuild IDE > wait and the IDE will be restarted
TDBF is very easy to use similar to DBase, zero installation on the user computer and no *.dll file needed. This is the tutorial for TDBF:
https://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial (https://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial)
I use it in my personal note taking program and a small inventory software I wrote many years ago. They're working good, no problem so far.
But TDBF is not suitable for multi-client environment. If you need to store huge amount of data or performance is important, you should consider to use the other options:
https://wiki.lazarus.freepascal.org/Databases#Supported_databases (https://wiki.lazarus.freepascal.org/Databases#Supported_databases)
I ever roughly tested the SQLite, it is about 4 times faster than TDBF. Currently I am learning Firebird, which is similar to SQLite but it has some advantages.
https://wiki.lazarus.freepascal.org/Firebird (https://wiki.lazarus.freepascal.org/Firebird)
Firebird basically means free but if you modify the source code, you need to comply to their license. SQLite is in Public Domain license.
Also TDBF may have licensing issue, read here:
https://forum.lazarus.freepascal.org/index.php/topic,60100.msg448769.html#msg448769 (https://forum.lazarus.freepascal.org/index.php/topic,60100.msg448769.html#msg448769)
-
Hi Zvoni, in the past i used dBase in all projects.
SQLite ist a single component without server needed?
Correct. The only thing you‘d need is the sqlite-library/dll placed in the same folder as your executable
Not quite correct/complete, since Sqlite can be statically linked too if so required.
-
Yes, I had done some research about static linking SQLite. Not easy. That requires recompiling the source code to produce static library. Unfortunately, I'm not familiar with C.
-
Hi friends. I'm a beginner. That means, these are my first steps after a pause of 15 years. I would like to use simple databasestructures without client-Serverfunction.
A very good way was to use dBase in an older Delphi 7 version.
Is here a way to work with dbase structures in Lazarus (create database / add / edit / sql...) ?
I would not recommend to follow the advice of the others to use sqlite. Too much for a beginner in the Lazarus world. Maybe later, when you've succeeded with your first steps.
Use the TDbf component which is installed by default and does not require any third-party libraries. It is NOT a replacement for the BDE (which is not supported by Lazarus), but it can open dbase files, and maybe you can port most of your old Dephi7/DBase projects by using it. There is a wiki tutorial at https://wiki.freepascal.org/Lazarus_Tdbf_Tutorial to get you started.
-
TDBF use no SQL. As wp said, no replacement for the BDE. If you have used the BDE with SQL, SQlite can be a leightweigth solution. If you want to work across (Windows File)shares , the discussion is more complex.
And client/Server is not the same as SQL. The better Term is Desktop Database vs. Client Server.
-
Hi!
TDBF comes with a lot of problems.
What I did a long time ago:
Use TBDF to export the dBase tables to CSV
And then forget dBase forever
Import the CSV data to any SQL db that you like.
Winni
-
If you want to do something, anything, with a current database, you need to use SQL. SQLite makes that as painless as possible. Making an SQLite editor in Lazarus is 5 minutes work.
(Well, ok, there are NoSQL databases if you want it to be huge/scalable.)
-
Try Advantage Database - works with DBF files and has SQL. It is free for desktop applications.
https://wiki.freepascal.org/Advantage_Database_Server
-
TDBF comes with a lot of problems.
Hi Winni,
can you be a little bit more specific: Are the problems only related to index files or has TDbf general issues with reading/writing dbase files?
Best regards,
Stefan
-
TDBF should have no problem if you only use it on a local computer and connected by only one single access.
I have 2 programs using TDBF, never have any issue with it. A small stock control program, which has been running for more than 10 years written using Lazarus version 0.9x if I remember correctly. The user only contacted me once, because he purchased a laptop and wanted me to copy the program to the laptop. And the second one is my personal note-taking program, which I have been using it for more than 3 years.
-
Hmm, I thought I could share a dbf file with one of my old Delphi BDE programs which I can not replace right now.
Using TDbf seems to be working, but sometimes (e.g once a day) I get strange errors which seem to be related to a damaged index file.
Maybe I have to write a Delphi Dll now to access the dbase files.
-
Hi!
TDbf is NOT a replacement of BDE.
20 years ago there were problems with updating the index file. Hope that it is fixed now.
If your index file is damaged: Throw it away and create a new one.
Tutorial:
https://wiki.freepascal.org/Lazarus_Tdbf_Tutorial (https://wiki.freepascal.org/Lazarus_Tdbf_Tutorial)
Winni
-
Thank you, that is the information I was looking for, it does not seem to be fixed:-(
BTW: I'm aware that TDbf is not a replacement for BDE, I just wanted to use it to read/write information to one single dbase file that is also used by an BDE application. But of course corrupt index-handling does not allow that.
Already started to use a different approach now
-
Hmm, I thought I could share a dbf file with one of my old Delphi BDE programs which I can not replace right now.
Using TDbf seems to be working, but sometimes (e.g once a day) I get strange errors which seem to be related to a damaged index file.
Maybe I have to write a Delphi Dll now to access the dbase files.
In my experience, bugs most probably are in my own code, only rarely in code of others. And I believe the latter case only when it is possible to reproduce the bug by a simple stand-alone project in which everything has been reduced as much as possible.
I would not attempt to rewrite anything related to dbase - this would keep me busy for the next years.
If you really would come to the conclusion that FPC's code in TDbf is faulty but you cannot provide a simple test project for a bug report for someone of the db devs to have to look at, you could also try to replace the TDbf coming with FPC by the TDbf from its original author's site (https://sourceforge.net/p/tdbf/code/HEAD/tree/trunk/) which is still actively maintained (unlike its fork in FPC).
-
Unfortunately I can not reproduce the behaviour, it simply happens during daily work. Even my users don't recognize the problem directly, instead they will get an error from my old Delphi application minutes or hours later. Since the Delphi app is running since 2005 without these problems I'm 99.9% sure the problem is related to my new lazarus app.
Concerning the lazarus app:
* Reading the content of dbf files is no problem at all
* Writing to a record also seems to be no problem as long as I don't use the Delphi-App which relies on the index
* Interesting: Using "locate" is not possible, the DB cursor will not be set to the correct record
* Hmm, maybe I should take a closer look at this
Thanks for your tips
-
* Interesting: Using "locate" is not possible, the DB cursor will not be set to the correct record
This statement does not seem to be correct - have a look at the attached minimum sample project which works correctly.
-
How are the index files named?
-
... I just wanted to use it to read/write information to one single dbase file that is also used by an BDE application. But of course corrupt index-handling does not allow that.
Unfortunately I can not reproduce the behaviour, it simply happens during daily work. Even my users don't recognize the problem directly, instead they will get an error from my old Delphi application minutes or hours later. Since the Delphi app is running since 2005 without these problems I'm 99.9% sure the problem is related to my new lazarus app.
//...
* Writing to a record also seems to be no problem as long as I don't use the Delphi-App which relies on the index
Are you using the Delphi app and Lazarus app at the same time???
Don't do that with dBase files.
You might be lucky to only get index errors. You could also loose ALL data.
-
The index file is a mdx file named like the dbf file: elposi.dbf and elposi.mdx
Concerning the Locate:
* If I set "loPartialKey" everything works fine
* Strange: My Index field is an integer field, the index is unique: "partialkey" makes no sense here
Digging further:
* The locate only copies 4 digits of my 6 digit search-key into its internal buffer
--> Yes, that is kind o a partial key...
* TDbfViewer says: The field is "integer" with a length of 4
--> Just a wild guess: If TDbf takes 4 as the number of digits instead of using it as number of bytes this could be a reason for locate failing to find my record
I'll try to find out and give some feedback.
BTW: I'm now using TDbf from sourceforge, there's quite a lot of activity and it seems they fixed some issues related to index files
-
@rvk:
I understand it is not recommended nowadays to use dbase files on a shared network drive with multiple clients. On the other hand this has been used by thousands of programmers for more than two decades...
In the long run of course we'll replace the old Delphi app, my current struggling is an attempt to port parts of the app to lazarus/fpc
-
@rvk:
I understand it is not recommended nowadays to use dbase files on a shared network drive with multiple clients. On the other hand this has been used by thousands of programmers for more than two decades...
lazarus tdbf does not support sharing. You are corrupting your data.
In the long run of course we'll replace the old Delphi app, my current struggling is an attempt to port parts of the app to lazarus/fpc
you need to rethink your intermediate steps for porting. They need to be bigger. Maybe port the delphi dbase libraries to lazarus first which supports sharing.
-
I understand it is not recommended nowadays to use dbase files on a shared network drive with multiple clients. On the other hand this has been used by thousands of programmers for more than two decades...
Some dBase drivers and libraries build in some form of locking mechanisme (BDE does have such a form of locking scheme). But when you are mixing different dBase drivers (like BDE and Lazarus) and do simultaneous access you are guaranteed to have corruption. First in the index files (because they are more prone to these kind of corruption) but ultimately also in your actual data-file.
Stick to ONE kind of dBase driver at a time (and find out if it has some locking among different clients) or upgrade to another database engine.
(I'm not aware that TDBF has a locking scheme so don't use that with multiple clients, PERIOD)
-
I understand it is not recommended nowadays to use dbase files on a shared network drive with multiple clients. On the other hand this has been used by thousands of programmers for more than two decades...
It never was recommended, and the fact that people were usually able to get away with it is absolutely no justification.
Affordable multiuser-safe database backends started to appear circa 1990, and affordable SQL servers circa 2000. There is no excuse these days for not doing the job properly.
MarkMLl
-
Hi!
Finally you got two solutions:
* Move to SQL as I told you in post #9
* Rewrite the BDE which is not an easy job, because it is closed source of Borland/Embarcardero. Then you can continue with your beloved dBase files.
Winni
-
My Index field is an integer field [...] TDbfViewer says: The field is "integer" with a length of 4
--> Just a wild guess: If TDbf takes 4 as the number of digits instead of using it as number of bytes this could be a reason for locate failing to find my record
Did you specify the Size of the integer field as 4, based on your knowledge that this is the size of an integer? I never did this... I never specify the Size of fields with well-defined size (making it 0 internally, and TDataset can handle this automatically).
-
* TDbfViewer says: The field is "integer" with a length of 4
--> Just a wild guess: If TDbf takes 4 as the number of digits instead of using it as number of bytes this could be a reason for locate failing to find my record
Hi!
That is not just a wild guess but true:
Even for an int16 you have to declare 6 for 6 digits: -32767
dBase saves integers (and floats) as digits!!!
Set it to zero and let the system decide.
Winni
-
Hello,
thanks for all the people that tried to help with my TDbf problems.
Here's a short of my current findings for anybody also struggling with dbase files:
- My project is now working for 4 weeks without major issues (fingers crossed)
- Although it is working, I would never recommend to use dbase files in any new development
- TDbf from the fpc repositories has issues with index files, the version at sourceforge contains fixes for those
==> Use TDbf from https://sourceforge.net/p/tdbf/code/HEAD/tree/trunk/)
@WP: Thanks a lot for your tip, this was my breakthrough
- The documentation of TDbf (s. sourceforge) clearly states that "locking" should work and interoperability with BDE is also a goal of the project
- I suspect another index bug in TDbf, concerning indices on an integer column. It's too early to file a bug report but I will investigate some more time on this topic.
For those of you still wondering why I'm working with dbase files in this project:
- The original project started back in the 90's on a Novell server with DOS clients
- The software is vital for production: It manages the work of 8 different cable cutting machines and also has an interface to the ERP software and different proprietary industrial printers
- Replacing the system with another database solution would mean approx. >500 working hours, including the risk of production failure
- We are currently integrating a new machine and taking the opportunity to slowly prepare the software for a new database environment.
Bets regards
-
When I made single user single instance Delphi applications using dbase files long time ago, in app initialization I checked if last exit from application was regular, and if it wasn't I recreated indexes. Check was very simple - create file on app start, delete it on app exit, and on app start recreate indexes if file existed. That solved many dbase problems I faced and app was running well for at least 15 years.
-
When I made single user single instance Delphi applications using dbase files long time ago, in app initialization I checked if last exit from application was regular, and if it wasn't I recreated indexes.
Hard to do in a multi user environment where there are also BDE programs accessing the same database at the same time (like the case is with TS).
-
when i was doing DBF in Delphi 5 - i remember Torry Pages had VKDBF library too
all in all i ended with TDbf and even made some patches to it back then, but VK DBF was close second
additionally, back then there was a rather well-known product, Database Advantage, building an SQL layer on top of DBF files, whic his similar to what BDE LocalSQL was provided but allegedly more advanced
-
Hard to do in a multi user environment where there are also BDE programs accessing the same database at the same time (like the case is with TS).
BDE...
there can be also Mcirosoft Excel, and Microsoft Acccess and Microsoft FoxBase/FoxPro
and they support different flavours of DBF (like, excel was using DBF version 3 while ODBC FoxPro driver used DBF version 5, and those formats had different ways to specify Russian language and differnt charsets for Russian...). And probably different locking semantics too
Really, when it goes above write-copye-consume, but about continuous behavior, you'd better stick to some library... Can you just call BDE functions from Lazarus instead?
-
Finally I found a bug concerning indices on an integer column in TDbf. You can see the details, testprogram and fix here:
https://sourceforge.net/p/tdbf/bugs/109/ (https://sourceforge.net/p/tdbf/bugs/109/)
-
The TDbf version coming with FPC is a fork of the sourceforge version to which you sent your patch. If you want it to be included in FPC you must study the issue again with the FPC sources and submit your patch as a bugreport to Gitlab (see sidebar at the left of the forum).
-
Hi!
The bug in TDbf concerning the index is more than 15 years old and still not fixed.
Serious advice: Use TDbf only in readonly mode. And don't try to use it together with the BDE! Dangerous!
Use TDbf to export the data to a CSV file. And then import the data to some kind of SQL base.
Winni
-
The version from sourceforge (trunk) is working with read/write since 8 weeks in our production environment. No major issues, except that ist to slow because of the bug I have fixed now.
The FPC fork has much more issues with indices, it has corrupted my index file regularly. Unfortunately I have not the knowlede and not the time to fix this, although I would like to do that because of the different licence compared to the sourceforge repository. (s. https://forum.lazarus.freepascal.org/index.php/topic,60100.msg448775.html (https://forum.lazarus.freepascal.org/index.php/topic,60100.msg448775.html))
@Winni: As I already stated before, I have to stick with dbase files for the moment. I'm aware of the issues and I'm willing to fix them as far as it is necessary for my project.