You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by chris oberle <ch...@gmail.com> on 2010/03/25 17:03:45 UTC
MSSQL, schema migrations format issue
Hello,
I have a keen interest in using Ibatis Schema MIgrations but am having an
issue and am hoping someone can tell me if there is anything I can do about
it.
For some reason, when I use the system to define my stored procedures and
views, the text formatting is not preserved. In other words, after the SQL
sucessfully runs and I go to view the procedure or view in my console, most
everything is all on one line regardless of how I have it formatted in my
source for readability.
I'm using these settings in my environment properties:
# If set to true, each statement is isolated
# in its own transaction. Otherwise the entire
# script is executed in one transaction.
auto_commit=false
# This controls how statements are delimited.
# By default statements are delimited by an
# end of line semicolon. Some databases may
# (e.g. MS SQL Server) may require a full line
# delimiter such as GO.
delimiter=GO
full_line_delimiter=true
# This ignores the line delimiters and
# simply sends the entire script at once.
# Use with JDBC drivers that can accept large
# blocks of delimited text at once.
send_full_script=true
Here's an example block of the source:
--// create MyProcedure procedure
-- Migration SQL that makes the change goes here.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_MyProcedure]
@NPA CHAR(3),
@NXX CHAR(3)
AS
IF @NPA IS NULL
BEGIN
RAISERROR('Warning!! @NPA parameter cannot have NULL value passed
in! Procedure has ended!',16,1)
RETURN
END
IF @NXX IS NULL
BEGIN
RAISERROR('Warning!! @NXX parameter cannot have NULL value passed
in! Procedure has ended!',16,1)
RETURN
END
SET NOCOUNT ON;
BEGIN
SELECT DISTINCT P.OCN_CODE
FROM MyDb.dbo.MyTable P
INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
WHERE L6.NPA = @NPA AND l6.NXX = @NXX
END
SET NOCOUNT OFF;
GO
--//@UNDO
-- SQL to undo the change goes here.
DROP PROCEDURE [dbo].[usp_MyProcedure]
GO
Any ideas or help is greatly appreciated!
Thanks!
Re: MSSQL, schema migrations format issue
Posted by chris oberle <ch...@gmail.com>.
Looks like this took care of it. Thanks a million!
- Chris
On Sun, Mar 28, 2010 at 8:55 PM, Clinton Begin <cl...@gmail.com>wrote:
> Okay, I've just committed a change to preserve newline characters in
> scripts.
>
> You can wait for the next release, but I recommend trying it with the trunk
> build.
>
> It's a piece of cake...
>
> Check out: http://svn.apache.org/repos/asf/ibatis/java/ibatis-3/trunk/
>
> Run: mvn assembly:assembly
>
> 1 minute and 21 seconds later you'll have a snapshot build of the
> migrations in:
>
> <ibatis_project_root>/target/ibatis-core-3.0-SNAPSHOT-migrations.zip
>
> Unzip it and you'll recognize the contents to replace your existing
> Migrations implementation.
>
> Clinton
>
> On Sun, Mar 28, 2010 at 4:10 PM, Clinton Begin <cl...@gmail.com>wrote:
>
>> Okay, then yes, it's migrations stripping the newlines.
>>
>> I'll have a look and see if I can add an option to not do so.
>>
>> Clinton
>>
>>
>> On Sun, Mar 28, 2010 at 12:14 PM, chris oberle <ch...@gmail.com>wrote:
>>
>>> Hi Clinton,
>>>
>>> I'm referring to the SQL Server Management Console. Our DBA's use it
>>> along with a bunch of integrated Visual Studio packages. The DBA's are
>>> pretty particular about the text formatting on procedures and view
>>> definitions because they ultimately have to support them. When I use schema
>>> migrations, all of the new lines disappear. I wasn't sure if there was
>>> maybe a JDBC driver issue or if it was something that schema migrations was
>>> doing.
>>>
>>> I've tried saving the scripts in both windows and un*x line ending format
>>> and that didn't seem to have any effect.
>>>
>>>
>>> Thanks!
>>>
>>>
>>> On Fri, Mar 26, 2010 at 11:21 PM, Clinton Begin <clinton.begin@gmail.com
>>> > wrote:
>>>
>>>> What do you mean view in your console?
>>>>
>>>> Clinton
>>>>
>>>>
>>>> On Thu, Mar 25, 2010 at 10:03 AM, chris oberle <ch...@gmail.com>wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I have a keen interest in using Ibatis Schema MIgrations but am having
>>>>> an issue and am hoping someone can tell me if there is anything I can do
>>>>> about it.
>>>>>
>>>>> For some reason, when I use the system to define my stored procedures
>>>>> and views, the text formatting is not preserved. In other words, after the
>>>>> SQL sucessfully runs and I go to view the procedure or view in my console,
>>>>> most everything is all on one line regardless of how I have it formatted in
>>>>> my source for readability.
>>>>>
>>>>> I'm using these settings in my environment properties:
>>>>>
>>>>> # If set to true, each statement is isolated
>>>>> # in its own transaction. Otherwise the entire
>>>>> # script is executed in one transaction.
>>>>> auto_commit=false
>>>>>
>>>>> # This controls how statements are delimited.
>>>>> # By default statements are delimited by an
>>>>> # end of line semicolon. Some databases may
>>>>> # (e.g. MS SQL Server) may require a full line
>>>>> # delimiter such as GO.
>>>>> delimiter=GO
>>>>> full_line_delimiter=true
>>>>>
>>>>> # This ignores the line delimiters and
>>>>> # simply sends the entire script at once.
>>>>> # Use with JDBC drivers that can accept large
>>>>> # blocks of delimited text at once.
>>>>> send_full_script=true
>>>>>
>>>>>
>>>>> Here's an example block of the source:
>>>>>
>>>>> --// create MyProcedure procedure
>>>>> -- Migration SQL that makes the change goes here.
>>>>>
>>>>> SET ANSI_NULLS ON
>>>>> GO
>>>>>
>>>>> SET QUOTED_IDENTIFIER ON
>>>>> GO
>>>>>
>>>>> CREATE PROCEDURE [dbo].[usp_MyProcedure]
>>>>>
>>>>> @NPA CHAR(3),
>>>>> @NXX CHAR(3)
>>>>>
>>>>> AS
>>>>>
>>>>> IF @NPA IS NULL
>>>>> BEGIN
>>>>> RAISERROR('Warning!! @NPA parameter cannot have NULL value
>>>>> passed in! Procedure has ended!',16,1)
>>>>> RETURN
>>>>> END
>>>>>
>>>>> IF @NXX IS NULL
>>>>> BEGIN
>>>>> RAISERROR('Warning!! @NXX parameter cannot have NULL value
>>>>> passed in! Procedure has ended!',16,1)
>>>>> RETURN
>>>>> END
>>>>>
>>>>> SET NOCOUNT ON;
>>>>>
>>>>> BEGIN
>>>>>
>>>>> SELECT DISTINCT P.OCN_CODE
>>>>> FROM MyDb.dbo.MyTable P
>>>>> INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
>>>>>
>>>>> ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
>>>>>
>>>>> WHERE L6.NPA = @NPA AND l6.NXX = @NXX
>>>>>
>>>>> END
>>>>>
>>>>> SET NOCOUNT OFF;
>>>>> GO
>>>>>
>>>>>
>>>>> --//@UNDO
>>>>> -- SQL to undo the change goes here.
>>>>>
>>>>> DROP PROCEDURE [dbo].[usp_MyProcedure]
>>>>> GO
>>>>>
>>>>>
>>>>> Any ideas or help is greatly appreciated!
>>>>>
>>>>>
>>>>> Thanks!
>>>>
>>>>
>>>>
>>>
>>
>
Re: MSSQL, schema migrations format issue
Posted by Clinton Begin <cl...@gmail.com>.
Okay, I've just committed a change to preserve newline characters in
scripts.
You can wait for the next release, but I recommend trying it with the trunk
build.
It's a piece of cake...
Check out: http://svn.apache.org/repos/asf/ibatis/java/ibatis-3/trunk/
Run: mvn assembly:assembly
1 minute and 21 seconds later you'll have a snapshot build of the migrations
in:
<ibatis_project_root>/target/ibatis-core-3.0-SNAPSHOT-migrations.zip
Unzip it and you'll recognize the contents to replace your existing
Migrations implementation.
Clinton
On Sun, Mar 28, 2010 at 4:10 PM, Clinton Begin <cl...@gmail.com>wrote:
> Okay, then yes, it's migrations stripping the newlines.
>
> I'll have a look and see if I can add an option to not do so.
>
> Clinton
>
>
> On Sun, Mar 28, 2010 at 12:14 PM, chris oberle <ch...@gmail.com>wrote:
>
>> Hi Clinton,
>>
>> I'm referring to the SQL Server Management Console. Our DBA's use it
>> along with a bunch of integrated Visual Studio packages. The DBA's are
>> pretty particular about the text formatting on procedures and view
>> definitions because they ultimately have to support them. When I use schema
>> migrations, all of the new lines disappear. I wasn't sure if there was
>> maybe a JDBC driver issue or if it was something that schema migrations was
>> doing.
>>
>> I've tried saving the scripts in both windows and un*x line ending format
>> and that didn't seem to have any effect.
>>
>>
>> Thanks!
>>
>>
>> On Fri, Mar 26, 2010 at 11:21 PM, Clinton Begin <cl...@gmail.com>wrote:
>>
>>> What do you mean view in your console?
>>>
>>> Clinton
>>>
>>>
>>> On Thu, Mar 25, 2010 at 10:03 AM, chris oberle <ch...@gmail.com>wrote:
>>>
>>>> Hello,
>>>>
>>>> I have a keen interest in using Ibatis Schema MIgrations but am having
>>>> an issue and am hoping someone can tell me if there is anything I can do
>>>> about it.
>>>>
>>>> For some reason, when I use the system to define my stored procedures
>>>> and views, the text formatting is not preserved. In other words, after the
>>>> SQL sucessfully runs and I go to view the procedure or view in my console,
>>>> most everything is all on one line regardless of how I have it formatted in
>>>> my source for readability.
>>>>
>>>> I'm using these settings in my environment properties:
>>>>
>>>> # If set to true, each statement is isolated
>>>> # in its own transaction. Otherwise the entire
>>>> # script is executed in one transaction.
>>>> auto_commit=false
>>>>
>>>> # This controls how statements are delimited.
>>>> # By default statements are delimited by an
>>>> # end of line semicolon. Some databases may
>>>> # (e.g. MS SQL Server) may require a full line
>>>> # delimiter such as GO.
>>>> delimiter=GO
>>>> full_line_delimiter=true
>>>>
>>>> # This ignores the line delimiters and
>>>> # simply sends the entire script at once.
>>>> # Use with JDBC drivers that can accept large
>>>> # blocks of delimited text at once.
>>>> send_full_script=true
>>>>
>>>>
>>>> Here's an example block of the source:
>>>>
>>>> --// create MyProcedure procedure
>>>> -- Migration SQL that makes the change goes here.
>>>>
>>>> SET ANSI_NULLS ON
>>>> GO
>>>>
>>>> SET QUOTED_IDENTIFIER ON
>>>> GO
>>>>
>>>> CREATE PROCEDURE [dbo].[usp_MyProcedure]
>>>>
>>>> @NPA CHAR(3),
>>>> @NXX CHAR(3)
>>>>
>>>> AS
>>>>
>>>> IF @NPA IS NULL
>>>> BEGIN
>>>> RAISERROR('Warning!! @NPA parameter cannot have NULL value
>>>> passed in! Procedure has ended!',16,1)
>>>> RETURN
>>>> END
>>>>
>>>> IF @NXX IS NULL
>>>> BEGIN
>>>> RAISERROR('Warning!! @NXX parameter cannot have NULL value
>>>> passed in! Procedure has ended!',16,1)
>>>> RETURN
>>>> END
>>>>
>>>> SET NOCOUNT ON;
>>>>
>>>> BEGIN
>>>>
>>>> SELECT DISTINCT P.OCN_CODE
>>>> FROM MyDb.dbo.MyTable P
>>>> INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
>>>> ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
>>>>
>>>> WHERE L6.NPA = @NPA AND l6.NXX = @NXX
>>>>
>>>> END
>>>>
>>>> SET NOCOUNT OFF;
>>>> GO
>>>>
>>>>
>>>> --//@UNDO
>>>> -- SQL to undo the change goes here.
>>>>
>>>> DROP PROCEDURE [dbo].[usp_MyProcedure]
>>>> GO
>>>>
>>>>
>>>> Any ideas or help is greatly appreciated!
>>>>
>>>>
>>>> Thanks!
>>>
>>>
>>>
>>
>
Re: MSSQL, schema migrations format issue
Posted by Clinton Begin <cl...@gmail.com>.
Okay, then yes, it's migrations stripping the newlines.
I'll have a look and see if I can add an option to not do so.
Clinton
On Sun, Mar 28, 2010 at 12:14 PM, chris oberle <ch...@gmail.com>wrote:
> Hi Clinton,
>
> I'm referring to the SQL Server Management Console. Our DBA's use it along
> with a bunch of integrated Visual Studio packages. The DBA's are pretty
> particular about the text formatting on procedures and view definitions
> because they ultimately have to support them. When I use schema migrations,
> all of the new lines disappear. I wasn't sure if there was maybe a JDBC
> driver issue or if it was something that schema migrations was doing.
>
> I've tried saving the scripts in both windows and un*x line ending format
> and that didn't seem to have any effect.
>
>
> Thanks!
>
>
> On Fri, Mar 26, 2010 at 11:21 PM, Clinton Begin <cl...@gmail.com>wrote:
>
>> What do you mean view in your console?
>>
>> Clinton
>>
>>
>> On Thu, Mar 25, 2010 at 10:03 AM, chris oberle <ch...@gmail.com>wrote:
>>
>>> Hello,
>>>
>>> I have a keen interest in using Ibatis Schema MIgrations but am having an
>>> issue and am hoping someone can tell me if there is anything I can do about
>>> it.
>>>
>>> For some reason, when I use the system to define my stored procedures and
>>> views, the text formatting is not preserved. In other words, after the SQL
>>> sucessfully runs and I go to view the procedure or view in my console, most
>>> everything is all on one line regardless of how I have it formatted in my
>>> source for readability.
>>>
>>> I'm using these settings in my environment properties:
>>>
>>> # If set to true, each statement is isolated
>>> # in its own transaction. Otherwise the entire
>>> # script is executed in one transaction.
>>> auto_commit=false
>>>
>>> # This controls how statements are delimited.
>>> # By default statements are delimited by an
>>> # end of line semicolon. Some databases may
>>> # (e.g. MS SQL Server) may require a full line
>>> # delimiter such as GO.
>>> delimiter=GO
>>> full_line_delimiter=true
>>>
>>> # This ignores the line delimiters and
>>> # simply sends the entire script at once.
>>> # Use with JDBC drivers that can accept large
>>> # blocks of delimited text at once.
>>> send_full_script=true
>>>
>>>
>>> Here's an example block of the source:
>>>
>>> --// create MyProcedure procedure
>>> -- Migration SQL that makes the change goes here.
>>>
>>> SET ANSI_NULLS ON
>>> GO
>>>
>>> SET QUOTED_IDENTIFIER ON
>>> GO
>>>
>>> CREATE PROCEDURE [dbo].[usp_MyProcedure]
>>>
>>> @NPA CHAR(3),
>>> @NXX CHAR(3)
>>>
>>> AS
>>>
>>> IF @NPA IS NULL
>>> BEGIN
>>> RAISERROR('Warning!! @NPA parameter cannot have NULL value passed
>>> in! Procedure has ended!',16,1)
>>> RETURN
>>> END
>>>
>>> IF @NXX IS NULL
>>> BEGIN
>>> RAISERROR('Warning!! @NXX parameter cannot have NULL value passed
>>> in! Procedure has ended!',16,1)
>>> RETURN
>>> END
>>>
>>> SET NOCOUNT ON;
>>>
>>> BEGIN
>>>
>>> SELECT DISTINCT P.OCN_CODE
>>> FROM MyDb.dbo.MyTable P
>>> INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
>>> ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
>>>
>>> WHERE L6.NPA = @NPA AND l6.NXX = @NXX
>>>
>>> END
>>>
>>> SET NOCOUNT OFF;
>>> GO
>>>
>>>
>>> --//@UNDO
>>> -- SQL to undo the change goes here.
>>>
>>> DROP PROCEDURE [dbo].[usp_MyProcedure]
>>> GO
>>>
>>>
>>> Any ideas or help is greatly appreciated!
>>>
>>>
>>> Thanks!
>>
>>
>>
>
Re: MSSQL, schema migrations format issue
Posted by chris oberle <ch...@gmail.com>.
Hi Clinton,
I'm referring to the SQL Server Management Console. Our DBA's use it along
with a bunch of integrated Visual Studio packages. The DBA's are pretty
particular about the text formatting on procedures and view definitions
because they ultimately have to support them. When I use schema migrations,
all of the new lines disappear. I wasn't sure if there was maybe a JDBC
driver issue or if it was something that schema migrations was doing.
I've tried saving the scripts in both windows and un*x line ending format
and that didn't seem to have any effect.
Thanks!
On Fri, Mar 26, 2010 at 11:21 PM, Clinton Begin <cl...@gmail.com>wrote:
> What do you mean view in your console?
>
> Clinton
>
>
> On Thu, Mar 25, 2010 at 10:03 AM, chris oberle <ch...@gmail.com>wrote:
>
>> Hello,
>>
>> I have a keen interest in using Ibatis Schema MIgrations but am having an
>> issue and am hoping someone can tell me if there is anything I can do about
>> it.
>>
>> For some reason, when I use the system to define my stored procedures and
>> views, the text formatting is not preserved. In other words, after the SQL
>> sucessfully runs and I go to view the procedure or view in my console, most
>> everything is all on one line regardless of how I have it formatted in my
>> source for readability.
>>
>> I'm using these settings in my environment properties:
>>
>> # If set to true, each statement is isolated
>> # in its own transaction. Otherwise the entire
>> # script is executed in one transaction.
>> auto_commit=false
>>
>> # This controls how statements are delimited.
>> # By default statements are delimited by an
>> # end of line semicolon. Some databases may
>> # (e.g. MS SQL Server) may require a full line
>> # delimiter such as GO.
>> delimiter=GO
>> full_line_delimiter=true
>>
>> # This ignores the line delimiters and
>> # simply sends the entire script at once.
>> # Use with JDBC drivers that can accept large
>> # blocks of delimited text at once.
>> send_full_script=true
>>
>>
>> Here's an example block of the source:
>>
>> --// create MyProcedure procedure
>> -- Migration SQL that makes the change goes here.
>>
>> SET ANSI_NULLS ON
>> GO
>>
>> SET QUOTED_IDENTIFIER ON
>> GO
>>
>> CREATE PROCEDURE [dbo].[usp_MyProcedure]
>>
>> @NPA CHAR(3),
>> @NXX CHAR(3)
>>
>> AS
>>
>> IF @NPA IS NULL
>> BEGIN
>> RAISERROR('Warning!! @NPA parameter cannot have NULL value passed
>> in! Procedure has ended!',16,1)
>> RETURN
>> END
>>
>> IF @NXX IS NULL
>> BEGIN
>> RAISERROR('Warning!! @NXX parameter cannot have NULL value passed
>> in! Procedure has ended!',16,1)
>> RETURN
>> END
>>
>> SET NOCOUNT ON;
>>
>> BEGIN
>>
>> SELECT DISTINCT P.OCN_CODE
>> FROM MyDb.dbo.MyTable P
>> INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
>> ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
>>
>> WHERE L6.NPA = @NPA AND l6.NXX = @NXX
>>
>> END
>>
>> SET NOCOUNT OFF;
>> GO
>>
>>
>> --//@UNDO
>> -- SQL to undo the change goes here.
>>
>> DROP PROCEDURE [dbo].[usp_MyProcedure]
>> GO
>>
>>
>> Any ideas or help is greatly appreciated!
>>
>>
>> Thanks!
>
>
>
Re: MSSQL, schema migrations format issue
Posted by Clinton Begin <cl...@gmail.com>.
What do you mean view in your console?
Clinton
On Thu, Mar 25, 2010 at 10:03 AM, chris oberle <ch...@gmail.com>wrote:
> Hello,
>
> I have a keen interest in using Ibatis Schema MIgrations but am having an
> issue and am hoping someone can tell me if there is anything I can do about
> it.
>
> For some reason, when I use the system to define my stored procedures and
> views, the text formatting is not preserved. In other words, after the SQL
> sucessfully runs and I go to view the procedure or view in my console, most
> everything is all on one line regardless of how I have it formatted in my
> source for readability.
>
> I'm using these settings in my environment properties:
>
> # If set to true, each statement is isolated
> # in its own transaction. Otherwise the entire
> # script is executed in one transaction.
> auto_commit=false
>
> # This controls how statements are delimited.
> # By default statements are delimited by an
> # end of line semicolon. Some databases may
> # (e.g. MS SQL Server) may require a full line
> # delimiter such as GO.
> delimiter=GO
> full_line_delimiter=true
>
> # This ignores the line delimiters and
> # simply sends the entire script at once.
> # Use with JDBC drivers that can accept large
> # blocks of delimited text at once.
> send_full_script=true
>
>
> Here's an example block of the source:
>
> --// create MyProcedure procedure
> -- Migration SQL that makes the change goes here.
>
> SET ANSI_NULLS ON
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE PROCEDURE [dbo].[usp_MyProcedure]
>
> @NPA CHAR(3),
> @NXX CHAR(3)
>
> AS
>
> IF @NPA IS NULL
> BEGIN
> RAISERROR('Warning!! @NPA parameter cannot have NULL value passed
> in! Procedure has ended!',16,1)
> RETURN
> END
>
> IF @NXX IS NULL
> BEGIN
> RAISERROR('Warning!! @NXX parameter cannot have NULL value passed
> in! Procedure has ended!',16,1)
> RETURN
> END
>
> SET NOCOUNT ON;
>
> BEGIN
>
> SELECT DISTINCT P.OCN_CODE
> FROM MyDb.dbo.MyTable P
> INNER JOIN OtherDB.dbo.[OTHER TABLE] l6
> ON P.field1 = l6.[field1] AND [BLOCK ID] = 'A'
> WHERE L6.NPA = @NPA AND l6.NXX = @NXX
>
> END
>
> SET NOCOUNT OFF;
> GO
>
>
> --//@UNDO
> -- SQL to undo the change goes here.
>
> DROP PROCEDURE [dbo].[usp_MyProcedure]
> GO
>
>
> Any ideas or help is greatly appreciated!
>
>
> Thanks!