A bit basic but is the transaction being committed? Sent from my BlackBerry® wireless device
-----Original Message----- From: Tomasz Tyrakowski <[email protected]> Sender: [email protected] Date: Tue, 22 Nov 2011 11:55:00 To: <[email protected]> Reply-To: [email protected] 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 [email protected], 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
