• Index
  •  » Talend Enterprise Data Integration » General questions
  •  » Problem of adding Sybase CDC on the table that has composit index.

#1 2011-10-05 07:49:20

mohtake
New member
Registered: 2011-07-20
Posts: 6

Problem of adding Sybase CDC on the table that has composit index.

Hi, I'm in trouble with addting Sybase CDC on the table that has composit index.

The table has BANGOU, SHIMEI, ADDRESS, PHONE, COMPANY, EMAIL and AGE.
PRIMARY KEY CLUSTERED ( BANGOU, SHIMEI ).

I tried to "add CDC" to this table and clicked "Execute" button.
The pop up message "Incorrect syntax near ','." appeared three times.

I've confirmed otherwise trigger generation succeeded.
I guess that three trigger creation of insert, update, and delete shows three pop-up messages.
On the other hand one of index tables if successful.

What's wrong ?
What should I do ?

Environment :
  Sybase Adaptive Server Enterprise Edition 15.5 Developer's Edition
  Talend Data Quality Enterprise Edition MPX (4.2.3.r67267)

Adding script is as follows
-----------------------------

INSERT INTO  "cdcdb"."cdc"."TSUBSCRIBERS"
  (
   "TALEND_CDC_TABLE_TO_WATCH",
   "TALEND_CDC_SUBSCRIBER_NAME",
   "TALEND_CDC_CREATION_DATE"
  )
   values ('data1db.ohtake.meibo2','APP1',getdate())


CREATE TABLE  "cdcdb"."cdc"."TCDC_meibo2"
  (
   "TALEND_CDC_SUBSCRIBERS_NAME" VARCHAR(50) NOT NULL,
   "TALEND_CDC_STATE" VARCHAR(1),
   "TALEND_CDC_TYPE" VARCHAR(1),
   "TALEND_CDC_CREATION_DATE" DATETIME,
   "BANGOU" INT  NOT NULL,
   "SHIMEI" VARCHAR(5)   NOT NULL
  )


CREATE VIEW "cdc"."TCDC_VIEW_meibo2" AS
SELECT
  SRC."TALEND_CDC_TYPE"  TALEND_CDC_TYPE,
  SRC."TALEND_CDC_SUBSCRIBERS_NAME"  TALEND_CDC_SUBSCRIBERS_NAME,
  max(SRC."TALEND_CDC_CREATION_DATE")  TALEND_CDC_CREATION_DATE,
  SRC."BANGOU"  BANGOU,
  SRC."SHIMEI"  SHIMEI,
  TARG."ADDRESS"  ADDRESS,
  TARG."PHONE"  PHONE,
  TARG."COMPANY"  COMPANY,
  TARG."EMAIL"  EMAIL,
  TARG."AGE"  AGE
FROM "cdcdb"."cdc"."TCDC_meibo2" SRC  LEFT JOIN "data1db"."ohtake"."meibo2" TARG 
ON SRC."BANGOU"=TARG."BANGOU" AND SRC."SHIMEI"=TARG."SHIMEI"
WHERE SRC."TALEND_CDC_STATE"='1'
GROUP BY SRC."TALEND_CDC_SUBSCRIBERS_NAME", SRC."BANGOU", SRC."SHIMEI", TARG."ADDRESS", TARG."PHONE", TARG."COMPANY", TARG."EMAIL", TARG."AGE",
    SRC."TALEND_CDC_TYPE"


USE data1db


CREATE TRIGGER "cdc"."TCDC_TG_meibo2_I" ON "data1db"."ohtake"."meibo2"
FOR INSERT
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
  (
   "TALEND_CDC_SUBSCRIBERS_NAME",
   "TALEND_CDC_STATE",
   "TALEND_CDC_TYPE",
   "TALEND_CDC_CREATION_DATE",
   "BANGOU",
   "SHIMEI"
  ) SELECT
     "TALEND_CDC_SUBSCRIBER_NAME",
     '0',
     'I',
     getdate(),
     (SELECT "BANGOU","SHIMEI" FROM inserted )     
    FROM "cdcdb"."cdc"."TSUBSCRIBERS"
    WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'

CREATE TRIGGER "cdc"."TCDC_TG_meibo2_U" ON "data1db"."ohtake"."meibo2"
FOR UPDATE
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
  (
   "TALEND_CDC_SUBSCRIBERS_NAME",
   "TALEND_CDC_STATE",
   "TALEND_CDC_TYPE",
   "TALEND_CDC_CREATION_DATE",
   "BANGOU",
   "SHIMEI"
  ) SELECT
     "TALEND_CDC_SUBSCRIBER_NAME",
     '0',
     'U',
     getdate(),
     (SELECT "BANGOU","SHIMEI" FROM inserted )
    FROM "cdcdb"."cdc"."TSUBSCRIBERS"
    WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'

CREATE TRIGGER "cdc"."TCDC_TG_meibo2_D" ON "data1db"."ohtake"."meibo2"
FOR DELETE
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
  (
   "TALEND_CDC_SUBSCRIBERS_NAME",
   "TALEND_CDC_STATE",
   "TALEND_CDC_TYPE",
   "TALEND_CDC_CREATION_DATE",
   "BANGOU",
   "SHIMEI"
  ) SELECT
     "TALEND_CDC_SUBSCRIBER_NAME",
     '0',
     'D',
     getdate(),
     (SELECT "BANGOU","SHIMEI" FROM deleted )     
    FROM "cdcdb"."cdc"."TSUBSCRIBERS"
    WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'


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


Uploaded Images

Offline

#2 2011-10-06 03:04:06

mohtake
New member
Registered: 2011-07-20
Posts: 6

Re: Problem of adding Sybase CDC on the table that has composit index.

Anybody can answer this question?

Thanks,
mohtake

Offline

#3 2011-10-07 09:22:43

mohtake
New member
Registered: 2011-07-20
Posts: 6

Re: Problem of adding Sybase CDC on the table that has composit index.

I found three facts.

I tried and successed to create triggers after this operation with Sybase Central's Interactive SQL tool as follows.


(1) CREATE TRIGGER FOR INSERT AND DELETE
    change (SELECT "BANGOU","SHIMEI" FROM inserted )    into  (SELECT "BANGOU" FROM inserted) , (SELECT "SHIMEI" FROM inserted).

(2) CREATE TRIGGER FOR DELETE
    change      (SELECT "BANGOU","SHIMEI" FROM deleted )    into      (SELECT "BANGOU" FROM deleted ) ,  (SELECT "SHIMEI" FROM deleted )

This must be a bug.

Offline

  • Index
  •  » Talend Enterprise Data Integration » General questions
  •  » Problem of adding Sybase CDC on the table that has composit index.

Board footer

Powered by FluxBB