endDelimiter for SQLServer?

General Discussion
1

Hi folks,

A couple more issues using our existing changesets with the current trunk. It seems that, following revision 1773 (http://liquibase.jira.com/source/changelog/CORE/?cs=1773), for raw sql changesets in SQLServer, any EOL ("/r/n", “/r” and “/n”) now acts as a statement terminator (where splitStatements has not explicitly been set to false) and that it is no longer possible to override the endDelimiter.

This presents us with a problem, as we have been using semi-colon to delimit blocks of sql. We could modify our changesets, but will this give us a problem when liquibase compares the checksums of changesets?

Also, this change has introduced another problem with statement splitting - we have some empty lines in our sql changesets, which now get passed through as empty sql statements and this causes an exception to be thrown.

Best wishes,

Dean.

2

I’ve created a patch (attached) for the second problem I mentioned - the empty sql statement issue.

Dean.

3

I applied the patch, thanks!

Nathan

4

Hi,

Can this first issue be resolved? Currently all my MSSQL sqlFile scripts are now failing.

If I undo the change introduced by this:

http://liquibase.jira.com/browse/CORE-426

All works as expected again.

The test case is as follows:

changeSet:

    test.sql:

          /*

          NAME        create_site

          DESCRIPTION    Create a new site.

          ARGUMENTS
              p_sitename        Site name for new site
              p_defaultmethodid    Default method ID for new site
              p_localeid        Locale ID for new site

          RETURNS
              Site ID of new (or existing) site.
          */
          IF OBJECT_ID ( SCHEMA_NAME() + ‘.[ms_schema_create_site]’, ‘P’ ) IS NOT NULL
              DROP PROCEDURE ms_schema_create_site;
          GO
         
          CREATE PROCEDURE ms_schema_create_site
              @p_sitename NVARCHAR(32),
              @p_defaultmethodid INT,
              @p_localeid INT
          AS
          BEGIN
              DECLARE @row_count INT
              DECLARE @error_code INT
              DECLARE @v_siteid INT

              – Check for exisiting site
              SELECT    @v_siteid = siteid
              FROM    site
              WHERE    sitename = @p_sitename;

              – Determine whether site needs creating
              SELECT @error_code = @@ERROR, @row_count = @@ROWCOUNT
              IF @error_code != 0 OR @row_count = 0
              BEGIN
                  – Create new site
                  EXEC nextval ‘seq_objectid’, @v_siteid OUTPUT

                  INSERT TOP (1) INTO site (siteid, sitename, defaultmethodid,
                      sessioncookies, authentication, localeid,
                      sitebuild, maxhosts, maxusers, defaultprocessdefid,
                      defaultstatusid)
                  SELECT    @v_siteid, @p_sitename, @p_defaultmethodid, 0, 1,
                      @p_localeid, schemabuild, 0, 0, NULL, NULL
                  FROM    systemdefaults;
              END

              RETURN @v_siteid
          END;
          GO

    Note that the procedure has commands which include ; characters - so we can’t use the default endDelimiter - using GO on its own seems to work -and remains compatible when run as raw SQL. Note that even with CORE-426 removed, this works fine against my SQL Server 2005 server, regardless of the file encoding of the original file. I wonder whether CORE-426 was only an issue against 1.9 - and that it is not need against 2.0?

    Ben

    5

    I’ve now created a JIRA issue (http://liquibase.jira.com/browse/CORE-751) for this problem.

    6

    It should be fixed now

    Nathan