Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

cursor in a procedure & transaction

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
ExDev001
Asked:
ExDev001
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ExDev001Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what i mentioned is with respect to oracle database only.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Depends how you have that database configured. If it's set to dirty bits you can see records that weren't committed yet.
0
 
ExDev001Author Commented:
interesting!
could you explain a bit 'more about "dirty bits"? I've never heard of this for firebird
thanks!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ExDev001Author Commented:
yes, sure, sorry, I forget it in my first post... I use FIREBIRD 2.5
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ExDev001Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The answer is NO.
The cursor don't change nothing by itself.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now