API problem "var row in db.Query("SELECT rowid, * FROM foo").ToArray()"

[gentledepp]

Hi!

I migrated SyncClient.SQLite from SQLitePCL to your SQLitePCL.pretty.

Honestly, I do like your api better, but there is a pretty serious gotcha:

The following code will give you an ObjectDisposedException:

// all fine here
var query in db.Query("SELECT rowid, * FROM foo");
// now, when enumerated, the inner IStatement is actually disposed
var rows = query.ToArray()
// so the following line gives you an "objectdisposedexception"
var rowId = rows[0][1].ToInt();

I tried to find out how that works underneath, but I could not find any clue.
What I do know, is that you need to dispose the internals IStatement somehow, but cant there be another way of doing it?

[bordoley]

The issue you are encountering is that the the enumerated IResultSetValue instances are references to underlying SQLite dynamic types, and only valid for a specific iteration of the underly sqlite statement. The following should work.

var rows = db.Query("SELECT rowid, * FROM foo").Select(row => row[0].ToInt()).ToArray();
var rowId = rows[0];

I was pretty sure I documented this but can't seem to find the explanation in the docs, though all the examples illustrate how to use IResultSetValue correctly.

[gentledepp]

I know, and I also found that solution.
However, I guess it would be good to at least throw an exception, which contains e.g. a link to the documentation like:

"the statement is already disposed. Calling any member of IResultSetValue after the enumeration has completed is not supported. Go to https://github.com/bordoley/SQLitePCL.pretty/blob/master/README.md to learn more"

[bordoley]

Yeah, I'd accept a PR for that.

I'm pretty sure the exception is thrown by accessing the StatementImpl.sqlite3_stmt property here:
https://github.com/bordoley/SQLitePCL.pretty/blob/master/SQLitePCL.pretty/Implementation.cs#L130

That property access is called from ResultSetValueImpl.ToInt() here:
https://github.com/bordoley/SQLitePCL.pretty/blob/master/SQLitePCL.pretty/SQLiteValue.cs#L631

[gentledepp]

kk. will do
I already have an open PR ;-)