Translating Saved Procedures Between Dialects – Java, SQL and jOOQ.

Previously years, we’ve invested plenty of effort into bettering our procedural language capabilities in jOOQ. What began with a easy inside API to assist the emulations of DDL clauses like these:

-- Some dialect that helps this
create desk if not exists t (i varchar(10));

-- Db2
start
  declare proceed handler for sqlstate '42710' start finish;
  execute rapid 'create desk T (I varchar(10))';
finish

-- Oracle
start
  execute rapid 'create desk T (I varchar2(10))';
exception
  when others then
    if sqlerrm like 'ORA-00955%' then null;
    else increase;
    finish if;
finish;

-- SQL Server
start strive
  create desk T (I varchar(10))
finish strive
start catch
  if error_number() != 2714 throw;
finish catch

… advanced right into a full fledged API for all kinds of procedural logic executed in your database server.

Nameless blocks

The above examples present what most RDBMS name “nameless blocks”, much like Java’s nameless courses, i.e. components of procedural logic that shouldn’t have a reputation.

Relying on the database, these blocks are interpreted on the fly, or compiled and cached like extraordinary SQL statements. They are often very helpful for a wide range of causes:

• To create atomic ad-hoc items of code, executed in a single server spherical journey, much like the above DDL scripts with built-in exception dealing with
• To create dynamic procedural code. This can be esoteric for a lot of, however precisely the correct factor to do for others. All of jOOQ is about dynamic SQL, so why not additionally dynamic PL/SQL, T-SQL, pgplsql, and so forth?
• To work round limitations imposed by Conway’s Legislation, when there is no such thing as a manner you will get the required GRANT or different bureaucratic token to deploy your process in manufacturing at your discretion. I imply, that is nonetheless a factor in plenty of firms.
• A lesser case of Conway’s Legislation could also be whenever you’re a product vendor, and also you don’t know if you happen to can create procedures in your shoppers’ manufacturing system. Simply don’t. Run your procedural logic as an nameless block if you happen to can’t, or as a process if you happen to can. Similar jOOQ code.
• In case your procedural code adjustments very ceaselessly (even dynamically), storing it would trigger tough points. For those who’ve ever labored with Oracle and bumped into the dreaded latch free occasion, you realize what I imply.

I’m under no circumstances advocating it is best to use nameless blocks over saved procedures normally. For those who can, retailer your code within the database for higher efficiency and re-use. However typically you may’t, and typically you shouldn’t.

So, jOOQ helps – as at all times – a mixture of numerous procedural logic components, together with:

• Blocks with variable declarations
• IF assertion
• Loops together with LOOP, WHILE, REPEAT, FOR loops
• EXIT (or LEAVE) and CONTINUE (or ITERATE) for loop management circulate
• RETURN to return from procedures or features
• GOTO (ghasp!)
• SIGNAL or RAISE
• Labels
• CALL assertion to name different saved procedures
• EXECUTE assertion (for working dynamic SQL from inside procedural logic. Which stage of inception is that?)

And we’re including extra assist on a regular basis. The Java code would possibly look one thing like this:

Variable<Integer> i = var(title("i"), INTEGER);
ctx.start(
  for_(i).in(1, 10).loop(
    insertInto(T).columns(T.COL).values(i)
  )
).execute();

Assuming you can not run a bulk insert assertion for some purpose, this could be the best way to go. It interprets to varied dialects as follows.

Db2 and MySQL (which doesn’t assist nameless blocks, however assertion batches)

start
  declare I bigint;
  set I = 1;
  whereas I <= 10 do
    insert into T (COL) values (I);
    set I = (I + 1);
  finish whereas;
finish;

PostgreSQL

do $$
start
  for I in 1 .. 10 loop
    insert into T (COL) values (I);
  finish loop;
finish;
$$

Oracle

start
  for I in 1 .. 10 loop
    insert into T (COL) values (I);
  finish loop;
finish;

SQL Server

start
  declare @I bigint = 1;
  whereas @I <= 10 start
    insert into T (COL) values (I);
    set @I = (@I + 1);
  finish;
finish;

As at all times with jOOQ, you don’t have to start out out with writing jOOQ API based mostly code. Whereas that is the really helpful method when your procedural (or SQL) logic is dynamic, jOOQ can even parse and translate static SQL in string type. The babelfish of SQL. Mess around with it right here to study extra: https://www.jooq.org/translate/

Storing the code as a process

For those who don’t have any of the above use-cases, it would be best to retailer this code as a process (or perform):

• For larger re-use
• For higher efficiency

In that case, ranging from jOOQ 3.15, you need to use our CREATE PROCEDURE, CREATE FUNCTION, and even CREATE TRIGGER assist.

Be aware: CREATE PACKAGE is excessive on our want checklist, however may not make it into 3.15 anymore. If packages are used for namespacing solely, they could be emulated utilizing schemas in different dialects. Different bundle stage options, akin to bundle state could also be tougher to translate.

The earlier nameless block might be simply wrapped in a DSLContext.createProcedure() name

Variable<Integer> i = var("i", INTEGER);
Parameter<Integer> i1 = in("i1", INTEGER);
Parameter<Integer> i2 = in("i2", INTEGER);

ctx.createProcedure("insert_into_t")
   .parameters(i1, i2)
   // Chances are you'll or could not wrap your block in BEGIN .. END.
   // jOOQ will determine it out per dialect...
   .as(for_(i).in(i1, i2).loop(
     insertInto(T).columns(T.COL).values(i)
   ))
   .execute();

Which might produce the next procedures:

Db2 and MySQL

create process INSERT_INTO_T(
  I1 integer,
  I2 integer
)
start
  declare I bigint;
  set I = I1;
  whereas I <= I2 do
    insert into T (COL) values (I);
    set I = (I + 1);
  finish whereas;
finish;

MariaDB

create process INSERT_INTO_T(
  I1 int,
  I2 int
)
start
  for I in I1 .. I2 do
    insert into T (COL) values (I);
  finish for;
finish;

Oracle

create process INSERT_INTO_T(
  I1 quantity,
  I2 quantity
)
as
start
  for I in I1 .. I2 loop
    insert into T (COL) values (I);
  finish loop;
finish;

PostgreSQL

create process INSERT_INTO_T(
  I1 int,
  I2 int
)
language plpgsql
as
$$
start
  for I in I1 .. I2 loop
    insert into T (COL) values (I);
  finish loop;
finish;
$$

SQL Server

create process INSERT_INTO_T
  @I1 int,
  @I2 int
as
start
  declare @I bigint = @I1;
  whereas @I <= @I2 start
    insert into T (COL) values (@I);
    set @I = (@I + 1);
  finish;
finish;

Mess around with it right here to study extra: https://www.jooq.org/translate/. We’re additionally wanting ahead to your bug reviews and/or function requests right here: https://github.com/jOOQ/jOOQ/points/new/select.

Tough transformations

Procedural languages are standardised through the ISO/IEC 9075-4 commonplace, and a few RBDMS surprisingly comply with a big extent on the usual, together with:

Others do much less so, however all procedural languages agree on the truth that they’re quite simple languages, with none such “fancy” issues like subtype or parametric polymorphism (OK, PL/SQL has some subtype polymorphism, however not a really refined one. We gained’t assist it for now), lambda expressions, dynamic dispatch, algebraic information varieties, and so forth. and so forth.

What they do have in widespread is a decent integration with the SQL language, which is the place they shine.

However there are delicate variations, nonetheless. For instance, they differ in the place you may declare variables. Some have block scope, others don’t. And a few adhere to the usual, the place LEAVE requires a label, others don’t.

Think about you write this “fantasy” jOOQ code

Identify t = unquotedName("t");
Identify a = unquotedName("a");
Variable<Integer> i = var(unquotedName("i"), INTEGER);

ctx.start(
     insertInto(t).columns(a).values(1),
     declare(i).set(2),
     loop(
       insertInto(t).columns(a).values(i),
       i.set(i.plus(1)),
       if_(i.gt(10)).then(loop(exit()), exit())
     )
   )
   .execute();

That is only a extra difficult model of the unique loop, which inserts values 1-10 right into a desk. There’s no purpose apart from to indicate off the transformation capabilities for the nesting of loop(exit()), in addition to the infinite LOOP with EXIT utilization, reasonably than the listed FOR loop.

There are some things that don’t at all times work precisely like this in some dialects!

Let’s take a look at what Db2 does with this.

start
  -- Variable declarations must be "pulled up" to the start
  -- of the block, i.e. earlier than the INSERT assertion
  declare i integer;
  insert into t (a) values (1);

  -- Whereas irrelevant to this instance, the init worth for the
  -- variable declaration should stay on the unique location
  set i = 2;

  -- We'd like a label to have the ability to go away this loop
  alias_1:
  loop
    insert into t (a) values (i);
    set i = (i + 1);
    if i > 10 then

      -- Similar right here, a label is required
      alias_2:
      loop
        go away alias_2;
      finish loop;
      go away alias_1;
    finish if;
  finish loop;
finish

If we don’t use EXIT on a loop, then there gained’t be a label. Or, you may clearly label your loops explicitly, which is at all times really helpful. However typically, you don’t have that in your unique supply code.

What does Oracle do with this?

Oracle has a barely completely different syntax right here:

declare
  i quantity(10);
start
  insert into t (a) values (1);
  i := 2;
  loop
    insert into t (a) values (i);
    i := (i + 1);
    if i > 10 then
      loop
        exit;
      finish loop;
      exit;
    finish if;
  finish loop;
finish;

The principle distinction being that the declaration can be pulled up, however a separate DECLARE block is required to declare variables outdoors of BEGIN .. END. Label-less EXIT is supported natively, so nothing must be reworked right here.

For those who’re considering how this transformations work, learn this submit.

Conclusion

Whether or not you’re migrating off one dialect onto one other, or whether or not you’re supporting a number of dialects without delay, otherwise you’re writing dynamic SQL and dynamic procedural logic, otherwise you similar to writing issues in Java reasonably than native SQL, otherwise you endure from Conway’s Legislation and can’t retailer your procedural code simply, jOOQ can assist you with these endeavours.

For some time now, jOOQ has supported procedural statements as nameless blocks for the most well-liked dialects. Ranging from jOOQ 3.15, we’ll additionally assist storing this logic within the database in a dialect agnostic manner, in addition to parsing / translating procedural code on our web site, or as a library / CLI or JDBC proxy to switch your SQL / procedural code ad-hoc in a legacy JDBC software.

Keep tuned for extra on this thrilling space of jOOQ growth!