Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » DTP » Sybase "create procedure" scripts
Sybase "create procedure" scripts [message #667977] Tue, 03 May 2011 04:09 Go to next message
Maarten Boekhold is currently offline 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
Re: Sybase "create procedure" scripts [message #667982 is a reply to message #667977] Tue, 03 May 2011 04:24 Go to previous messageGo to next message
Maarten Boekhold is currently offline 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
Re: Sybase "create procedure" scripts [message #671379 is a reply to message #667977] Tue, 17 May 2011 12:48 Go to previous message
Brian Fitzpatrick is currently offline Brian Fitzpatrick
Messages: 495
Registered: July 2009
Senior Member
Maarten, unfortunately we no longer have any Sybase database experts left on the DTP team.

Can anybody from Sybase perhaps offer some help?

--Fitz
Previous Topic:BadParseException in mysql db
Next Topic:SQL Editor keywords
Goto Forum:
  


Current Time: Fri Aug 29 12:30:58 EDT 2014

Powered by FUDForum. Page generated in 0.01555 seconds