sqlite3_column_text returned data gets corrupted during finalize/close

Question

I'm not sure what's happening here but I'm finding that the returned data from sqlite3_column_text is being altered during the finalize/close sqlite stage.

  // rc not handled in this abbreviated code

  sqlite3 *db;
  sqlite3_stmt *stmt;
  char * sql;

  const char * tail;
  int rc;

  char * dbName = "C:\\db\\myblobs.db";
  int myIndex = 0;

  char * myLocation1;
  string myLocation2;   

  rc = sqlite3_open(dbName, &db);

  sql = "SELECT location FROM blobs WHERE key = ?";
  rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
  sqlite3_bind_int(stmt, 1, myIndex);
  rc = sqlite3_step(stmt);

  myLocation1 = (char*)sqlite3_column_text(stmt, 0);
  myLocation2 = (char*)sqlite3_column_text(stmt, 0);

  // can process myLocation1 & myLocation2 fine here

  sqlite3_finalize(stmt); // data myLocation1 points to get corrupted
  sqlite3_close(db);      // data myLocation2 points to gets further corrupted

The problem is in relation to myLocation1. The data it points to is fine until it hits the sqlite3_finalize and sqlite3_close statements. However mylocation2 remains unaltered. So not sure what's happening here. After executing sqlite3_close(db), myLocation1 is identified as a "Bad Ptr" within Visual Studio 2010.

Any help most appreciated.

Answer 1

From the fine manual:

const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
[...]
The pointers returned are valid until a type conversion occurs as described above, or until sqlite3_step() or sqlite3_reset() or sqlite3_finalize() is called.

So as soon as you call sqlite3_finalize, the return values from sqlite3_column_text become invalid and your myLocation1 and myLocation2 pointers point to garbage.

If you need those strings after you call sqlite3_finalize then you'll have to copy them into memory that you control. In fact, due to the are valid until a type conversion occurs note, you should copy them immediately and only use (char*)sqlite3_column_text(stmt, 0); while you're making your own copies.