Exporting the sqlite3 table into file using "C" execute API

The SQLite API call you are using only accepts SQL:
"The sqlite3_exec() interface runs zero or more UTF-8 encoded, semicolon-separate SQL statements passed into its 2nd argument, in the context of the database connection passed in as its 1st argument." [from http://www.sqlite.org/c3ref/exec.html]

If you browse through the source of SQLite you can see that the .output command opens a file and then uses the sqlite3_snprintf API to write the contents of the query result to the opened file handle.

The most recent source that shows what is happening is here:
http://www.sqlite.org/src/artifact/076e1c90d594644f36027c8ecff9a392cf2d3a06

The relevant part for .output is this:

if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){    if( p->outfile[0]=='|' ){      pclose(p->out);    }else{      output_file_close(p->out);    }    p->outfile[0] = 0;    if( azArg[1][0]=='|' ){      p->out = popen(&azArg[1][1], "w");      if( p->out==0 ){        fprintf(stderr,"Error: cannot open pipe \"%s\"\n", &azArg[1][1]);        p->out = stdout;        rc = 1;      }else{        sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);      }    }else{      p->out = output_file_open(azArg[1]);      if( p->out==0 ){        if( strcmp(azArg[1],"off")!=0 ){          fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]);        }        p->out = stdout;        rc = 1;      } else {        sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);      }    }

The .output command of the SQLite shell is a feature of that shell (along with all the other commands that begin with .). If you're working with the C interface, you should do a query to fetch the rows you want, and iterate over them writing them one at a time to the file.

Adapting from the sample code found in this tutorial…

static int callback(void *handle, int argc, char **argv, char **azColName) {     FILE *f = handle;     int i;     const char *sep = "";     for (i=0;i<argc;i++) {         fprintf(f, "%s\"%s\"", sep, argv[i]);         sep = ", ";     }     fprintf(f, "\n");     return 0;}

const char *sql = "SELECT * FROM sample;";sqlite3 *db;FILE *f = fopen("sample.csv", "w"); // ought to check for errors here; demo code!char *errs = NULL;if (sqlite3_open("data.db", &db)) {    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));    sqlite3_close(db);    exit(1);}if (sqlite3_exec(db, sql, callback, f, &errs) != SQLITE_OK)    fprintf(stderr, "SQL error: %s\n", errs);sqlite3_close(db);

The post seems a bit old, but I just want to update for any visitor looking for similar information. There is an open source C/C++ API for SQLite3 database import/export functions.

The API can be accessed via here