sqlite_blob.c

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

void select(sqlite3 *db) {
    const char *tail;
    sqlite3_stmt *stmt;

    char *query = "SELECT x'666f6f00626172';";
    sqlite3_prepare_v2(db, query, strlen(query)-1, &stmt, &tail);
    sqlite3_step(stmt);
    char *result = (char *) sqlite3_column_blob(stmt, 0);

    for(size_t i = 0; i < sizeof(result)-1; i++) {
        if (result[i] == '\0') {
            fputs("\\0", stdout);
        }
        printf("%c", result[i]);
    }
    printf("\n");
    printf("%s", result);
    printf("\n");
    sqlite3_finalize(stmt);
}

void length(sqlite3 *db) {
    const char *tail;
    sqlite3_stmt *stmt;

    char *query = "SELECT LENGTH(x'666f6f00626172');";
    sqlite3_prepare_v2(db, query, strlen(query)-1, &stmt, &tail);
    sqlite3_step(stmt);
    char *result = (char *) sqlite3_column_text(stmt, 0);

    printf("%s\n", result);
    sqlite3_finalize(stmt);
}

int main() {
    sqlite3* db;
    sqlite3_open("sq", &db);

    select(db);
    length(db);

    sqlite3_close(db);
    return 0;
}

gcc -o sqlite -g sqlite_blob.c -lsqlite3
./sqlite

result:

foo\0bar
foo
7