Lazarus
Programming => Databases => Topic started by: Gizmo on June 26, 2019, 10:51:34 am
-
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?
-
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.
-
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
-
You summed it up!
Actually, this is a question in the category of DWIM. Which is wishful thinking....
-
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 /
-
Thanks for the answers guys.
I'm going to have a go with the generic component which gets high praise (TSQLConnector) for now
-
I use zeroslib and user can choose between:
I think you you meant ZeosLib (without 'r'):
https://sourceforge.net/projects/zeoslib/