Test doesnt get executed properly

21 views
Skip to first unread message

Matthias Melzner

unread,
Sep 3, 2021, 9:30:08 AM9/3/21
to SQL Workbench/J - DBMS independent SQL tool
Hi,
I tried to run a small test in the Statement1 tab of the SQL workbench:

DROP PROCEDURE IF EXISTS stp_TestFM;

-- Stored Procedure in MS SQL
CREATE PROCEDURE stp_TestFM
AS SET NOCOUNT ON;
BEGIN
   PRINT 'I am a test: abc'
END;
 
EXECUTE stp_TestFM


When I execute my test the workbench does't print the expected message in the Messages tab. 

Thank you for the help in advance!

Thomas Kellerer

unread,
Sep 3, 2021, 11:06:49 AM9/3/21
to sql-wo...@googlegroups.com
There is no API (neither "official" nor "unoffical") to retrieve those messages through JDBC (at least I am not aware of anything).

SQL Server used to return these on the Statement object as warnings, but apparently this doesn't happen anymore.
See SqlCommand.appendWarnings() and SqlUtils.getWarnings()

Oracle has a defined API through dbms_output to retrieve message like that and Postgres does return those messages as SQLWarnings on the Statement.

If SQL Server stopped doing that, I have no idea why and how to re-activate the old behaviour.

Regards
Thomas

Franz Mayer

unread,
Sep 15, 2021, 6:20:03 AM9/15/21
to SQL Workbench/J - DBMS independent SQL tool
Hi Thomas,

I just retried it. And the problem apparently seems to be the semi-colon after the SET command.

When I execute it in Workbench as following it works like a charm:


CREATE PROCEDURE stp_TestFM
AS SET NOCOUNT ON
BEGIN
   PRINT 'Ich bin ein Test: abc'
   RAISERROR (N'Ich bin eine Warnung', 9, 1)
END;

In MS SQL Server Management Studio however, it doesn't makes a difference, if you a semi-colon in between; that means the Statement Matthias posted works in SSMS also fine.

Is there maybe any line / statement delimiter setting to be adjusted?

Thanks in advance and beste regards,
Franz

Thomas Kellerer

unread,
Sep 15, 2021, 7:20:51 AM9/15/21
to sql-wo...@googlegroups.com
If you want to embed semicolons inside a block, you will have to use an alternate delimiter, otherwise the block would be split at the semicolon.

In SQL Server it's common to use GO as this delimiter:

When I run this:

CREATE PROCEDURE test_print
AS
SET NOCOUNT ON;
BEGIN
PRINT 'I am a test: abc';
END
GO

EXECUTE test_print
go

I do see the output "I am a test: abc" in the message pane.

Wether or not the message is displayed seems to depend on a combination of SQL Server and the driver version.

Regards
Thomas
> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/af75f3bb-f12a-4ad2-a444-c4af2a29032en%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/af75f3bb-f12a-4ad2-a444-c4af2a29032en%40googlegroups.com?utm_medium=email&utm_source=footer>.
Reply all
Reply to author
Forward
0 new messages