Recent

Author Topic: Is there an all-in-one component for database connectivity?  (Read 2098 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 683
Hi

I suspect the answer to this will be "no" but I wanted to make sure I am not missing it.

I have an existing program that works with SQLite, and aside from that I have zero experience of database connectivity. However, I want to scale the program somewhat so that users can either use SQLite in standalone mode, or connect to a network based database server, but I don't want to limit it to just one specific kind of database e.g. MySQL only, or Firebird only. I need the program to ask the user for username, password and server name\IP, and for my program to simply connect to whatever database lives there no matter whether it is Firebird, MySQL, MS SQL, Oracle, etc. From what I can gather, I need to add components for all the various databases from the Lazarus Database tab and configure them all separately. Is that right? Or is there an all-in-one component for database management?
Lazarus 2.0.2 and fpc 3.0.4 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Majove
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

mangakissa

  • Hero Member
  • *****
  • Posts: 944
Re: Is there an all-in-one component for database connectivity?
« Reply #1 on: June 26, 2019, 11:12:41 am »
There's no really an one to one component. SQLdb can connect to several databases. But as developer you need to configure each database, because each database has his own client library to connect.
It's only the connection you have to manage. TSQLTransaction and TSQLQuery works with all databases.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

Zvoni

  • Sr. Member
  • ****
  • Posts: 267
Re: Is there an all-in-one component for database connectivity?
« Reply #2 on: June 26, 2019, 11:23:59 am »
1) For the Connection there is TSQLConnector, which is kind of generic, but you have to declare against which DBMS you try to connect.
Oh, and you have to include the units for the DBMS, e.g. mysql57conn etc.
2) Next: The different SQL-Dialects
I've seen a setup which in itself is quite charming:
The guy had a separate table in his DB where he stored all SQL-Statements in the specific dialect, one column for the Statement, another column just a (unique) numeric value identifying the Statement.
Fo MySQL he had something like
TableName "SQLQueries":
Column "Statement": 'SELECT ClientNo, ClientName, ClientAddress FROM Clients WHERE ClientName=:ParamClientName LIMIT 10;'
Column "Value": 123456

For MSSQL he had like
TableName "SQLQueries":
Column "Statement": 'SELECT TOP 10 ClientNo, ClientName, ClientAddress FROM Clients WHERE ClientName=:ParamClientName;'
Column "Value": 123456

In his code he just had to do a
'SELECT Statement FROM SQLQueries WHERE Value=123456;'
And then assign the returned String to the TSQLQuery-Object, assign the parameters, and go
« Last Edit: June 26, 2019, 11:25:54 am by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

Thaddy

  • Hero Member
  • *****
  • Posts: 9184
Re: Is there an all-in-one component for database connectivity?
« Reply #3 on: June 26, 2019, 11:54:50 am »
You summed it up!

Actually, this is a question in the category of DWIM. Which is wishful thinking....
also related to equus asinus.

bylaardt

  • Sr. Member
  • ****
  • Posts: 303
Re: Is there an all-in-one component for database connectivity?
« Reply #4 on: June 26, 2019, 01:42:53 pm »
I use zeroslib and user can choose between:
- Mysql 5
- MariaDB 10
- Firebird 3
- SQLite 3
- Oracle
- MSSql Server
- Postgres 9

Anyway, cares are needed:
you should parse instructions in SQL statments such as:
"concat" or ||
Div or /

Gizmo

  • Hero Member
  • *****
  • Posts: 683
Re: Is there an all-in-one component for database connectivity?
« Reply #5 on: July 02, 2019, 04:05:44 pm »
Thanks for the answers guys.

I'm going to have a go with the generic component which gets high praise (TSQLConnector) for now
Lazarus 2.0.2 and fpc 3.0.4 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Majove
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

Handoko

  • Hero Member
  • *****
  • Posts: 3188
  • My goal: build my own game engine using Lazarus
Re: Is there an all-in-one component for database connectivity?
« Reply #6 on: July 02, 2019, 07:16:15 pm »
I use zeroslib and user can choose between:

I think you you meant ZeosLib (without 'r'):
https://sourceforge.net/projects/zeoslib/