Use of sqlite3_exec

Question

I have the next SQLITE3 commands that generates a file with more than 60 million records:

.mode csv
.output matppp.csv
select mat, ppp from matppp order by mat;
.output stdout

How can I include these commands into a C program using:

 sqlite3_exec(db, "..........", NULL, 0, &db_err);

?

When I attempt to do it myself, the c program generates an expression error when executing.

Thanks!!

Answer 1

I think you really want to use a callback function and perhaps fprintf() to write your formatted output to a file. Fortunately, the prototype for the callback pointer contains an extra (optional) void * which could serve as a FILE * stream, making the callback more re-usable in the future.

AFAIK, sqlite3_exec() does not offer the same interface as the sqlite3 CLI. Its just for queries, not output modifiers.

Check out the example code at the bottom of the link I gave, its very easy to use a callback function.

Answer 2

If you want to do this within C (as opposed to piping something to sqlite3's command line program that has those nifty dot commands) then you will have to use a callback.

For your cutting and pasting convenience, here is the code, hacked out of the Apophenia library for statistical computing.

Part I:

sqlite3 *db=NULL; //The global database handle.

static int apop_db_open(char *filename){
    if (!filename)  
        sqlite3_open(":memory:",&db);
    else            
        sqlite3_open(filename,&db);
    if (!db)
        printf("Not sure why, but the database didn't open.\n");
}

//From the SQLite manual:
#define ERRCHECK {if (err!=NULL) {printf("%s\n",err);  return 0;}}

apop_data * apop_sqlite_query_to_screen(char *query){
  char *err = NULL;
    if (db==NULL) 
        apop_db_open(NULL);
    sqlite3_exec(db, query, The_Callback, a_param, &err); 
    ERRCHECK
}

Part II:

The callback will have the following form, and runs once for each line returned. Notice how the parameter a_param transfers; if you don't need it (as in this example), just set it to NULL above.

int The_Callback(void *a_param, int argc, char **argv, char **column){
    for (int i=0; i< argc; i++)
        printf("%s,\t", argv[i]);
    printf("\n");
}