TIP #308: Twylite's concerns

Twylite wrote:

> So here is a list of specific concerns that arise from this
> exercise:
>
> (1) The DBI is complex. The comparative lengths of my example
> functions illustrate that clearly. If ::tcl::db::execute had a
> mechanism to report the ordered list of columns returned this would
> make "simple" development a lot easier (comparable to SQLite). This
> change alone would also void my comments about error handling (at
> least to some degree).

OK, so we start needing like [$db execute $sql -columns colsVar etc...]
That's easy enough to add; I can throw that in for the next round.

> (2) The amount of error handling this DBI requires is significant -
> 4 nested error handlers. Other DBIs require 1 to 3. This level of
> error handling detracts from my task of writing in functionality
> makes quality assurance more difficult.

If, as you say, the simple change of adding a place for [$db execute]
to return the column list fixes this, I'm not going to address it
for now. Feel free to follow up if you have further issues.

> (3) Taking variables from the current stack frame is a recipe for
> trouble, and makes writing logic like extract_report more complex
> and slower (best case: [dict with ...]; worst case: foreach {name
> value} { set ... }) I would prefer to see execute take a dict; then
> it is at least clear what variables you are (and are not) providing.
> Taking variables from the stack without clearly indicating them _in
> Tcl code_ (as opposed to another language like SQL) is a little to
> much of a DWIM for my liking.

The latest edits to TIP #308 allow for both versions. They are
both needed: fixed transactional queries like

select balance from accounts where account_number = :acct

really benefit from not having to fabricate a dictionary with the
substituents, while, as you observe, ad-hoc queries, often built
on the fly, need the extra isolation that a dictionary provides.

> (4) Returning the columns as a resultset is a real pain. Rather make
> the columns available as
> 4.a) a list of tuples; or
> 4.b) a list of column names, plus a function to get a dict of
> information about the column (given its name)

I'll think about this one a little bit. You're right that it's
most natural to the user to have it as list-of-tuples, and
that's easy to provide. It also seems more natural to the
database implementor to provide it as a result set, because it's
likely the result of a query against a system catalog. Probably
this is a spot where a little more glue is needed, to give both
sides of the interface their natural representation of the data.

> (5) The primitive function to retrieve a row needs to retrieve it as
> a list, IM(NS)HO.

I address this point in a separate message, and still consider it
an open item.

> (6) I am concerned by the number of ensembles that a simple query
> must create(and clean up). This smells like a performance concern,
> especially for a pure-Tcl implementation for a specific database.

Essentially, it's three: the database connection (which likely
persists, and likely has a fixed name), the statement, and the result
set. Given the fact that database drivers are free to overload [$db
execute] (and in so doing, eliminate the need for the statement and
result-set handles), and given that performance-critical applications
are likely to want the explicit statement handles anyway (because of
the gains achievable with prepared statements), do the tweaks to the
'execute' functionality that you discussed above address this concern?

If the concern is instead a general one with the "noun-verb" syntax of
[$handle command], I'd say, "it works for widgets, why shouldn't it
work for database connections?" This style has been around as long as
Tk has. While I agree that it remains controversial, I'd classify
under, "you can't please everyone."

> (7) A SELECT operation is fundamentally different from other
> commands that can be executed. This is clear for example when you
> read the spec for "resultset rows", which does not provide a
> meaningful value in the case of SELECT. How you query a resultset
> is thus dependant on the operation that was requested, which can
> make creating generic DB manipulation functions rather fun.

What's even more fun is that some things do both: Oracle's "UPDATE
.... RETURNING ..." is a case in point. In any case, the 'statement'
and 'result set' interfaces proposed in the TIP are rather spare: it's
not immediately obvious to me that having separate 'exeute query' and
'execute update' functions will actually simplify things.

Summary:

- [$db execute] needs a place to store the column list, so that
user code doesn't need the result set for that purpose. Will do.
- [$statement execute] and friends all need to take, optionally
a dictionary of substituents. In the TIP already.
- Returning metadata as a result set is unnatural. Conceded.
I'll fix it.

--
73 de ke9tv/2, Kevin


.

Relevant Pages

  • Re: Newbie question about formula based values
    ... if the query hangs or if Access crashes or is someone restarts ... If you want to execute the code from your web site, you can do that as well. ... If your web site can execute SQL code against an Access database, ... In Windows scheduler, create a new scheduled task based on Microsoft ...
    (comp.databases.ms-access)
  • Re: .Execute vs. .RunSQL - Speed vs. Size
    ... >to know which is faster - Dim a database and then use the db.execute method ... Execute a sequence of action queries. ... Note that Execute requires you to resolve any query ... can gain orders of magnitude better improvement by making ...
    (microsoft.public.access.formscoding)
  • Re: disable index while doing insert into
    ... source and target table have multiple indexes, ... to execute this query but when i've deleted the indexes on target ... This is a price you have to pay on any database not just Sybase. ...
    (comp.databases.sybase)
  • Re: Execute queries in back-end database via ADO !?!
    ... > I found that by defining an ADO connection to my back end database, ... execute a SQL query stored in my back end database. ... As long as your data is stored in an MDB file all query processing is ... What program would you expect to execute the query on the back end? ...
    (microsoft.public.access.queries)
  • Re: Duplicating lines
    ... but I am a junior but aspiring database ... functionality while cleaning up. ... A very small sample of the datasheet view for the above query with the ... If I look at a table, everything is good, no duplicates. ...
    (microsoft.public.access.externaldata)