Data comparer using cursors full example
<< | IBEBlock | >>
Data comparer using cursors full example
execute ibeblock (
ProcessInserts boolean = TRUE,
ProcessUpdates boolean = TRUE,
ProcessDeletes boolean = TRUE)
returns (
InsertedRecs integer = 0 comment 'Records inserted',
UpdatedRecs integer = 0 comment 'Records updated',
DeletedRecs integer = 0 comment 'Records deleted',
TotalTime double precision = 0 comment 'Time spent (seconds)')
as
begin
RecNum = 5000; -- How many records will be inserted into our test table
if (not ibec_fileexists('d:\MasterDB.fdb')) then
ibec_CreateDatabase(__ctFirebird, 'DBName="localhost:d:\MasterDB.fdb";
ClientLib="fbclient.dll";
User=SYSDBA; Password=masterkey; PageSize=16384;
DefaultCharset=UTF8; SQLDialect=3');
if (not ibec_fileexists('d:\SubscriberDB.fdb')) then
ibec_CreateDatabase(__ctFirebird, 'DBName="localhost:d:\SubscriberDB.fdb";
ClientLib="fbclient.dll";
User=SYSDBA; Password=masterkey; PageSize=16384;
DefaultCharset=UTF8; SQLDialect=3');
DropStmt = 'drop table IBE$$TEST_DATA';
CreateStmt =
'create table IBE$$TEST_DATA (
ID integer not null,
ID2 varchar(20) not null,
F_INTEGER integer,
F_VARCHAR varchar(100),
F_DATE date,
F_TIME time,
F_TIMESTAMP timestamp,
F_NUMERIC numeric(15,2),
F_BOOL char(1) check (F_BOOL in (''T'', ''F'')),
F_BLOB blob sub_type 1,
F_SEASON varchar(15) check(F_SEASON in (''Spring'', ''Summer'', ''Autumn'', ''Winter'')))';
AlterStmt =
'alter table IBE$$TEST_DATA add constraint PK_TEST_DATA primary key (ID)';
MasterDB = ibec_CreateConnection(__ctFirebird,'DBName="localhost:d:\MasterDB.FDB";
ClientLib=fbclient.dll;
user=SYSDBA; password=masterkey; names=UTF8; sqldialect=3');
SubscriberDB = ibec_CreateConnection(__ctFirebird,'DBName="localhost:d:\SubscriberDB.FDB";
ClientLib=fbclient.dll;
user=SYSDBA; password=masterkey; names=UTF8; sqldialect=3');
ibec_UseConnection(MasterDB);
--Let's drop table for our example, if you test it repeatedly
if (exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
begin
execute statement :DropStmt;
commit;
end
if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
begin
execute statement :CreateStmt;
commit;
execute statement :AlterStmt;
commit;
i = 0;
while (i < RecNum) do
begin
fid2 = ibec_randomstring(1,20,65,90);
fint = ibec_random2(1, 100000);
fvarc = ibec_randomstring(1,100,65,90);
fdate = ibec_random2(20000,40000);
ftime = ibec_random(0);
ftimest = ibec_random2(20000,40000) + ibec_random(0);
fnum = ibec_random2(1,40000) + ibec_random(0);
fbool = ibec_randomval('T','F');
fblob = ibec_randomstring(500, 1000, 65, 90);
fseason = ibec_randomval('Spring', 'Summer', 'Autumn', 'Winter');
insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);
i = i + 1;
if (ibec_mod(i, 500) = 0) then
begin
ibec_progress(i || ' records inserted...');
commit;
end;
end;
commit;
end;
--Let's drop table for our example, if you test it repeatedly
ibec_UseConnection(SubscriberDB);
if (exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
begin
execute statement :DropStmt;
commit;
end
if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
begin
execute statement :CreateStmt;
commit;
execute statement :AlterStmt;
commit;
ibec_UseConnection(MasterDB);
i = 0;
k = 0;
for select * from IBE$$TEST_DATA
into vals
do
begin
ibec_UseConnection(SubscriberDB);
k = k + 1; -- Just a counter...
if (ibec_mod(k,100) <> 0) then
begin
if (ibec_mod(i,10) = 0) then
vals[7] = null;
if (ibec_mod(i,30) = 0) then
vals[9] = ibec_randomstring(500, 1000, 65, 90);
insert into SubscriberDB.IBE$$TEST_DATA values :vals;
i = i + 1;
if (ibec_mod(i, 500) = 0) then
begin
ibec_progress(i || ' records inserted...');
commit;
end
end
end
ibec_UseConnection(SubscriberDB);
commit;
i = k + 1;
while (i < (RecNum + 1000 + 1)) do
begin
fid2 = ibec_randomstring(1,20,65,90);
fint = ibec_random2(1, 100000);
fvarc = ibec_randomstring(1,100,65,90);
fdate = ibec_random2(20000,40000);
ftime = ibec_random(0);
ftimest = ibec_random2(20000,40000) + ibec_random(0);
fnum = ibec_random2(1,40000) + ibec_random(0);
fbool = ibec_randomval('T','F');
fblob = ibec_randomstring(500, 1000, 65, 90);
fseason = ibec_randomval('Spring', 'Summer', 'Autumn', 'Winter');
insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);
if (ibec_mod(i, 500) = 0) then
begin
ibec_progress(i || ' records inserted...');
commit;
end
i = i + 1;
end
commit;
end
use MasterDB;
i = 0;
for select i.rdb$field_name
from rdb$relation_constraints rc, rdb$index_segments i, rdb$indices idx
where (i.rdb$index_name = rc.rdb$index_name) and
(idx.rdb$index_name = rc.rdb$index_name) and
(rc.rdb$constraint_type = 'PRIMARY KEY') and
(rc.rdb$relation_name = 'IBE$$TEST_DATA')
order by i.rdb$field_position
into fldname
do
begin
PKFields[i] = ibec_trim(fldname);
i = i + 1;
end
SelStmt = 'select rdb$field_name
from rdb$relation_fields
where (rdb$relation_name = ''IBE$$TEST_DATA'')';
i = 0;
HighDim = ibec_high(PKFields);
for i = 0 to HighDim do
SelStmt = SelStmt || ' and (rdb$field_name <> ' || ibec_QuotedStr(PKFields[i], '''') || ')';
SelStmt = SelStmt || ' order by rdb$field_position';
i = 0;
for execute statement :SelStmt
into :s
do
begin
NonPKFields[i] = ibec_trim(:s);
i = i + 1;
end
--SelStmt will be used to retrieve data
--UpdStmt will be used to update the second table if two records differ:
SelStmt = '';
UpdStmt = 'update ibe$$test_data set ';
WhereClause = ' where ';
HighDim = ibec_high(NonPKFields);
for i = 0 to HighDim do
begin
SelStmt = SelStmt || NonPKFields[i];
SelStmt = SelStmt || ', ';
UpdStmt = UpdStmt || ibec_chr(13) || NonPKFields[i] || ' = :' || NonPKFields[i];
if (i < HighDim) then
UpdStmt = UpdStmt || ', ';
end
HighDim = ibec_high(PKFields);
for i = 0 to HighDim do
begin
SelStmt = SelStmt || ibec_trim(PKFields[i]);
WhereClause = WhereClause || '(' || ibec_trim(PKFields[i]) || ' = :' || ibec_trim(PKFields[i]) || ')';
if (i < HighDim) then
begin
SelStmt = SelStmt || ', ';
WhereClause = WhereClause || ' and ';
end
end
SelStmt = 'select ' || SelStmt || ' from IBE$$TEST_DATA order by ';
for i = 0 to HighDim do
begin
SelStmt = SelStmt || ibec_trim(PKFields[i]);
if (i < HighDim) then
SelStmt = SelStmt || ', ';
end
PKFieldCount = ibec_high(PKFields)+1;
PKFieldIndex = ibec_high(NonPKFields)+1;
StartTime = ibec_gettickcount(); -- Note the time...
MasterCR = ibec_cr_OpenCursor(MasterDB, SelStmt);
SubscriberCR = ibec_cr_OpenCursor(SubscriberDB, SelStmt);
InsFields = ''; InsValues = '';
FldCount = ibec_cr_FieldCount(SubscriberCR);
for i = 0 to (FldCount-1) do
begin
FldName = ibec_Trim(ibec_cr_FieldName(SubscriberCR, i));
InsFields = InsFields || FldName;
InsValues = InsValues || ':' || FldName;
if (i < (FldCount-1)) then
begin
InsFields = InsFields || ', ';
InsValues = InsValues || ', ';
end
end
InsStmt = 'insert into ibe$$test_data (' || InsFields || ') values (' || InsValues || ')';
ibec_UseConnection(SubscriberDB);
while (not (ibec_cr_Eof(MasterCR) and ibec_cr_Eof(SubscriberCR))) do
begin
CompResult = 0;
if (ibec_cr_Eof(MasterCR)) then
CompResult = 1;
else if (ibec_cr_Eof(SubscriberCR)) then
CompResult = -1;
else
begin
ibec_cr_Fetch(MasterCR, MasterPK, PKFieldIndex, PKFieldCount);
ibec_cr_Fetch(SubscriberCR, SubscriberPK, PKFieldIndex, PKFieldCount);
CompResult = ibec_CmpRecords2(MasterPK, SubscriberPK);
end
if (ProcessUpdates and (CompResult = 0)) then
begin
ibec_cr_Fetch(MasterCR, MasterVals, 0, PKFieldIndex);
ibec_cr_Fetch(SubscriberCR, SubscriberVals, 0, PKFieldIndex);
CompResult = ibec_CmpRecords(MasterVals, SubscriberVals);
if (CompResult <> -1) then
begin
UpdatedRecs = UpdatedRecs + 1;
ibec_progress('Record must be updated...');
ibec_cr_Fetch(MasterCR, MasterVals, 0, null);
execute statement :UpdStmt || WhereClause values :MasterVals;
end
ibec_cr_Next(MasterCR);
ibec_cr_Next(SubscriberCR);
end
else if (ProcessInserts and (CompResult < 0)) then
begin
InsertedRecs = InsertedRecs + 1;
ibec_progress('Record must be inserted...');
ibec_cr_Fetch(MasterCR, MasterVals, 0, null);
execute statement :InsStmt values :MasterVals;
ibec_cr_Next(MasterCR);
end
else if (ProcessDeletes and (CompResult > 0)) then
begin
DeletedRecs = DeletedRecs + 1;
ibec_progress('Record must be deleted...');
ibec_cr_Fetch(SubscriberCR, SubscriberPK, PKFieldIndex, PKFieldCount);
execute statement 'delete from ibe$$test_data ' || WhereClause values :SubscriberPK;
ibec_cr_Next(SubscriberCR);
end;
end
ibec_cr_CloseCursor(MasterCR);
ibec_cr_CloseCursor(SubscriberCR);
commit;
close connection MasterDB;
close connection SubscriberDB;
EndTime = ibec_gettickcount();
TotalTime = (EndTime - StartTime) / 1000;
suspend;
end;
back to top of page
<< | IBEBlock | >>
