Re: [sqlite] problem with blobs (perl code)

[Nathan Kurz]

On Mon, Dec 05, 2005 at 08:23:19AM -0500, [EMAIL PROTECTED] wrote:
> > OK, so 1.11 is on CPAN which fixes this. However I have another bug 
> > report about this not working for user defined functions, where I do 
> > this:
> > 
> >          s = SvPV(result, len);
> >          sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );

I'm the original reporter of that bug, and I can confirm that while it
did not work in DBD::SQLite 1.09, it works without problem in 1.11.

sqlite3_result_text() appears to deal fine with embedded NUL's.
Attached is a tiny C program I just used to confirm this to myself.

Large thanks to Matt and Richard for so quickly ending up with a great
solution that just transparently works!

--nate



#include <stdio.h>
#include <sqlite3.h>

/* gcc test_blob_as_text.c -lsqlite3 -o test_blob_as_text */


static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}

char blob[] = { 'b', 'l', 0, 'o', 'b'};

void blob_as_text(sqlite3_context *context,
                  int argc,
                  sqlite3_value **argv) {
    sqlite3_result_text( context, blob, sizeof(blob), SQLITE_TRANSIENT );
}

void blob_as_blob(sqlite3_context *context,
                  int argc,
                  sqlite3_value **argv) {
    sqlite3_result_blob( context, blob, sizeof(blob), SQLITE_TRANSIENT );
}

void bytes(sqlite3_context *context,
           int argc,
           sqlite3_value **argv) {
    int length = sqlite3_value_bytes(argv[0]);
    sqlite3_result_int( context, length );
}


int main(int argc, char **argv){
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

  if( argc!=2 ){
    fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
    exit(1);
  }
  rc = sqlite3_open(argv[1], &db);
  if( rc ){
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(1);
  }

  rc = sqlite3_create_function(db, "blob_as_text", 0, SQLITE_UTF8,
                               db, blob_as_text, 0, 0);
  if( rc!=SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
  }

  rc = sqlite3_create_function(db, "blob_as_blob", 0, SQLITE_UTF8,
                               db, blob_as_text, 0, 0);
  if( rc!=SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
  }

  rc = sqlite3_create_function(db, "bytes", 1, SQLITE_UTF8,
                               db, bytes, 0, 0);
  if( rc!=SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
  }

  char query[1000];
  sprintf(query, "SELECT %d as 'Correct Answer', "
          "bytes(blob_as_text()), bytes(blob_as_blob())", sizeof(blob));

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

  sqlite3_close(db);
  return 0;
}