🚀 go-pugleaf

Ailton,
you need to declare the values in the stored procedure in a set order.

Fields,
Key Fields,
The Type

It looks like P_CODIGO is your key field, try
ALTER PROCEDURE SPFORTALEZAREPL
(
P_CIDADE VARCHAR(40),
P_ENDERECO VARCHAR(100),
P_NOME VARCHAR(100),
P_CODIGO INTEGER,
THE_TYPE CHAR(1)
)
RETURNS....................

Select the P_CODIGO as the key field in the replicator scheme and add all
the others over as you would normally.

Steve



"Ailton Queiroz" <ailtonqueiroz@yahoo.com.br> wrote in message
news:3c0e4fd3$1_1@dnews...
>
>    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
>