SQLite3 Notification Callbacks (sqlite3_update_callback / sqlite3_commit_callback / sqlite3_rollback_callback)

SQLite provides mechanism to registers a callback function to be invoked whenever a transaction is ‘Committed’ or ‘Rollbacked’ or whenever a row is ‘Updated, Inserted or Deleted’.

void *sqlite3_update_hook(sqlite3*, void(*)(void *,int ,char const *,char const *,                          sqlite3_int64), void*);
void *sqlite3_commit_hook(sqlite3*, int(*)(void*), void*);
void *sqlite3_rollback_hook(sqlite3*, void(*)(void *), void*);

=====================================================================

Sample Program

======================================================================

Click here to Download sample program.

/* C code for SQLite3 Update/Commit/Rollback hook */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

#define ENABLE_DB_TRANSACTION
#define BUFFER_SIZE 256

void
update_callback ( void *user_data, int operation_type,
char const *database, char const *table,
sqlite3_int64 rowid)
{
printf (“\n Operation type – %d \n Database – %s\n Table – %s\n RowId – %d\n User-Data – %s \n”,
operation_type, database, table, rowid, (char *)user_data);
}

int
commit_callback (void *user_data)
{
printf (“\nuser-data : %s\n”,user_data);

/* Return 0 to commit */
/* Return -1 to rollback transaction instead of commit */
return -1;
}

void
rollback_callback (void *user_data)
{
printf (“\nuser-data : %s\n”,user_data);
}

/* Main Routine */
int
main ()
{
sqlite3 *dbc = NULL;
sqlite3_stmt *stmt = NULL;
int ret = 0;
char buf[BUFFER_SIZE] = {0};
int i = 0;

/* ===== Connect to DB ===== */

ret = sqlite3_open_v2 (“test.db”, &dbc,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
NULL);

/* ======== Create a table in Database ====== */

memset (buf, 0, BUFFER_SIZE);
sprintf (buf, “CREATE TABLE JI ( id integer, name varchar(64), age SMALLINT, PRIMARY KEY(id))”);

ret = sqlite3_prepare_v2 (dbc, buf, -1, &stmt, NULL);

ret = sqlite3_step (stmt);

ret = sqlite3_reset (stmt);

sqlite3_update_hook(dbc, update_callback, (void *)buf);
sqlite3_commit_hook(dbc, commit_callback, (void *)”MY COMMIT DATA”);
sqlite3_rollback_hook(dbc, rollback_callback, (void *)”MY ROLLBACK DATA”);

/* ===== Insert 2 Rows ===== */

#ifdef ENABLE_DB_TRANSACTION
ret = sqlite3_prepare_v2 (dbc, “BEGIN IMMEDIATE TRANSACTION”, -1, &stmt, NULL);

ret = sqlite3_step (stmt);

ret = sqlite3_reset (stmt);
#endif

for (i=1; i<=2; i++ )
{
sprintf (buf, “INSERT INTO JI (id, name, age) values (%d, ‘sp%d’, %d)” , i,i,24+i);

ret = sqlite3_prepare_v2 (dbc, buf, -1, &stmt, NULL);

ret = sqlite3_step (stmt);

ret = sqlite3_reset (stmt);
}

#ifdef ENABLE_DB_TRANSACTION
ret = sqlite3_prepare_v2 (dbc, “COMMIT”, -1, &stmt, NULL);

ret = sqlite3_step (stmt);

ret = sqlite3_reset (stmt);
#endif

/* =========== Free handlers ============ */
exit:

/* Free statement handler */
if (stmt)
{
ret = sqlite3_finalize (stmt);
}

/* Free Connection handler */
if (dbc)
{
ret = sqlite3_close_v2 (dbc);
}
return 0;
}

=================================================================
Sample Output
=================================================================

[root@localhost anukul]# gcc -o ji ji_hook_sqlite.c -lsqlite3

[root@localhost anukul]# ./ji

Operation type – 18
Database – main
Table – JI
RowId – 1
User-Data – INSERT INTO JI (id, name, age) values (1, ‘sp1’, 25)

Operation type – 18
Database – main
Table – JI
RowId – 2
User-Data – INSERT INTO JI (id, name, age) values (2, ‘sp2’, 26)

user-data : MY COMMIT DATA

user-data : MY ROLLBACK DATA

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s