Stored Procedures
Robert Schieck
Rob Schieck
MER Systems Inc.
Joseph Alba wrote:
>
> Actually, Interbase tradition dictates that you place a suspend within
> each and every stored procedure (even non-select ones). Believe me, it
> will keep you out of trouble.
>
> Joseph Alba
> ja...@iloilo.net
>
> -----Original Message-----
> From: interba...@mers.com [mailto:interba...@mers.com] On
> Behalf Of Lucas Franzen
> Sent: Monday, October 15, 2001 2:22 PM
> To: inte...@mers.com
> Subject: Re: Stored Procedures
>
> Joseph Alba schrieb:
> >
> > And place a SUSPEND line after the select, so that the values get
> > returned to the client.
>
> That depends on how you're accessing to the returned value.
>
> Luc.
> _______________________________________________
> Interbase mailing list
> Inte...@mers.com
> http://mers.com/mailman/listinfo/interbase
> InterBase Search Engine - http://www.mers.com/searchsite.html
>
> _______________________________________________
> Interbase mailing list
> Inte...@mers.com
> http://mers.com/mailman/listinfo/interbase
> InterBase Search Engine - http://www.mers.com/searchsite.html
Joseph Alba
>Rob Schieck
>MER Systems Inc.
Answer: An Interbase expert once said:
Replacing the "suspend" statement in InterBase will cause you the most
grief.
hth
Robert Schieck
MER Systems Inc.
----
So, to be safe, I take care never to forget the all important suspend
statement in stored procedures. ( :), :)
But seriously, here is just one situation where a suspend statement can
save the day for an unwary developer:
When executing a SP inside a loop that accepts input parameters, the
server hangs on the second iteration if the statement is not unprepared
and re-prepared between iterations. This is an old bug which goes back
to v. 5.x as well.
Note that, if you add a SUSPEND statement at the end of the SP and
return a dummy parameter, you can multi-call the SP with SELECT instead
of EXECUTE without the need to do the unprepare/prepare when passing
subsequent sets of input. (From Helen Borrie)
--
There are other instances, which can be easily found in the mers.com
knowledgebase.
So as a rule, I just place a suspend in my stored procedures to enable
the server to clean up certain things. Some may say it is not necessary,
but for me it is safe. (Unless of course, one is migrating from
Interbase to Oracle).
Joseph Alba
ja...@iloilo.net
Henry FRANQUET
I have a select stored procedure wich insert records in a table and then
suspend error reporting about the insert for each row.
This procedure takes about 3 mn to complete (thousands of records)
As I call it with select * from stored_procedure, with administration tools
or with SQLExplorer from Borland, I can see just a few lines and then I gain
control on the interface 5 seconds later. As I inspect the inserted rows
directly in the table, I can't see all the inserted row, just the 500 first.
If I call select distinct * from stored_procedure, the call last about 3 mn
and when I look in the table I can see the whole insert
It seems that each statement act as isolated from the next statement, and
the first suspend in the stored procedure act as a commit, so
another statement can see only the "commited part"
In other word, It seems that suspend statement in a procedure breaks the
ACID property of the procedure, and the procedure regain it's ACID property
only when called in a explicit or implicit transaction
rsch...@mers.com
I believe that statment was in reference to porting an InterBase database
to Oracle. Simulating a suspend in oracle is somewhat more complictated.
hth
Rob
Robert Schieck
You may want to re-read the message in its full context, it can be found
at :
you will have to rebuild the url to get it to work
hth
Joseph Alba
:), :).
And I also mentioned... But seriously ... phrase to direct attention to
the more meaty portion.
I do remember several discussions in the past (maybe here and/or also in
other IB lists) where it was suggested that placing SUSPEND in a stored
procedure was a good safety net. I had always done this and it had done
me no harm. There are other examples and other work-arounds that I have
come across but I've got to get back to my Palm programming for the
moment.
Joseph Alba
ja...@iloilo.net
-----Original Message-----
From: interba...@mers.com [mailto:interba...@mers.com] On
Behalf Of Robert Schieck
Sent: Wednesday, October 17, 2001 6:57 AM
To: inte...@mers.com
Subject: Re: Stored Procedures
rsch...@mers.com
Sorry if I went overboard, I have enough problems reading the words and
usually don't get to the :) ...
Rob
Ivan Prenosil
and it can always come handy when you encounter bug (that still was not fixed, afaik)
that force you to call such procedure as select-procedure.
If you have not encountered this bug yet, it is because you do not use execute procedure
command enough, or you have good luck :-)
Aleksey Karyakin
Can you provide a exact reference to the mentioned bug
reports, description, symptoms, or something? I've
heard about that bug several times but never was able
to see something material and never have encountered
it myself.
Wasn't it just a legend, based on which advices on
database design are often given?
Regards,
Aleksey Karyakin
Lucas Franzen
Aleksey Karyakin schrieb:
>
> Ivan,
> Can you provide a exact reference to the mentioned bug
> reports, description, symptoms, or something? I've
> heard about that bug several times but never was able
> to see something material and never have encountered
> it myself.
> Wasn't it just a legend, based on which advices on
> database design are often given?
create procedure sp_bug
returns ( ZERO CHAR(1) )
as
begin
ZERO = '0';
end
Access this SP by a StoredProc component, like (I do hope I remember
right how to use them, since I'm working with IBO I forgot how to handle
these):
with spBug do
begin
ExecProc;
ShowMessage ( 'Result = ' + ParambyName ( 'ZERO' ).AsString );
end;
if you execute this procedure a second time, you'll get an error, when:
- NOT explicitely unpreparing it
- and __ONLY__ if you run it in the network; locally this bug never
shows up!
Luc.
Aleksey Karyakin
Tried this using:
ODS8 database (dialect 1) created in IB4.2.
IB4.2
IB6.0.0.627
with appropriate GDS32.DLL from each version and TCP
protocol.
Delphi 5 with BDE 4.0.1.16.
Program code:
with sp1 do
begin
Prepare;
ExecProc;
ShowMessage ( 'Result = ' + ParambyName
( 'ZERO' ).AsString );
ExecProc;
ShowMessage ( 'Result = ' + ParambyName
( 'ZERO' ).AsString );
end;
No problems.
Maybe somebody have any hint on versions where it is
reproduced? Just very curious.
Regards,
Aleksey Karyakin
Luc.
Ivan Prenosil
....
> No problems.
The bug is not easily reproducible, it does not manifest always.
I use EXECUTE PROCEDURE without problems in many places.
One day I modified a SP (added one more output parameter)
and it started to fail.
Another case is more strange - execte procedure works from all
computers but one (the same database, identical application).