TIP #308 - Tcl Database Connectivity - revision 1.7 available
Kevin Kenny
http://tip.tcl.tk/308.
This is a major version change with considerable new and modified
functionality. Chief changes from the initial version:
2007-11-23
Expanded transaction management to have both the transaction
command and explicit transaction boundaries. Added transaction isolation
levels.
Added lists as an alternative to dicts as a representation of rows
in result sets. Added a side interface for retrieving the set of column
names in the convenience procedures.
Simplified introspection to return lists instead of result sets
Added batch processing.
Added asynchronous query processing.
Added an interface for stored procedures.
Added a discussion of returning refcursors.
2007-11-16
Changed the transaction management API from explicit commit and
rollback to a model where a script is executed as an atomic operation.
Changed the "execute" API and the convenience procedures that use
it to accept an optional dictionary containing substituents, so the
substituents need not pollute the local namespace. The version accepting
variables is still provided, because it is useful in the case of static
queries where the substitutions follow a predetermined pattern.
Added reference to the author's cover letter on tcl-core.
Added missing citation of the nstcl-database API.
--
73 de ke9tv/2, Kevin
Twylite
As this TIP grows I suspect there may be a need to define 2 levels of
functionality/support - those features offered by basic DBMSs and easy
to implement, and features associated with high-end DBMS which may not
be used by the 80% case and may require emulation for less powerful
DBMSs.
For example, Level 2 support would include all Level 1 features plus
batch processing, async, stored procedure support, and possibly even
transaction management.
As a separate issue I am far from convinced by the "-as dict|list"
argument. This smells (like a hack), it adds the need to end options
in "--" (or don't, and see what unexpected errors you find after you
deploy ...), and it doesn't have any precedent in Tcl core APIs that I
am aware of (it's not Tclish).
What's wrong with -listVar xx -dictVar yy ? It provides the same
functionality, is cleaner, and allows you to get BOTH dict and list
representations if you need/want them.
I also found the docs on nextrow confusing - does it return {} if
there is no row (that's how I read it)? That would mean the return
value changes depending on if you request dicts or lists? Yuk :( And
if there are no rows then the proc shouldn't touch the variable at all
(at least the caller should expect to be able to make no assumptions
about the content of the variable, which is how pretty much all APIs
that look like this work).
Similar complaint about the tcl::db convenience methods. The
allrows / execute / execcute_with_dictionary can be replaced by a
single function with a non-confusing API and greater functionality
than the three separate functions put together.
Sorry - I'd like to be more specific here but I can't access the TIP
at the moment (server is giving a 402 error, looks like a
misconfiguration). I also contributed a comment that summarised the
issues raised on the c.l.t thread into response to the TIP's
announcement, but that appears to have gone AWOL :( Next time I'll
save a local copy.
Regards,
Twylite
suchenwi
> I also found the docs on nextrow confusing - does it return {} if
> there is no row (that's how I read it)? That would mean the return
> value changes depending on if you request dicts or lists? Yuk :(
{} is the string rep of an empty list as well as an empty dict :^)
> And
> if there are no rows then the proc shouldn't touch the variable at all
> (at least the caller should expect to be able to make no assumptions
> about the content of the variable, which is how pretty much all APIs
> that look like this work).
But this is comparable to: gets $channel varName
which sets varName to "" on EOF.
Twylite
bauersa...@siemens.com> wrote:
> But this is comparable to: gets $channel varName
> which sets varName to "" on EOF.
... or on insufficient-data-in-nonblocking-mode, or on a valid but
empty line. In other words you can only trust the contents of varName
when [gets] returns >= 0; and with it returns <0 you must check [eof]
and/or [fblocked].
Setting varName to "" on EOF is therefore largely pointless. It would
be better for the API to state clearly that nothing can be assumed
about the contents of varName unless [gets] returns >= 0, thereby
ensuring correct use of the API. In fact you shouldn't even be able
to assume that varName is defined after a call like this (the fact
that is needs to be places two non-obvious burdens on implementors:
you must force the variable to exist in the caller's scope unless you
throw an error, even if the contents of that variable are meaningless;
and you must avoid altering the variable until you have achieved
complete success).
This "out variable contents unreliable except on complete success" is
a common patterns in APIs, including libc and the MS platform API.
My 2c
Twylite
Twylite
> As a separate issue I am far from convinced by the "-as dict|list"
> argument. This smells (like a hack), it adds the need to end options
> in "--" (or don't, and see what unexpected errors you find after you
> deploy ...), and it doesn't have any precedent in Tcl core APIs that I
> am aware of (it's not Tclish).
Let me be a little more specific about that, now that I can see the
TIP again.
--
set sql_stmt [::msgcat::mc sql_get_mail_list]
::tcl::db::execute $mydb -as lists $sql_stmt v { send_mail_to {*}$v }
--
You saw the error, right? We're missing that "optional" --.
The statement that msgcat loaded from the SQL dialect file is
equivalent to this:
::msgcat::mcset mysql sql_get_mail_list {-- Get e-mail addresses and
salutations
SELECT email, name FROM t_mail_users}
So we're going to get an error 'unknown option "-- Get e-mail
addresses ..."'.
Ask someone who's used tcllib's des package how often you get nailed
by this problem, and why testing doesn't necessarily pick it up.
Regards,
Twylite
Kevin Kenny
> You saw the error, right? We're missing that "optional" --.
>
> The statement that msgcat loaded from the SQL dialect file is
> equivalent to this:
> ::msgcat::mcset mysql sql_get_mail_list {-- Get e-mail addresses and
> salutations
> SELECT email, name FROM t_mail_users}
>
> So we're going to get an error 'unknown option "-- Get e-mail
> addresses ..."'.
We are going to get no such thing. "-- Get e-mail addresses..."
is not "--". And the specification does not state that anything
beginning with a hyphen is an unknown option.
The '--' is there because I routinely put it in whenever a command
sprouts -options. There have been several commands that take
-options and don't allow for an explicit 'end of options' marker
that have been shown in retrospect to need one.
Yes, it's probably not needed here, or rather, I can't show
an example where it is. It's still Mostly Harmless.