Export sqlite3 table to a text file from C program

Question

Iam having a database in Sqlite3. I am doing all my operations by writing application in Linux C(API:sqlite3_exec). Currently Iam trying to export the database to a text file. Can anyone suggest query statements fro the same.

Answer 1

This is not exactly what you are looking for but pretty close. This will output your query in JSON format to outbuf, I did not include b64encode() in the snippet.

 int sqlite3_json(sqlite3_stmt *s, char *outbuf, size_t blen, size_t *used)
{
    int nuse=0;
    int res,i=0,ncolumn=sqlite3_column_count(s);

    nuse+=snprintf(outbuf+nuse,blen-nuse,"[\n");
    while(SQLITE_ROW==(res=sqlite3_step(s))) {
        nuse+=snprintf(outbuf+nuse,blen-nuse, "\t{\n");
        for(i=0;i<ncolumn;++i) {
            nuse+=snprintf(outbuf+nuse,blen-nuse,
                    "\t\t\"%s\" : ", sqlite3_column_name(s,i));
            switch (sqlite3_column_type(s,i)) {
                case SQLITE_INTEGER:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "%d,\n", sqlite3_column_int(s,i));
                    break;
                case SQLITE_FLOAT:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "%e,\n", sqlite3_column_double(s,i));
                    break;
                case SQLITE_TEXT:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "\"%s\",\n", sqlite3_column_text(s,i));
                    break;
                case SQLITE_BLOB:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,"\"");
                    nuse+=b64encode((char*) sqlite3_column_blob(s,i),
                            sqlite3_column_bytes(s,i),
                            outbuf+nuse,blen-nuse);
                    nuse+=snprintf(outbuf+nuse,blen-nuse,"\",\n");
            }
        }
        nuse-=2; // trailing ',\n'
        nuse+=snprintf(outbuf+nuse,blen-nuse, "\n\t},\n");
        if( blen < nuse ) {
            printf("buffer overflow\n");
            break;
        }
    }
    if(i&&res==SQLITE_DONE) nuse-=2; //trailing ',\n'
    nuse+=snprintf(outbuf+nuse,blen-nuse,"\n]\n");
    if(used) *used=nuse;
    return res;
}