Hi,
I have Firebird 2.5.1 classic on Linux.
Is it possible to run an external procedure from the trigger using execute
statement like this:
execute statement :stat on external :db as user :user password :pass into
:result ?
I need insert/update same records in external database after insert/update
on local database.
I wrote procedure on external database:
create or alter procedure WYKONAJ_ZDALNIE (
POLECENIE DOM_VARCHAR_2000)
returns (
WYNIK DOM_SMALLINT)
AS
begin
begin
execute statement POLECENIE;
WYNIK=0;
suspend;
exit;
when any do
begin
WYNIK=1;
end
end
suspend;
end
I can execute it from local procedure:
create or alter procedure TEST_ZDALNY (
BAZA WP_VARCHAR100,
POM WP_VARCHAR2000)
returns (
WYNIK WP_INTEGER)
AS
begin
baza='CBI-SQL:/Dane/E_FIRMA.GDB';
pom='select wynik from WYKONAJ_ZDALNIE(''insert into kpl_firma(firma)
values(''''ALA MA ASA'''') '')' ;
for execute statement :pom on external :baza as user '****' password
'****' into :wynik
do suspend;
--wynik=0;
end
but it doesn't work in trigger:
CREATE OR ALTER TRIGGER ROLES_AI0 FOR ROLES
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
declare variable BAZA type of WP_VARCHAR100;
declare variable POM type of WP_VARCHAR2000;
declare variable WYNIK type of WP_INTEGER;
begin
if (new.GROUPNAME='FIRMA') then
begin
select DBPATH from MEMBERSHIP_GETUSERDBPATH (new.APPLICATIONNAME,(select
USERNAME from users where pkid=char_to_uuid(new.firmaid))) into :BAZA;
POM='select WYNIK from WYKONAJ_ZDALNIE(''insert into KPL_FIRMA(FIRMA)
values ('''''||new.FIRMA||''''') '')';
execute statement :POM on external :BAZA as user '****' password '****'
into :WYNIK;
-- execute statement 'select wynik from WYKONAJ_ZDALNIE(''insert into
kpl_firma(firma) values('''''||new.firma||''''') '')' on external :BAZA as
user '****' password '****' into :WYNIK;
-- select wynik from test_zdalny(:baza,:pom) into :wynik; ------- prom
outside the trigger it runs
-- execute statement 'select wynik from WYKONAJ_ZDALNIE(''insert into
kpl_firma(firma) values(''''TTTT'''') '')' on external :BAZA as user '****'
password '****' into :WYNIK;
end
end
With regards,
Jacek
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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/
