Solved

cursor in a procedure & transaction

Posted on 2011-03-01
12
911 Views
Last Modified: 2012-05-11
Hello everyone!
I have a doubt about the use of the cursor.
I had a procedure by which, according to a parameter, decide whether to retrieve the data from Table A or from Table B, and in both cases, processes the data in the same way, so I decided to use two cursor. I did a test and saw that the cursor query returned to the procedure some records that were inserted after the execute of the procedure. Hence, my question: when I open a cursor within a procedure, I will open a new transaction or it's used the same transaction of the procedure?

My procedure is something like this:

CREATE PROCEDURE myprocedure
AS
declare variable v_1 integer;
declare variable v_2 integer;

DECLARE cursor1 CURSOR FOR (
  SELECT field1, field2
  FROM tableA
  WHERE...
  );
DECLARE cursor2 CURSOR FOR (
  SELECT field1, field2
  FROM tableB
  WHERE...
  );
BEGIN

  ...SOME instruction...

  IF (v_param IS NULL) THEN
    OPEN cursor1;
  ELSE
    OPEN cursor2;

  WHILE (0=0) DO
    BEGIN
      IF (v_param IS NULL) THEN
        FETCH cursor1 INTO :v_1, :v_2;
      ELSE
        FETCH cursor2 INTO :v_1, :v_2;

      IF (ROW_COUNT = 0) THEN
        BEGIN
          IF (v_param IS NULL) THEN
            CLOSE cursor1;
          ELSE
            CLOSE cursor2;

          LEAVE;
        END
      ELSE
        BEGIN
          ...SOME instruction...
        END
    END
  ...SOME instruction...
END


something  wrong?
Thanks!
0
Comment
Question by:ExDev001
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
The whole thing is one transaction only and will be active until you commit or rollback.  Once a commit or rollback is done, the next transaction starts and not sure what is the direct relationship of this transaction to cursors here.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Transactions only exists when you declare them with BEGIN and END, so it's up to you to set when SQL Server should execute as transaction or not.
0
 

Author Comment

by:ExDev001
Comment Utility
uhm, sorry, I don't understand if my question is clear...
I use firebird 2.5, and I execute the procedure with a snapshot transaction. It's possible that during the execution of the procedure, the query of the cursor get me some data inserted after the start of the execution? I think that it should be one transaction for the whole procedure, so I don't understand why I see data that were not in the database at the begin moment. Maybe there is something wrong in my cursor declaration?
thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
what i mentioned is with respect to oracle database only.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Depends how you have that database configured. If it's set to dirty bits you can see records that weren't committed yet.
0
 

Author Comment

by:ExDev001
Comment Utility
interesting!
could you explain a bit 'more about "dirty bits"? I've never heard of this for firebird
thanks!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Sorry, I meant "dirty reads" and not bits.
That's in SQL Server (in others databases I don't know) and it depends how your transaction level are set.
Check the concept in http://en.wikipedia.org/wiki/Isolation_(database_systems)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Different SQL databases use very different approaches for read consistency, record locks, transaction control, etc.  When you ask a question like this you need to tell us exactly which databse you are working in because the answers are different for each database.
0
 

Author Comment

by:ExDev001
Comment Utility
yes, sure, sorry, I forget it in my first post... I use FIREBIRD 2.5
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
A quick search in google and I went to this page. Check the link http://www.firebirdsql.org/dotnetfirebird/blog/2005/02/transaction-isolation-levels-in.html
0
 

Author Comment

by:ExDev001
Comment Utility
thanks for  the links, but  I was trying to figure out if the transactions have a different behavior in the presence  of the cursor. I checked the documentation that explains the use of the cursor, but have not found reports about the transaction, so I wanted to know if someone has already used it and if you have had  the same problem.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
The answer is NO.
The cursor don't change nothing by itself.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now