--- In firebird-support@yahoogroups.com, "Myles Wakeham" <myles@t...>
wrote:

> I have what I think is a pretty simple question about how to call a

Firebird

> Stored Procedure and get back a result in Delphi. I wish to add a

record to

> a Firebird 1.5 database from a D7 application. I am using FIBPlus

to

> connect to the database.
>
> I have a form that has a number of non-data aware component objects

for the

> field data entry, and the user simply fills out the form and clicks

on a

> SAVE button to save it to the database.
>
> The stored procedure that is called returns back the unique record

ID of the

> newly created record after it creates the record through a

generator.

>
> What I'm not sure how to do is to structure the procedure that has

to setup

> the SQL and get the single result back from it. I believe I cannot

use the

> syntax EXECUTE PROCEDURE for this because it will only return back

the no of

> rows affected. I believe I have to use a SELECT * FROM SP_NAME

(arg1, arg2)

> type syntax, but I'm not sure how to structure the D7 code to setup

the

> transaction parameters for a write to the database, structure the

SQL

> statement, have it executed and handle a single result value coming

back.

>

I am not sure how FIBPlus works, there may well be a stored procedure
component.

There are two ways to do it

You CAN use the execute procedure syntax if it is returning ONLY a
single record using RETURNING_VALUES.

If you have a suspend call inside the stored procedure, then you can
run a query with the select from method.

SELECT OutputParam1, OutputParam2
from SP_Name(InputParam1, InputParam2)

But if you don't have suspend, it wont return anything.

When you are returning a single record, you should probably use the
execute procedure method, however I do not see any problem with using
the select method (invitation to point one out).

The structure of the procedure will be something like this

CREATE PROCEDURE SP_NAME
(
InputParam1 integer,
InputParam2 varchar(100)
)
returns
(
ID integer
)
AS
BEGIN
ID = GEN_ID(GEN_NAME,1);

INSERT INTO TABLEA (ID, AMOUNT, DESCR) VALUES
(:ID, :INPUTPARAM1, :INPUTPARAM2);

SUSPEND; -- only if you are calling it using a select, if you are
using execute procedure skip this line.
END
^

Hope that helps

Adam