Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating Rapid SQL Cursor's????

Posted on 2009-02-11
6
Medium Priority
?
1,193 Views
Last Modified: 2012-06-21
I am trying to create a cursor in Rapid SQL when I exceute the cursor in MS SQL it executes fine when I move it to Rapid SQL i get the following error "Declare Curosr must be the only statement in the querty batch"

Are there any syntax differeneces from running cursors's in Rapid SQL from MS
DECLARE match_cursor Cursor  
FOR 
    SELECT DISTINCT Owner FROM dbo.ScheduledJob
    GO
OPEN match_cursor 
    DECLARE @DOC_NUM char(10)
   
    FETCH NEXT FROM match_cursor INTO @DOC_NUM
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	SELECT @DOC_NUM
FETCH NEXT FROM match_cursor INTO @DOC_NUM
END
CLOSE match_cursor
DEALLOCATE match_cursor
GO

Open in new window

0
Comment
Question by:LJordanMS
6 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23615409
Remove the GO command from line 4
0
 
LVL 19

Expert Comment

by:grant300
ID: 23615765
O.K., first of all, you are confusing the tools and the databases.

The databases you are using are Sybase (some unspecified product and version but we assume it is such because you posted it in the Sybase area) and MS SQL Server.

The tool you are using with Sybase is RapidSQL; an database IDE, not a database in and of itself.  There is no mention of the tool you are using with MS SQL Server to issue queries.

Next, Sybase is telling you that you have to run the DECLARE CURSOR statement separately.

BEGIN
  DECLARE CURSOR.......
END
go
BEGIN
   .... the rest of your code.....
END
go

Regards,
Bill
0
 

Author Comment

by:LJordanMS
ID: 23615852
The tool that I am using in MS SQL is Microsoft SQL Server Management Studio, some old projects are using Embarcadero Rapid SQL which is connecting to Sybase.  

I am trying to understand the syntax that Embarcadero Rapid SQL uses for cursors, it doesn't seem to confrom to my understanding of SQL cursors (MS).
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 19

Accepted Solution

by:
grant300 earned 375 total points
ID: 23615977
This issue you are having has nothing to do with RapidSQL, you are encountering differences between Sybase and MS SQL Server.

Sybase has it's rules and you have to obey them.  MS SQL Server is a derivative of an old version of Sybase ASE.  MS has loosened up on some of the rules that Sybase instantiated, usually to protect data and transaction integrity.

Cursors are almost exclusively used by applications and in stored procedures.  Using them in an interactive query is just not something one would do.  If you need to write procedural code to do something, create a stored procedure and be done with it.  Then you can test basic syntax at compile time and debug it with an interactive debugger at run time.

I assume the example you gave above was just that, an example, and not an actual requirement.  Clearly what you have there could be done in a single SQL statement.

Regards,
Bill
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 23618117
As much as I understand Rapid SQL is running the whole code as one batch rather than two. It looks like the first 'go' is ignored since it is not in the first column. The solution is either you can create a stored proc. to run the whole thing once as Bill suggested, or you can try to move the first "go" to the first column:

DECLARE match_cursor Cursor  
FOR
    SELECT DISTINCT Owner FROM dbo.ScheduledJob
GO
OPEN match_cursor
    DECLARE @DOC_NUM char(10)
   
    FETCH NEXT FROM match_cursor INTO @DOC_NUM
WHILE (@@FETCH_STATUS <> -1)
BEGIN
      SELECT @DOC_NUM
FETCH NEXT FROM match_cursor INTO @DOC_NUM
END
CLOSE match_cursor
DEALLOCATE match_cursor
GO
0
 

Author Comment

by:LJordanMS
ID: 23621622

Thats what I am trying to, is build my stor proc by running and testing it before I complie it into a stor proc. I will just complie it into a stor proc as you suggest and test the script that way I am to use to MS by running parts of the SQL code snippets before bringing it all together into one stor proc.

Thanks for the help Bill
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
This blog will spread awareness about Dropbox. We have given the statements based upon our experience. Along with this, there is a section of some new plans that should be added in Dropbox this year. This will make the storage service enhanced from …
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question