bug#61151: 30.0.50; sqlite-more-p is confusing

[Helmut Eller]

On Sun, Jan 29 2023, Eli Zaretskii wrote:

>> However, sqlite-next seems to return nil once, but then it starts over.
>
> This should be easy to fix.

The documentation[1] also says: 

  SQLITE_DONE means that the statement has finished executing
  successfully. sqlite3_step() should not be called again on this
  virtual machine without first calling sqlite3_reset() to reset the
  virtual machine back to its initial state.

So this would seems like prudent fix.

>> In summary, I think that sqlite-next should signal and error instead of
>> returning nil.
>
> I think we can only signal an error if sqlite-next is called again
> after it returned nil, otherwise it is not justified.  But I'm not an
> expert on databases, so maybe I'm missing something.
>
>> And sqlite-more-p should be used to detect the end of
>> the stream.
>
> I don't think it's possible without trying to read.

If we don't change the way the API works, then maybe the documentation
should mention that the proper way to iterate over the results is a bit
unusual. E.g.:

(let* ((db (sqlite-open))
       (stmt (sqlite-select db "values ('a',0), ('b',1)" nil 'set))
       (rows '())
       row)
  (while (progn (setq row (sqlite-next stmt))
                (sqlite-more-p stmt))
    (push row rows))
  rows)

And perhaps provide a function like sqlite-for-each that hides this
peculiarity.

Helmut

[1] https://sqlite.org/c3ref/step.html