🚀 go-pugleaf

   Hi,

   I'm doing replication with stores procedures and in the
replication is occur some mistakes. I will like know, which mistake is this?  What's happening?
   This is the store procedure and the replication structure executed in the Interbase Replication Server.  

   Thank you!

DATABASE:

SET SQL DIALECT 1;

/* CREATE DATABASE 'C:\Exemplo\bi_direcional\FORTALEZA_DB.GDB' PAGE_SIZE 2048

 DEFAULT CHARACTER SET  */

/* Table: CLIENTE, Owner: SYSDBA */

CREATE TABLE CLIENTE 
(
  CODIGO_CLIENTE	INTEGER NOT NULL,
  NOME_CLIENTE	VARCHAR(100) NOT NULL,
  ENDERECO_CLIENTE	VARCHAR(100),
  CIDADE_CLIENTE	VARCHAR(40),
 PRIMARY KEY (CODIGO_CLIENTE)
);

/* Table: CLIENTE_CONTA, Owner: SYSDBA */

CREATE TABLE CLIENTE_CONTA 
(
  CODIGO_CLIENTE	INTEGER NOT NULL,
  NUMERO_CONTA	INTEGER NOT NULL,
  DATA_CRIACAO	TIMESTAMP NOT NULL,
 PRIMARY KEY (CODIGO_CLIENTE, NUMERO_CONTA)
);

/* Table: CONTA, Owner: SYSDBA */

CREATE TABLE CONTA 
(
  NUMERO_CONTA	INTEGER NOT NULL,
  SALDO_CONTA	DOUBLE PRECISION NOT NULL,
 PRIMARY KEY (NUMERO_CONTA)
);

/* Table: MANUAL_LOG, Owner: SYSDBA */

CREATE TABLE MANUAL_LOG 
(
  REPLNO	INTEGER NOT NULL,
  PUBDBNO	INTEGER NOT NULL,
  SUBDBNO	INTEGER NOT NULL,
  SEQNO	INTEGER NOT NULL,
  RELATIONNO	INTEGER NOT NULL,
  REPTYPE	CHAR(1),
  OLDKEY	VARCHAR(256),
  NEWKEY	VARCHAR(256),
  FORCECOUNT	INTEGER DEFAULT 0,
  ERROR_CODE	INTEGER,
  ERROR_MSG	VARCHAR(256),
 PRIMARY KEY (REPLNO, PUBDBNO, SUBDBNO, SEQNO)
);

/* Table: REPL_LOG, Owner: SYSDBA */

CREATE TABLE REPL_LOG 
(
  REPLNO	INTEGER NOT NULL,
  PUBDBNO	INTEGER NOT NULL,
  SUBDBNO	INTEGER NOT NULL,
  SEQNO	INTEGER NOT NULL,
  RELATIONNO	INTEGER NOT NULL,
  REPTYPE	CHAR(1),
  OLDKEY	VARCHAR(256),
  NEWKEY	VARCHAR(256),
  FORCECOUNT	INTEGER DEFAULT 0,
 PRIMARY KEY (REPLNO, PUBDBNO, SUBDBNO, SEQNO)
);

/* Table: REPL_SEPARATOR, Owner: SYSDBA */

CREATE TABLE REPL_SEPARATOR 
(
  REPLNO	INTEGER NOT NULL,
  PUBDBNO	INTEGER NOT NULL,
  SUBDBNO	INTEGER NOT NULL,
  RELATIONNO	INTEGER NOT NULL,
  RELATIONNAME	VARCHAR(100) NOT NULL,
  SEP	CHAR(1),
 PRIMARY KEY (REPLNO, PUBDBNO, SUBDBNO, RELATIONNO)
);

/*  Index definitions for all user tables */

CREATE INDEX ITESTE ON CLIENTE(CODIGO_CLIENTE);
CREATE INDEX I_REPL_LOG ON REPL_LOG(SEQNO);
ALTER TABLE CLIENTE_CONTA ADD FOREIGN KEY (CODIGO_CLIENTE) REFERENCES CLIENTE (CODIGO_CLIENTE) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE CLIENTE_CONTA ADD FOREIGN KEY (NUMERO_CONTA) REFERENCES CONTA (NUMERO_CONTA) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE GENERATOR REPL_GENERATOR;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE SPFORTALEZAREPL 
(
  P_CIDADE VARCHAR(40),
  P_CODIGO INTEGER,
  P_ENDERECO VARCHAR(100),
  P_NOME VARCHAR(100),
  THE_TYPE CHAR(1)
)
RETURNS
(
  RESULT INTEGER
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE SPFORTALEZAREPL 
(
  P_CIDADE VARCHAR(40),
  P_CODIGO INTEGER,
  P_ENDERECO VARCHAR(100),
  P_NOME VARCHAR(100),
  THE_TYPE CHAR(1)
)
RETURNS
(
  RESULT INTEGER
)
AS
declare variable COUNTER INTEGER;
BEGIN
RESULT=0;
/*****************/
/*  error check  */
/*****************/
select count(*) from cliente where codigo_cliente = :p_codigo
into counter;
if (the_TYPE = 'I' and counter > 0) then
begin
  RESULT = 1;
  exit;
end
if (the_TYPE = 'U' and counter = 0) then
begin
  RESULT = 1;
  exit;
end
if (the_TYPE = 'D' and counter = 0) then
begin
  RESULT = 1;
  exit;
end
/****************************************/
/*       do the insert    */
/****************************************/
if (the_TYPE = 'I') then
  begin
    insert into cliente
    (codigo_cliente,nome_cliente)
    VALUES
    (:p_codigo,:p_nome);
    exit;
  end
/****************************************/
/*       Do Update */
/****************************************/
if (the_TYPE = 'U') then
  begin
    update cliente set
     cidade_cliente = :p_cidade,
     codigo_cliente = :p_codigo,
     endereco_cliente = :p_endereco,
     nome_cliente = :p_nome
    where codigo_cliente = :p_codigo;
    exit;
  end
/****************************************/
/*       Do delete */
/****************************************/
if (the_TYPE = 'D') then
  begin
    delete from cliente where codigo_cliente = :p_codigo;
    exit;
  end
END
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER REPL_GEN_SEQNO FOR REPL_LOG 
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN NEW.SEQNO = GEN_ID(REPL_GENERATOR,1);POST_EVENT 'REPLNOW';END
 ^

COMMIT WORK ^
SET TERM ;^

/* Grant Roles for this database */


/* Grant permissions for this database */

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON CLIENTE TO REPL;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON CLIENTE_CONTA TO REPL;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON CONTA TO REPL;
GRANT INSERT, REFERENCES ON REPL_LOG TO PUBLIC;
GRANT SELECT, REFERENCES ON REPL_SEPARATOR TO PUBLIC;
GRANT EXECUTE ON PROCEDURE SPFORTALEZAREPL TO REPL;



REPLICATION:

Configuration details loaded
Connected to Source:  C:\Exemplo\bi_direcional\FORTALEZA_DB.GDB      Char Set:  NONE
Connected to Target:  C:\Exemplo\bi_direcional\RECIFE_DB.GDB      Char Set:  NONE
STATISTICS FOR SOURCE DATABASE:
0 Hours, 0 Minutes, 0 Seconds
Inserts  :        0
Updates  :        0
Deletes  :        0
Conflicts:        0
Errors   :        0
Disconnect Source Database
Connected to Source:  C:\Exemplo\bi_direcional\RECIFE_DB.GDB      Char Set:  NONE
Disconnect Target Database
Connected to Target:  C:\Exemplo\bi_direcional\FORTALEZA_DB.GDB      Char Set:  NONE
Update  2
SELECT CIDADE_CLIENTE,ENDERECO_CLIENTE,NOME_CLIENTE,CODIGO_CLIENTE FROM CLIENTE WHERE CODIGO_CLIENTE = ?
EXECUTE PROCEDURE SPFORTALEZAREPL (? , ? , ? , ? , 'I')
EXECUTE PROCEDURE SPFORTALEZAREPL (? , ? , ? , ? , 'U')
EXECUTE PROCEDURE SPFORTALEZAREPL (NULL , NULL , NULL , ? , 'D')

PLAN (CLIENTE INDEX (RDB$PRIMARY1))
ErrorNo: 44 - Dynamic SQL Error
-SQL error code = -303
-conversion error from string "R E S Jr, 20"
Incompatible column/host variable data type
STATISTICS FOR SOURCE DATABASE:
0 Hours, 0 Minutes, 0 Seconds
Inserts  :        0
Updates  :        0
Deletes  :        0
Conflicts:        0
Errors   :        1
Disconnect Source Database
Disconnect Target Database
TOTAL STATISTICS:
0 Hours, 0 Minutes, 0 Seconds
Inserts  :        0
Updates  :        0
Deletes  :        0
Conflicts:        0
Errors   :        1