At 03:10 AM 4/02/2006, Ronni Greven wrote:

>Hi,
>
>I have a question to Stored Procedures and Transactions.

OK, there are several wrong assumptions
here. Let's start with the transaction.

EVERY transaction must be started and
ended. Starting a transaction encloses a
complete task in which the client's view of
database stays consistent until the transaction
ends, by being committed or by being rolled back.
All SELECT statements happen (and can only
happen) inside a started transaction.

The purpose of SELECT is to request a
dataset. In all SQL database engines, this is a
request from the client for the server to extract
data from real tables and return a table-like
structure consisting of one or more columns and zero or more rows.

It happens that, in Firebird and InterBase, you
can use a stored procedure to define a virtual
set that the client can request to output rows
that the client application can treat *in some
ways* as though they had been extracted from real
tables. We call this a "selectable" stored procedure.

Now, let's address the stored procedure.

The normal kind of stored procedure - one that
executes one or a few DML operations on data in
the database - is known to us as an "executable"
stored procedure. In this procedure, we execute
the operations we have defined, e.g. inserts,
updates or deletes. If these operations succeed,
then we have changed the view of the database
state that our transaction can see. Until our
transaction is committed, the rest of the world
continues to see database state as it was when our transaction started.

A SELECTABLE stored procedure, while *capable* of
executing DML operations, must not do so. A
selectable procedure is designed to output one
row of virtual (artificial) data at each
iteration of a loop. The magic keyword SUSPEND
literally suspends execution of this procedure
until the client application asks to FETCH this
row. Once the FETCH is complete, the procedure
then proceeds to the next iteration of the loop
and outputs the next manufactured row.

Your SP has a number of problems.

The first problem in your SP is that you do not
have a loop to process multiple row output. The SELECT specification is:

select Datum, Zeit, DatumZeit from RS_GETSERVERDATETIME
into :DATUM, :ZEIT, :DATUMZEIT;

Either RS_GETSERVERDATETIME is a table or it is
another selectable procedure. You will
potentially return multiple rows here and, if you
do, your procedure will fail with a "Multiple
rows in singleton select" error. You don't have
any error handling in your procedure, either.
-- Multiple-row returns must be in a FOR SELECT....INTO...DO loop:

FOR select Datum, Zeit, DatumZeit from RS_GETSERVERDATETIME
into :DATUM, :ZEIT, :DATUMZEIT
DO
BEGIN
..........
END

However, for this particular situation,
RS_GETSERVERDATETIME seems to be a procedure
designed to return one and only one row in its
return values. It should be written as an
executable SP and this SP should invoke it using
EXECUTE PROCEDURE and RETURNING_VALUES():
execute procedure RS_GETSERVERDATETIME
returning_values(:DATUM, :ZEIT, :DATUMZEIT);

The next problem is that each row of output from
a selectable SP is artificial, representing a
micro-snapshot of our transaction's view of
database state at the moment that the artificial
row was created. If you use a selectable
procedure to change that view of database state,
then all previous rows output from the procedure
become invalid. So you must not write a
selectable procedure that changes database state - which is what you did here.

Your procedure must be written as an executable
procedure and must be invoked using EXECUTE
PROCEDURE, not SELECT. After executing the
procedure, you can read your return value[s] in the statement's Fields[] array.

So let's start by redefining your SP for use as an executable procedure.

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
EXECUTE PROCEDURE RS_GETSERVERDATETIME
RETURNING_VALUES(:DATUM, :ZEIT, :DATUMZEIT);

Intern = 'TEMPORÄR' || CAST((Gen_ID(TEMPABSCHLUSS,1)) as Varchar(18));

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

Now, your application needs to EXECUTE this procedure.

>The following constellation :
>
>I use a TIB_Query and a TIB_DataSource in my project.

For an executable procedure
--- use a TIB_Cursor if you need to display
the returned value in a data-aware control. In
that case, you do need a TIB_Datasource.

--- use a TIB_DSQL if you only want to read
the return value, but don't need to display it in
a data-aware control. For this, you do not need a TIB_Datasource.

>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;

OK, this is horrible code for a selectable stored
procedure. But let's put that aside and rewrite
it so that it returns a string variable from your
revised stored procedure. IB_Temp is a TIB_DSQL,
not a TIB_Query. Its IB_Transaction property is set to be IB_Transaction1:

var
returnval: string;
nr: integer;
....
returnval := '';
nr := [ something!! ]
with DataModule1.IB_Temp do
try
if IB_Transaction.InTransaction then
IB_Transaction.Commit;
SQL.Clear;
SQL.Add('execute procedure RS_SETTEMPABSCHLUSS(:Nr)');
Prepare;
ParamByName('Nr').AsInteger := Nr;
Execute;
returnval := FieldByName('Intern').AsString;
except
...
end;

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

I hope you now understand that this it is a wrong
assumption to suppose that SELECT statements are
not executed in a transaction context.

You need to be clear that you do not commit
statements, you commit TRANSACTIONS. It's easy
to get confused if you are using AutoCommit,
which is OK for some things (and not typically
for this kind of thing). It can also cause you
great confusion; and it has some bad side
effects if not used with care. But that is another story....

>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.

I hope this long posting helps to get you on track.

Helen