Solved

Commit rollback inside cursor

Posted on 2002-04-20
3
1,395 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
  • 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 50 total points
ID: 6960983
Thats great.
you may please close this question.

thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase SQL Anywhere ISQL 8 1,005
performance measure of  running SSIS package 4 211
Cluster Resource error 3 69
sybase 3 48
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The business world is becoming increasingly integrated with tech. It’s not just for a select few anymore — but what about if you have a small business? It may be easier than you think to integrate technology into your small business, and it’s likely…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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