Sambal

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

First:
[cpp]
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;
}
[/cpp]

Second:
[cpp highlihght=”11″]
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;
}
[/cpp]

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.