split statements

Create stored procedure with SQL tag in Liquibase

Create stored procedure with SQL tag in Liquibasestored procedure

Stored procedures and functions can be easily managed through Liquibase Pro edition. Are we doomed when using the Community one? Oh, no. As with any other database change, it can be done using the <SQL> tag which can work with any SQL code. But Liquibase may complain on an SQL syntax error ...

 

 

Stored procedure in SQL

Any database engine has its own syntax for stored code like procedures, functions, and triggers. It makes it difficult for any parsing tools that try to be universal between various SQL dialects. Here is an example of a statement that creates a stored procedure in MySQL database:

DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
DELIMITER ;

It is worth pointing out that by default SQL uses a semicolon (;) to separate statements in a batch or a script. As a stored procedure may contain multiple statements, they can be separated using semicolons. A problem begins with detecting the end of the stored procedure create statement - the parser may not be sure if a particular semicolon means the end of a statement inside the procedure or the end of the procedure.

That is the reason why the above script defines a new delimiter on the first line. Thanks to that, the parser stops treating further semicolons as delimiters and passes them to the database engine as any other character.

 

Would you like to learn Liquibase? Enroll to my course on Udemy.

liquibase course promo

review

Stored procedure in Liquibase

Liquibase Community does not provide any custom tag for creating a stored procedure, but we can successfully use the <SQL> tag, which comes in handy when no special tag is available.

 

Failed 1st attempt

The thing is that the most straight-forward solution fails with SQL syntax error.

<changeSet id="test 1 - failed" author="DBA presents">
<sql>
DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
DELIMITER ;
</sql>
</changeSet>
Unexpected error running Liquibase: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //
create procedure some_proc (out title int)
' at line 1 [Failed SQL: (1064) DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123]

 Liquibase returned the error because the DELIMITER command is known to MySQL, not Liquibase, so it does not change the delimiter for Liquibase parser.

 

endDelimiter attribute

So maybe we should use a DELIMITER alternative that is actually known to Liquibase? Yes! That is an option. Although it is not a command, but the <SQL> tag has the endDelimiter attribute that we can use. It can be set to the new delimiter string that we want to use. Here is a Liquibase change that does that:

<changeSet id="test 2 - endDelimiter" author="DBA presents">
<sql endDelimiter="//">
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
</sql>
</changeSet>

It works, but we can still make it better.

 

splitStatements attribute

When there is only one statement in the whole <SQL> tag, why to even bother defining a new delimiter? We can simply tell Liquibase to not even try splitting the <SQL> body to separate statements by setting splitStatements to false.

<changeSet id="test 2 - splitStatements" author="DBA presents">
<sql splitStatements="false">
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end
</sql>
</changeSet>

By default the splitStatements attribute is set to true, so Liquibase tries to parse and split all code provided between <SQL> tags. When set to false, Liquibase does not split it, but sends the whole directly to the database engine.

We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.