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
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
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:
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:
| ID | Statement | Constant | ConnType | | 1 | SELECT ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT 10; | 1000 | SQLite | | 4 | SELECT TOP(10) ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC; | 1000 | MSSQL |
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
|
//This is usually in its own unit
Interface
//This is it. This Function is a "Write Once" - "Use everywhere". Could even be a Method of a "public" Class or whatever
Function 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-Object
Implementation
Function 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 above
Uses SQLUnit;
Const
SELECT_TOP10_EMP_SALARY=1000;
//Again somewhere else in your code-unit
Begin
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
Recent
