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 am doing some experiments with SQLite with a simple test harness using a single table that contains a char string key and a single integer value. The following are pieces of source from the experimental test harness that I am using. I pulled these pieces in order to show the creation of the table along with the function I use to create a record set from a select SQL statement using the call back functionality of SQLite. There are printf() statements and fprintf() statements in various places so that I can see the results of actions as this is a simple console type application for the test harness.

Notice that there are times when you do not need the call back argument so SQLite allows you to specify a NULL pointer indicating not to bother with the call back.

And as you read over the source just remember this is an experimental hack!

The function to create the table looks like:

int CreateSetupTable (sqlite3 *db)
{
    char *zErrMsg = 0;
    int rc;
    char  *aszSqlCreate = "create table tbl1(one varchar(10), two smallint)";
    char  *aszSqlCreateIndex01 = "create unique index index1 on tbl1 (one)";

    do {
        rc = sqlite3_exec(db, aszSqlCreate, 0, 0, &zErrMsg);
        if( rc!=SQLITE_OK ){
          fprintf(stderr, "SQL error: %s\n", zErrMsg);
          sqlite3_free(zErrMsg);
          break;
        }

        rc = sqlite3_exec(db, aszSqlCreateIndex01, 0, 0, &zErrMsg);
        if( rc!=SQLITE_OK ){
          fprintf(stderr, "SQL error: %s\n", zErrMsg);
          sqlite3_free(zErrMsg);
          break;
        }
    } while (0);  // loop only once to allow breaks on errors

    return rc;
}

I insert some records into this table and then have a function that I call to get one or more records from the table using a select SQL statement. The select function retrieves the records and uses a call back to convert each record returned into a C struct. The C struct look like:

typedef struct {
    char cKey[20];
    int  iValue;
} Tbl1Record;

The call back used for the record set uses a struct that contains record select management data. By this what I mean is that the call back takes as its first argument a pointer to a struct that in turn points to the location to put the transformed data along with some information about the size of the memory area. Since a select might return more than one record depending on the where clause, the call back function uses the call back struct to know how many transformed records it can put into the memory area as well as an index so that as it is putting records, it can index through the memory area in order to return multiple transformed records.

The call back management struct looks like this:

typedef struct _RecordProcessor {
    void  *pRecordSet;
    int   nRecordSetMax;
    int   nRecordSetActual;
} RecordProcessor;

The select function looks like:

int SelectRecord (sqlite3 *db, char *cSelect, char *cKey)
{
    char *zErrMsg = 0;
    int rc;
    char aszSqlSelect[128];
    Tbl1Record  myRec[20];
    RecordProcessor myProcessor;

    myProcessor.pRecordSet = myRec;
    myProcessor.nRecordSetActual = 0;
    myProcessor.nRecordSetMax = 20;

    if (cKey) {
        sprintf (aszSqlSelect, "select %s from tbl1 where one='%s'", cSelect, cKey);
    } else {
        sprintf (aszSqlSelect, "select %s from tbl1", cSelect);
    }
    rc = sqlite3_exec(db, aszSqlSelect, MyRecordProcessor, &myProcessor, &zErrMsg);
    if( rc!=SQLITE_OK ){
      fprintf(stderr, "SQL error SelectRecord: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
    } else {
        int i;
        for (i = 0; i < myProcessor.nRecordSetActual; i++) {
            printf ("Rec #%d cKey = %s iValue = %d\n", i+1, myRec[i].cKey, myRec[i].iValue);
        }
    }

    return rc;
}

The call back that processes each record returned by the select looks like this:

static int MyRecordProcessor (void *callBackArg, int argc, char **argv, char **azColName)
{
    int iRetStatus = 0;
    char *colNameTable[] = {
        "one",
        "two"
    };
    Tbl1Record *pTbl1Record = (Tbl1Record *)((RecordProcessor *)callBackArg)->pRecordSet;

    if (((RecordProcessor *)callBackArg)->nRecordSetActual < ((RecordProcessor *)callBackArg)->nRecordSetMax) {
        int i, j;
        int iIndex = ((RecordProcessor *)callBackArg)->nRecordSetActual;

        memset (pTbl1Record + iIndex, 0, sizeof(Tbl1Record));
        ((RecordProcessor *)callBackArg)->nRecordSetActual++;
        for (i = 0; i < argc; i++){
            int j;
            for (j = 0; j < sizeof (colNameTable)/sizeof(colNameTable[0]); j++) {
                if (strcmp (azColName[i], colNameTable[j]) == 0) {
                    switch (j) {
                        case 0:
                            strncpy (pTbl1Record[iIndex].cKey, (argv[i] ? argv[i] : "NULL"), 19);
                            break;
                        case 1:
                            pTbl1Record[iIndex].iValue = atoi (argv[i] ? argv[i] : "0");
                            break;
                        default:
                            break;
                    }
                    break;
                }
            }
        }
    }

    return iRetStatus;
}

Answer 2

The companion web site of the book Using SQLite has some examples. In particular, chapter 7 has some examples of the C/C++ API.

Example code: http://examples.oreilly.com/9780596521196/

Answer 3

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 4

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");
    return 0;
}

//From the SQLite manual:
#define ERRCHECK {if (err!=NULL) {printf("%s\n",err); sqlite3_free(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");
    return 0;
}