Easy using same code diferent DB ?
lazarus / 16-Databases

----------------------------------------------------------------------
mirce.vladimirov · 2012-05-14 21:26

I had an idea to create aplication that doesnot have a DB connection
pre-defined, but it could be set at runtime (stored in some *.ini file
or similar) depending on what the user would prefer. But I dont know
much about diferences betwee databases and dialects of SQL. Curently
I'm using only MySQL, but i'm not using advanced features like stored-
procedures, views or triggers. Is there documentation that covers
diference between MySQL, SQLite, FBSQL, PGSQL, MSSQL... so i could
tune my application to offer choice of using 3 diferent DBs ?

----------------------------------------------------------------------
Lacak2 · 2012-05-15 05:20

You can as usualy google 
http://en.wikipedia.org/wiki/Comparison_of_relational_database_managem
ent_systems

But if you will use only "simple DML" (select/insert/update/delete) 
then portability will be good.

Most of databases complains to SQL standard (at least SQL 1992)

----------------------------------------------------------------------
exdatis · 2012-05-15 05:28

Any of databases have some special functions, and also different
syntax(a little). I use Firebird and Postgresql. Both have a generator
to control ID (gen_id or sequence). So, easy to use with SQLdb, and
there is not need to change much of source code.
   Regards.

----------------------------------------------------------------------
BigChimp · 2012-05-15 05:41

Nice idea; I've been thinking about making my programs a bit more
database agnostic as well. By using the SQLDB TSQLConnector and
casting it to a certain DB connector as needed that is actually fairly
easy to do (in my simple demo program at least).
See here https://bitbucket.org/reiniero/fpc_laz_patch_playground/src
in the SQL folder for my pasql program that supports multiple dbs
(Firebird, MS SQL Server, Sybase tested; code added for Oracle,
PostgreSQL...)

Stored procedures will differ among products for sure in the way you
have to write them: almost every db has a different language and
different features.

Triggers are also different IIRC, e.g. MSSQL Server exposes an entire
dataset (however you call it) while Firebird only exposes the row
involved in an AFTER INSERT trigger.

Views I don't know about... if they are in SQL92, then they are
probably similar in all dbs except some advanced functions.

As Lacak2 said, basic stuff like primary/foreign key support, select
and insert/update queries should work. (Unless you choose Mysql with
an engine that doesn't support it - don't know if that still is the
default, probably not - or the sqlite option that turns off foreign
key support etc)

Some datatypes are not available in all databases (e.g. BIT/BOOLEAN)
or implemented differently (different kind of BLOB/BINARY datatypes
with different size limits). However, VARCHAR, INTEGER etc should be
the same.
You can use INTEGER to emulate a BIT datatype; you can implement BLOB
support differently for each db; IIRC, sqldb should handle blobs
seamlessly across dbs (if not, it's a bug and should be fixed).

(In my pasql source, as I'm processing user-submitted SQL, I'm
avoiding having to check for these differences by just passing user
input almost unmodified to the db which will complain if something
doesn't work. So no hints for you there.)

Of course, connecting to dbs is also different; e.g. MSSSQL Server and
Firebird have the concept of "trusted authentication" where you pass
on your Windows credentials to log in without password.
IIRC, dbs like DB2 and Oracle also allow a certain OS user access to
the db without further authentication... but this can only work in
controlled environments where you can't fake a username.
These databases also support login through LDAP etc.

See my pasql source where you can see how I support MSSQL trusted
auth.

You can get some idea of workarounds etc for certain databases by
looking at the FPC dbtestframework source, and perhaps in
LazDatadesktop.

----------------------------------------------------------------------
BigChimp · 2012-05-15 05:44

Quote from: exdatis on May 15, 2012, 07:28:08 amI use Firebird and
Postgresql. Both have a generator to control ID (gen_id or sequence).
So, easy to use with SQLdb, and there is not need to change much of
source code.
Yep, good point generators and autonumbers: Oracle also supports
generators for generating unique prmiary key integer data.

MSSQL Server supports autonumbers (a sort of built in generator) - in
MSSQL 2012 apparently started support for generators.
Don't know what sqlite does or mysql.

This kind of functionality can be very useful in order to make sure
there is always a valid primary key, regardless of which
application/batch process inserts data. In practice, you need to set
up your generator/trigger or autonumber once and can then forget about
it.
So yes, this is a difference in SQL, but it is fairly self-contained.

----------------------------------------------------------------------
exdatis · 2012-05-15 06:52

CREATE VIEW V_SOME_ID (NEXT_ID)
AS 
SELECT
    GEN_ID(GEN_SOME, 1)
  FROM RDB$DATABASE -> return new_id, set generator (Firebird)
Or:
SELECT nextval('myseq');  -> return new_id, set sequence(Postgresql)
Quite Easy! 
As BigChimp said, Oracle also supports sequences!
Regards.