SQLite3 is not writing table creations to the database

September 6, 2022

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?

>Solution :

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;
}