Using update in a firebird stored procedure but data not changing

I have the following stored procedure. I found a bug in my code that had resulted in bad data. I wrote this procedure to fix the data.

CREATE OR ALTER PROCEDURE RESET_DISABLED_COUNT 
returns (
    person integer,
    game integer,
    disabled integer,
    cnt integer)
as
begin
  for select gr.person_id, gr.game_id, gr.disableds
  from game_roster gr
  into :person, :game, :disabled
  do begin
    select count(gr.id)
    from game_roster gr
    where gr.disabled_by_id = :person and gr.game_id = :game
    into cnt;

    if (cnt <> disabled) then begin
        update game_roster gr set gr.disableds = :cnt where (gr.person_id = :person) and (gr.game_id = :game);
    end
  end
end

I then run the procedure from IBExpert and commit. H开发者_开发问答owever, when I run a query on the table, it shows that the old data is still there. What am I missing?

---

1) Can Cnt and Disabled variables contain NULLs? If so, change condition for

if (:cnt IS DISTINCT FROM :disabled) then ...

2) Make sure that you commit transaction after SP run.

3) Make sure that transaction you select data on is not SNAPSHOT transaction. If so, commit and reopen it before running SELECT query.

4) Recheck logic of your procedure.

5) Do you run your procedure inside IBExpert's debugger?

---

If you are using C# and ado, this snippet will be handy. Works file on Firebird 1.5 the usp UPDATE_stuff was a typical update or insert type of proc you take for granted with MSSQL. This will allow you to use a stored procedure and actually save the data and save weeks of wasted time. Config.DB4FB.Open is just a nested class that opens a new FbConnection witt a optional connection string, cos if you are using firebird you need all the grief abstracted away:)

FbTransaction FTransaction=null;
using (FbConnection conn = Config.DB4FB.Open(ConnectionString))
{
    FbTransactionOptions options = new FbTransactionOptions();
    options.TransactionBehavior = FbTransactionBehavior.NoWait;
    FTransaction = conn.BeginTransaction(options);
    FbCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "UPDATE_stuff";
    cmd.Parameters.AddWithValue("KEY", key);
    cmd.Transaction = FTransaction;
    FbDataReader reader = cmd.ExecuteReader()
            while (reader.Read()){}
    FTransaction.Commit();
}

---

Do you need to return the 4 output variables you set?

If you DONT need the return values, this is the same thing and "should" work

CREATE PROCEDURE RESET_DISABLED_COUNT
AS
declare person integer;
declare game integer;
declare disabled integer;
declare cnt integer;

begin
  for select gr.person_id, gr.game_id, gr.disableds
  from game_roster gr
  into :person, :game, :disabled
  do begin 
    select count(gr.id)
    from game_roster gr
    where gr.disabled_by_id = :person and gr.game_id = :game
    into cnt;
    if (cnt <> disabled) then begin
        update game_roster gr set gr.disableds = :cnt where (gr.person_id = :person) and (gr.game_id = :game);
    end
  end
end