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

x
?
Solved

cursor in a procedure & transaction

Posted on 2011-03-01
12
Medium Priority
?
918 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
[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
  • 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 52

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

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

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 52

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 52

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 52

Accepted Solution

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

704 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