Subject Transactions and Stored Procedures
Author ronald_greven
Hi,

I have a question to Stored Procedures and Transactions.

The following constellation :

I use a TIB_Query and a TIB_DataSource in my project.

I have this code sample in my project :

try
with DataModule1 do
begin
if IB_Transaction1.InTransaction then IB_Transaction1.Commit;
IB_Temp.SQL.Clear;
IB_Temp.SQL.Add('select * from RS_SETTEMPABSCHLUSS(:Nr) ');

IB_Temp.ParamByName('Nr').AsInteger := Nr;

IB_Temp.Open;
IB_Temp.Close;
end;
except
...
end;


The stored procedure contains the following code :

CREATE PROCEDURE RS_SETTEMPABSCHLUSS (NR INTEGER)
returns (INTERN VARCHAR(30))
AS
declare variable DATUM Date;
declare variable ZEIT Varchar(8);
declare variable DATUMZEIT Date;
begin
select Datum, Zeit, DatumZeit from RS_GETSERVERDATETIME
into :DATUM, :ZEIT, :DATUMZEIT;

Intern = 'TEMPORÄR' || InttoStr(Gen_ID(TEMPABSCHLUSS,1));

update RS_Booklist
set Abschluss = 'J',
Intern = :Intern,
Abmeldedatum = :Datum,
Abmeldezeit = :Zeit
where Nr = :Nr
and Abschluss = 'N';

suspend;
end


As you can see in the Delphi Sample code, I do no commit after, I
opened the query, cause its a select.

After viewing the table there is no change.

If I restart the application, and do the same, the value of the action
before is done, but again, not the actual action.

After this, I added the following to the code before :

IB_Temp.Open;

Inttmp := IB_Temp.FieldByName('INTERN').AsString;

IB_Temp.Close;

After restarting the Application, the new values were written immediately.

And now the question :

What happens there? Was it commited only through reading the return
value?????
Do I have to do a commit after such a select from stored proc?

Please help, cause I want to understand, what really happens there,
and what is the right way to realize this.

Many thanks


Ronni