Creating stored procedure package with sqlFile tag of Liquibase on EnterpriseDB

General Discussion
1

Hi,

We are using EnterpriseDB (PostgreSQL Plus Advanced Server 9.2.1.3) and with Maven and Liquibase we wanted to synchronize database with SQL files. But while creating Stored Procedure Packages, we keep getting errors.

We first tried EnterpriseDB JDBC (edb-jdbc16.jar) but Liquibase gave error for Unknown Database:

  1. Caused by: java.lang.NullPointerException
        at liquibase.database.AbstractDatabase.rollback(AbstractDatabase.java:997)
        at liquibase.lockservice.LockService.acquireLock(LockService.java:124)
        at liquibase.lockservice.LockService.waitForLock(LockService.java:61)
        at liquibase.Liquibase.update(Liquibase.java:102)

We were able to create tables through Liquibase with sqlFile tag and PostgreSQL jdbc driver. You will see the connection settings and pom.xml in attached file. We used :
- liquibase-core 2.0.5
- liquibase-maven-plugin 2.0.5
- postgresql-9.2-1002.jdbc4 (but indeed Database is EnterpriseDB based on PostgreSQL with some Oracle features)

At the moment we are not able to run the package files. For instance ./scripts/002__error_pkg.sql as in attached file.
I have tried a couple of combination in attributes of sqlFile tag but in every case (maybe in different locations) I got following error :

  1. …
  2.     END IF;
        END;

    END;
    /: ERROR: syntax error at end of input
      Position: 93:
              Caused By: ERROR: syntax error at end of input
      Position: 93
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
        at liquibase.Liquibase.update(Liquibase.java:114)
        at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:31)
        at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:24)
        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:305)

I made a run of the file on EnterpriseDB with pgAdmin and it worked fine, created packages and executed other statements. But it looks like we are not able to tell Liquibase what are the statements and what to execute.
Here is a combination that I have tried and received above error:


  1.    
            Create error package
            <sqlFile path="./scripts/002__error_pkg.sql"
                     endDelimiter="\n/\s*\n|\n/\s*$"
                     splitStatements=“false”
                     stripComments=“true” />
       

If I use splitStatements=“true” then the error occurs after Spec of package:

  1.       PROCEDURE store_stacks(p_module character varying, OUT p_seq_num numeric, p_commit_flag boolean DEFAULT false);

    END;: ERROR: syntax error at end of input
      Position: 93:
              Caused By: ERROR: syntax error at end of input
      Position: 93
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
        at liquibase.Liquibase.update(Liquibase.java:114)
        at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:31)
        at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:24)
        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:305)
Could you please help to find the problem or to find a workaround?

Thanks in advance,

seckin

2

I have also tried $BODY$ which is successful while creating a procedure or a function but in this case it is not for a package.

3

I have downloaded liquibase-core source (3.0.0-beta2) and I added attached EnterpriseDBDatabase.java which is mixing PostgreSQL and Oracle features, to the project and built liquibase-core. Later I changed my connection settings so it can connect with EnterpriseDB url and jdbc-driver. The changeset has run successfully. So if you can add EnterpriseDB as supported Database with its own jdbc-driver, this will work. Only attribute, that I have used is splitStatements="false"

ps: I have only tested attached implementation with my liquibase changeset.