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