createProcedure and sybase

Liquibase Development
1

Hello

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

On a page http://www.liquibase.org/manual/create_stored_procedure 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 http://www.liquibase.org/manual/create_stored_procedure .

Thanks,
Maciek

2

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.

Nathan

3

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
else
    CREATE procedure

4

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

CREATE PROCEDURE dbo.sp_blah
AS
DECLARE @ReopenState INT
DECLARE @FixedState INT

BEGIN
…
END                                                                                                                                                           

go

Grant Execute on dbo.sp_blah to foo
go

sp_procxmode ‘sp_blah’, unchained
go

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

5

lance.java,

Do not use createProcedure tag, but . see http://liquibase.org/manual/custom_sql

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