Database agnostic. ANSI SQL or ORM?

Question

I work in an project that most of the code is written in PL/SQL. We need to refactor the code to support SQL Server (and if possible in the future to be database agnostic). They decided to transform the PL SQL code to ANSI SQL but for some reason it doesn't sound a good idea to me.

Can you please tell what would be the pros and cons of tranforming to ANSI instead of using an ORM?

Thanks

No authoritative answer could possibly be provided by anyone for this general of a question. Perhaps a practical need and comparing the validity of way versus another would be useful. — Michael O'Neill, Jan 24 '14 at 21:15

Brian McGinity · Answer 1 · 2014-01-23T15:49:51.930

4

The problem is that pl/sql is a lot more than just sql statements. It has logic, types, objects, functions, looping and ect....

So while you could change the sql statements to be ansi compatible, there is no such animal for the procedural language.

You would need to rewrite the plsql in some sort of independent middleware language and then you're dependent on that.

Additionally, different databases handle committing changes and locking differently, so when writing code, the differences need to be understood by the developers.

edited Jan 23 '14 at 15:49

answered Jan 23 '14 at 15:42

Brian McGinity

5,777
5
36
46

1

I feel like a consultant--working for green checks and up arrows. That will be one up arrow and one green check please. Good thing I have a day job. – Brian McGinity Jan 23 '14 at 15:53
1

"Database agnostic" is a fallacy regarding anything but the simplest of database applications. @Brian points out the pitfalls. Good luck. You will need it. – OldProgrammer Jan 23 '14 at 19:49

score 1 · Answer 2 · answered Aug 25 '21 at 10:39

One approach to implement vendor agnostic (dynamic) procedural logic is using a third party product like jOOQ, where you can write something like:

ctx.begin(
    for_(i).in(1, 10).loop(
        insertInto(t).columns(c).values(i)
    )
).execute();

Which would translate to (a selection):

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END
 
-- Firebird
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END
 
-- MariaDB
BEGIN NOT ATOMIC
  FOR i IN 1 .. 10 DO
    INSERT INTO t (c)
    VALUES (i);
  END FOR;
END;
 
-- Oracle
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
 
-- PostgreSQL
DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
$$
 
-- SQL Server
BEGIN
  DECLARE @i int;
  BEGIN
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO t (c)
      VALUES (@i);
      SET @i = (@i + 1);
    END;
  END;
END;

You'll be limited to the least common denominator that is currently being supported by jOOQ, which, as of jOOQ 3.15, removes some features from PL/SQL (such as RECORD types, TABLE types, etc.), but given the requirement of being Oracle/SQL Server agnostic, that may be good enough.

Disclaimer: I work for the vendor behind jOOQ.

Database agnostic. ANSI SQL or ORM?

2 Answers2