Eclipse Community Forums: DTP » Sybase "create procedure" scripts

Help

Home

Home » Eclipse Projects » DTP » Sybase "create procedure" scripts

Show: Today's Messages :: Show Polls :: Message Navigator

Sybase "create procedure" scripts [message #667977]

Tue, 03 May 2011 08:09

Maarten Boekhold

Messages: 2
Registered: May 2011

Junior Member

Hi,

When using Sybase, a typical SQL script/file to create a stored procedure looks like:

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'ProcName' AND TYPE = 'P')
    DROP PROCEDURE ProcName
go

CREATE PROCEDURE ProcName
AS
BEGIN
.... content
END
go

GRANT EXECUTE ON ProcName TO PUBLIC
go

If I enter something like the above in an SQL file in Eclipse and execute it using the DTP (Ctrl-Alt-X), it doesn't work. I get various "Error occurred when executing last statement(s). Do you want to continue?" error messages, and the SQL code is not executed on the database. Even more, the "Status" window doesn't show any useful error message.

Test case:

/* test */

if exists (select 1 from sysobjects where name = 'ProcName' and type = 'P')
    drop procedure ProcName
go

create procedure ProcName
as
begin
	select 1
end 
go

grant execute on ProcName to public
go

select 1 from sysobjects where name = 'ProcName' and type = 'P'
go

In the "Status" window all this shows is:

Group Execution

/* test */

if exists (select 1 from sysobjects where name = 'ProcName' and type = 'P')
    drop procedure ProcName
create procedure ProcName
as
begin
	select 1
end
grant execute on ProcName to public
select 1 from sysobjects where name = 'ProcName' and type = 'P'


Elapsed Time:  0 hr, 0 min, 0 sec, 23 ms.

and after Ctrl-Alt-X this, the ProcName procedure *does not exist* in the database.

How can typical Sybase DDL scripts like this be handled in DTP?

Kind regards, Maarten

Report message to a moderator

Re: Sybase "create procedure" scripts [message #667982 is a reply to message #667977]

Tue, 03 May 2011 08:24

Maarten Boekhold

Messages: 2
Registered: May 2011

Junior Member

Note that if I select the "individual" statements that make up this script in turn and run them with Alt-C, it works, even if I include the "go" in the text selection...

Also, a similar script that only uses DML, eg:

if exists (select 1 from sysobjects where name = 'ProcName' and type = 'P')
    select 1
go

select 2
go

select 1 from sysobjects where name = 'ProcName' and type = 'P'
go

select 3
go

works correctly:

1> if exists (select 1 from sysobjects where name = 'ProcName' and type = 'P')
2>     select 1
3> go
4> select 2
5> go
 
 -
 2
(1 row affected)
1> select 1 from sysobjects where name = 'ProcName' and type = 'P'
2> go
 
 
(0 rows affected)
1> select 3
2> go
 
 -
 3
(1 row affected)
 
 -
 3
(1 row affected)
1> Elapsed Time:  0 hr, 0 min, 0 sec, 4 ms.
2> go

So this really seems to be related to DDL statements only.

Kind regards, Maarten

Report message to a moderator