Problem with SQL scripts generated for loadUpdateData

General Discussion
1

Hi,


As a background, we run liquibase in our development environments and for production/staging deployments use liquibase (ant task updateDatabase) to generate the SQLs and then run the SQLs manually on the environments.


When generating SQL scripts for tasks that have loadUpdateData, liquibase generates anyonymous blocks of PL/SQL which need a “/” at the end of every block to be able to execute it. The behaviour is the same when the scripts are generated in either of the following ways:


a) java -jar liquibase-2.0.3.jar updateSQL

b) ant task updateDatabase. The task looks as below:

<updateDatabase

                changeLogFile=“changelog.xml”

                driver="${jdbc.driverClassName}"

                url="${jdbc.url}"

                username="${jdbc.username}"

                password="${jdbc.password}"

                promptOnNonLocalDatabase=“false”

                dropFirst=“false”

                classpathref=“classpath.db”

                outputFile=“tmp_db_migrate_output.sql”/>


The actual updates using liquibase (java -jar liquibase-2.0.3.jar update) seem to be fine, the problem is with the generated SQL scripts. When the missing ‘/’ are added after every block, the script works fine. I did read through the liquibase documentation and could not find a way of configuring this behaviour.


I am attaching a small package to reproduce the problem - readme.txt contains all the relevant instructions.


Am I missing something? Is there a workaround ? Do I need to log a defect in JIRA?


Thanks


P.S. >> Had to remove the the ojdbc and  liquibase jars because of size restrictions, we are using liquibase v2.0.3 and ojdbc14-10.2.0.4.jar for connectivity.

2

Anyone ? Do I need to chase this up in a different forum ?

3

Sorry, behind on the forum.


I committed a fix that should add the /. It will be in 2.0.5 which will hopefully be out tomorrow.

Nathan

4

Thanks Nathan, will wait for 2.0.5 to come out.


Regards.

5

We tried to load some data into the DB using loadUpdateData tag and we found that it is appending “/;” after each generated SQL block, which will break if we run updateDatabase task from ant target. 


e.g. 


DECLARE

        v_reccount NUMBER := 0;

BEGIN

        SELECT COUNT(*) INTO v_reccount FROM cupcakes WHERE code = ‘cac’;

        IF v_reccount = 0 THEN

INSERT INTO cupcakes (code, description) VALUES (‘cac’,‘cookies and cream’);

        ELSIF v_reccount = 1 THEN

UPDATE cupcakes SET description = 'cookies and cream ’ WHERE code = ‘cac’;

END IF;

END;

/;


We are using oracle 10.2.0.1.0 running on ubuntu 10.04.


Please advice on how to deal with this problem. Thanks


Andy

6

Hi Nathan / Dev group,


The comments above were logged by Andy who works with me on the same team. Did you have a chance to look into this? Basically, the fix you did resolves the problem with the generated SQL scripts, but causes the ant task to break.


Also, I could not find a corresponding JIRA issue in 2.0.5 release notes for the fix you committed…can you point me in the right direction?


Let me know if you need more info that could help.


Regards,





7

Hey,

you can take a look at this thread http://forum.liquibase.org/topic/loadupdatedata-problem. There is a link to the Jira Task with is tagged with version 2.06 in it (https://liquibase.jira.com/browse/CORE-1170).

Greetings