>     In practice, the useful ones are:
>
>     - I expect to receive exactly one scalar (i.e. one row with one column).
>
>     - I expect to receive exactly one vector (i.e. one row with with one or
>     more columns).
>
>     - I expect to receive zero or more vectors (i.e. zero or more rows,
>     each
>     with one or more columns).
>
> Those are sufficient if you only deal in relatively static SELECT
> statements, where the only thing you plug in is specific values.

Right, that's true.

> I think, having poked around a bit now, that -exec is meant for INSERT,
> UPDATE, and DELETE statements, and what it reports is the number of rows
> affected.  If you use it on a SELECT statement, you get the number of
> rows retrieved, but not the data, which indeed is not very useful.

Yeah, we'd use `select count(*) from foo` to get the count, or turn the
table rows into Scheme objects and count those objects.

>     row IDs
>
> They are non-portable but necessary, as long as you are using
> automatically generated primary keys.  (I happen to think this is bad
> style, but it's often very efficient so people do use it.)  So if you
> want to insert a row for a person into one table, and then rows for
> their dependents into another, you need the primary key (that is, the
> row ID) of the row you just added to the persons table, so that you can
> insert the value into the rows you will add to the dependents table as a
> foreign key column.  This procedure lets you get that key, which is not
> readily retrievable otherwise.  Most of the databases I've looked at
> (SQLite, Oracle, SQL Server, MySQL, PostgreSQL) have some equivalent of
> a row ID.

OK, that makes sense. You're probably right on all counts.

However, since we agree that use of rowid is questionable style, I think
we should require the user to explicitly ask for the rowid rather than
give it back implicitly as the default useful thing to return.

Here my next an API proposal with some example code:

;; API:

(sql-prepare    sql-string . params) -> statement

(sql-do         database statement) -> #f
(sql-do/row-id  database statement) -> integer
(sql-query-one  database statement [mapfun]) -> result
(sql-query-all  database statement [mapfun accumulator]) -> state

;; sql-query-one expects exactly one row back and does (apply mapfun
;; row). The mapfun may return multiple values, which are returned
;; from sql-query-one. It raises an exception if it gets no rows, or if
;; it gets more than one row.

;; sql-query-all expects zero or more rows. It does (apply mapfun row)
;; for each row. The mapfun should return one value. Each return value
;; is fed to the accumulator. Finally the accumulator is fed
;; (eof-object) and its final state is returned from sql-query-all.

;; * sql-query-one default mapfun is `values`.
;; * sql-query-all default mapfun is `vector`
;;   and the default accumulator is `list-accumulator` from SRFI 158.

;; Examples:

(define (get-setting name)
   (sql-query-one db (sql-prepare
                      "select type, value from settings where name = ?"
                      name)))

(define (get-user-count)
   (sql-query-one db "select count(*) from users"))

(define (get-users)
   (sql-query-all db "select username, realname from users" cons))

(define (add-user username realname)
   (sql-do
    db
    (sql-prepare "insert into users (username, realname) values (?, ?)"
                 username
                 realname)))

(define (add-user-with-pet username realname petname)
   (let ((userid
          (sql-do/row-id
           db
           (sql-prepare "insert into users (username, realname) values
(?, ?)"
                        username
                        realname))))
     (sql-do db
             (sql-prepare (string-append "insert into user_pets"
                                         " (userid, petname)"
                                         " values (?, ?)")
                          userid
                          petname))))