end-delimiter
SQL attribute
The end-delimiter
attribute can be set in a sql
or sqlFile
Change Type to override the default value of ;
. The end-delimiter
can be set to ''
or to a character other than ;
to indicate the end of the SQL statement. The attribute is generally used to prevent incomplete SQL statements from being processed when the SQL files contain statements referencing a ;
which Liquibase treats as the default end delimiter.
A corresponding rollback-end-delimiter
applies specifically to rollback for sql
and sqlFile
Change Types.
The end-delimiter
can also be set in the Liquibase properties file by setting delimiter=<string>
.
Uses
The following examples demonstrate use of the end-delimiters
SQL attribute:
Use end-delimiter
in SQL for PROCEDURE
and FUNCTION
An end-delimiter
can be used when the changeset contains SQL to create a stored procedure or function that contains the default ';'
end delimiter. To avoid incomplete statements being sent to the database, the changeset must be marked to have a different end-delimiter
.
Use end-delimiter
for some DBMS systems to run multiple statements
The end-delimiter
must be specified for some dbms systems to run multiple statements.
Setting the end-delimiter
attribute
Escaping an end-delimiter
When setting an end-delimiter
, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$
end-delimiter
with mysql
requires escaping as: end-delimiter="\$\$"
. A Liquibase migration error indicates the delimiter that needs to be escaped would be:
Unexpected error running Liquibase: Migration failed for change set <changelog>::<id>::<author>:
Reason: liquibase.exception.DatabaseException: 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 '$$$

end-delimiter
attribute
--liquibase formatted sql
--changeset your.name:101 end-delimiter:/
CREATE FUNCTION calcProfit2(cost FLOAT, price FLOAT)
RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,2);
SET profit = price-cost;
RETURN profit;
END
/
CREATE FUNCTION calcProfit3(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,3)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,3);
SET profit = price-cost;
RETURN profit;
END
/

end-delimiter
attribute
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.9.xsd">
<changeSet author="your.name" id="sql-example">
<sql dbms="mysql" splitStatements="true" end-delimiter="/" stripComments="true">
DROP FUNCTION IF EXISTS calcProfit;
/
CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,2);
SET profit = price-cost;
RETURN profit;
END
/
</sql>
</changeSet>
</databaseChangeLog>

end-delimiter
attribute
databaseChangeLog:
- changeSet:
id: 1001
author: your.name
changes:
- sql:
end-delimiter: "@@"
splitStatements: true
sql: DROP FUNCTION IF EXISTS calcProfit9; @@ CREATE FUNCTION calcProfit9(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2) DETERMINISTIC BEGIN DECLARE profit DECIMAL(9,2); SET profit = price-cost; RETURN profit; END @@
stripComments: true

end-delimiter
attribute
{
"databaseChangeLog":[
{
"changeSet":{
"id":"1001",
"author":"your.name",
"changes":[
{
"sql":{
"end-delimiter":"@@",
"dbms":"mysql",
"splitStatements":true,
"sql":"DROP FUNCTION IF EXISTS calcProfit;\n@@\nCREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)\nDETERMINISTIC\nBEGIN\nDECLARE profit DECIMAL(9,2);\nSET profit = price-cost;\nRETURN profit;\nEND\n@@",
"stripComments":true
}
}
]
}
}
]
}