Hi all, I hope I can get some suggestions or solution to my problem. I
am trying to update & insert data from a table in DB1 to the same table
in DB2 on a different server.
I can receive the data from DB2 into DB1, but need to send rather than
receive. Active rather than passive updates.
This is the data (test version) on DB1
MYFIELD1 MYFIELD2
1 stock 1
2 stock 2
3 stock 3
and after import
MYFIELD1 MYFIELD2
1 stock 1
2 stock 2
3 stock 3upd
4 stock 4
5 stock 5
This procedure works perfectly as above:
create or alter procedure sync_stocks_insert
returns (
mfld1 integer,
mfld2 char(15))
as
declare variable main_statement varchar(5000);
declare variable remote_data varchar(100);
begin
/* select remote data - same for insert & update */
main_statement = 'select myfield1,myfield2
from mytable ';
/* get the remote database from paramfil */
select remote_database
from paramfil
into remote_data;
/* extract data for processing updates */
for execute statement main_statement||'where insert_update=''U'' '
on external data source remote_data
as user 'sysdba'
password '***'
into :mfld1,:mfld2
/* update - check exists */
do
if (exists (select myfield1 from mytable where (myfield1=:mfld1))) then
update mytable t
set t.myfield2=:mfld2
where t.myfield1=:mfld1;
/* extract data for processing inserts */
for execute statement main_statement||'where insert_update=''I'' '
on external data source remote_data
as user 'sysdba'
password '***'
into :mfld1,:mfld2
/* insert - check not exists */
do
if (not exists (select myfield1 from mytable where
(myfield1=:mfld1))) then
insert into mytable (myfield1,myfield2)
values (:mfld1,:mfld2);
end
This is the export attempt which fails with error :
335544578 : Column unknown
335544382 : MFLD1
336397208 : At line 4, column 33
Statement :
update mytable t
set t.myfield2=:mfld2
where t.myfield1=:mfld1;
create or alter procedure sync_stocks_export
as
declare variable main_statement varchar(5000);
declare variable remote_data varchar(100);
declare variable mfld1 integer;
declare variable mfld2 char(15);
begin
/* select remote data - same for insert & update */
main_statement =
'update mytable t
set t.myfield2=:mfld2
where t.myfield1=:mfld1;';
/* get the remote database from paramfil */
select remote_database
from paramfil
into remote_data;
/* extract data for processing updates */
for select myfield1,myfield2
from mytable
where insert_update='U'
into :mfld1,:mfld2
do execute statement main_statement
on external data source remote_data
as user 'sysdba'
password '***';
end
Any help would be gratefully received.
Thanks
Alan
--
Alan J Davies
Aldis
