Hi Helen,

many, many thanks for your detailed answer. I will surely take it to
heart.
I think, that I have to learn a lot more, regarding to all this.
The problem, I only was learning by doing. No one shows me, how to do
this.
Right in front of me, is your "Firebird book" and its really great.
I wished, you would write a book about the IB_Objects. ;-)


Best wishes

Ronni

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> 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
>