Note for the "Named vs numbered SQL parameters" discussion, this
discusses a feature I'd like to fit into it.

> From: Alaric Snell-Pym <xxxxxx@snell-pym.org.uk>
> Date: Wednesday, September 18, 2019 5:18 AM
>
> On 18/09/2019 02:31, xxxxxx@ancell-ent.com wrote:
>
> In general, I reckon drivers can provide their own library that can
> be imported directly by apps that need to use features specific to a
> specific driver.

Yes; what I want is procedures in such database specific libraries,
plus I expect some genetic ones for common data transformations, to be
callable when using the DBI level and above, with the DBI level
passing the results without modification to the DBD driver layers.

>>> The best we can do there is probably to have a "default encoding"
>>> set as part of the options when making a connection, maybe with
>>> the option to override it per-column when binding strings into
>>> queries through some MySQL-specific mechanism....
>>
>> Per column optional pass throughs ... could that be a general
>> mechanism to possibly accomplish other things?  How else are we
>> going to say, *this* value gets interpreted in *this* non-default
>> way?  How ugly might that make the API?  Here's a bit more from
>> Alaric on this:
>
> I was thinking something like, assuming we have a query procedure
> that binds values to a query, like so:
>
> (query "SELECT * FROM people WHERE name = ?" user-name)
>
> ...that normally does automatic type conversions, eg if user-name is
> a string it'll be handed in as a string to the DB engine using the
> "default" encoding.
>
> We might also have mysql-specific mapping from a mysql-specific opaque
> type that wraps a string and an encoding to send it to the DB in:
>
> (import (prefix mysql mysql:))
>
> (query "SELECT * FROM people WHERE name = ?"
>      (mysql:encoded-string user-name 'EBCDIC))

That's exactly what I came up with last night after posting the
message you were replying to.  The input parameters can be wrapped
with database specific and probably generic "directive" procedures
that Do The Right Thing in concert with their DBD layers below.

The results of those procedures, or the example above without a
modification procedure call, should be passed through the DBI level
and only converted into a string or whatever to send to the database
at the DBD level, which might have to consult configuration
information previously fed to it, and/or metadata it's gleaned from
the database.

What I haven't yet figured out is how we should direct the DBD to do
the same sorts of things with results.  Can it all be done at the DBI
level when we get them back, perhaps with what are logically
callbacks to the DBI level, or do queries and other statement types
that return data need to capture and send down to the DBD output data
and quirk directives.

Maybe something like an optional end parameter, a list with #f for
each part of the tuple that doesn't need special treatment.  I.e.
something like:

(query "SELECT address, last_login FROM people WHERE name = ?"
       user-name
       '(#f [do-some-magic-with-timestamps]))

(Side note: if starting a project, you should never, EVER, have a
table named users.  I append something semi-random to such tables,
like "users_plover" fir Defense in depth against injection attacks.)

- Harold