SQLite C API another convenience routine example

Last time we presented sqlite3_exec example. Beside sqlite3_exec we can use wrapper around  sqlite3_exec and that is sqlite3_get_table. We will get, if call is successful, array of UTF-8 zero terminated strings and we have to free that array at the end. Here is interface for sqlite3_get_table and sqlite3_free_table:

int sqlite3_get_table( sqlite3 *db, /* An open database */ const char *zSql, /* SQL to be evaluated */ char ***pazResult, /* Results of the query */ int *pnRow, /* Number of result rows written here */ int *pnColumn, /* Number of result columns written here */ char **pzErrmsg /* Error msg written here */ ); void sqlite3_free_table(char **result);

It is quite self-explanatory. Number of rows doesn't count column names and we need to add one to it. Cleanup is required in the any case, if all is ОК we need to call sqlite3_free_table, if there is failure we need to do cleanup of error message, like with  sqlite3_exec. We will retrieve content of that jane table from sqlite3_exec example, then we inserted three rows into table. Here is the code:

#include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = NULL, *sql = NULL, **pazResult = NULL; int row, col,i,j; int rc = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL); if (rc != SQLITE_OK) { printf("Failure to open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sql = "select * from jane;"; rc = sqlite3_get_table(db, sql, &pazResult, &row, &col, &err_msg); if (rc != SQLITE_OK ) { printf("Get table failure: %s\n", err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } for(i=0;i<row+1;i++){ for(j=0;j<col;j++) printf("%s\t",*(pazResult+i*col+j)); printf("\n"); } sqlite3_free_table(pazResult); sqlite3_close(db); return 0; }

Very simple and very user friendly API. If we build and execute binary, we should get error:

Get table failure: no such table: jane

Our table is in surogat.db, we repair example, recompile and we should see table printout:

id title full_name 1 Mr Smith 2 Mrs Doe 3 Mr Doe

That is legacy interface and usage is not recommended, though it is very user friendly and that is the reason I supplied example.
As usual, example is built on Linux using gcc and successfully tested. Didn't try on different OS or compiler.