0

As the title says does the SQL implementation in Adobe's AIR platform support "ON DUPLICATE KEY UPDATE"? I am unable to get it to work but it is possible I am using wrong syntax especially if it's different from MySQL syntax. Documentation is very sparse for SQL in flex and the little that I found had no mention of ON DUPLICATE KEY UPDATE. The error I get is quite meaningless: "Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=near 'ON': syntax error"

The database file does not insert any values at all if I get the error.

var insertQuery:String = "INSERT INTO movies(id, title, type, date_added) VALUES('" +
movieData['id'] + "', '" +
movieData['title'] + "', '" +
movieData['type'] + "', '" +
"2012-01-18 06:05:01" +
"') ON DUPLICATE KEY UPDATE title = 'TEST', type = 'movie', date_added = '2011-11-11 08:02:01'";

It works as expected without the "ON DUPLICATE KEY UPDATE";

DominicM
  • 6,520
  • 13
  • 39
  • 60

1 Answers1

1

SQL for AIR is based on SQLite. Although auto-resolve conflict resolution doesn't apply to foreign keys, it's easy to create an alternate using a before insert trigger.

Mike Petty
  • 949
  • 6
  • 6
  • I've just figured out that it's SQLite, but could you elaborate on the alternative? – DominicM Aug 07 '12 at 20:21
  • 1
    CREATE TRIGGER my_trigger BEFORE UPDATE ON my_table_name FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'account_id violates foreign key account(id)') WHERE new.account_id IS NOT NULL AND (SELECT id FROM account WHERE id = new.account_id) IS NULL; END; If you use a separate tool like sqliteman, this and others can help you "automate" creating triggers like this that mimic the use of enforcing foreign key constraints. – Mike Petty Aug 08 '12 at 05:02
  • Is there a performance improvement over using an if statement to either insert or update? I went this an if statement for now, will try out sqliteman when releasing final version of the app, Thanks. – DominicM Aug 09 '12 at 14:09
  • Honestly I'm not sure (Flex developer background not Db), it would probably be a lot easier in the long run for a lot of tables - but reading the syntax diagram, it doesn't appear to support expressions in that area of the statement. http://www.sqlite.org/lang_createtrigger.html – Mike Petty Aug 09 '12 at 15:18