在DELPHI 10.3.1中，ON CONFLICT(id)DO UPDATE SET对FireDAC不起作用 [英] ON CONFLICT(id) DO UPDATE SET does not work for FireDAC in DELPHI 10.3.1

问题描述

附加第二个数据库后，我尝试使用ON CONFLICT(id)DO UPDATE在第一个数据库的表中插入和更新数据.字段ID是第一个表的主键.

After attaching the second database I tried to insert and update data in the table of the first database using ON CONFLICT(id) DO UPDATE. Field id is the primary key for the first table.

  FDQuery1.EXECSQL('ATTACH ''D:\Update2019.DB'' AS DBUpdate');

  FDQuery1.SQL.Text:=
    'INSERT INTO acts (id,title) SELECT id,title FROM DBUpdate.acts'+
    '   WHERE (DBUpdate.acts.id >100)'+
    '   ON CONFLICT(id) DO UPDATE SET'+
    '   title=excluded.title;'
  FDQuery1.ExecSQL;

我收到错误消息:靠近"ON"的错误:语法错误" 当我删除WHERE(DBUpdate.acts.id> 100条件 错误消息AS:"DO"附近的错误:语法错误".

I get error message: "ERROR near "ON":syntax error" When I removed WHERE (DBUpdate.acts.id >100 condition the error message AS: "ERROR near "DO":syntax error".

同一查询在SQLITESTUDIO中工作正常.

The same query works fine in SQLITESTUDIO.

推荐答案

正如注释中正确指出的，您的应用程序使用的SQLite不支持此语法.您将需要使用SQLite的较新版本.为此，请遵循 FireDAC SQLite connection 主题，否则，Delphi会使用随发布的版本的静态链接的SQLite对象来构建FireDAC应用程序(这使您的应用程序依赖于此类SQLite版本，而无论周围有什么SQLite DLL).

As correctly indicated in comments, SQLite that your application uses does not support this syntax. You will need to use newer version of SQLite. To do this, follow the Dynamic linking paragraph of FireDAC SQLite connection topic, otherwise Delphi builds your FireDAC application with statically linked SQLite objects of the version distributed with it (which makes your application dependent on such SQLite version no matter what SQLite DLLs are all around).

请不要忘记此任务中最重要的部分.修改 FireDAC.inc 包含文件以使用动态链接.如果您不想破坏Delphi设置源文件夹，请将所有FireDAC模块复制到其他位置，将它们添加到项目的构建搜索路径中，然后修改 FireDAC.inc 包含文件在那里.

Just don't forget the most important part of this task; modifying the FireDAC.inc include file to use dynamic linking. If you don't want to break your Delphi setup source folder, copy for example all the FireDAC modules somewhere else, add them into the build search path of your project, and modify the FireDAC.inc include file there.

这篇关于在DELPHI 10.3.1中，ON CONFLICT(id)DO UPDATE SET对FireDAC不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！