Sqlite3_exec () Callback Function Refinement

Question

I'm having trouble understanding the use of the callback function in a SQLite3 database.

I understand that it is used to move SELECT statements with multiple records. But I don’t understand how this is done or how to make my own useful callback. I read TutorialsPoint several times to try to understand, but that just doesn't do it for me.

When I use my example and debug in Visual Studio to see how the arrays of arguments fill and intersect, I get lost. In addition, VS only shows the current slot in the array, not the entire array.

If you need any clarification, please let me know how I am here to find out!

I ask someone to explain how the callback is used. Perhaps some examples of how others used it. Just an explanation of what this does:

static int callback(void *data, int argc, char **argv, char **azColName){ int i; fprintf(stderr, "%s: ", (const char*)data); for(i=0; i<argc; i++){ printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } printf("\n"); return 0; } 

Answer 1

Suppose you have a very simple table called User , which looks something like this:

  ╔════╦═════════╗
 ║ ID ║ Name ║
 ╟────╫──────────╢
 ║ 1 ║ Slvrfn ║
 ║ 2 ║ Sean ║
 ║ 3 ║ Drew ║
 ║ 4 ║ mah ║
 ╚════╩═════════╝

And you call sqlite3_exec as follows (the arguments are described in detail in the documentation ):

 /* Error handling omitted for brevity */ sqlite3_exec(db, "SELECT * FROM User", my_special_callback, NULL, NULL); 

SQLite will execute the passed SQL statement, and for each row of results found, it will call my_special_callback . So, in our User table example, my_special_callback will be called 4 times. So create my_special_callback :

 /* * Arguments: * * unused - Ignored in this case, see the documentation for sqlite3_exec * count - The number of columns in the result set * data - The row data * columns - The column names */ static int my_special_callback(void *unused, int count, char **data, char **columns) { int idx; printf("There are %d column(s)\n", count); for (idx = 0; idx < count; idx++) { printf("The data in column \"%s\" is: %s\n", columns[idx], data[idx]); } printf("\n"); return 0; } 

Given our sample table and data, the result will look like this:

  There are 2 column (s)
 The data in column "ID" is: 1
 The data in column "Name" is: Slvrfn

 There are 2 column (s)
 The data in column "ID" is: 2
 The data in column "Name" is: Sean

 There are 2 column (s)
 The data in column "ID" is: 3
 The data in column "Name" is: Drew

 There are 2 column (s)
 The data in column "ID" is: 4
 The data in column "Name" is: mah

Now, how to do this is where the 4th argument to sqlite3_exec . From the documentation:

The 4th argument of sqlite3_exec () is passed up to the 1st argument of each callback call.

So, let's say that we want to run our SQL and build a linked list of the names of all our users. The first thing we need to do is change the way we call sqlite3_exec :

 /* Create my fictional linked list */ struct my_linked_list *head = my_linked_list_alloc(); /* * Pass a pointer to my list as the 4th argument to sqlite3_exec. Error * handling omitted for brevity */ sqlite3_exec(db, "SELECT * FROM User", my_special_callback, head, NULL); /* My list is now built, I can do stuff with it... */ my_linked_list_traverse(head, /* ... Stuff ... */); 

And change my_special_callback to use it

 /* * Arguments: * * list - Pointer to a linked list of names * count - The number of columns in the result set * data - The row data * columns - The column names */ static int my_special_callback(void *list, int count, char **data, char **columns) { struct my_linked_list *head = list; /* * We know that the value from the Name column is in the second slot * of the data array. */ my_linked_list_append(head, data[1]); return 0; } 

Now, if you used the callback that you included in your question, you would call it like this:

 /* * Pass the table name as the 4th argument to sqlite3_exec. Error * handling omitted for brevity */ sqlite3_exec(db, "SELECT * FROM User", callback, "User", NULL); 

The conclusion will be:

  User: 
 ID = 1
 Name = Slvrfn

 User: 
 ID = 2
 Name = Sean

 ... etc ...

(Except that the User: part will be printed in stderr instead of stdout)

Hope this helps you figure it out. Let me know if there is something you don’t understand.

Answer 2

This tutorial is terrible because it uses nothing but sqlite3_exec() .

In general, the only useful way to use sqlite3_exec() is to replace it with sqlite3_prepare_v2 () / sqlite3_step () / sqlite3_column _ * () / sqlite3_finalize () so that you can read the data in the place where you really need to process it:

 sqlite3_stmt *stmt; const char *sql = "SELECT ID, Name FROM User"; int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); if (rc != SQLITE_OK) { print("error: ", sqlite3_errmsg(db)); return; } while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { int id = sqlite3_column_int (stmt, 0); const char *name = sqlite3_column_text(stmt, 1); // ... } if (rc != SQLITE_DONE) { print("error: ", sqlite3_errmsg(db)); } sqlite3_finalize(stmt);