A bit basic but is the transaction being committed?

Sent from my BlackBerry� wireless device

-----Original Message-----
From: Tomasz Tyrakowski <t.tyrakowski@...>
Sender: firebird-support@yahoogroups.com
Date: Tue, 22 Nov 2011 11:55:00
To: <firebird-support@yahoogroups.com>
Reply-To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: why failed to insert into a table from
a storeprocedure?

I still claim insert...select.. from stored proc works as expected.
Executing this:

-- ----------------------------

create table t1(
f1 integer not null,
primary key(f1)
);

create table t2(
f2 integer not null,
primary key(f2)
);

insert into t2(f2) values (1);
insert into t2(f2) values (2);
insert into t2(f2) values (3);
insert into t2(f2) values (4);
insert into t2(f2) values (5);

set term ^;
create procedure p2(
param integer
)
returns(
res integer
)
as
begin
for select f2 from t2
where f2 >= :param
into :res
do suspend;
end
^

create procedure p1(
param integer
)
as
begin
insert into t1(f1)
select res from p2(:param);
end
^

set term ;^

execute procedure p1(4);

select * from t1;

-- ----------------------------

leaves t1 with two records (f1=4 and f1=5), exactly as expected. Are you
sure you posted the real source of your procedure test1? (of course not
;) ). If you don't prefix param1 and param2 with colons , the line
select fa, fb, fc from test2(param1, param2);
actually contains a syntax error and you can't create such procedure.
IMO there's no point in further looking for a bug in FB in this case -
everything works as it ought to. Double check your procedures and you'll
be fine.

regards
Tomasz


W dniu 2011-11-22 11:35, ibmcom2011 pisze:

> Tomasz,
>
> thank you.
>
> in fact, the test1 have the same parameters as test2. it's my negligence in describing my problem.
>
> create procedure test1(
> param1 varchar(20),
> param2 varchar(20)
> )
> as
> begin
> insert into table1(f1, f2, f3)
> select fa, fb, fc from test2(param1, param2);
> end
>
> i want to save the result data set which got from a complex store procedure (test2) into a temp table (table1) so that i can use it to build an new query.
>
> as you say, a invoke b, if b has been changed, the a would be changed. but the calling shouldn't be omitted, i think.
>
> in the structure
> insert into t2 (..)
> select .. from t1
> , can the t1 be a store procedure ?
>
>
>
>
> --- In firebird-support@yahoogroups.com, Tomasz Tyrakowski<t.tyrakowski@...> wrote:
>>
>> Hi,
>>
>> At the first glance, it should work as expected.
>> If you run by hand
>> select fa, fb, fc from test2(param1,param2)
>> and it does return a data set, then I'd take a closer look at the params
>> you pass to test2 inside test1. Make sure they're really what you think
>> they are.
>> Also, if you altered test2 in the database after creating/altering
>> test1, alter test1 again to itself (all procedures depending on X should
>> be altered, that is, byte-compiled, every time X is altered).
>> If that doesn't help, send more details.
>>
>> regards
>> Tomasz
>>
>> On 2011-11-22 09:15, ibmcom2011 wrote:
>>> hi, all,
>>>
>>> in a store procedure, i try to insert into a table some records from an other store procedure, it seems like this:
>>>
>>> create procedure test1
>>> as
>>> begin
>>> insert into table1(f1, f2, f3)
>>> select fa, fb, fc from test2(param1, param2);
>>> end
>>>
>>> test2 is a store procedure defined in the same database. it fetchs records from some tables and an other store procedure.
>>>
>>> but nothing have done without any error. if execute alone the store procedure test2, lots of records can be founded.
>>>
>>> why?
>>>
>>> thanks.
>>>
>>>
>>>
>>>
>>
>>
>> --
>> __--==============================--__
>> __--== Tomasz Tyrakowski ==--__
>> __--== SOL-SYSTEM ==--__
>> __--== http://www.sol-system.pl ==--__
>> __--==============================--__
>>
>
>
>

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__



[Non-text portions of this message have been removed]