sqlite - 在 C 代码中返回 expr 不适用于 sqlite3_exec()

Question

返回 expr 不使用 sqlite3_exec() 在 C 代码中给出以下错误，但在 sqlite3 终端上工作正常

sqlite> insert INTO mqtt6 values('abcd',1623066863471168894,1623066863471,119) RETURNING (SELECT sum(size_t) from mqtt6)  ;
76041
sqlite> 

但是当我尝试从 C 代码运行相同的命令时，出现如下错误：

loki@loki-01 ~/sqlite_dbstat/simple_sqlite - $ ./a.out

bytes_written=0  insert OR REPLACE INTO mqtt6 values ('abcd',1623066863471168258,1623066863471168258,119) RETURNING (SELECT sum(size_t) from mqtt6);
**SQL error: near "RETURNING": syntax error**

完整的C代码：

#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#define TABLE_NAME "mqtt5"
int main(void) {

        sqlite3 *db;
        char *err_msg = 0;

        int rc = sqlite3_open("test.db", &db);

        if (rc != SQLITE_OK) {

                fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
                sqlite3_close(db);

                return 1;
        }
        //make table
        char *sql_cr = "CREATE TABLE IF NOT EXISTS mqtt6 (payload TEXT NOT NULL, timestamp INTEGER , uuid TEXT PRIMARY KEY NOT NULL,size_t INTEGER);"; 
        rc = sqlite3_exec(db, sql_cr, 0, 0, &err_msg);

        unsigned long long ts=1623066863471168257;
        unsigned long long bytes_written=0;
        char sql[1000]={'\0'};
        char * str="abcd";
        sprintf(sql,"insert  INTO mqtt6 values ('%s',%llu,%llu,%lu) RETURNING (SELECT sum(size_t) from mqtt6);",str,ts,ts,strlen(str)+8+19+4); 
        printf("bytes_written=%llu  %s\n",bytes_written,sql);

        rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

        if (rc != SQLITE_OK ) {

                fprintf(stderr, "SQL error: %s\n", err_msg);

                sqlite3_free(err_msg);        
                sqlite3_close(db);

                return 1;
        } 
        sqlite3_close(db);
        return 0;
}

标签： sqlitesql-insert