Creating Stored Procedures in Firebird

[sir.wally.lewis]

I cannot at any time create a firebird stored procedure using
any ZeosLib component. I have tried them all.

My stored procedures compile nicely in Database WorkBench, IBOConsole.

But I cannot get ZeosLib to create them

Has anyone else had this problem?

[andrevanzuydam]

Hi Wally

I have had great success in this area to write patches for Firebird applications using the ZSQLProcessor component. There are some tricks to make it work and I hope this helps you:

The delimiter property must be set to ^ (just not ; for obvious reasons)
The delimiter type must be set to dtSetTerm
There must be no spaces between lines in stored proc.

Populate the Script Param with something like below:

DECLARE EXTERNAL FUNCTION UDF_SOME_EXPORT
CSTRING(1024), CSTRING(1024)
RETURNS CSTRING(100)
ENTRY_POINT '_some_export'
MODULE_NAME 'EXPORT'^

CREATE PROCEDURE MONTH_NAME (
IMONTH Integer )
RETURNS (
MONTHNAME Varchar(30) )
AS
begin
if (imonth = 1) then monthname = 'JANUARY';
if (imonth = 2) then monthname = 'FEBRARY';
if (imonth = 3) then monthname = 'MARCH';
if (imonth = 4) then monthname = 'APRIL';
if (imonth = 5) then monthname = 'MAY';
if (imonth = 6) then monthname = 'JUNE';
if (imonth = 7) then monthname = 'JULY';
if (imonth = then monthname = 'AUGUST';
if (imonth = 9) then monthname = 'SEPTEMBER';
if (imonth = 10) then monthname = 'OCTOBER';
if (imonth = 11) then monthname = 'NOVEMBER';
if (imonth = 12) then monthname = 'DECEMBER';
suspend;
end^

The example above has two statements which run in the script which I sent to my customers.

Have fun!

[sir.wally.lewis]

Unfortunately this does not solve my problem even with your stored proc. I get the same problem. I am using your suggestion:

Dynamic SQL Error SQL error code = -104 Token unknown - line 14, column 15 then. Error Code: -104. Invalid token

With storedprocs I get

Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 544 ?. Error Code: -104. Invalid token

Where char(544) is the last char. :(

[sir.wally.lewis]

Success!
1. I wrote my own proc to seperate out SQL's
2. I called the below:

try
stmt := FConDB.DbcConnection.CreateStatement;
stmt.ExecuteUpdate(slSQL.Text);
except
on E:Exception do
begin
result := False;
GenerateSQLError(slSQL,SQLName,E.Message);
end;
end;
stmt:=nil;


So much troubles now resolved.

[andrevanzuydam]

Hi there you're going to have to pm me a small sample of your code, I'm sure there is something basic missing. I also notice I have set paramcheck to false ?

Perhaps thats the missing clue ?

[andrevanzuydam]

Great - glad you came right