Search code examples
delphidelphi-2009firebirdzeos

Firebird Insert Distinct Data Using ZeosLib and Delphi

I'm using Zeos 7, and Delphi 2009 and want to check to see if a value is already in the database under a specific field before I post the data to the database.

Example: Field Keyword
Values of Cheese, Mouse, Trap

tblkeywordKEYWORD.Value = Cheese

What is wrong with the following? And is there a better way?

zQueryKeyword.SQL.Add('IF NOT EXISTS(Select KEYWORD from KEYWORDLIST ='''+
  tblkeywordKEYWORD.Value+''')INSERT into KEYWORDLIST(KEYWORD) VALUES ('''+
  tblkeywordKEYWORD.Value+'''))');
zQueryKeyword.ExecSql;

I tried using the unique constraint in IBExpert, but it gives the following error:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. attempt to store duplicate value (visible to active transactions) in unique index "UNQ1_KEYWORDLIST".

Solution

  • Consider to use UPDATE OR INSERT or MERGE statements:

    update or insert into KEYWORDLIST (KEYWORD) values(:KEYWORD) matching(KEYWORD)

    For details check the following documents in your Firebird installation folder:

    • doc\sql.extensions\README.update_or_insert.txt
    • doc\sql.extensions\README.merge.txt