Instead of this workaround, it would be much better if we could change Connection_lastRowId() to work with postgresql somehow. Any ideas anyone?
And yes, reusing a prepared statement after it returns a result set is problematic for SQLite, hence Connection_clear() was exposed. Creating a new statement for the result set instead of reusing the prepared one should work, but it has not been a big enough nag to refactor.
On 1. mai. 2008, at 17.45, Paul J Stevens wrote:
Paul J Stevens wrote:
That was my point. I *need* to used executeQuery to enable the postgres work-around. Remember that my code needs to work for all backends. Using executeQuery works for both postgres (using the returned resultset) as well as for mysql (getting lastRowId directly).
I've figured out what was wrong. I need to call ResultSet_next() before I can determine the lastRowId() after calling executeQuery. Makes sense after reading the code. (sqlite3_step only being called from ResultSet_next).
However, using this pattern means I have to a full Connection_preparedStatement, PreparedStatement_setXXX, PreparedStatement_executeQuery, Connection_clear for each row I insert where I need to obtain the ROWID. More room for improvement, imo.
This makes me suspicious a more general rule may apply: can I re-use a prepared statement that returns a result set? I suspect not, at least not for sqlite....