SQLite3 is not writing table creations to the database-CodePudding

I am currently struggling with a problem that looks to me like it's a regression since I have not previously experienced it before.

The following program is supported to create a SQLite3 database, a new table called sample, and populate it with a single row.

#include <sqlite3.h>
#include <stdio.h>

int main() {
  int rc;
  sqlite3_stmt* stmt;
  sqlite3* db;
  rc = sqlite3_open("/tmp/test_second.db", &db);
  if (rc) {
     printf("Failed to open sqlite3 database: %s\n", sqlite3_errmsg(db));
     return 1;
  }

  rc = sqlite3_prepare_v2(db, "CREATE TABLE sample (anum INTEGER PRIMARY KEY); ", -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
       printf("Failed to create table: %s\n", sqlite3_errmsg(db));
       return 1;
  }
  sqlite3_finalize(stmt);

  rc = sqlite3_prepare_v2(db, "INSERT INTO sample (anum) VALUES (0); ", -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
       printf("Failed to insert row: %s\n", sqlite3_errmsg(db));
       return 1;
  }
  sqlite3_finalize(stmt);
  sqlite3_close(db);

  return 0;
}

I have compiled this on two different environments: CentOS 7.9 with GCC 4.8.5 and SQLite 3.7.17, and Ubuntu 20.04.5 LTS with GCC 9.4.0 and SQLite 3.31.1.

On both environments, compiling and running the program gives the following error:

Failed to insert row: no such table: sample

And the database is a zero-length file with no tables inside. However, pasting these SQL statements directly into an sqlite3 shell works flawlessly. What is wrong with the code?

CodePudding user response：

You're just preparing statements to be run, but you're not running them using the sqlite3_step() function.

The flow is written out in the intro to the SQLite C interface document.

For your two static SQL statements, the convenience wrapper sqlite3_exec() would work out fine:

#include <sqlite3.h>
#include <stdio.h>

int main() {
  int rc = 0;
  sqlite3 *db;
  rc = sqlite3_open("/tmp/test_second.db", &db);
  if (rc) {
    printf("Failed to open sqlite3 database: %s\n", sqlite3_errmsg(db));
    return 1;
  }

  rc = sqlite3_exec(db, "CREATE TABLE sample (anum INTEGER PRIMARY KEY); ", NULL, NULL, NULL);
  if (rc != SQLITE_OK) {
    printf("Failed to create table: %s\n", sqlite3_errmsg(db));
    return 1;
  }

  rc = sqlite3_exec(db, "INSERT INTO sample (anum) VALUES (0); ", NULL, NULL, NULL);
  if (rc != SQLITE_OK) {
    printf("Failed to insert row: %s\n", sqlite3_errmsg(db));
    return 1;
  }
  sqlite3_close(db);

  return 0;
}