Sambal

You might think that the following two designs for a routine are equivalent.

First:

 int lookup( int nKey )
 {
   m_qLookup.Reset();
   m_qLookup.Bind( 1, nKey );
   int rc = m_qLookup.Step();
   if( rc != SQLITE_ROW )
     return 0;

   int n = m_qLookup.GetColumnInt(0);

   // some more processing on n

   return n;
 }
 

Second:

 int lookup( int nKey )
 {
   int n = 0;

   m_qLookup.Bind( 1, nKey );
   int rc = m_qLookup.Step();
   if( rc == SQLITE_ROW )
   {
     n = m_qLookup.GetColumnInt(0);

     // some more processing on n
   }
   m_qLookup.Reset();
   
   return n;
 }
 

Yeah, they’re logically equivalent. It’s no error to Reset() a query before it’s been used, and it’s fine for a query to be in-progress when you Finalize() it at the end of processing. The first way has an early return, which some people find ugly. Personally I think that indenting the extra processing (line 11 in example 2) is uglier, especially when it’s more than a few lines.

Except for the one big difference — that is, that the first version, once called, makes it impossible to drop any table, even a temp table, from the database.

I just ran into this. Apparently having any query in progress (after the first Step() and before any Reset()) creates a lock on the schema which prevents certain schema changes. In particular, adds are OK but drops are not. And the apparent action is non-local — shall I say, spooky action at a distance? In my case the query that was un-Reset was in one module and the code that created, used, and dropped (or rather, failed to drop) the temp table was in another module entirely.

Fortunately for me, I had not merged in anybody else’s code yet, so I knew that the the problem had been caused by something I did recently, and it only took a little while to track down. More fortunately, the code was trivially transformed into something that didn’t leave the query open.