Database LAN lazarus / 16-Databases ---------------------------------------------------------------------- rjms1974 · 2017-12-02 16:49 Hi guys. I developed a database desktop app using sqlite to a friend and it's working perfectly fine. Now he needs a second pc to access the data. I'm clueless... It's a pretty simple database with only 3 tables. Can sqlite handle simultaneous connections over LAN? How difficult will be the implementation of a different database? What should i use (mysql, postgres...)? Thanks. ---------------------------------------------------------------------- Leledumbo · 2017-12-02 16:52 Quote from: rjms1974 on December 02, 2017, 05:49:01 pmCan sqlite handle simultaneous connections over LAN? SQLite FAQ already answers that: https://sqlite.org/faq.html#q5 Quote from: rjms1974 on December 02, 2017, 05:49:01 pmHow difficult will be the implementation of a different database? Depends on your query and schema. Quote from: rjms1974 on December 02, 2017, 05:49:01 pmWhat should i use (mysql, postgres...)? Only you know, each has its own (dis)advantages. ---------------------------------------------------------------------- tudi_x · 2017-12-02 17:40 IMHO currently Postgres SQL is the best RDBMS. ---------------------------------------------------------------------- KemBill · 2017-12-02 18:55 Quote from: tudi_x on December 02, 2017, 06:40:22 pmIMHO currently Postgres SQL is the best RDBMS. The best RDBMS is only the one you know the best To my mind, Postgres is great, no doubt about it, but it can be difficult to understand (tablespaces, page size, and so on) for only 3 tables. mysql is ACID compliant (almost) if you use innodb storage engine. the main idea is KISS = keep it simple stupid ---------------------------------------------------------------------- rjms1974 · 2017-12-02 20:50 Well, i'm considering give mysql a try... Just to be clear, this is my current situation: - i need to install 2 copies of my program in 2 laptops. - my program consists in a "clients\sessions organizer", so the 2 copies needs to access the same DB and read\write data at the same time. - i believe one of the laptops have to be the DB server (i'm kind lost here, never did it before), and on the other laptop i have to connect the DB through LAN (is it correct?). - i have it running fine using sqlite, but i'm afraid about how difficult and time consuming will be the code implementation once i have to migrate from sqlite to mysql... Do you think that mysql is a good solution for my problem? What mysql version should i use? Do Lazarus have native support? any sugestions will be appreciated. Once again, thank you all. ---------------------------------------------------------------------- mirce.vladimirov · 2017-12-02 21:41 Quotei'm considering give mysql a tryMy opinion is that's good choice. Small or big application, MySQL is good when you need simultaneous multiuser acces. Quotei believe one of the laptops have to be the DB server (i'm kind lost here, never did it before), and on the other laptop i have to connect the DB through LAN (is it correct?).Yes. Quotei have it running fine using sqlite, but i'm afraid about how difficult and time consuming will be the code implementation once i have to migrate from sqlite to mysql...If you use Lazarus's sqlDB then you can use same SQL queries. Maybe small changes will be needed in SQL queries. Very small changes. I say maybe. Perhaps. More probably is that you wont need any changes in your queries. QuoteWhat mysql version should i use?MySQL 5.0 and above is good. I use 5.0 and it's good.  As always, Higher version is better I guess. QuoteDo Lazarus have native support? I dont uderstand what do you mean ? Lazarus does have it's own DB library named sqldb and I gues you was using that to write applications that access SQLite. Only choose different connection type and thats it. ---------------------------------------------------------------------- GAN · 2017-12-03 01:11 Quote from: rjms1974 on December 02, 2017, 09:50:56 pm- my program consists in a "clients\sessions organizer", so the 2 copies needs to access the same DB and read\write data at the same time. If same time means to write at the same millisecond, then don't use SQLite, otherwise, you can still using SQLite, because SQLite block the database when you update/insert/delete (write). ---------------------------------------------------------------------- rjms1974 · 2017-12-03 01:55 Quote from: mirce.vladimirov on December 02, 2017, 10:41:30 pm I dont uderstand what do you mean ? Lazarus does have it's own DB library named sqldb and I gues you was using that to write applications that access SQLite. Only choose different connection type and thats it. I mean not have to install any additional package, like Zeoslib... I feel way better after read your answer. Thanks a lot man. ---------------------------------------------------------------------- rjms1974 · 2017-12-03 02:16 Quote from: GAN on December 03, 2017, 02:11:56 amQuote from: rjms1974 on December 02, 2017, 09:50:56 pm- my program consists in a "clients\sessions organizer", so the 2 copies needs to access the same DB and read\write data at the same time. If same time means to write at the same millisecond, then don't use SQLite, otherwise, you can still using SQLite, because SQLite block the database when you update/insert/delete (write). Not necessarily at the same millisecond... But after a few tests of 2 instances of the app, both connected to the same sqlite db, when i saved the data to db, the other instance lost connection. To be more precise, i had 2 apps running, i was at the same form on both apps, datafields connected and showing data on both apps. At the time i saved a new record on app1, datafields on app2 lost connection. I have to close an reopen the form to see the data again... So, i don't think sqlite is the best choice to this project. Thanks for you answer. ---------------------------------------------------------------------- GAN · 2017-12-03 02:38 I don't have this problem, may be because I use ZeosLib (with autocommit connection). I have working a program in a office with 5 desktops PC over a LAN, the SQLite DB on a server and all works fine. Of course, with other DBMS (Client/Server) you have a full control of all situation. Best regards, GAN ---------------------------------------------------------------------- rjms1974 · 2017-12-03 03:42 Quote from: GAN on December 03, 2017, 03:38:55 amI don't have this problem, may be because I use ZeosLib (with autocommit connection). I have working a program in a office with 5 desktops PC over a LAN, the SQLite DB on a server and all works fine. Of course, with other DBMS (Client/Server) you have a full control of all situation. Best regards, GAN Wow GAN! I just installed ZeosLib and runned the same tests, it looks like ZeosLib can handle multiple connections on a sqlite db better than SQLdb. Now i'm able to keep 2 instances of my app without being disconnected after a insert or an update. Looks like i will go with ZeosLib and i will keep sqlite as my db... Could you please explain to me why ZeosLib don't need a transaction component like TSQLTransaction? Thanks a lot man. ---------------------------------------------------------------------- miab3 · 2017-12-03 12:09 Quote from: rjms1974 on December 03, 2017, 04:42:15 amCould you please explain to me why ZeosLib don't need a transaction component like TSQLTransaction? Because ZEOS works by default in the AutoCommit mode. You can also work with ZEOS (if you need) using: ZConnection1.StartTransaction; ZConnection1.Rollback/Commit; Michał ---------------------------------------------------------------------- rjms1974 · 2017-12-03 14:15 Quote from: miab3 on December 03, 2017, 01:09:32 pmQuote from: rjms1974 on December 03, 2017, 04:42:15 amCould you please explain to me why ZeosLib don't need a transaction component like TSQLTransaction? Because ZEOS works by default in the AutoCommit mode. You can also work with ZEOS (if you need) using: ZConnection1.StartTransaction; ZConnection1.Rollback/Commit; Michał Thanks Michał. ---------------------------------------------------------------------- rjms1974 · 2017-12-05 00:54 Well guys, it took me a couple of hours but i manage to replace sqlDB for ZeosLib. Everything is working fine so far.  I have just one last question. Looks like Zeos do some trick and i don't need to keep the sqlite aditional files (sqlite3.dll and sqlite3.def) on my app folder. Is it correct? ---------------------------------------------------------------------- GAN · 2017-12-05 04:50 When you distribute your program, include the Sqlite3.dll file in the same folder of your program, that's all you need. And in library location in Zeos Connection, leave it blank. ---------------------------------------------------------------------- rjms1974 · 2017-12-05 13:01 Quote from: GAN on December 05, 2017, 05:50:38 amWhen you distribute your program, include the Sqlite3.dll file in the same folder of your program, that's all you need. And in library location in Zeos Connection, leave it blank. Thanks a lot, GAN.