Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Commit rollback inside cursor

Posted on 2002-04-20
3
Medium Priority
?
1,410 Views
Last Modified: 2012-06-27
Hello experts,
             I have written a Sybase 11.9 (Adaptive Server) cursor which is basically  supposed to support the following functionality:
(1) I am migrating a table from an old schema to a new schema. I take the old schema table, select a
group of records having a particular value in a column 'X' , check if the number of records in that
group is 12, and if so I delete any 11 of those 12 and retain just 1 record. I repeat this for all distinct
values in column 'X'
Here is the algorithm for how the cursor implements above functionality:

1)Sort table by column X and copy it to temp table
2)Use the temptable as input to the cursor
3)For every record returned by cursor, retain it if it is the first record with a particular value (eg:'ABCD' )
in column X and do BEGIN TRAN
4)If it is not the first record with the above value 'ABCD' in column X I delete it. I keep repeating
this delete till the value in column X changes.
5) Then I check the counter to see if it = 12. If so, I COMMIT the transaction. If not, I rollback the
transaction so that the deletes for 'ABCD' are rolledback.
6) I do this for every distinct value in column X.


Problem: The cursor is not returning all the rows in the temptable. So my process is working only on
part of the table. For some reason I suspect the cursor is getting closed in the middle or something
else is happening.
I am using rollbacks because I do not know before hand if for a given value in column X, there are
12 records or (more/less). If 12 I commit the deletes else I rollback all previous deletes for that
value of column X.

I would appreciate if you could provide a solution to the problem.

thanks
grao5
0
Comment
Question by:grao5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960011

select pri_key_col1, pri_key_col2 ...
from tableName
where some_col = "X"
group by pri_key_col1, pri_key_col2 ..
having count(*) = 12


as far as i understand ..the above query is all you need

could you please post more details , like table definition, some sample data .. it will help us easily answer ur quest.
0
 

Author Comment

by:grao5
ID: 6960977
Hi,
  Thanks for the suggestion. Actually I found an alternate solution where I have completely eliminated cursors, because cursors have the property of closing upon use of commit/rollback transaction. I tried to eliminate this problem using chained transaction mode & hold locks  when declaring the cursor. But it did not work.
So now I eliminated cursors completely, and instead do lot of intermediate processing using temporary tables, while loops, changing rowcounts and other transact sql stuff and I think I am able to achieve the desired results. If I experience any additional problems I will get in touch.

thanks
grao5
0
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 150 total points
ID: 6960983
Thats great.
you may please close this question.

thanks.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

722 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