createProcedure and sybase


Sorry if I’m contributing this in a wrong place.

On a page it is said that Sybase doesn’t have problems with statement in change set. In fact it does. Sybase doesn’t support CREATE OR REPLACE PROCEDURE statement. Sproc needs to be dropped first and then created. Due to JDBC nature of Liquibase both statements cannot go together in one DB update instruction.

This is kind of a bugger, because every statement needs to have it’s own execution. There is a workaround for above, which can look like this:

              if exists(select * from sysobjects where name = 'your-stored-proc-X' and type = 'P') drop procedure your-stored-proc-X           Stored Proc code with proper XML escaping.     Sproc GRANT statement   Sproc PERMISSION statement   etc.

It’s not the cleanest solution but works.

In our case, because we have way too many old stored procs (to maintain and potentially convert) we went with calling isql from Maven mojo solution for every Sproc file (isql supports multiple statements and GO keyword).

Because of above complications I’d suggest changing comment for Sybase at .


We could have the if exists drop statement auto-included for sybase if using the createProcedure with the replaceIfExists attribute, but from your example it looks like you would still need to re-do the grant and permission statements, correct? 

If that is the case, it is probably better to not allow the replaceIfExists statement for sybase since the permission loosing would be a major functional difference from one database to the next.


Sybase support ALTER procedure … syntax, but not CREATE or ALTER procedure … :frowning:
To make this in live first we have to read the metadata from the current database before execute the changelog. This was early discussed, but you do not want to implement it.
Another way is generate a tricky code  such as
if exists (select 1 from sysprocedures where …)
    ALTER procedure
    CREATE procedure

Is there any way in Liquibase that you can specify a statement delimiter?

eg. If the statement deleimiter was “go”
And you had the following:

IF OBJECT_ID('sp_blah') IS NOT NULL BEGIN     DROP procedure sp_blah END go

DECLARE @ReopenState INT




Grant Execute on dbo.sp_blah to foo

sp_procxmode ‘sp_blah’, unchained

Can you tell Liquibase to execute 4 statements instead of 1?,

Do not use createProcedure tag, but . see

you are interested in the attribute endDelimiter=“go”

NOTE: latest snapshot of the liquibase is case-sensitive to the delimiter, 1.9.5 - no.

Cheers, Oleg