[How To] Designing a Program to support multiple DBMS
lazarus / 16-Databases

----------------------------------------------------------------------
Zvoni · 2022-11-18 10:02

Hi Folks,

on request of a User i'm going to show/explain how i code Frontends
which have to support multiple DBMS, without having to change your
code of the Frontend.

Up Front:
1) There will be not that much FPC-Code here. It's more about the
concept/systematic behind it.
2) It's mostly about SQL and Database-techniques
3) You can keep any Syntax-Errors you find 
3) I don't use visual components. Period.

Peer-Review is welcome. Someone might even point out a pitfall i
missed.

Off we go......

Let's say, you've started to code a Database-Program.
You start out with your client (GUI) and, say, SQLite. (e.g. I always
start with SQLite)

You often end up with code looking like this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var   
sSQL:String;Begin   //Example is: Show me the 10 best paid employees
for that Department   sSQL:='SELECT ID, EmpName, EmpSalary FROM
employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT
10;';   MyQuery.SQL.Text:=sSQL;  //Or assigning the SQL-Statement
directly   MyQuery.ParamByName('DepID').AsInteger:=6; 
 MyQuery.Open;End; and so on and so forth.
After some time, your source-code becomes littered/cluttered with SQL-
Statements.
Then you decide: OK, this is a maintenance nightmare. Let's move the
SQL-Statements to a separate area (might even be a separate unit) and
declare them as Constants
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Const 
 SELECT_TOP10_EMP_SALARY='SELECT ID, EmpName, EmpSalary FROM employees
WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT 10;';..Begin 
 MyQuery.SQL.Text:=SELECT_TOP10_EMP_SALARY;   
 MyQuery.ParamByName('DepID').AsInteger:=6;   MyQuery.Open;End; 
Looks familiar?

After some time you think (or your customer decides): Hey, would be a
neat idea to support MS-SQL-Server, too.
Let's integrate it.
Most people (at least that's what i found out) end up with code like
this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Const 
 SELECT_TOP10_EMP_SALARY_SQLITE='SELECT ID, EmpName, EmpSalary FROM
employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT
10;';   SELECT_TOP10_EMP_SALARY_MSSQL='SELECT TOP(10) ID, EmpName,
EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary
DESC;';..begin   Case  Connectiontype Of        SQLite:
MyQuery.SQL.Text:=SELECT_TOP10_EMP_SALARY_SQLITE;        MSSQL:
MyQuery.SQL.Text:=SELECT_TOP10_EMP_SALARY_MSSQL;      End; 
 MyQuery.ParamByName('DepID').AsInteger:=6;   MyQuery.Open;End; You
see the difference? It's the SQL-Dialect

What i do:
I store the SQL-Statements themselves in the Database.
Such a table looks like this:
IDStatementConstantConnType1SELECT ID, EmpName, EmpSalary FROM
employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT
10;1000SQLite4SELECT TOP(10) ID, EmpName, EmpSalary FROM employees
WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC;1000MSSQL
Now before actually firing of the SQL-Statement i want to display the
result to the User, i first retrieve the SQL-Statement from the
Database according to DBMS
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //This is
usually in its own unitInterface//This is it. This Function is a
"Write Once" - "Use everywhere". Could even be a Method of a "public"
Class or whateverFunction GetSQLStatement(Const AStatement:Integer;
Const AConnType:String):String; //i usually have a second TSQLQuery-
Object, or even use the "global" one. Here i use a second TSQLQuery-
ObjectImplementationFunction GetSQLStatement(Const AStatement:Integer;
Const AConnType:String):String;Const   GetSQL='SELECT Statement FROM
tbl_sql WHERE Constant=:pConst AND ConnType:=:pConnType;';  //This is
ANSI-SQL!!! Every DBMS should understand this!Var 
 QrySQL:TSQLQuery;begin   QrySQL:=TSQLQuery.Create;   //Connect to
Transaction and Database. I'll omit that here 
 QrySQL.SQL.Text:=GetSQL; 
 QrySQL.ParamByName('pConst').AsInteger:=AStatement;  //First Argument
of the Function. Which Statement? 
 QrySQL.ParamByName('pConnType').AsString:=AConnType;  //Second
Argument of the Function. For which DBMS?   QrySQL.Open;   //Sanity-
check if recordCount=1? 
 Result:=QrySQL.Fieldbyname('Statement').AsString; 
 QrySQL.Free;End; //This is somewhere in another unit. Of course, you
have to "use" the unit aboveUses SQLUnit; Const 
 SELECT_TOP10_EMP_SALARY=1000; //Again somewhere else in your code-
unitBegin   MyQuery.SQL.Text:=GetSQLStatement(SELECT_TOP10_EMP_SALARY,
MyConnType);   //MyConnType could be a global value retrieved/set at
the beginning, or it could even be the Connection-Type of
TSQLConnector!!!   //That's why i used DataType String/Text for
ConnType   MyQuery.ParamByName('DepID').AsInteger:=6;   MyQuery.Open; 
The only thing you should research beforehand is, if the Database-
Systems (SQLite, MySQL, MSSQL etc.) you want to support,
support named Parameters, and in which format
I used here the format "... SomeField=:SomeParam..."
There is also the Format "..... SomeField=@SomeParam..." but i haven't
used it so far.

Now try to apply this systematic/technique to all the other SQL-
Statements (INSERT, UPDATE, DELETE, CREATE TABLE/VIEW etc.) across the
different DBMS and their own SQL-Dialects.
Nevermind SQL-Dialects differing in functional areas
e.g. SQLite supports the ROW_NUMBER-Window-Function.
MySQL does, too..... from version 8 up
What to do if customer uses MySQL5.7? You have to use the Workaround
to simluate the ROW_NUMBER-Function (there is one!)

Oh, and i'm sure you can see how you can maintain the Statements
themselves outside your app with an DB-Admin-Program (DB-browser for
SQLite, MySQL-Workbench),
if your customer decides: "Nahh.... change that Statement to show the
Top 5 employees" (And no, we're not going to discuss to parametrize
that value ).

You can adjust/maintain the Statements outside your app to your hearts
content, as long as the "interface" of the Statement doesn't change.
In my example above, it's 3 Output-Columns (Integer, String/Text,
Double) and 1 Input-Value (Integer)

Thoughts?
Opinions?
Reviews?

Feel free to add here.
We might end up with a Wiki-Entry for this

----------------------------------------------------------------------
MarkMLl · 2022-11-18 10:42

I think you're saying that's distinct from any stored procedure
facility offered by the servers (the details of which might, of
course, be server- and possibly version-specific.

I've been down that route to fairly good effect, although in my case
(a) I stored fairly comprehensive scripting in the database, (b) had
the details of the connection stored in a .ini file and (c) saved
copies of the .ini files in tables whence they could be easily
retrieved if setting up another client system (application server).

I settled on PostgreSQL fairly early, but note that Firebird's SQL
dialect is very similar and your SQLite fragment is also close. I did
at one point have to change date format in some queries when Postgres
became more "standards compliant".

I admit to being a little wary of stored procedures (and triggers
etc.) since it can be very difficult to reconstruct everything that's
needed for workflow. As a result, I recreate almost everything when
required from the custom scripting: as such all that's needed when
doing a server update is to move the data tables and start running the
client-side scripts.

MarkMLl

----------------------------------------------------------------------
Чебурашка · 2022-11-18 10:55

I like the idea of not having in code the specific db depended sql
text, and storing it in a "code-extension-db" is nice. I suggest to
consider another possible persistence locus, for example in text
file(s) of filesystem, after all the db in this case acts only as
storage place of something "fixed" because follows what is implemented
in code.

I never had this need because my queries are not db dependent, but in
case this need arises, this approach is a valid solution.

One question: from what source are the db specific sqls stored in db
initially? I mean: in a software solution that makes something one
probably starts with some (several) features out of the box. In this
case several queries should already exists at first program execution,
say after (pay,) dowload and install.

----------------------------------------------------------------------
nummer8 · 2022-11-18 10:59

Your solution is up for debate. There are always pros and cons for
this method.
I am not interested in tha discussion that is a matter of choice in a
specific situation.

One thing I want to mention for your method is that I would not use
const numbers to identifiy the query.
With a limited amount of query's you know what const belongs to what
query. Beyond that amount you have to maintain al ist of const/ query.
A small alteration could be to add an extra field where you write down
a decriptive name just like in your first two examples.
"SELECT_TOP10_EMP_SALARY"
In this way you have all the information in the DB.

Further more there is the question do you use your DB server as a real
server or just as a data container. (Same remark as MArkMli) Meaning:
what do you do with the triggers, functions and stored procedures. Do
you use the DB- server for that or do you handle that in the software.

Just my 2ct.

Nummer8

----------------------------------------------------------------------
MarkMLl · 2022-11-18 11:10

Quote from: nummer8 on November 18, 2022, 11:59:58 amOne thing I want
to mention for your method is that I would not use const numbers to
identifiy the query.
With a limited amount of query's you know what const belongs to what
query.

What is the internationalisation convention for naming phrases?

MarkMLl

----------------------------------------------------------------------
Thaddy · 2022-11-18 11:11

I would also mention ODBC and CLI in this context.
QuoteI admit to being a little wary of stored procedures (and triggers
etc.)Stored procedures and triggers can simplify the client code a lot
with the chance that you can achieve client code that does not need to
change much or at all between dialects. Stored procedures and triggers
also contribute to much safer code.

----------------------------------------------------------------------
Чебурашка · 2022-11-18 11:18

One extra observation.

In complex SQL queries it might happen that the operations possible in
one DB are not possible in another DB (example DB that does not
support outer join).
In cases like this there might come up the possibility of
postprocessing made at code level in order to obtain the same final
result equivalently on the 2 different DBMS.

In a situation like this, even if the statements do not appear in
code, code will still show this like

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if connType
= x     do logic for x elseif connType = y    do logic for y 
But this in not avoidable and does not make the solution proposed less
interesting.

----------------------------------------------------------------------
MarkMLl · 2022-11-18 11:32

Quote from: Thaddy on November 18, 2022, 12:11:35 pmI would also
mention ODBC and CLI in this context.
QuoteI admit to being a little wary of stored procedures (and triggers
etc.)Stored procedures and triggers can simplify the client code a lot
with the chance that you can achieve client code that does not need to
change much or at all between dialects. Stored procedures and triggers
also contribute to much safer code.

I don't deny that, I'm pointing out that they can be a management
issue.

Quote from: tt on November 18, 2022, 12:18:15 pmIn complex SQL queries
it might happen that the operations possible in one DB are not
possible in another DB (example DB that does not support outer join).

Outer joins were certainly an issue at one point, as was the inability
of at least one popular database to do a rollback. However a better
example might these days be patchy support for analytic (AKA window)
functions: I have in the past resorted to APL post-processing to
extract e.g. rate-of-change information.

MarkMLl

----------------------------------------------------------------------
Zvoni · 2022-11-18 11:57

Holy smoke!
i didn't expect so many reactions in this short a time.

@Mark
Yes, it's distinct from stored procedures (at least for me), since not
all DBMS support stored procedures (SQlite being the prominent one).
Triggers.... Good point. Would have to think on it.

@tt
Yes, there has to be an "initial" catalogue locally somewhere. Could
be simple Textfiles (*.sql-Extensions, whatever)
As to persistence locus (fun fact: in germany the word "locus" is
often used for "shitter", "toilett" )
Yes, i was thinking about it. If it's local, you have to provide that
File (or whatever) to each client. Might result in maintenance-
nightmare again.
If it's part of the Database itself (especially server-based DBMS),
then you just rollout the Client, configure connection, and off you go

@nummer8
I declare my consts in the unit they are used in.
It makes no sense (at least to me, no offense) to have a Const for
employees available (!!) in a unit where i handle suppliers.

@"Outer Join"
I take it you're talking about Full Outer Join, since i wouldn't know
of a single DBMS which doesn't support Left Join (and a Right Join is
just a reversed Left Join).
DBMS not supoorting Full outer Join are the two prominent ones :
SQLite and MySQL
But there is a Workaround for it.
It was one of my other topics for the "HowTo"'s i asked about in
"Other" Thread

Comments welcome

----------------------------------------------------------------------
Чебурашка · 2022-11-18 12:07

Not being the sql text compiled in the executable, but loaded at
runtime from a external source, might also lead to security issues.

What if mr FunnyHacker changes the query (1034, *) to 'drop database';

(I know, in some cases this could be also possible in the case sql
text is compiled into the exe, by patching it).

----------------------------------------------------------------------
Чебурашка · 2022-11-18 12:13

Quote from: Zvoni on November 18, 2022, 12:57:07 pmIf it's part of the
Database itself (especially server-based DBMS), then you just rollout
the Client, configure connection, and off you go

Yes I was also thinking about the advantage of using a DB for storing,
but it is mandatory that is a centralized db server (for example using
SQLite as queries repo is equivalent to using files on fs).

----------------------------------------------------------------------
Zvoni · 2022-11-18 12:20

Quote from: tt on November 18, 2022, 01:13:29 pmQuote from: Zvoni on
November 18, 2022, 12:57:07 pmIf it's part of the Database itself
(especially server-based DBMS), then you just rollout the Client,
configure connection, and off you go

Yes I was also thinking about the advantage of using a DB for storing,
but it is mandatory that is a centralized db server (for example using
SQLite as queries repo is equivalent to using files on fs).
Yes, but if you use SQLite for Multi-user-access, then there is
something wrong from the get go, though it would still be the
"central" database (-file)

As for the "Hacker"-Comment: Correct.
But, encrypt your Statements?
reading "zurOBfJHE89376298JjfuUuumdmele" instead of "SELECT Bla FROM"
...... :-)

----------------------------------------------------------------------
MarkMLl · 2022-11-18 12:38

Quote from: Zvoni on November 18, 2022, 01:20:41 pmYes, but if you use
SQLite for Multi-user-access, then there is something wrong from the
get go, though it would still be the "central" database (-file)

I'm not sure whether this would be better described as "multi-user" or
"multi-host"... but whatever it's called, it's best avoided :-)

Just because a server program's been retrofitted for a modicum of
network operation- or some equivalent using VMs or containers- doesn't
mean that its use in that role is defensible. In the community built
(loosely) on top of Borland products we've got the painful example of
the number of times Paradox was overextended, and the less said about
MySQL or the early days of most databases' clustering the better...

MarkMLl

----------------------------------------------------------------------
Zvoni · 2022-11-18 12:44

Quote from: MarkMLl on November 18, 2022, 01:38:00 pmQuote from: Zvoni
on November 18, 2022, 01:20:41 pmYes, but if you use SQLite for Multi-
user-access, then there is something wrong from the get go, though it
would still be the "central" database (-file)

I'm not sure whether this would be better described as "multi-user" or
"multi-host"... but whatever it's called, it's best avoided :-)

Just because a server program's been retrofitted for a modicum of
network operation- or some equivalent using VMs or containers- doesn't
mean that its use in that role is defensible. In the community built
(loosely) on top of Borland products we've got the painful example of
the number of times Paradox was overextended, and the less said about
MySQL or the early days of most databases' clustering the better...

MarkMLl
Correct.

The purpose of this Thread is to display "my way" (to quote Frank
Sinatra), how i do what and why, and invite everyone to chip in.
As said in the preamble:
I might learn something i missed
But for someone else it might be the holy grail ("Why have i never
thought of that?")

If at the end the result is kind of a "this is the best practice in
that scenario" on the wiki, then i would celebrate.
if there is only one single user saying "thanks, never thought of
that." i'd consider this thread a capital success

i changed my style of Database-coding completely, after discovering
above mentioned systematic/technique.
I even figured out a method to parametrize Column- and Tablenames
using above technique (would have been another topic for the
"HowTo"'s)

----------------------------------------------------------------------
Чебурашка · 2022-11-18 12:46

Quote from: Zvoni on November 18, 2022, 01:44:10 pmif there is only
one single user saying "thanks, never thought of that." i'd consider
this thread a capital success

I think you can celebrate then, because if somebody has this need,
this can anyway can be a good hint about a possible way to go.

----------------------------------------------------------------------
MarkMLl · 2022-11-18 13:00

Quote from: Zvoni on November 18, 2022, 01:44:10 pmThe purpose of this
Thread is to display "my way" (to quote Frank Sinatra), how i do what
and why, and invite everyone to chip in.

...and everybody's jumping in enthusiastically.

Or (to quote Nancy Sinatra), "these boots are made for walking..." :-)

MarkMLl

----------------------------------------------------------------------
Martin_fr · 2022-11-18 13:21

Quote from: Zvoni on November 18, 2022, 11:02:18 amMost people (at
least that's what i found out) end up with code like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Const 
 SELECT_TOP10_EMP_SALARY_SQLITE='SELECT ID, EmpName, EmpSalary FROM
employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT
10;';   SELECT_TOP10_EMP_SALARY_MSSQL='SELECT TOP(10) ID, EmpName,
EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary
DESC;'; You see the difference? It's the SQL-Dialect

What i do:
I store the SQL-Statements themselves in the Database.
Such a table looks like this:
IDStatementConstantConnType1SELECT ID, EmpName, EmpSalary FROM
employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT
10;1000SQLite4SELECT TOP(10) ID, EmpName, EmpSalary FROM employees
WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC;1000MSSQL
Now before actually firing of the SQL-Statement i want to display the
result to the User, i first retrieve the SQL-Statement from the
Database according to DBMS
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Const 
 SELECT_TOP10_EMP_SALARY=1000;Begin 
 MyQuery.SQL.Text:=GetSQLStatement(SELECT_TOP10_EMP_SALARY,
MyConnType); 
Not sure if that is what sparked the "stored procedure" and "trigger"
discussion? But the above is neither. The above is not even a
"prepared statement".

In any case, I don't see the advantage...

1) You still need to maintain all the versions of your statements.
2) Why store all forms (mysql and sqlite)? If you connect to a mysql
server, you will only need the mysql forms of the statement. All
others are dead weight.
3) You have the cost of executing the query to load the statement.
That can significantly impact the server load / performance of the
app.
4) "SELECT_TOP10_EMP_SALARY=1000;" mapping to numbers is a source of
errors...

Sure you can cache the loaded statements. But then you can also just
have them in a unit (either one unit for each db-engine, or one unit
with IFDEF, or one unit with an "array [enum_db_engine] of").

In order to have your table, you somewhere need code to populate that
table. So likely you have a big "insert into stmt_table (...) values
(sql,sql,sql,....)" => In other words you must store the text
somewhere in a text-based file.
And yes, what if the table structure changes? At first glance it looks
like having the sql closer to the table definitions will help keeping
them in sync... But, then the params may need updates too, so updating
the code may still be required. And testing needs to be done against
all db-engines. And fixing bugs in the statements is harder, if they
are in a table, because after each fix to the sql, you need to
additionally update that table.

IMHO having a unit like
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
TDbEngine = (eMysql, ePostgres, eSQLite);  TSqlStatement = (sqlTop10,
sqlSalary, ...>)const  MySql = array [TSqlStatement] of array
[TDbEngine] of string = ( // order can be swapped  ); 
If most statements are engine agnostic, you have an "var stmt = array
[TSqlStatement]  of string" and just override the 1 or 2 engine
specific statements (init function in the sql statement unit / so
still all in one unit).


The alternative is to use a framework that builds the sql itself. Then
you support all engines that the framework knows.

Of course you can't hand optimize your sql any more. You will have to
describe the table structures in some form defined by the frame work.
(Something I don't like, because why replace sql with some other form,
just to then generate sql from it....)

----------------------------------------------------------------------
Zvoni · 2022-11-18 13:32

Martin, very nice.
And yes, agreed with the "dead weight", but my initial thoughts on
this were:
1) I mainly use ODBC-Connections (Please no discussion about that), so
for a different DBMS i would only need the driver, and the connection-
string (very basic, of course!).
2) Because i have the dead weight on the DB-Server itself (say on
MySQL), and decide: Let's move to MS-SQL, everything is already there.
Pull the "CreateDatabaseMSSQL"-Statement from the MySQL-Table, assign
it to the TSQLQuery connected to the MSSQL-Connection, and Fire!
Pull the "CreateTableMSSQL"-Statement from the MySQL-Table, assign it
to the TSQLQuery connected to the MSSQL-Connection, and Fire!
and so on and so forth.
If done correctly, you could even do a complete migration without
adjusting a single thing
I wouldn't have to change, never mind to recompile my frontend.

That's the initial idea behind that concept.

Of course you're right, that as with everything in life, this is not
the be all, end all, and it certainly has its downsides (as mentioned
above), but i found out (at least for myself), that this concept is
the "best of all worlds" for me.

Comments welcome

----------------------------------------------------------------------
440bx · 2022-11-18 13:46

First, an important disclaimer, it isn't very important to me to have
the same code support multiple database systems because normally, I
choose the DBMS, therefore there is only one to support.  It also
means that my experience writing code that supports multiple DBMS is
rather limited (actually, close to none.)

With that said, if I had to solve the problem, I'd create a set of
functions that carry out the atomic logical operations on each
database using the best methods offered by each DBMS.   I'd create a
table of function pointers for each DBMS and, early in the program I'd
get a pointer to the table that corresponds to the DBMS in use.  In
each table, entry "n" solves the same problem - possibly in different
ways - for each DBMS.

Basically, all access to the database is done through polymorphic
calls  to functions tailored for that DBMS.

----------------------------------------------------------------------
Чебурашка · 2022-11-18 13:49

I recall a old paper, related to ORMs but by extension applicable very
often when code and databases meet each other:

Object-Relational Mapping is the Vietnam of Computer Science
http://www.odbms.org/wp-content/uploads/2013/11/031.01-Neward-The-
Vietnam-of-Computer-Science-June-2006.pdf

----------------------------------------------------------------------
Zvoni · 2022-11-18 13:52

Quote from: 440bx on November 18, 2022, 02:46:05 pmFirst, an important
disclaimer, it isn't very important to me to have the same code
support multiple database systems because normally, I choose the DBMS,
therefore there is only one to support.  It also means that my
experience writing code that supports multiple DBMS is rather limited
(actually, close to none.)

With that said, if I had to solve the problem, I'd create a set of
functions that carry out the atomic logical operations on each
database using the best methods offered by each DBMS.   I'd create a
table of function pointers for each DBMS and, early in the program I'd
get a pointer to the table that corresponds to the DBMS in use.  In
each table, entry "n" solves the same problem - possibly in different
ways - for each DBMS.

Basically, all access to the database is done through polymorphic
calls  to functions tailored for that DBMS.
hmmm..... so basically, kind of like in a separate library ("Black
Box"), decoupled from the Frontend, so the Frontend still remains
DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls

----------------------------------------------------------------------
440bx · 2022-11-18 13:54

Quote from: Zvoni on November 18, 2022, 02:52:28 pmhmmm..... so
basically, kind of like in a separate library ("Black Box"), decoupled
from the Frontend, so the Frontend still remains DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls
That's it, you got it.

----------------------------------------------------------------------
Zvoni · 2022-11-18 14:16

Quote from: 440bx on November 18, 2022, 02:54:25 pmQuote from: Zvoni
on November 18, 2022, 02:52:28 pmhmmm..... so basically, kind of like
in a separate library ("Black Box"), decoupled from the Frontend, so
the Frontend still remains DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls
That's it, you got it.
Yeah, but looking closely at my approach: it's the same (just
"executed" differently).
You have your Black Box in a separate library/unit, where you execute
your pointer-magic.
I have my Black Box, that i'm pulling the needed statements from
somewhere outside of my programcode.
and i offer also the same "interface" (Constant plus DBMSQualifier).

But i think with this we would drift off into the philosophical branch
of programming 
Let's avoid that.

In any way: 440bx. Nice one! A valid approach

----------------------------------------------------------------------
Martin_fr · 2022-11-18 14:26

Quote from: Zvoni on November 18, 2022, 02:32:44 pmIf done correctly,
you could even do a complete migration without adjusting a single
thing
I wouldn't have to change, never mind to recompile my frontend.

But with the proposed unit, you would neither.

Well, I take it you have some config, with the connection details (IP
or similar, username, password). Those you give to the ODBC.

In this you could also have the DB engine type. In fact, if you don't
recompile, then you must have it, because
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Function
GetSQLStatement(Const AStatement:Integer; Const
AConnType:String):String;takes AConnType.

So you could easily map this into
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var
MyConnType: TDbEngine;And use it to look up statements in the table.

Well, ok, you need to recompile to add a new engine....
But then, if your client wants to run on a new engine, how is is DB
being populated? Does your app include the code to create the db on
first run? Or do users need to manually run some SQL.
Only in the latter case, you could indeed just supply some new sql, to
support the new db-engine.
IMHO (matter of opinion) that is rare enough to allow for a new
compile... (and make it easier for the user, by allowing the app to
initialize the db on first run)

Then again, you run the risk that you run an invalid combinations of
versions.
Well ok, if your app expects a diff version of the sql (and table
structures) it will likely fail (because a new field from a table
might be missing, either in the app, or in the db).
But with the sql in the table, there is an extra layer of potential
error. Let's say the name of two sql-params (for one of the
statements) where changed (they still exist, but have new meanings) =>
your app may run. No hard error, just incorrect action on the data.
Yes that can happen with the sql coded into you app too. As I said,
just one more layer.


There are lots of considerations that come for different use case
scenarios.

If you write a desktop app, that stores a list of local documents and
allows to browse them, with comments and other meta data... => You
neither have particular needs for speed nor security.

If the app runs as server (website for a business), with millions of
clients connecting, speed may be really important. You may group
statements that often run together into stored procedures. Different
db engines may have different support for that. So the amount and
order of interactions with the DB may differ between engines. It's not
just replacing the sql text. There may be different design approaches.
(E.g. some db can take multiple statements in a single call, other can
or can't handle stored proc. and yet others again may do some work in
triggers, and not need all the statements....)

You also have security considerations. You may need to limit what your
application is allowed to see/change on the db.
If you store statements in the db and your app will execute those
statements with certain permissions granted, then that table needs to
be protected. Otherwise an attacker may be able to sneak in
"destructive" statements.
Yes, you have to design correct grants for all else anyway, so you can
to that for the statement table too. It's just one more table to take
care off in your security concept.

----------------------------------------------------------------------
Martin_fr · 2022-11-18 14:29

Quote from: 440bx on November 18, 2022, 02:46:05 pmWith that said, if
I had to solve the problem, I'd create a set of functions that carry
out the atomic logical operations on each database using the best
methods offered by each DBMS.   I'd create a table of function
pointers for each DBMS and, early in the program I'd get a pointer to
the table that corresponds to the DBMS in use.  In each table, entry
"n" solves the same problem - possibly in different ways - for each
DBMS.

Which additionally helps when the order/count of params changes. You
can fix it in a single place (e.g. by adding a default)

@Zvoni: And if provided as so/dll => the main app does not need to be
recompiled....

----------------------------------------------------------------------
440bx · 2022-11-18 14:52

Quote from: Zvoni on November 18, 2022, 03:16:37 pmYeah, but looking
closely at my approach: it's the same (just "executed" differently).

In any way: 440bx. Nice one! A valid approach
Thank you and, yes, it is conceptually the same approach just taken a
little farther by creating tables of functions for each DBMS.

Basically, complete "blackboxing" of where the data comes from and how
it's obtained.  The front end asks for data, doesn't have a clue
(other than the interface) about how its obtained and manipulates it
as needed.

----------------------------------------------------------------------
Zvoni · 2022-11-18 14:59

All valid arguments.

I like discussions like this.
As i said: i might even get a nugget for something i haven’t
considered.

----------------------------------------------------------------------
MarkMLl · 2022-11-18 15:04

Quote from: Zvoni on November 18, 2022, 02:32:44 pm1) I mainly use
ODBC-Connections (Please no discussion about that), so for a different
DBMS i would only need the driver, and the connection-string (very
basic, of course!).

I for one have nothing against that: anything's better than a
graphical ODBC (or whatever) connection that's difficult to archive or
document.

However I would caution that, at least the last time I looked at this
issue, ODBC can miss out on things like asynchronous notifications:
for that you really do have to go through the backend-specific
library.

MarkMLl