mercredi 9 mars 2016

How to load and select BLOB as text using SQLite and C?

I create a new table like this using C and SQLite 3.11.1:

PRAGMA encoding = "UTF-8";
DROP TABLE IF EXISTS t;
CREATE TABLE IF NOT EXISTS t(compi BLOB);

To insert a register with non-ASCII characters I do the following:

  • Read n bytes of a binary file.
  • Convert it to hex string.
  • Concatenate "INSERT INTO t VALUES (x'" + mihexstring + ");"

Resulting this:

INSERT INTO t VALUES (x'0000000003127c0000000000309c0000000000000c0000000000001c');

First question: there is a simpler way to load the row skipping the hex conversion?

Well, then the row is loaded correctly, but I can't select the data an save the content of BLOB as text to a file (or a variable). At the momment I'm doing this:

sql = "select compi from t;";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

Then I want to save it to a file converted to text again, so this is the callback of the select's query:

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   FILE *f = fopen("file.txt", "wb");
   if (f == NULL){
      printf("Error opening file!\n");
      exit(1);
   }

   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i]);
      fprintf(f,"%s\n",argv[i]); // does not works...
   }
   printf("\n");

   fclose(f);
   return 0;
}

Before I execute it file.txt is empty. How could I save the BLOB into a file or a variable? And to store the BLOB in a variable what kind of type should be? Thank you in advance!

Aucun commentaire:

Enregistrer un commentaire