dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO #69

almothafar · 2019-07-21T09:32:51Z

I'm using Play Framework with Ebean plugin, which is using Ebean DB Migration to generate scripts:

SpiEbeanServer spiServer = (SpiEbeanServer) server;
CurrentModel ddl = new CurrentModel(spiServer);

String ups = ddl.getCreateDdl();
String downs = ddl.getDropAllDdl();

https://github.com/playframework/play-ebean/blob/6877301da0121ced9016c08b15e7ae2993b1e484/play-ebean/src/main/java/play/db/ebean/EbeanDynamicEvolutions.java#L90

My problem is that when I use com.microsoft.sqlserver.jdbc.SQLServerDriver the script generated contains delimiter $$ and $$which is not correct for SQL Server:

# --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions

# --- !Ups

-- init script create procs
-- Initial script to create stored procedures etc for sqlserver platform

-- create table-value-parameters
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name  from sys.types where name = 'ebean_float_tvp') create type ebean_float_tvp as table (c1 float);
if not exists (select name  from sys.types where name = 'ebean_bit_tvp') create type ebean_bit_tvp as table (c1 bit);
if not exists (select name  from sys.types where name = 'ebean_date_tvp') create type ebean_date_tvp as table (c1 date);
if not exists (select name  from sys.types where name = 'ebean_time_tvp') create type ebean_time_tvp as table (c1 time);
if not exists (select name  from sys.types where name = 'ebean_uniqueidentifier_tvp') create type ebean_uniqueidentifier_tvp as table (c1 uniqueidentifier);
if not exists (select name  from sys.types where name = 'ebean_nvarchar_tvp') create type ebean_nvarchar_tvp as table (c1 nvarchar(max));

delimiter $$
--
-- PROCEDURE: usp_ebean_drop_indices TABLE, COLUMN
-- deletes all indices referring to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_indices @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @indexName nvarchar(255)
BEGIN
  DECLARE index_cursor CURSOR FOR SELECT i.name from sys.indexes i
    join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
    join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
    where i.object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
  OPEN index_cursor
  FETCH NEXT FROM index_cursor INTO @indexName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'drop index ' + @indexName + ' on ' + @tableName;
      EXECUTE(@sql);

      FETCH NEXT FROM index_cursor INTO @indexName
    END;
  CLOSE index_cursor;
  DEALLOCATE index_cursor;
END
$$

delimiter $$
--
-- PROCEDURE: usp_ebean_drop_default_constraint TABLE, COLUMN
-- deletes the default constraint, which has a random name
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_default_constraint @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @tmp nvarchar(1000)
BEGIN
  select @tmp = t1.name from sys.default_constraints t1
    join sys.columns t2 on t1.object_id = t2.default_object_id
    where t1.parent_object_id = OBJECT_ID(@tableName) and t2.name = @columnName;

  if @tmp is not null EXEC('alter table ' + @tableName +' drop constraint ' + @tmp);
END
$$

delimiter $$
--
-- PROCEDURE: usp_ebean_drop_constraints TABLE, COLUMN
-- deletes constraints and foreign keys refering to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_constraints @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @constraintName nvarchar(255)
BEGIN
  DECLARE name_cursor CURSOR FOR
  SELECT cc.name from sys.check_constraints cc
    join sys.columns c on c.object_id = cc.parent_object_id and c.column_id = cc.parent_column_id
    where parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName
  UNION SELECT fk.name from sys.foreign_keys fk
    join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
      and  fkc.parent_object_id = fk.parent_object_id
    join sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
    where fkc.parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName;

  OPEN name_cursor
  FETCH NEXT FROM name_cursor INTO @constraintName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'alter table ' + @tableName + ' drop constraint ' + @constraintName;
      EXECUTE(@sql);

      FETCH NEXT FROM name_cursor INTO @constraintName
    END;
  CLOSE name_cursor;
  DEALLOCATE name_cursor;
END
$$

delimiter $$
--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column annd ensures that all indices and constraints are dropped first
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_column @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
BEGIN
  EXEC usp_ebean_drop_indices @tableName, @columnName;
  EXEC usp_ebean_drop_default_constraint @tableName, @columnName;
  EXEC usp_ebean_drop_constraints @tableName, @columnName;

  set @sql = 'alter table ' + @tableName + ' drop column ' + @columnName;
  EXECUTE(@sql);
END
$$
-- ... create tables scripts ...

The text was updated successfully, but these errors were encountered:

rbygrave · 2019-07-21T09:52:01Z

The problem here is not that the sql is invalid but that the play-ebean plugin is unable to parse and execute the scripts right.

That is, the Ebean's ddl runner can execute these scripts but the ddl runner used by play-ebean can not.

What is the actual error you see? I'm expecting it to be that the play-ebean ddl runner is unable to parse the script. Run those scripts directly against the DB or via Ebean and they will be executed.

almothafar · 2019-07-21T11:30:12Z

Well, I even see that error on console when I try to apply it manually, also, when I use IDE Microsoft SQL Dialect:

The other issue you mention is correct for Postgres, and to work around ebean issue I simply remove delimiter $$ and any ; inside the body of procedure.

Manually I was able to run other script contains delimiter $$ generated for Postgres but for SQL Server I get [S0001][102] Incorrect syntax near '$' error.

…eate_procs.sql Note that this test will run slowly unless the docker images has already been pulled from docker hub.

rbygrave · 2019-07-21T12:28:45Z

Update the test to run the sql server I__create_procs.sql

Runs successfully with output:

[TestNG] Running:
  /home/rob/.IntelliJIdea2019.1/system/temp-testng-customsuite.xml
[main] INFO io.ebean.docker.commands.Commands - Run container test_ebean_migration_sql17 with port:2433 db:mig_test user:mig_test mode:DropCreate shutdown:
[main] INFO io.ebean.DDL - Executing create migration table - 1 statements
[main] INFO io.ebean.migration.MigrationRunner - local migrations:3  existing migrations:0  checkState:false
[main] INFO io.ebean.DDL - Executing run migration version: I__create_procs - 11 statements
[main] INFO io.ebean.DDL - Executing run migration version: 1.1__initial - 2 statements
[main] INFO io.ebean.DDL - Executing run migration version: 1.2__add_m3 - 2 statements

You could git pull master to get this updated test and run: MigrationRunner_platform_Test.sqlServer_migration() which runs all the migrations contained in src/test/resources/dbmig_sqlserver

This is using sql server 2017 docker image mcr.microsoft.com/mssql/server:2017-GA-ubuntu

almothafar · 2019-07-21T12:47:49Z

This is strange, I tried your script, but without luck, got: [S0001][102] Incorrect syntax near 'delimiter'.

And I'm using docker image as well: mcr.microsoft.com/mssql/server:2017-latest

Full error:

[2019-07-21 15:45:43] Run /Users/almothafar/.../conf/evolutions/default/1.sql
[2019-07-21 15:45:43] Connecting to Microsoft SQL Server - @dev_msdb...
-- create table-value-parameters
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name  from sys.types where name = 'ebean_float_tvp') creat...
[2019-07-21 15:45:48] [S0001][102] Incorrect syntax near 'delimiter'.
[2019-07-21 15:45:48] Summary: 1 of 1 statements executed, 1 failed in 5 s 90 ms (4336 symbols in file)

Note: I just used the file 1.sql but the content fully overwritten with the script I copied from https://github.com/ebean-orm/ebean-migration/blob/09ca161dc246d47c1786b0f1f69c267ea3b5b5e7/src/test/resources/dbmig_sqlserver/I__create_procs.sql .

rbygrave · 2019-07-21T21:31:21Z

The ddl runner being used by play-ebean ... isn't parsing those multiple statements correctly by the looks.

…

On Mon, 22 Jul 2019 at 00:47, Al-Mothafar Al-Hasan ***@***.***> wrote: This is strange, I tried your script, but without luck, got: [S0001][102] Incorrect syntax near 'delimiter'. And I'm using docker image as well: mcr.microsoft.com/mssql/server:2017-latest Full error: [2019-07-21 15:45:43] Run /Users/almothafar/Sources/caso-auth/conf/evolutions/default/1.sql[2019-07-21 15:45:43] Connecting to Microsoft SQL Server - @dev_msdb...-- create table-value-parametersif not exists (select name from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint);if not exists (select name from sys.types where name = 'ebean_float_tvp') creat...[2019-07-21 15:45:48] [S0001][102] Incorrect syntax near 'delimiter'.[2019-07-21 15:45:48] Summary: 1 of 1 statements executed, 1 failed in 5 s 90 ms (4336 symbols in file) [image: image] <https://user-images.githubusercontent.com/13887564/61591359-d532ce00-abce-11e9-8ed9-ca0c5a082bcf.png> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#69?email_source=notifications&email_token=AABTATMFINH5QRUS3MLFVNLQARLHLA5CNFSM4IFRT2EKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2OCZFY#issuecomment-513551511>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABTATIUOITKRZT4WLNFF43QARLHLANCNFSM4IFRT2EA> .

almothafar · 2019-07-22T08:44:22Z

But as I said, I copied your script in the test, and run it with DataGrip -> Run SQL Script, what play-ebean has to do with this?

rbygrave · 2019-07-22T10:00:08Z

what play-ebean has to do with this?

The issue logged is using play-ebean - that is the code link that is included in the issue description. If the ddl script also fails with native sql server tools then that is a different matter.

almothafar · 2019-07-22T12:18:00Z

@rbygrave yup, the issue is about delimiter $$ with native SQL server tools too, I started docker image with MS SQL server 2017, and tried to run the script using the tool (DataGrip in this case).

The issue you were talking about is already known, and I'm not talking about it, you can see it is opened for play-ebean already and you commented there in the past: playframework/play-ebean#166 so that issue is not ebean issue but play-ebean plugin, I mentioned play-ebean as the workaround for that issue worked for Postgres but not for SQL Server because of another issue that I found (and this issue is this one), sorry for the confusion.

rbygrave · 2019-07-22T21:09:18Z

DataGrip isn't a MS SQL Server tool though right? It's a IntelliJ IDEA tool.

rbygrave · 2019-07-22T21:21:24Z

Hmmm, ok I think I see ... I'll have a look ...

almothafar · 2019-07-23T13:04:11Z

There is no SSMS for macOS, so I'm using IntelliJ tool, and it supports SQL Server, to be sure I'll try AzureDataStudio, but meanwhile, I don't see any resource that mentions that SQL Server support adding delimiter using delimiter $$ keyword as Postgres and MySQL do.

almothafar · 2019-07-23T13:05:29Z

And this is Azure Data Studio:

I manage to run a script using tools after adding GO and remove delimiter $$ and $$ (which is ebean issue):

-- Initial script to create stored procedures etc for sqlserver platform

-- create table-value-parameters
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp')
create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name  from sys.types where name = 'ebean_float_tvp')
create type ebean_float_tvp as table (c1 float);
if not exists (select name  from sys.types where name = 'ebean_bit_tvp')
create type ebean_bit_tvp as table (c1 bit);
if not exists (select name  from sys.types where name = 'ebean_date_tvp')
create type ebean_date_tvp as table (c1 date);
if not exists (select name  from sys.types where name = 'ebean_time_tvp')
create type ebean_time_tvp as table (c1 time);
if not exists (select name  from sys.types where name = 'ebean_uniqueidentifier_tvp')
create type ebean_uniqueidentifier_tvp as table (c1 uniqueidentifier);
if not exists (select name  from sys.types where name = 'ebean_nvarchar_tvp')
create type ebean_nvarchar_tvp as table (c1 nvarchar(max));
GO
--
-- PROCEDURE: usp_ebean_drop_indices TABLE, COLUMN
-- deletes all indices referring to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_indices @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @indexName nvarchar(255)
BEGIN
  DECLARE index_cursor CURSOR FOR SELECT i.name from sys.indexes i
    join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
    join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
    where i.object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
  OPEN index_cursor
  FETCH NEXT FROM index_cursor INTO @indexName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'drop index ' + @indexName + ' on ' + @tableName;
      EXECUTE(@sql);

      FETCH NEXT FROM index_cursor INTO @indexName
    END;
  CLOSE index_cursor;
  DEALLOCATE index_cursor;
END
GO

--
-- PROCEDURE: usp_ebean_drop_default_constraint TABLE, COLUMN
-- deletes the default constraint, which has a random name
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_default_constraint @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @tmp nvarchar(1000)
BEGIN
  select @tmp = t1.name from sys.default_constraints t1
    join sys.columns t2 on t1.object_id = t2.default_object_id
    where t1.parent_object_id = OBJECT_ID(@tableName) and t2.name = @columnName;

  if @tmp is not null EXEC('alter table ' + @tableName +' drop constraint ' + @tmp);
END
GO
--
-- PROCEDURE: usp_ebean_drop_constraints TABLE, COLUMN
-- deletes constraints and foreign keys refering to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_constraints @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @constraintName nvarchar(255)
BEGIN
  DECLARE name_cursor CURSOR FOR
  SELECT cc.name from sys.check_constraints cc
    join sys.columns c on c.object_id = cc.parent_object_id and c.column_id = cc.parent_column_id
    where parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName
  UNION SELECT fk.name from sys.foreign_keys fk
    join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
      and  fkc.parent_object_id = fk.parent_object_id
    join sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
    where fkc.parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName;

  OPEN name_cursor
  FETCH NEXT FROM name_cursor INTO @constraintName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'alter table ' + @tableName + ' drop constraint ' + @constraintName;
      EXECUTE(@sql);

      FETCH NEXT FROM name_cursor INTO @constraintName
    END;
  CLOSE name_cursor;
  DEALLOCATE name_cursor;
END
GO

--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column annd ensures that all indices and constraints are dropped first
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_column @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
BEGIN
  EXEC usp_ebean_drop_indices @tableName, @columnName;
  EXEC usp_ebean_drop_default_constraint @tableName, @columnName;
  EXEC usp_ebean_drop_constraints @tableName, @columnName;

  set @sql = 'alter table ' + @tableName + ' drop column ' + @columnName;
  EXECUTE(@sql);
END
GO

Result:

While it was working in play-ebean because they split statements depend on ; so GO not needed there, what I did the workaround is to remove ; inside the procedure body and add it after END (which is not your issue):

-- init script create procs
-- Initial script to create stored procedures etc for sqlserver platform

-- create table-value-parameters
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name  from sys.types where name = 'ebean_float_tvp') create type ebean_float_tvp as table (c1 float);
if not exists (select name  from sys.types where name = 'ebean_bit_tvp') create type ebean_bit_tvp as table (c1 bit);
if not exists (select name  from sys.types where name = 'ebean_date_tvp') create type ebean_date_tvp as table (c1 date);
if not exists (select name  from sys.types where name = 'ebean_time_tvp') create type ebean_time_tvp as table (c1 time);
if not exists (select name  from sys.types where name = 'ebean_uniqueidentifier_tvp') create type ebean_uniqueidentifier_tvp as table (c1 uniqueidentifier);
if not exists (select name  from sys.types where name = 'ebean_nvarchar_tvp') create type ebean_nvarchar_tvp as table (c1 nvarchar(max));

--
-- PROCEDURE: usp_ebean_drop_indices TABLE, COLUMN
-- deletes all indices referring to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_indices @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @indexName nvarchar(255)
BEGIN
  DECLARE index_cursor CURSOR FOR SELECT i.name from sys.indexes i
    join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
    join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
    where i.object_id = OBJECT_ID(@tableName) AND c.name = @columnName
  OPEN index_cursor
  FETCH NEXT FROM index_cursor INTO @indexName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'drop index ' + @indexName + ' on ' + @tableName
      EXECUTE(@sql)

      FETCH NEXT FROM index_cursor INTO @indexName
    END
  CLOSE index_cursor
  DEALLOCATE index_cursor
END;

--
-- PROCEDURE: usp_ebean_drop_default_constraint TABLE, COLUMN
-- deletes the default constraint, which has a random name
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_default_constraint @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @tmp nvarchar(1000)
BEGIN
  select @Tmp = t1.name from sys.default_constraints t1
    join sys.columns t2 on t1.object_id = t2.default_object_id
    where t1.parent_object_id = OBJECT_ID(@tableName) and t2.name = @columnName

  if @Tmp is not null EXEC('alter table ' + @tableName +' drop constraint ' + @tmp)
END;

--
-- PROCEDURE: usp_ebean_drop_constraints TABLE, COLUMN
-- deletes constraints and foreign keys refering to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_constraints @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @constraintName nvarchar(255)
BEGIN
  DECLARE name_cursor CURSOR FOR
  SELECT cc.name from sys.check_constraints cc
    join sys.columns c on c.object_id = cc.parent_object_id and c.column_id = cc.parent_column_id
    where parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName
  UNION SELECT fk.name from sys.foreign_keys fk
    join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
      and  fkc.parent_object_id = fk.parent_object_id
    join sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
    where fkc.parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName

  OPEN name_cursor
  FETCH NEXT FROM name_cursor INTO @constraintName
  WHILE @@FETCH_STATUS = 0
    BEGIN
      set @sql = 'alter table ' + @tableName + ' drop constraint ' + @constraintName
      EXECUTE(@sql)

      FETCH NEXT FROM name_cursor INTO @constraintName
    END
  CLOSE name_cursor
  DEALLOCATE name_cursor
END;

--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column annd ensures that all indices and constraints are dropped first
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_column @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
BEGIN
  EXEC usp_ebean_drop_indices @tableName, @columnName
  EXEC usp_ebean_drop_default_constraint @tableName, @columnName
  EXEC usp_ebean_drop_constraints @tableName, @columnName

  set @sql = 'alter table ' + @tableName + ' drop column ' + @columnName
  EXECUTE(@sql)
END;

I hope I'm helping with this issue with my scenarios.

rbygrave · 2019-07-23T21:29:06Z

That's great thanks. I've reproduced with the command line tool sqlcmd ... so yes, working on a fix.
Thanks for the above - that has made it very clear.

Cheers, Rob.

rbygrave · 2019-07-24T10:55:11Z

So the "fix" for this is to change the sql server script to use GO. Ebean's DDL parser is modified to detect GO as statement delimiter.

This means that the DDL ought to be safe for execution by sql server tools, command line etc.

FYI @rPraml ... would be good for you to have a look at this if/when you get a chance - thanks.

…ures script - change to use GO

rbygrave · 2019-07-24T11:03:48Z

FYI: The create_procs script for sql server will become: https://github.com/ebean-orm/ebean-migration/blob/master/src/test/resources/dbmig_sqlserver/I__create_procs.sql

rbygrave · 2019-07-24T11:39:30Z

@almothafar If you get a chance would be great to confirm that updated ddl script that uses GO instead will work with play-ebean. It looks all good from my testing.

See here: ebean-orm/ebean@5c38561#diff-f3f4bc912a46a099dc85c620c2dc93c6R7

Cheers, Rob.

almothafar · 2019-07-24T17:44:13Z

@rbygrave the script seems ok with native tools, but not going to work in play-ebean, the problem already discussed in playframework/play-ebean#166

The solution for it is to split using GO instead of semi-colons ; in case SQL server driver used, but this is play-ebean issue, for this issue I meant only to report SQL Server issue with ebean.

BTW, I forgot to test create table part and downs, will check them later tomorrow.

rPraml · 2019-07-25T06:55:53Z

@rbygrave I've reviewed this under the aspect of backward compatibility

as far as I see, delimiter $$ is still supported for SqlServer for old migration scripts, so they do not have to get converted. (This is important for us)
We have still some auto generated migration scripts that use delimiter, before usp_ebean_drop_default_constraint was introduced:

delimiter $$
DECLARE @Tmp nvarchar(200);select @Tmp = t1.name  from sys.default_constraints t1
  join sys.columns t2 on t1.object_id = t2.default_object_id
  where t1.parent_object_id = OBJECT_ID('mytable') and t2.name = 'mycolumn';
if @Tmp is not null EXEC('alter table mytable drop constraint ' + @Tmp)$$;

almothafar · 2019-07-25T09:26:35Z

@rPraml delimiter $$ is rejected in SQL Server 2012 and 2017 (tested both), and causes errors when I run the script run natively.

Edit: @rbygrave tested downs and ups with create tables and drop tables, types and so on without any issues.

rbygrave · 2019-07-25T09:34:14Z

Yeah, the prior commit included a regression. Pushing fix for that now.

rbygrave · 2019-07-25T10:57:08Z

as far as I see, delimiter $$ is still supported for SqlServer for old migration scripts, so they do not have to get converted. (This is important for us)

Yes that is correct, that is the intention. Anything out there using the $$ should still be all good.

rbygrave added a commit that referenced this issue Jul 21, 2019

Modify test for #69 such that sql server platform test includes I__cr…

09ca161

…eate_procs.sql Note that this test will run slowly unless the docker images has already been pulled from docker hub.

rbygrave changed the title ~~dbmigration adding delimiter $$ for Microsoft SQL Server procedures script~~ dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO Jul 24, 2019

rbygrave added a commit that referenced this issue Jul 24, 2019

#69 - dbmigration adding delimiter $$ for Microsoft SQL Server proced…

2af8ea4

…ures script - change to use GO

rbygrave added a commit that referenced this issue Jul 24, 2019

#69 - dbmigration adding delimiter $$ for Microsoft SQL Server proced…

afe032e

…ures script - change to use GO

rbygrave self-assigned this Jul 24, 2019

rbygrave added the bug label Jul 24, 2019

rbygrave added this to the 11.18.2 milestone Jul 24, 2019

rbygrave closed this as completed Jul 24, 2019

rbygrave mentioned this issue Jul 24, 2019

Change built in SQL Server I___create_procs DDL script to use GO (and not $$ delimiters) ebean-orm/ebean#1774

Closed

rbygrave reopened this Jul 25, 2019

rbygrave added a commit that referenced this issue Jul 25, 2019

#69 - Extra tests for regression introduced by the prior commit

3c3b6ee

rbygrave added a commit that referenced this issue Jul 25, 2019

#69 - Fix for regression

ee725ab

rbygrave modified the milestones: 11.18.2, 11.19.1 Jul 25, 2019

rbygrave closed this as completed Jul 25, 2019

rbygrave mentioned this issue Aug 24, 2019

Change MySQL DDL to not use helper stored procedures by default - was auto-generated script containing create procedures fails ebean-orm/ebean#1802

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO #69

dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO #69

almothafar commented Jul 21, 2019

rbygrave commented Jul 21, 2019

almothafar commented Jul 21, 2019 •

edited

Loading

rbygrave commented Jul 21, 2019

almothafar commented Jul 21, 2019 •

edited

Loading

rbygrave commented Jul 21, 2019 via email

almothafar commented Jul 22, 2019

rbygrave commented Jul 22, 2019 •

edited

Loading

almothafar commented Jul 22, 2019 •

edited

Loading

rbygrave commented Jul 22, 2019 via email •

edited

Loading

rbygrave commented Jul 22, 2019

almothafar commented Jul 23, 2019

almothafar commented Jul 23, 2019 •

edited

Loading

rbygrave commented Jul 23, 2019

rbygrave commented Jul 24, 2019

rbygrave commented Jul 24, 2019

rbygrave commented Jul 24, 2019

almothafar commented Jul 24, 2019 •

edited

Loading

rPraml commented Jul 25, 2019

almothafar commented Jul 25, 2019 •

edited

Loading

rbygrave commented Jul 25, 2019

rbygrave commented Jul 25, 2019

dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO #69

dbmigration adding delimiter $$ for Microsoft SQL Server procedures script - change to use GO #69

Comments

almothafar commented Jul 21, 2019

rbygrave commented Jul 21, 2019

almothafar commented Jul 21, 2019 • edited Loading

rbygrave commented Jul 21, 2019

almothafar commented Jul 21, 2019 • edited Loading

rbygrave commented Jul 21, 2019 via email

almothafar commented Jul 22, 2019

rbygrave commented Jul 22, 2019 • edited Loading

almothafar commented Jul 22, 2019 • edited Loading

rbygrave commented Jul 22, 2019 via email • edited Loading

rbygrave commented Jul 22, 2019

almothafar commented Jul 23, 2019

almothafar commented Jul 23, 2019 • edited Loading

rbygrave commented Jul 23, 2019

rbygrave commented Jul 24, 2019

rbygrave commented Jul 24, 2019

rbygrave commented Jul 24, 2019

almothafar commented Jul 24, 2019 • edited Loading

rPraml commented Jul 25, 2019

almothafar commented Jul 25, 2019 • edited Loading

rbygrave commented Jul 25, 2019

rbygrave commented Jul 25, 2019

almothafar commented Jul 21, 2019 •

edited

Loading

almothafar commented Jul 21, 2019 •

edited

Loading

rbygrave commented Jul 22, 2019 •

edited

Loading

almothafar commented Jul 22, 2019 •

edited

Loading

rbygrave commented Jul 22, 2019 via email •

edited

Loading

almothafar commented Jul 23, 2019 •

edited

Loading

almothafar commented Jul 24, 2019 •

edited

Loading

almothafar commented Jul 25, 2019 •

edited

Loading