views:

364

answers:

1

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".

+1  A: 

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
da-soft
Thanks for the information Dmitry
Brad
Dmitry, would have bought your components, but over my current budget.
Brad
Well, you always may ask for a discount.
da-soft