comment moved

Thank you, Tomas

Look at the trigger generated by Replication Manager:


IF (NEW."IMPORTE" IS NULL) THEN
INSERT INTO "REPL$FIELD"(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE) VALUES (:VAR$SCHEMAID,:VAR$ID,8,'N','N');
ELSE
INSERT INTO "REPL$FIELD"(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE,DATA) VALUES (:VAR$SCHEMAID,:VAR$ID,8,'N','I',"REPL$INTEGER_TO_BLOB"(NEW."IMPORTE"));
IF (NEW."SALDO" IS NULL) THEN
INSERT INTO "REPL$FIELD"(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE) VALUES (:VAR$SCHEMAID,:VAR$ID,9,'N','N');
ELSE
INSERT INTO "REPL$FIELD"(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE,DATA) VALUES (:VAR$SCHEMAID,:VAR$ID,9,'N','I',"REPL$INTEGER_TO_BLOB"(NEW."SALDO"));


IMPORTE and SALDO are NUMERIC(15,2) fields.

As you can see the generated TRIGGER is using "REPL$INTEGER_TO_BLOB" function so if i have a 260.58 (numeric 15,2 value) i get a 261 (integer value) stored for replication.

There should be a "REPL$NUMERIC_TO_BLOB" to properly store the numeric value in the REPL$FIELD table.

Thank you very much!
Hernan

It's strange, my numeric field generates DOUBLE_TO_BLOB. Can you copy&paste metadata of related table ? Are you using latest version of replicator (but I don't think I've changed something related to this problem recently) ?

CREATE TABLE BYTY (
  ID TID NOT NULL,
  TEST NUMERIC(15,2),
  PRIMARY KEY (ID)
);

IF (NEW.TEST IS NULL) THEN
  INSERT INTO REPL$FIELD(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE)
  VALUES (:VAR$SCHEMAID,:VAR$ID,12,'N','N');
ELSE
  INSERT INTO REPL$FIELD(SCHEMAID,SEQID,FIELDID,MODE,DATATYPE,DATA)
  VALUES (:VAR$SCHEMAID,:VAR$ID,12,'N','D',REPL$DOUBLE_TO_BLOB(NEW.TEST));

Type of field is examined from system repository using following SQL command

SELECT * FROM RDB$RELATION_FIELDS RF INNER JOIN RDB$FIELDS F
INNER JOIN RDB$TYPES T ON F.RDB$FIELD_TYPE=T.RDB$TYPE AND T.RDB$FIELD_NAME='RDB$FIELD_TYPE'
ON RF.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
WHERE RF.RDB$RELATION_NAME='---RELATION_NAME---' AND RF.RDB$FIELD_NAME='---FIELD_NAME---'

Use correct ---RELATION_NAME--- and ---FIELD_NAME--- (i.e. IMPORTE/SALDO) and post result. RDB$TYPE_NAME should be DOUBLE. It looks like incompatability between SQL server versions. What version FB/IB are you using?

Versions :
IBReplication 2.0 build 19.1

Tested in this two Firebird Versions ON Windows:

Firebird Version 1.5.4.4910
Firebird 2.1.1.1.12855.

Table Metadata:

CREATE TABLE BYTY (
ID INTEGER NOT NULL,
TEST NUMERIC(15,2),
PRIMARY KEY (ID)
);

Metadata Query

SELECT * FROM RDB$RELATION_FIELDS RF INNER JOIN RDB$FIELDS F
INNER JOIN RDB$TYPES T ON F.RDB$FIELD_TYPE=T.RDB$TYPE AND T.RDB$FIELD_NAME='RDB$FIELD_TYPE'
ON RF.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
WHERE RF.RDB$RELATION_NAME='BYTY'


Result:

RRDB$FIELD_NAME : TEST
RDB$RELATION_NAME : BYTY
RDB$FIELD_SOURCE : RDB$2
RDB$FIELD_LENGTH : 16
RDB$FIELD_SCALE : -2
RDB$FIELD_PRECISION : 15
RDB$FIELD_TYPE : 16
RDB$FIELD_SUB_TYPE : 1
RDB$TYPE: 16
RDB$TYPE_NAME : INT64

RDB$TYPE_NAME of NUMERIC(15,2) depends on SQL dialect. For dialect 1 RDB$TYPE_NAME is 'DOUBLE', for dialect 3 it's RDB$TYPE_NAME is 'INT64' (or 'SMALL/LONG') and RDB$FIELD_SCALE is negative. I fixed this problem but I'm not sure if it won't bring precision lost for numbers having many decimal digits because precise numeric are converted using DOUBLE_TO_BLOB, i.e. using DOUBLE type.

Hi Tomas,

I've tested (numeric(15,2)) ver. 2.0.19.3 and worked ok!

Great support, thank you!

Hernan