Create function from SQL script by Liquibase

liquibase generate sql file
liquibase run sql scripts
liquibase yaml
liquibase sql
liquibase create procedure
liquibase create database
liquibase sql server
liquibase xml to sql

Project configuration:

  • data base - MySQL 5.7
  • orm - Hibernate 4.3.11.Final / JPA 1.3.1.RELEASE
  • Liquibase 3.4.2

My problem dont exist when i run script from workBeanch only from Liquibase.

<changeSet author="newbie" id="function_rad2deg" dbms="mysql,h2">
    <sqlFile encoding="utf8" path="sql/function_rad2deg.sql" relativeToChangelogFile="true"  splitStatements="false" stripComments="false"/>
</changeSet>

My sql script looks like this:

DROP FUNCTION IF EXISTS rad2deg;

DELIMITER //

CREATE FUNCTION rad2deg(rad DOUBLE)
    RETURNS DOUBLE
    BEGIN
        RETURN (rad * 180 / PI());
    END

//

DELIMITER ;

Ok and log:

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 'DELIMITER //

CREATE FUNCTION rad2deg(rad DOUBLE)
    RETURNS DOUBLE
    BEGIN
 ' at line 3 [Failed SQL: DROP FUNCTION IF EXISTS rad2deg;

DELIMITER //

CREATE FUNCTION rad2deg(rad DOUBLE)
    RETURNS DOUBLE
    BEGIN
        RETURN (rad * 180 / PI());
    END

//

DELIMITER ;]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1251)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1234)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:554)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
    at liquibase.Liquibase.update(Liquibase.java:212)
    at liquibase.Liquibase.update(Liquibase.java:192)
    at liquibase.Liquibase.update(Liquibase.java:327)
    at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:33)
    at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:30)
    at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:394)
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80)
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128)
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307)
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193)
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106)
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:862)
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:286)
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:197)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
    at org.codehaus.classworlds.Launcher.main(Launcher.java:47)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 FUNCTION rad2deg(rad DOUBLE)
    RETURNS DOUBLE
    BEGIN
 ' at line 3
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:840)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:740)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299)
    ... 41 more

If you are using yaml file then here is the configuration

changeSet:
  id: sqlFile-function
  author: sandeep
  logicalFilePath: baseFunctionScript
  changes:
  - sqlFile:
      dbms: mysql
      encoding: utf8
      endDelimiter: \nGO
      path: my/path/baseScripts.sql
      splitStatements: true
      stripComments: false

Make sure stripComments should be false else it will throw an error. I spent 4 hours because of it.

createFunction - Liquibase, The SQL creating the procedure. Either this or the path attribute needs to be defined. all, all. relativeToChangelogFile, Whether the file path relative to the root​  To Create your changelog. Create a file in your Liquibase project directory called changelog.sql. For this example, enter the following information into the changelog.sql file.--liquibase formatted sql Step 2: Add a changeset. changeSets are units of change that Liquibase can execute on a database.

I think you need to add the endDelimiter clause in the Liquibase tag, based on the documentation found here

Below is an example

<changeSet author="newbie" id="function_rad2deg" dbms="mysql,h2">
    <sqlFile encoding="utf8" 
             path="sql/function_rad2deg.sql" 
             relativeToChangelogFile="true"  
             splitStatements="false" 
             stripComments="false"
             endDelimiter="\nGO"
    />
</changeSet>

Your SQL file with the above delimiter would then look like

DROP FUNCTION IF EXISTS rad2deg;
GO

CREATE FUNCTION rad2deg(rad DOUBLE)
RETURNS DOUBLE
BEGIN
    RETURN (rad * 180 / PI());
END
GO

Hope this helps

Liquibase : Automating Your SQL and PL/SQL , ORACLE-BASE - Liquibase : Automating Your SQL and PL/SQL Deployments. I think creating a rollback script for each schema change makes sense, but I think CURRVAL); COMMIT; CREATE OR REPLACE FUNCTION get_tab1_count  Generating SQL to Update Database Schemas There are two reasons you would want to generate SQL in Liquibase . You need to know exactly what is being done to your database.

With above example there are two problems one is it will not work on h2 and 2 is splitStatements should be true:

<changeSet author="me" id="01_functions_mysql" dbms="mysql">
    <sqlFile encoding="utf8" path="sql/01_functions.mysql.sql" 
    relativeToChangelogFile="true" 
    splitStatements="true" 
    stripComments="false" 
    endDelimiter="\nGO" />
</changeSet>

and than something like this:

DROP FUNCTION IF EXISTS FIRST_DAY_THIS_MONTH;

GO

CREATE FUNCTION FIRST_DAY_THIS_MONTH (day date) 
RETURNS date
DETERMINISTIC
BEGIN 
  RETURN STR_TO_DATE  ( DATE_FORMAT    ( day,'%Y%m01' ),'%Y%m%d');
END

GO

Creating function using liquibase sql changelog is successful but , I am trying to create function in oracle 12c database using liquibase, below The same thing would happen if you ran the SQL script outside of  Creating function using liquibase sql changelog is successful but function has compilation warning. I am trying to create function in oracle 12c database using liquibase, below is the sql formatted changelog. I am doing negative testing to see what response i get, purposefully using wrong keyword returns. --liquibase formatted sql --changeset your.name:1 failOnError:true CREATE OR REPLACE FUNCTION get_tab1_count RETURNS NUMBER AS l_count NUMBER; BEGIN SELECT COUNT (*) INTO l_count FROM

Introduction to Liquibase and Managing Your Database Source , A changeset that creates PL/SQL function, package and procedure. Note that in Line 3, the dbms="oracle" means this script will only run when  Liquibase : Automating Your SQL and PL/SQL Deployments This article gives a quick overview of applying changes to the database using Liquibase. There is a rewrite of this article using the SQLcl implementation of Liquibase , instead of the Liquibase client.

Database-independent script in Liquibase, SQL Server has getDate() function but Oracle has sysdate. An easy way to create a changeset to set MSG_SEND_DATE of all rows to the current  You can use the “createProcedure” command or the “sqlFile” command. Depending on your database stack, you’ll need to use different techniques to “CREATE OR MODIFY” the procedure or function. You might need to include a separate command in the changeset to drop the object before attempting to create the stored proc or function.

Using Liquibase with SQLcl, environment. Add the oracle-liquibase.jar file in liquibase/lib/ext . the privilege to create a table. CreateOracleFunction, Creates a function from SQL. createProcedure. Defines the definition for a stored procedure. This command is better to use for creating procedures than the raw SQL command because it will not attempt to strip comments or break up lines. Often times it is best to use the CREATE OR REPLACE syntax along with setting runOnChange=’true’ on the enclosing changeset tag. That

Comments
  • you have dbms="mysql,h2" but i do not think it works with h2