Alan,

from what I understand the biggest problem though is with select procedures
where you might want to refine the returned result set with a where clause
such as:

select * from myProc(:aValue) where Lastname Starting With 'A'

In this case the engine cannot optimize the select statement and things can
get a little slow. We will be doing some testing to see what the
differences are..

Rob

----- Original Message -----
From: "Alan J Davies" <Aldis@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, April 04, 2002 1:56 PM
Subject: [ib-support] RE:Using Stored procedures exclusively


> On 2nd April rob wrote
> 1. We code the insert update and delete procedures to execute the SP's in
> question. This seemed to work well until i thought we should return the
> newly generated primary key as part of the Insert SP. When we did this we
> started getting an error about expecting a message length of 8 and getting
> 0. As I am writing this I am thinking that in general it really doesn't
> make sense to have the stored procedure return the primary key of the
> record
> it just inserted. Comments here?
>
> 2. Do we need to set the keylinks properties still? So far everything
> still
> seems to work as expected but its very early to tell for sure.
>
> 3. Anybody have a general comments about doing all activities through SPs
> and how it changes the way a delphi app is coded? We are doing this to
set
> the stage for a developing a solid journaling system.
>
> My thoughts for what they are worth are:
> 1. Have you set your parameters char or varchar length correctly?
> 2. Can't comment on that one.
> 3. Definitely. Using SPs confers major benefits such as:
> transfer the workload to the server
> ease of management of code
> control of code in one location per database
> ability to verify the code in the SP while in IBConsole - not requiring
> Delphi to be running
> Unload and load a new SP to make a significant change in a working system
> with one small .sql file. Email a 1-2 KB file to your user. Compare that
to
> making a change in Delphi, compling, testing and (possibly) emailing a few
> MB
> There are others - but if these don't convince you then nothing will!
> A couple of ways to use SPs in Delphi are:
>
> // run it like a normal sql select statement
> SP_TransSupplier is the SP
> (:Supplier) is the parameter to pass to the SP
> with IBQuery1 do
> begin
> Sql.clear;
> Sql.Add('Select * From SP_TransSupplier(:Supplier)');
> Params.ParamValues['Supplier']:=Supplier;
> Open;
> end;
>
> // Drop an IBStoredProc from the Interbase palette on to a datamodule and
> re-use that single IBStoredProc for any SP by setting its name before
> executing it - you can also supply parameters if you need to
> IBStoredProc1.StoredProcName:='Update_Farm';
> IBStoredProc1.ExecProc;
> FrmUpdate.CheckBox1.Checked:=True;
> IBStoredProc1.StoredProcName:='SP_FarmAsh';
> IBStoredProc1.ExecProc;
> FrmUpdate.CheckBox2.Checked:=True;
> IBStoredProc1.StoredProcName:='SP_FarmMoisture';
> IBStoredProc1.ExecProc;
> FrmUpdate.CheckBox3.Checked:=True;
>
> // Use individual IBStoredProcs with the name set to an SP in your
database
> procedure TSupplierDM.SupplierAshRateInsert(Supplier: String);
> begin
> try
> InsertAshRateTemplate.Params.ParamValues['Supplier']:=Supplier;
> InsertAshRateTemplate.ExecProc;
> IBTransaction1.Commit;
> except
> IBTransaction1.Rollback;
> end;
> end;
>
> COMMIT WORK;
> SET AUTODDL OFF;
> SET TERM ^ ;
>
> /* Stored procedures */
>
> CREATE PROCEDURE INSERT_ASHRATE_TEMPLATE
> (
> SUPPLIER CHAR(5)
> )
> AS
> Begin
> /* Update Std Table with Supplier Code */
> UPDATE Supp_AshRatesStd
> SET Supp_Code=:Supplier;
> /* Insert Into Std Table into Supplier Rates Table */
> Insert Into Supp_AshRates
> SELECT * From Supp_AshRatesStd;
> /* Reset Std Table with 'Std' */
> UPDATE Supp_AshRatesStd
> SET Supp_Code='Std';
> When SQLCode -803 Do
> Exception InsertException; /* Already On File */
> end
> ^
>
> SET TERM ; ^
> COMMIT WORK;
> SET AUTODDL ON;
>
> These are snippets from a running system - compare the 2 lines of code in
> Delphi to run the SP above to the content of the SP itself. OK I know you
> have to code the SP without the help of Code Completion, but this simple
> example demonstrates the power of database exceptions - if there is an
> error the database handles it with Exception InsertException - you can
> forget about it in Delphi - no need to have loads of if table eof, if not
> table eof statements....Couple that with unique indexes to prevent
> duplicates, triggers for anything you can think of...
> Basically whatever you can do in Dephi, you can do better in
> Firebird/interbase
> Here, all new apps are being designed that way and when time permits,
> existing apps are being amended to move all database handling out of
Delphi
> and into FB/IB
> I hope this has been of some help to you
>
> Regards
> Alan J Davies
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>