Can liquibase syntactically validate the SQL generated using updateSql command?

0
votes

I found 2 issues per se when I ran the updateSQL commandline in Liquibase

  1. The last statement in Liquibase updateSQL output viz Insert into DBCHANGELOG table does not commit automatically when the sql is run via sqlplus commandline

    As a result of this, though the changeset gets executed, the DBCHANGELOG table does not have the insert statement to record it. So when I run the updateSQL once again, the last changeset is once again created in the SQL output which is incorrect.

  2. Liquibase does not validate / check syntax errors in SQL.

    As a result of this, even if the changeset SQL fails, the insert to DBChangeLog table for the changeset succeeds which is incorrect. Is there a way that the insert statement following the changeset be stopped / failed if the changeset SQL actually failed ?

Any help is greatly appreciated... we are this close to getting Liquibase implemented... !!

2
sqldatabaseoracleliquibase
liquibase.jira.com/browse/CORE-1653 will hopefully make it into liquibase 3.3.0 which will help by adding a "commit" statement at the end of each changeSet in updateSql – Nathan Voxland

2 Answers

3
votes

To answer the question in your subject line, no, Liquibase cannot validate the SQL. Liquibase supports many different databases, and each has different SQL syntax.

If you can, stop using the SQL generated by updateSQL to actually do the updates, and use Liquibase itself to do the updates. That way Liquibase can detect errors and behave more properly. I recommend that if DBAs are scared of Liquibase touching the database that teams use the generateSQL as a pre-check to see what Liquibase will do, but let Liquibase do its job.

1
votes

I also find best practice of Liquibase is not to use a SQL script but to manually write the Liquibase XML file for the change.

I've tried using the ExecuteCommand tag to lunch sqlplus or sqlcmd (as I know my target database) and it has a bug which as for now it is closed?! (but this is open source, so I can't complain :) )

Having said that, I found that working on XML to specify the changes causes many other challenges, for example: 1. Making sure that every change was included in the changelog xml file. I've heard many organizations who forget to add the file to the changelog. 2. Making sure the file for the specific change is always in sync with the file-based version control. Imagine what will happen if it doesn't - which happens to many of my customers... 3. Wasted time spent on merging changelogs between different environments (branches, UAT - critical fixes, sandboxes, etc...)