[Squirrel-sql-users] Now Interbase, was Re: Informix: unable to create any stored procedures or functions

[info <in...@dr...>]

Same problem with Interbase. No stored procedure can be created through 
Squirrel!
With this script, the procedure should be created successfully but it 
errors out:

--drop table tt
create table tt(id int not null, a varchar(10), b timestamp)
alter table tt add constraint pk_tt primary key (id)

create procedure tt_ins(AA varchar(10))
as
BEGIN
     INSERT INTO tt (ID, a, b) VALUES (GEN_ID(G_TT, 1), :AA, 
CURRENT_TIMESTAMP);
END;

/*
Error: [interclient][interbase]Dynamic SQL Error
[interclient][interbase]SQL error code = -104
[interclient][interbase]Unexpected end of command
SQLState:  ICI00
ErrorCode: 335544569
Error occurred in:
create procedure tt_ins(AA varchar(10))
as
BEGIN
     INSERT INTO tt (ID, a, b) VALUES (GEN_ID(G_TT, 1), :AA, 
CURRENT_TIMESTAMP)
*/

Then I take this body and paste it into IBConsole. It creates the SP 
successfully. Then I take the SP body from the Metadata tab of the 
properties in IBConsole and paste it back into Squirrel:

CREATE PROCEDURE TT_INS
(
   AA VARCHAR(10)
)
AS
BEGIN EXIT; END ;
/*
Error: [interclient][interbase]Dynamic SQL Error
[interclient][interbase]SQL error code = -104
[interclient][interbase]Unexpected end of command
SQLState:  ICI00
ErrorCode: 335544569
Error occurred in:
CREATE PROCEDURE TT_INS
(
   AA VARCHAR(10)
)
AS
BEGIN EXIT
*/

ALTER PROCEDURE TT_INS
(
   AA VARCHAR(10)
)
AS
BEGIN
     INSERT INTO tt (ID, a, b) VALUES (GEN_ID(G_TT, 1), :AA, 
CURRENT_TIMESTAMP);
END;

I did not try to execute the ALTER part because even the CREATE failed.
This is occurring with Interbase.Interclient.jar that came with 
Interbase dev edition 2020.

Thank you!


On 2021-06-02 10:30 AM, info wrote:
> Hello all:
>
> The following code works fine in Informix 14.10 dbaccess:
>
> create table aat (id serial, b lvarchar(1024));
>
> create procedure aap (v_b like aat.b) -- tried int, integer
>     insert into aat(b) values(v_b);
> end procedure;
>
> But when I try to execute it from Squirrel 4.1.0 (each statement 
> separately) it always gives me a syntax error for the parameter data 
> type of the 2nd statement:
>
> Error: A syntax error has occurred. SQLState:  42000 ErrorCode: -201 
> Position: 30 Error occurred in: create procedure use0.aap(in v_id int) 
> returning lvarchar(1024)
>
> The position changes but the error is always the same and it points at 
> the parameter declaration.
>
> Do I have to change any settings in the driver/alias? I am using 
> jdbc-4.10.12.jar.
>
> Thank you!
>