c++ - sqlite 库无法更新已安装的数据库文件

问题描述

我可以使用 sqlite3 命令行工具打开、读取和更新安装程序放置的文件中的数据库，如下所示：

C:\ProgramData\spectronix\oximeter>C:\Users\James\code\bin\sqlite3 spectrumalpha.dat
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE run ( run INTEGER PRIMARY KEY, timestamp, desc );
CREATE TABLE distance ( hwid, distance );
CREATE TABLE spectrum ( run, timestamp, hwid, distance, data );
CREATE TABLE waveDescription ( run, timestamp, hwid );
CREATE TABLE wave ( wave, idx, data );
sqlite> select desc from run where run is 5;
update from sqlite3 tool
sqlite> UPDATE run SET desc='update #2 from sqlite3 tool' WHERE run IS 5;
sqlite> select desc from run where run is 5;
update #2 from sqlite3 tool

但是当我运行这段代码时

#include <iostream>
#include "sqlite3.h"

int main()
{
    sqlite3 * db;
    sqlite3_stmt *st = 0;
    int ret;
    char const * tail = 0;

    ret = sqlite3_open("C:/ProgramData/spectronix/oximeter/spectrumalpha.dat",&db);

    std::cout << "working with db in "
        << sqlite3_db_filename( db, "main" ) << "\n";

    // read initial value
    ret = sqlite3_prepare_v2(
              db,
              "SELECT desc FROM run WHERE run IS 5;"
              , -1, &st, &tail );
    if( ret != SQLITE_OK )
        throw std::runtime_error("sqlite first read");
    sqlite3_step( st );
    std:: cout << "first read is " << sqlite3_column_text(st, 0) << "\n";
    sqlite3_finalize( st );

    return 0;
}

输出显示数据的先前版本

working with db in C:\ProgramData\spectronix\oximeter\spectrumalpha.dat
first read is test4

另一个奇怪的是库代码拒绝更新数据

#include <iostream>
#include "sqlite3.h"

    int main()
    {
        sqlite3 * db;
        sqlite3_stmt *st = 0;
        int ret;
        char const * tail = 0;

        ret = sqlite3_open("C:/ProgramData/spectronix/oximeter/spectrumalpha.dat",&db);

        std::cout << "working with db in "
            << sqlite3_db_filename( db, "main" ) << "\n";

        // read initial value
        ret = sqlite3_prepare_v2(
                  db,
                  "SELECT desc FROM run WHERE run IS 5;"
                  , -1, &st, &tail );
        if( ret != SQLITE_OK )
            throw std::runtime_error("sqlite first read");
        sqlite3_step( st );
        std:: cout << "first read is " << sqlite3_column_text(st, 0) << "\n";
        sqlite3_finalize( st );

        // update value
        ret = sqlite3_prepare_v2(
                  db,
                  "UPDATE run SET desc = 'new_value' WHERE run IS 5;"
                  , -1, &st, &tail );
        if( ret != SQLITE_OK )
            throw std::runtime_error("sqlite update error");
        sqlite3_finalize( st );
        std::cout << sqlite3_changes( db ) << " rows updated\n";

        return 0;
    }

输出

working with db in C:\ProgramData\spectronix\oximeter\spectrumalpha.dat
first read is test4
0 rows updated

我尝试将数据库移动到应用程序的工作目录中，将打开数据库的调用更改为

ret = sqlite3_open("spectrumalpha.dat",&db);

但同样的问题也会发生。

所以，最后，我删除了数据库并从头开始重新创建它。问题，无论它可能是什么，都消失了！任何人都知道它可能是什么 - 以防它再次发生？

在我使用安装程序编写数据库文件之后，问题再次出现的第二天。

标签： c++sqlite

c++ - sqlite 库无法更新已安装的数据库文件

问题描述

解决方案

推荐阅读