Set Delimiter for Exasol

[Sargul]

System information:

• Operating system (distribution) and version: Windows 10
• DBeaver version: devel version

Connection specification:

• Database name and version: Exasol 6.2.12

Describe your question:

I was trying to implement getScriptDelimiterRedefiner/getBlockBoundStrings in the ExasolSQLDialect like DBVisualizer is handling this.

https://docs.exasol.com/connect_exasol/sql_clients/db_visualizer.htm#EXAplustoDbVisualizer

So in DBVisualizer you can define an SQL Block that will be executed as one statement by using this syntax:

--/
CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS
output("debug");
return query([[select * from cat]]);
/

Basically you can define blocks in DBeaver by implementing getBlockBoundStrings however '--/' is not valid at it will first be parsed as comment before it's parsed as SQL Block.

So I tried getScriptDelimiterRedefiner and set it to '--$' and tried to execute a "block"

--$ /
CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS
output("debug");
return query([[select * from cat]]);
/

However this is also parsed as comment and not as execution block.

Is it even possible to override the parsing of this as comment from the Exasol Plugin or are there changes to the core dbeaver plugins necessary (I would rather not do this).

Thx

Kind Regards

[Alexander-at-Blue]

I have the exact same problem, while trying to create a db2 stored procedure.

At least some notation that would allow dbeaver to understand that a set of lines should be executed as a single statement, instead of a series of isolated statements, would simply be great.

[Sargul]

@Alexander-at-Blue :

I've created a PR for implementing DELIMITER keyword for the db2 plugin:

10412

If this is approved you can use a delimiter command in db2 to set the statement terminator within an sql script

DELIMITER /
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
     DECLARE v_numRecords INT DEFAULT 1;
     DECLARE v_counter INT DEFAULT 0;

     DECLARE c1 CURSOR FOR
       SELECT CAST(salary AS DOUBLE)
         FROM staff
         ORDER BY salary;
     DECLARE c2 CURSOR WITH RETURN FOR
       SELECT name, job, CAST(salary AS INTEGER)
         FROM staff
         WHERE salary > medianSalary
         ORDER BY salary;

     DECLARE EXIT HANDLER FOR NOT FOUND
       SET medianSalary = 6666;

     SET medianSalary = 0;
     SELECT COUNT(*) INTO v_numRecords
       FROM STAFF;
     OPEN c1;
     WHILE v_counter < (v_numRecords / 2 + 1)
     DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
     CLOSE c1;
     OPEN c2;
   END
/
DELIMITER ;