Hi,

I require replication in my database, the master database is on a
server and all the slaves use embedded firebird.

Using embedded limits my options as I can't disconnect to let a
replication engine 'do its thing', so I have decided to use IBLM as
the majority of my replication needs is one way only.

I have written a couple of stored procedures which iterate through the
IBLM tables and generate scripts to update the slave database. This
seems to work for me but I was wondering if anyone would help by
looking at the code as it has some limitations in relation to blob's
(and probably other areas) as any improvements would be appreciated.

The code is below, its a bit rough in places so any help appreciated.

regards

Si


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

ROLLBACK;

--ALTER TABLE IBLM$OPERATIONLOG ADD REPLICATED INTEGER DEFAULT 0;--
Add new column to IBLM$OPERATIONLOG -
--UPDATE IBLM$OPERATIONLOG SET REPLICATED = 0 WHERE REPLICATED IS
NULL; COMMIT; -- Add when creating above column
-- remove IBLM triggers from target database

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_IBLM$QUOTESTR(IPTABLE VARCHAR(31),
IPCOLUMN VARCHAR(31))
RETURNS (opCANQUOTE CHAR(1))
AS
BEGIN
SELECT CASE f.RDB$FIELD_TYPE WHEN 14 THEN 'T' WHEN 40 THEN 'T' WHEN
12 THEN 'T' WHEN 13 THEN 'T' WHEN 35 THEN 'T' WHEN 37 THEN 'T' ELSE
'F' END
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
WHERE r.RDB$RELATION_NAME = :ipTABLE
AND r.RDB$FIELD_NAME = :ipCOLUMN
INTO :opCANQUOTE;

SUSPEND;
END ^

SET TERM ; ^

COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_IBLM$REPLICATEINSERT(ipOPERATIONID BIGINT)
RETURNS (opSQL VARCHAR(32000))
AS
DECLARE VARIABLE vTableName VARCHAR(31);
DECLARE VARIABLE vColumnName VARCHAR(31);
DECLARE VARIABLE vKey1 VARCHAR(31);
DECLARE VARIABLE vKey2 VARCHAR(31);
DECLARE VARIABLE vKey3 VARCHAR(31);
DECLARE VARIABLE vKey4 VARCHAR(31);
DECLARE VARIABLE vKey5 VARCHAR(31);
DECLARE VARIABLE vKey1v VARCHAR(24);
DECLARE VARIABLE vKey2v VARCHAR(24);
DECLARE VARIABLE vKey3v VARCHAR(24);
DECLARE VARIABLE vKey4v VARCHAR(24);
DECLARE VARIABLE vKey5v VARCHAR(24);
DECLARE VARIABLE vFirst INTEGER;
DECLARE VARIABLE vParamCount INTEGER;
DECLARE VARIABLE vNewValue VARCHAR(300); -- set to max varchar size for IBLM
DECLARE VARIABLE vNewValueBlob BLOB SUB_TYPE 0;
DECLARE VARIABLE vOldValue VARCHAR(300); -- set to max varchar size for IBLM
DECLARE VARIABLE vOldValueBlob BLOB SUB_TYPE 0;
DECLARE VARIABLE vQuoteString CHAR(1);
DECLARE VARIABLE vInsertColumns VARCHAR(700);
DECLARE VARIABLE vInsertValues VARCHAR(700);
BEGIN
vFirst = 0;
vParamCount = 0;

SELECT ol.TABLE_NAME, ol.PKEY1, ol.PKEY1_VALUE, ol.PKEY2,
ol.PKEY2_VALUE, ol.PKEY3, ol.PKEY3_VALUE, ol.PKEY4, ol.PKEY4_VALUE,
ol.PKEY5, ol.PKEY5_VALUE
FROM IBLM$OPERATIONLOG ol
WHERE ol.ID = :ipOPERATIONID
INTO :vTableName, :vKey1, :vKey1v, :vKey2, :vKey2v, :vKey3, :vKey3v,
:vKey4, :vKey4v, :vKey5, :vKey5v;

--Delete if EXISTS
opSQL = 'DELETE FROM ' || vTableName || ' WHERE ' || vKey1 || ' = ' || vKey1v;
EXECUTE STATEMENT opSQL;

FOR
SELECT r.COLUMN_NAME, r.NEW_VALUE_BLOB, r.NEW_VALUE,
r.OLD_VALUE_BLOB, r.OLD_VALUE
FROM IBLM$COLUMNLOG r
WHERE r.OPERATIONLOG_ID = :ipOPERATIONID
INTO :vColumnName, :vNewValueBlob, :vNewValue, :vOldValueBlob, :vOldValue
DO
BEGIN
vParamCount = vParamCount + 1;
EXECUTE PROCEDURE P_IBLM$QUOTESTR(vTableName, vColumnName)
RETURNING_VALUES vQuoteString;

IF (vFirst = 0) THEN
BEGIN
IF ((vNewValueBlob IS NULL AND vOldValueBlob IS NULL) AND
(vOldValue IS NOT NULL OR vNewValue IS NOT NULL)) THEN
BEGIN
IF (vQuoteString = 'T') THEN
BEGIN
vInsertColumns = vColumnName;
vInsertValues = '''' || vNewValue || '''';
END ELSE
BEGIN
vInsertColumns = vColumnName;
vInsertValues = vNewValue;
END
END ELSE
BEGIN
vInsertColumns = vColumnName;
vInsertValues = '''' || vNewValueBlob || '''';
END

vFirst = 1;
END ELSE
BEGIN
IF ((vNewValueBlob IS NULL AND vOldValueBlob IS NULL) AND
(vOldValue IS NOT NULL OR vNewValue IS NOT NULL)) THEN
BEGIN
IF (vQuoteString = 'T') THEN
BEGIN
vInsertColumns = vInsertColumns || ', ' || vColumnName;
vInsertValues = vInsertValues || ', ''' || vNewValue || '''';
END ELSE
BEGIN
vInsertColumns = vInsertColumns || ', ' || vColumnName;
vInsertValues = vInsertValues || ', ' || vNewValue;
END
END ELSE
BEGIN
vInsertColumns = vInsertColumns || ', ' || vColumnName;
vInsertValues = vInsertValues || ', ''' || vNewValueBlob || '''';
END
END
END

opSQL = 'INSERT INTO ' || vTableName || ' (' || vInsertColumns || ')
VALUES (' || vInsertValues || ');';
SUSPEND;
END ^

SET TERM ; ^

COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_IBLM$REPLICATEDELETE(ipOPERATIONID BIGINT)
RETURNS (opSQL VARCHAR(32000))
AS
DECLARE VARIABLE vTableName VARCHAR(31);
DECLARE VARIABLE vKey1 VARCHAR(31);
DECLARE VARIABLE vKey2 VARCHAR(31);
DECLARE VARIABLE vKey3 VARCHAR(31);
DECLARE VARIABLE vKey4 VARCHAR(31);
DECLARE VARIABLE vKey5 VARCHAR(31);
DECLARE VARIABLE vKey1v VARCHAR(24);
DECLARE VARIABLE vKey2v VARCHAR(24);
DECLARE VARIABLE vKey3v VARCHAR(24);
DECLARE VARIABLE vKey4v VARCHAR(24);
DECLARE VARIABLE vKey5v VARCHAR(24);
BEGIN
SELECT ol.TABLE_NAME, ol.PKEY1, ol.PKEY1_VALUE, ol.PKEY2,
ol.PKEY2_VALUE, ol.PKEY3, ol.PKEY3_VALUE, ol.PKEY4, ol.PKEY4_VALUE,
ol.PKEY5, ol.PKEY5_VALUE
FROM IBLM$OPERATIONLOG ol
WHERE ol.ID = :ipOPERATIONID
INTO :vTableName, :vKey1, :vKey1v, :vKey2, :vKey2v, :vKey3, :vKey3v,
:vKey4, :vKey4v, :vKey5, :vKey5v;

-- Assume primary keys are integer based!!!!
opSQL = 'DELETE FROM ' || vTableName || ' WHERE (';

IF ((vKey1v IS NOT NULL) AND (vKey1 IS NOT NULL)) THEN
opSQL = opSQL || :vKey1 || ' = ' || vKey1v;

IF ((vKey2v IS NOT NULL) AND (vKey2 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey2 || ' = ' || vKey2v;

IF ((vKey3v IS NOT NULL) AND (vKey3 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey3 || ' = ' || vKey3v;

IF ((vKey4v IS NOT NULL) AND (vKey4 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey4 || ' = ' || vKey4v;

IF ((vKey1v IS NOT NULL) AND (vKey5 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey5 || ' = ' || vKey5v;

opSQL = opSQL || ');';

SUSPEND;
END ^

SET TERM ; ^

COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_IBLM$REPLICATEUPDATE(ipOPERATIONID BIGINT)
RETURNS (opSQL VARCHAR(32000))
AS
DECLARE VARIABLE vTableName VARCHAR(31);
DECLARE VARIABLE vColumnName VARCHAR(31);
DECLARE VARIABLE vKey1 VARCHAR(31);
DECLARE VARIABLE vKey2 VARCHAR(31);
DECLARE VARIABLE vKey3 VARCHAR(31);
DECLARE VARIABLE vKey4 VARCHAR(31);
DECLARE VARIABLE vKey5 VARCHAR(31);
DECLARE VARIABLE vKey1v VARCHAR(24);
DECLARE VARIABLE vKey2v VARCHAR(24);
DECLARE VARIABLE vKey3v VARCHAR(24);
DECLARE VARIABLE vKey4v VARCHAR(24);
DECLARE VARIABLE vKey5v VARCHAR(24);
DECLARE VARIABLE vFirst INTEGER;
DECLARE VARIABLE vParamCount INTEGER;
DECLARE VARIABLE vNewValue VARCHAR(300); -- set to max varchar size for IBLM
DECLARE VARIABLE vNewValueBlob BLOB SUB_TYPE 0;
DECLARE VARIABLE vOldValue VARCHAR(300); -- set to max varchar size for IBLM
DECLARE VARIABLE vOldValueBlob BLOB SUB_TYPE 0;
DECLARE VARIABLE vQuoteString CHAR(1);
BEGIN
vFirst = 0;
vParamCount = 0;

SELECT ol.TABLE_NAME, ol.PKEY1, ol.PKEY1_VALUE, ol.PKEY2,
ol.PKEY2_VALUE, ol.PKEY3, ol.PKEY3_VALUE, ol.PKEY4, ol.PKEY4_VALUE,
ol.PKEY5, ol.PKEY5_VALUE
FROM IBLM$OPERATIONLOG ol
WHERE ol.ID = :ipOPERATIONID
INTO :vTableName, :vKey1, :vKey1v, :vKey2, :vKey2v, :vKey3, :vKey3v,
:vKey4, :vKey4v, :vKey5, :vKey5v;

-- Assume primary keys are integer based!!!!
opSQL = 'UPDATE ' || vTableName || ' SET ';

FOR
SELECT r.COLUMN_NAME, r.NEW_VALUE_BLOB, r.NEW_VALUE,
r.OLD_VALUE_BLOB, r.OLD_VALUE
FROM IBLM$COLUMNLOG r
WHERE r.OPERATIONLOG_ID = :ipOPERATIONID
INTO :vColumnName, :vNewValueBlob, :vNewValue, :vOldValueBlob, :vOldValue
DO
BEGIN
vParamCount = vParamCount + 1;
EXECUTE PROCEDURE P_IBLM$QUOTESTR(vTableName, vColumnName)
RETURNING_VALUES vQuoteString;

IF (vFirst = 0) THEN
BEGIN
IF ((vNewValueBlob IS NULL AND vOldValueBlob IS NULL) AND
(vOldValue IS NOT NULL OR vNewValue IS NOT NULL)) THEN
BEGIN
IF (vQuoteString = 'T') THEN
BEGIN
opSQL = opSQL || ' ' || vColumnName || ' = ''' || vNewValue || '''';
END ELSE
BEGIN
opSQL = opSQL || ' ' || vColumnName || ' = ' || vNewValue;
END
END ELSE
BEGIN
opSQL = opSQL || ' ' || vColumnName || ' = ' || vNewValueBlob;
END

vFirst = 1;
END ELSE
BEGIN
IF ((vNewValueBlob IS NULL AND vOldValueBlob IS NULL) AND
(vOldValue IS NOT NULL OR vNewValue IS NOT NULL)) THEN
BEGIN
IF (vQuoteString = 'T') THEN
opSQL = opSQL || ', ' || vColumnName || ' = ''' || vNewValue || '''';
ELSE
opSQL = opSQL || ', ' || vColumnName || ' = ' || vNewValue;
END ELSE
BEGIN
IF (vQuoteString = 'T') THEN
vNewValue = '''' || vNewValue || '''';

opSQL = opSQL || ', ' || vColumnName || ' = ' || vNewValueBlob;
END
END
END

IF (vParamCount > 0) THEN
BEGIN
opSQL = opSQL || ' WHERE (';

IF ((vKey1v IS NOT NULL) AND (vKey1 IS NOT NULL)) THEN
opSQL = opSQL || :vKey1 || ' = ' || vKey1v;

IF ((vKey2v IS NOT NULL) AND (vKey2 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey2 || ' = ' || vKey2v;

IF ((vKey3v IS NOT NULL) AND (vKey3 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey3 || ' = ' || vKey3v;

IF ((vKey4v IS NOT NULL) AND (vKey4 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey4 || ' = ' || vKey4v;

IF ((vKey1v IS NOT NULL) AND (vKey5 IS NOT NULL)) THEN
opSQL = opSQL || ' AND ' ||vKey5 || ' = ' || vKey5v;

opSQL = opSQL || ');';

SUSPEND;
END ELSE
opSQL = NULL;
END ^

SET TERM ; ^

COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_IBLM$REPLICATECHANGES
--RETURNS (opSQL varchar(32000)) -- add this line to view output
AS
DECLARE VARIABLE opSQL varchar(32000); -- remove this line to view output
DECLARE VARIABLE vOperationID BIGINT;
DECLARE VARIABLE VOperationType VARCHAR(6);
BEGIN
RDB$SET_CONTEXT('USER_TRANSACTION', 'REPLICATING', 'YES');

FOR
SELECT ol.ID, ol.OPERATION
FROM IBLM$OPERATIONLOG ol
WHERE ol.REPLICATED = 0
INTO :vOperationID, :vOperationType
DO
BEGIN
IF (vOperationType = 'UPDATE') THEN
BEGIN
EXECUTE PROCEDURE P_IBLM$REPLICATEUPDATE(vOperationID)
RETURNING_VALUES :opSQL;
END ELSE IF (vOperationType = 'DELETE') THEN
BEGIN
EXECUTE PROCEDURE P_IBLM$REPLICATEDELETE(vOperationID)
RETURNING_VALUES :opSQL;
END ELSE IF (vOperationType = 'INSERT') THEN
BEGIN
EXECUTE PROCEDURE P_IBLM$REPLICATEINSERT(vOperationID)
RETURNING_VALUES :opSQL;
END

IF (opSQL IS NOT NULL) THEN
BEGIN
--SUSPEND; -- add this line to view output
EXECUTE STATEMENT opSQL; -- remove this line to test and view
SQL statements

UPDATE IBLM$OPERATIONLOG SET REPLICATED = 1 WHERE ID = :vOperationID;
END
END

RDB$SET_CONTEXT('USER_TRANSACTION', 'REPLICATING', 'NO');
END^

SET TERM ; ^

COMMIT;

--SELECT * FROM P_IBLM$REPLICATECHANGES
execute PROCEDURE P_IBLM$REPLICATECHANGES