A C cgi script to serve binary file from sqlite3_column_blob pointer

Question

I retrieved binary data from sqlite database and now I have pointer to binary data (sqlite3_column_blob). How output this data to client with C cgi script?

char *sql = "SELECT Data FROM Images WHERE Id = 1";

sqlite3_stmt *pStmt;
rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, 0);

if (rc != SQLITE_OK ) {

    fprintf(stderr, "Failed to prepare statement\n");
    fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));

    sqlite3_close(db);

    return 1;
} 

rc = sqlite3_step(pStmt);

int bytes = 0;

if (rc == SQLITE_ROW) {

    bytes = sqlite3_column_bytes(pStmt, 0);
}
// >>>>next row is function to save file but I want output binary data to client<<<<  
fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, fp);

Answer 1

Ok, thanks to all for the support. Below the code I'm looking for, simply send "sqlite3_column_blob" to stdout...easy!

int main(void) {
    sqlite3 *db;
    char *err_msg = 0;
    int rc = sqlite3_open("./test.db", &db);
    if (rc != SQLITE_OK) {
        // error opening the SQLite database, send an error message to
        // requesting application as plain text.
        fprintf(stdout, "%s", "Content-Type: text/plain;\n\n");
        fprintf(stdout, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    char *sql = "SELECT Data FROM Images WHERE Id = 1";
    sqlite3_stmt *pStmt;
    rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, 0);
    if (rc != SQLITE_OK ) {
        // error with SQLite retrieving data, send an error message to
        // requesting application as plain text.
        fprintf(stdout, "%s", "Content-Type: text/plain;\n\n");
        fprintf(stdout, "Failed to prepare statement\n");
        fprintf(stdout, "Database error: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    rc = sqlite3_step(pStmt);
    int bytes = 0;
    if (rc == SQLITE_ROW) {
        // send the content type HTTP response directive to requesting
        // application. the SQLite BLOB contains JPEG image data.
        fprintf(stdout, "%s", "Content-Type: image/jpeg;\n\n");
        bytes = sqlite3_column_bytes(pStmt, 0);
    }

    bytes = sqlite3_column_bytes(pStmt, 0);
    // send the image data from the SQLite BLOB to the requesting application
    fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, stdout);

    // cleanup and close SQLite connection and exit
    rc = sqlite3_finalize(pStmt);
    sqlite3_close(db);

    return 0;
}

Answer 2

Common Gateway Interface is an older form of web server interface (see the W3 page here http://www.w3.org/CGI/ ) and from my memory the web server passes the socket to the CGI application which is started as a child process I think as both STDIN and STDOUT. The CGI application is responsible for all of the HTTP headers and all the rest of the content management.

So what your application is going to need to do is to create the necessary HTTP header directives to indicate the content-type and other pieces then send down the bytes of content as the body.

One question I would ask is why do this in C versus some other language such as Perl or Php which have nice support for this sort of thing?

The first step is to get and to then process the request header data. This may vary depending on the specific web server and the CGI functionality it uses. It seems in the past it was a matter of just reading from STDIN processing the HTTP request header one carriage return separated line at a time until an empty line is found. However your particular web server may provide a different mechanism.

The HTTP response header information is usually fairly standard and much of it will not vary other than the content-length which will vary due to image size hence number of bytes being sent down. I am pretty sure that in the past I have actually ignored the content-length HTTP response directive since sending data down and then closing the socked is an indication that the content download is done.

If you read over the IETF RFC 3875 Common Gateway Interface you will see a lot of "system defined" loopholes so much what you will need to do will depend on your web server.

Here is a How To for CGI programming with Apache web server.

This article W3 HTTP Header Field Definitions provides information about the various header directives.

This W3 Content-type header field provides information specifically about the content-type directive.

Here is an O'Reilly open book on CGI Programming to provide an overview of the fundamentals however it looks to be a bit old.

Also take a look at the answer for this stack overflow CGI c file open on CGI which mentions a couple of libraries that may be helpful.