[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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