Wednesday, 7 May 2008

Sybase Identity Columns Bug / Workaround

**Update:This is now fixed in SQL Developer 1.5.1.**
Bug Alert! Identity Columns are not converted correctly for Sybase in SQL Developer 1.5.
Heres how it should be done manually till we get it fixed.

Replace columname and tablename with the identity column details.

CREATE SEQUENCE  <tablename>_<columnname>_SEQ
MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 NOCYCLE ;

CREATE OR REPLACE TRIGGER <tablename>_<columnname>_TRG BEFORE INSERT OR UPDATE ON <tablename>
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.<columnname> IS NULL THEN
SELECT <tablename>_<columnname>_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(<columnname>),0) INTO v_newVal FROM <tablename>;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT <tablename>_<columnname>_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- save this to emulate @@identity
sybase_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.<columnname> := v_newVal;
END IF;
END;


We should have this automated in the next release.

Note I used this great tool to format the code in this blog
http://formatmysourcecode.blogspot.com/