🚀 go-pugleaf

I've had some problems early on that were rather silly, below is an example
of writing a stored procedure, I've posted it here incase anyone else is
trying to look at this as there seem to have been a number of questions in
this group about setting this kind of thing up.

There should be a full document available on line that exalains how its all
setup etc..
http://www.replication.co.za/borland/interbase/ibreplicator/papers/3158.html

The only thing really to note is the order of the fields.
1) Put them alphabetically but remember "_" comes before "A". -  I tried
typing all the fields out and ordering them in a spread sheet as I was being
lazy and had 130+ fields, but this didn't work the down fall was the "_"
character. This is lower in the alphabet then the letter "A". I've
capitalised all fields as its easier for me to read and is our internal
practice. but note eg MEMBER_NUMBER comes before MEMBERSHIP_TYPE.
2) after the fields list the Unique key(s) - mines just the UID field.
3) Then add THE_TYPE, this was just TYPE in some docs i've seen, but this
was a typo as its a reserved word. This enables you to specify certain
actions depending on an insert, update, or delete.
4) when setting the replication up, you must pass in all the fields to the
stored procedure. If you don't or there declared in the wrong order you will
get errors like Invalid varient type.
5) Finally, don't forget to grant permissions to the procedure.

I've not tested passing Blobs throught the stored procedure, I've been told
by some people it can be done, and others it can't.
I hope this will save some people some time.

Steve


**********************

set term ^ ;


CREATE PROCEDURE TestProc
(
/*        <input paramaters>            */

  ADDRESS   CHAR(80),
  ADDRESS_1   CHAR(35),
  ADDRESS_2   CHAR(35),
  MEMBER_NUMBER   INTEGER,
  MEMBER_STATUS    CHAR(50),
  MEMBERSHIP_TYPE  CHAR(50),
  MISC    CHAR(50),
  MISC_COMBO   CHAR(50),
  ZIP_CODE    CHAR(10),
  UID    CHAR(20),
  THE_TYPE  CHAR(1)

)
RETURNS
(
RESULT INTEGER
)

AS

/*        <local variable declarations> */
declare variable COUNTER INTEGER;


BEGIN

/*        <procedure statements>        */

RESULT=0;

/*****************/
/*  error check  */
/*****************/
select count(*) from members where uid = :uid
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 table1
    (
       UID,
       ZIP_CODE
       )
    VALUES
      (
     :UID,
       :ZIP_CODE
      );

    exit;
  end

/****************************************/
/*       Do Update */
/****************************************/

if (THE_TYPE = 'U') then
  begin
    update table1 set
      ADDRESS = :ADDRESS,
      ADDRESS_1 = :ADDRESS_1,
      ADDRESS_2 = :ADDRESS_2,
      MEMBER_NUMBER = :MEMBER_NUMBER,
      MEMBER_STATUS = :MEMBER_STATUS,
      MEMBERSHIP_TYPE = :MEMBERSHIP_TYPE,
      ZIP_CODE = :ZIP_CODE

    where UID = :UID;

    exit;
  end

/****************************************/
/*       Do delete */
/****************************************/

if (THE_TYPE = 'D') then
  begin
    delete from table1 where UID = :UID;

    exit;
  end


END ^

set term ; ^

Commit;

grant execute on procedure TestProc to repl;

Commit;