Re: SQLite and callbacks

[Stefan Sperling]

On Tue, Nov 30, 2010 at 01:32:27PM +0000, Philip Martin wrote:
> Stefan Sperling <s...@elego.de> writes:
> 
> > If you run this version of your test, it will run in an endless loop.
> > The callback inserts new values, the caller will see those and invoke
> > the callback again.
> 
> Hmm, I expected the select to block the write causing it to return
> SQLITE_BUSY.  That is what happens if the write is done by a separate
> process, but apparently not when it's the same process (or maybe it's
> using the same database handle that makes it work).

With a different db handle, the callback cannot insert values into the db:

$ ./test                                                
Got number: 1
45: 5: database is locked
48: 5: database is locked
Got number: 2
45: 5: database is locked
48: 5: database is locked
Got number: 3
45: 5: database is locked
48: 5: database is locked
Got number: 4
45: 5: database is locked
48: 5: database is locked
Got number: 5
45: 5: database is locked
48: 5: database is locked
Got number: 6
45: 5: database is locked
48: 5: database is locked
Got number: 7
45: 5: database is locked
48: 5: database is locked
Got number: 8
45: 5: database is locked
48: 5: database is locked


#include <stdio.h>
#include <string.h>

#include <sqlite3.h>

#define CHECK_ERR  \
  if (sqlite3_errcode(db) \
        && (sqlite3_errcode(db) != SQLITE_ROW) \
        && (sqlite3_errcode(db) != SQLITE_DONE))  \
    fprintf(stderr, "%d: %d: %s\n", __LINE__, sqlite3_errcode(db), 
sqlite3_errmsg(db));

#define TEST_DATA \
  "create table foo (num int, message text); "  \
  "" \
  "insert into foo values (1, 'A is for Allegator'); " \
  "insert into foo values (2, 'B is for Bayou'); " \
  "insert into foo values (3, 'C is for Cyprus Trees'); " \
  "insert into foo values (4, 'D is for Dew'); " \
  "insert into foo values (5, 'E is for Everything like'); " \
  "insert into foo values (6, 'F Ferns or'); " \
  "insert into foo values (7, 'G Grass that''s'); " \
  "insert into foo values (8, 'H Home to you'); " \
  ""

void callback(int num)
{
  const char *query = "insert into foo values (?1,?2);";
  sqlite3_stmt *stmt;
  const unsigned char *msg = "new message for you!";
  sqlite3 *db;

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                  NULL);

  printf("Got number: %d\n", num);

  sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
  CHECK_ERR;

  sqlite3_bind_int(stmt, 1, 42);
  sqlite3_bind_text(stmt, 2, msg, strlen(msg) + 1, SQLITE_STATIC);
  CHECK_ERR;

  sqlite3_step(stmt);
  CHECK_ERR;

  sqlite3_finalize(stmt);
  CHECK_ERR;

  sqlite3_close(db);
}

void get_numbers(sqlite3 *db,
                 void (*callback)(int))
{
  const char *query = "select num from foo;";
  sqlite3_stmt *stmt;
  int code;

  sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
  CHECK_ERR;

  code = sqlite3_step(stmt);
  CHECK_ERR;
  while (code == SQLITE_ROW)
    {
      int number = sqlite3_column_int(stmt, 0);
      callback(number);

      code = sqlite3_step(stmt);
      CHECK_ERR;
    }

  sqlite3_finalize(stmt);
  CHECK_ERR;
}


int
main(int argc, char *argv[])
{
  sqlite3 *db;

  remove("test.db");

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                  NULL);
  CHECK_ERR;

  sqlite3_extended_result_codes(db, 1);
  CHECK_ERR;

  sqlite3_exec(db, TEST_DATA, NULL, NULL, NULL);
  CHECK_ERR;

  get_numbers(db, callback);

  sqlite3_close(db);

  return 0;
}