Solved

cursor in a procedure & transaction

Posted on 2011-03-01
12
912 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
ID: 35014904
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 47

Expert Comment

by:Vitor Montalvão
ID: 35015661
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
ID: 35015884
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35015891
what i mentioned is with respect to oracle database only.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 35016271
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
ID: 35016310
interesting!
could you explain a bit 'more about "dirty bits"? I've never heard of this for firebird
thanks!
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 35016445
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 35

Expert Comment

by:Mark Geerlings
ID: 35017948
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
ID: 35024733
yes, sure, sorry, I forget it in my first post... I use FIREBIRD 2.5
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 35025349
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
ID: 35036556
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 47

Accepted Solution

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database connect change in Access 2015 5 58
SQL anywhere 11 databases 1 82
Runtime 3044 error 14 44
T-SQL Default value in Select? 5 27
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
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…

803 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