Recent

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

Gizmo

  • Hero Member
  • *****
  • Posts: 831
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?

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
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 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
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 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

Thaddy

  • Hero Member
  • *****
  • Posts: 14214
  • Probably until I exterminate Putin.
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....
Specialize a type, not a var.

bylaardt

  • Sr. Member
  • ****
  • Posts: 309
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: 831
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

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • 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/

 

TinyPortal © 2005-2018