Recent

Author Topic: Database LAN  (Read 6582 times)

rjms1974

  • New member
  • *
  • Posts: 9
Database LAN
« on: December 02, 2017, 05:49:01 pm »
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

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Database LAN
« Reply #1 on: December 02, 2017, 05:52:12 pm »
Can sqlite handle simultaneous connections over LAN?
SQLite FAQ already answers that: https://sqlite.org/faq.html#q5
How difficult will be the implementation of a different database?
Depends on your query and schema.
What should i use (mysql, postgres...)?
Only you know, each has its own (dis)advantages.

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: Database LAN
« Reply #2 on: December 02, 2017, 06:40:22 pm »
IMHO currently Postgres SQL is the best RDBMS.
Lazarus 2.0.2 64b on Debian LXDE 10

KemBill

  • Jr. Member
  • **
  • Posts: 74
Re: Database LAN
« Reply #3 on: December 02, 2017, 07:55:18 pm »
IMHO currently Postgres SQL is the best RDBMS.
The best RDBMS is only the one you know the best :D

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

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #4 on: December 02, 2017, 09:50:56 pm »
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

  • Sr. Member
  • ****
  • Posts: 256
Re: Database LAN
« Reply #5 on: December 02, 2017, 10:41:30 pm »
Quote
i'm considering give mysql a try
My opinion is that's good choice. Small or big application, MySQL is good when you need simultaneous multiuser acces.

Quote
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?).
Yes.

Quote
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...
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.

Quote
What 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.

Quote
Do 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

  • Sr. Member
  • ****
  • Posts: 370
Re: Database LAN
« Reply #6 on: December 03, 2017, 02:11:56 am »
- 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).
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

rjms1974

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #7 on: December 03, 2017, 02:55:45 am »

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

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #8 on: December 03, 2017, 03:16:13 am »
- 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

  • Sr. Member
  • ****
  • Posts: 370
Re: Database LAN
« Reply #9 on: December 03, 2017, 03:38:55 am »
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
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

rjms1974

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #10 on: December 03, 2017, 04:42:15 am »
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

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

  • Full Member
  • ***
  • Posts: 145
Re: Database LAN
« Reply #11 on: December 03, 2017, 01:09:32 pm »
Could 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ł
« Last Edit: December 03, 2017, 01:15:41 pm by miab3 »

rjms1974

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #12 on: December 03, 2017, 03:15:22 pm »
Could 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

  • New member
  • *
  • Posts: 9
Re: Database LAN
« Reply #13 on: December 05, 2017, 01:54:24 am »
Well guys, it took me a couple of hours but i manage to replace sqlDB for ZeosLib.
Everything is working fine so far.  :D
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

  • Sr. Member
  • ****
  • Posts: 370
Re: Database LAN
« Reply #14 on: December 05, 2017, 05:50:38 am »
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.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

 

TinyPortal © 2005-2018