Very Computer

I am new to informix .We are working on informix 7.1.
I want to know if I can begin work and commit inside a storeproc.
Before you answer here is the problem.It is in a foreach loop.I
want to process transactions that I get with the foreach select
statement and these transactions is in groups of 5 transaction
that must be committed together.If one of these is wrong it must be
rolled back otherwise it must be committed.
here
--

               \\://      http://www.geocities.com/Area51/9680/

------------ooO-(_)-Ooo------------------------------------------
      .oooO              
      (   )   Oooo.       Rudolph Terblanche
-------\ (----(   )----------------------------------------------
        \_)    ) /
              (_/   A Harmless instrument in the Hands of a Fool,
                                 is DANGEROUS !!!!

I am new to informix .We are working on informix 7.1.
I want to know if I can begin work and commit inside a storeproc.
Before you answer here is the problem.It is in a foreach loop.I
want to process transactions that I get with the foreach select
statement and these transactions is in updates in 5 different places
that must be committed together.If one of these is wrong it must be
rolled back otherwise it must be committed.The problem is it exits out
of
the for each statement as soon as it gets to to 'commit work' statement.
it doesn't process the rest of the transaction
Here is some sample code of what I want to do.

Foreach TxFT For
  Select tx.number,tx.amount into nNumber,nAmount
  for transactiondt
  where txind <> 'E'
    begin work;           ??????????????????????
    let nError = 0;
    if nAmount = 0 then
      nError = 1;
    end if;
    if nError = 0 then
      nError = UpdateBalances(nNumber,nAmount);   {call other storeproc}
    end if;                                       {that returns
errorcode}
    if nError = 0 then
      commit work;      ?????????????
    else
      rollback work;      ????????????
      begin work;       ????????????
      insert into errordt (txerrornumber,txerrocode)
        values(nNumber,nError);
      commit work;      ?????????????????
    end if;
end Foreach;

I hope somebody can help me .It is kind of critical.

--

               \\://      http://www.geocities.com/Area51/9680/

------------ooO-(_)-Ooo------------------------------------------
      .oooO              
      (   )   Oooo.       Rudolph Terblanche
-------\ (----(   )----------------------------------------------
        \_)    ) /
              (_/   A Harmless instrument in the Hands of a Fool,
                                 is DANGEROUS !!!!

:I am new to informix .We are working on informix 7.1.
:I want to know if I can begin work and commit inside a storeproc.
:Before you answer here is the problem.It is in a foreach loop.I
:want to process transactions that I get with the foreach select
:statement and these transactions is in updates in 5 different places
:that must be committed together.If one of these is wrong it must be
:rolled back otherwise it must be committed.The problem is it exits out
:of
:the for each statement as soon as it gets to to 'commit work' statement.
:it doesn't process the rest of the transaction

That's because the ANSI sql standard spesifies that a commit work
shall close all open cursors. Your foreach is therefore closed as soon
as you execute the commit work. Luckily Informix has learened that
this is stupid behavior (the ANSI commity isn't allways too smart) and
have implemented a "with hold" option on cursors. Such cursors are
kept open accross transactions. I don't remember off hand exactly
where you write the "with hold" in a stored procedure, but it should
be in the manual.

:Here is some sample code of what I want to do.
:
:Foreach TxFT For
:  Select tx.number,tx.amount into nNumber,nAmount
:  for transactiondt
:  where txind <> 'E'
:    begin work;           ??????????????????????
:    let nError = 0;
:    if nAmount = 0 then
:      nError = 1;
:    end if;
:    if nError = 0 then
:      nError = UpdateBalances(nNumber,nAmount);   {call other storeproc}
:    end if;                                       {that returns
:errorcode}

Make sure these other stored procedures do not have their own
begin/commit work. You allways have to control transactions from one
place only, as they can't be nested.

:    if nError = 0 then
:      commit work;      ?????????????
:    else
:      rollback work;      ????????????

I wouldn't do the insert below here. If you get to this place due to a
database error it's likely the whole instance may be down. I would
write such errors to a file. You can do that with the "system" command
in stored procedures.

:      begin work;       ????????????
:      insert into errordt (txerrornumber,txerrocode)
:        values(nNumber,nError);
:      commit work;      ?????????????????
:    end if;
:end Foreach;
:
:I hope somebody can help me .It is kind of critical.

NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org

Nils & Family,

I missed Rudolphs original post so I respond to Nils' post.
Here comes some preachiness: While I know it is legal syntax to BEGIN
and COMMIT work within a stored procedure, I have always felt this is
bad practice because the program calling the procedure could easily be
in a transaction already at the time of the call, resulting in the error
"Already in a transaction".  (No, I don't remember the error number and
I probably don't want to know you if you have it memorized.. ;-)

This said, I would use a FOREACH loop in my 4GL code and place the BEGIN
WORK and COMMIT WORK statements inside the loop.

There is also the issue

FOREACH cursor INTO variables.*
    BEGIN WORK
    EXECUTE prepared statements that call the procedure
        USING any variables the procedure might need
END FOREACH

Nils' answer is fine if you absolutely MUST put the FOREACH loop inside
the procedure.  This awful situation is possible only if you have horrid
security considerations that forbid a user from reading the table he
working on.  In that case, only a stored procedure will allow the needed
access to the table.  And in that case, you would (grumble, grumble)
need to BEGIN & COMMIT WORK inside the procedure.

Most environments are not as restrictive and should allow you to do your
table access, calling the procedure for each row you fetch.

As for the syntax of WITH HOLD:

4GL:
DECLARE yutz_curs CURSOR WITH HOLD FOR statement
DECLARE yutz_curs CURSOR WITH HOLD FOR prepared-statement-ID

SPL:
FOREACH cursor_name WITH HOLD FOR statement
. . . .
END FOREACH

+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+

:I am new to informix .We are working on informix 7.1.
:I want to know if I can begin work and commit inside a storeproc.
:Before you answer here is the problem.It is in a foreach loop.I
:want to process transactions that I get with the foreach select
:statement and these transactions is in updates in 5 different places
:that must be committed together.If one of these is wrong it must be
:rolled back otherwise it must be committed.The problem is it exits out
:of
:the for each statement as soon as it gets to to 'commit work' statement.
:it doesn't process the rest of the transaction

That's because the ANSI sql standard spesifies that a commit work
shall close all open cursors. Your foreach is therefore closed as soon
as you execute the commit work. Luckily Informix has learened that
this is stupid behavior (the ANSI commity isn't allways too smart) and
have implemented a "with hold" option on cursors. Such cursors are
kept open accross transactions. I don't remember off hand exactly
where you write the "with hold" in a stored procedure, but it should
be in the manual.

:Here is some sample code of what I want to do.
:
:Foreach TxFT For
:  Select tx.number,tx.amount into nNumber,nAmount
:  for transactiondt
:  where txind <> 'E'
:    begin work;           ??????????????????????
:    let nError = 0;
:    if nAmount = 0 then
:      nError = 1;
:    end if;
:    if nError = 0 then
:      nError = UpdateBalances(nNumber,nAmount);   {call other storeproc}
:    end if;                                       {that returns
:errorcode}

Make sure these other stored procedures do not have their own
begin/commit work. You allways have to control transactions from one
place only, as they can't be nested.

:    if nError = 0 then
:      commit work;      ?????????????
:    else
:      rollback work;      ????????????

I wouldn't do the insert below here. If you get to this place due to a
database error it's likely the whole instance may be down. I would
write such errors to a file. You can do that with the "system" command
in stored procedures.

:      begin work;       ????????????
:      insert into errordt (txerrornumber,txerrocode)
:        values(nNumber,nError);
:      commit work;      ?????????????????
:    end if;
:end Foreach;
:
:I hope somebody can help me .It is kind of critical.

NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org