Re: TDBC documentation, examples, syntax?

tom.rmadilo wrote:
TDBC tries to define transaction semantics which don't match up with
current SQL standards. Not good.

If this is the case, it's surely unintentional, and most likely
a documentation problem. If a program is using the tdbc::odbc bridge,
then [$connection begintransaction] maps exactly to setting the
'autocommit' attribute to 0; [$connection commit] to performing
SQLEndTran(SQL_HANDLE_DBC, hDBC, SQL_COMMIT) and turning 'autocommit'
back on, and [$connection rollback] to performing
SQLEndTran(SQL_HANDLE_DBC, hDBC, SQL_ROLLBACK) and turning
'autocommit' back on.

I oversimplify a trifle; for performance reasons, 'autocommit' doesn't
actually get turned on or off until the next statement is
executed. (It doesn't matter until then, and this detail makes
a fairly sizable performance difference by avoiding excess calls
to SQLSetConnectAttr.) Otherwise, for ODBC (or SQL/CLI), these
equivalences are precise. An error is thrown if you attempt to begin
a transaction with a transaction already in progress, since those
are the ODBC semantics.

There exist vendor-specific APIs that allow for nested transactions,
and TDBC doesn't intentionally forbid them, which is why TIP 308
mentions the possibility of transaction nesting. The tdbc::odbc bridge
does not allow for it.

Don't be confused, either, with the [$connection transaction {...}]
method. It's merely a way to execute a script and do all the
necessary bookkeeping to (a) perform all the SQL operations of the
script as a single transaction; (b) commit at the end of the script,
and (c) roll back if the script errors out. Since, at least in
my programs, most of my transactions follow that pattern anyway,
I thought it was a useful abstraction to put in the library.
(Actually, I added it at the suggestion of others, but most of my
transactions *do* follow that pattern.)

TDBC confuses binding styles at the C level and those at the Tcl
scripting level. On the Tcl scripting level we use variable names, but
on the C level we cannot risk using generic variable names, and of
course, C is call by value. This is a tricky problem, but by no means
insoluble. Personally I have solved a more difficult problem:
translating between named inputs and an overloaded parameter-ordered
function with multiple signatures _and_ supporting defaults. In my
opinion, trying to solve all problems in a single API layer leads to
rigidity.

I'm horribly confused by this remark. TDBC says nothing about how
binding is accomplished at the C level, merely that TDBC's lexical
structure for SQL statements follows a particular convention
for variable binding at the Tcl level. The tdbc::odbc bridge
happens to accomplish the variable binding by replacing every
:variable with a ?, and maintaining the mapping between position
and name. (And yes, it does handle the case where the same name
appears in multiple positions in the query.) The tdbc::sqlite3
driver, instead, uses SQLite3's name binding mechanism and
leaves the SQL statement alone. If I were to do an Oracle native
API, I'd probably use Oracle's bound session variables and pass
the content of Tcl variables into them. TDBC is a language binding
to SQL, not a Tcl embedding of ODBC.

As a convenience for driver writers, TDBC provides a tokenizer
to find the :variable substituents and let the driver manipulate
them. A driver doesn't have to use it, and if it has a variant
SQL syntax that requires special handling, it will likely do its
own lexical analysis.

Anyway, not every database supports statement preparation and variable
binding/substitution, so an abstraction layer such as TDBC could
usefully bridge this gap.

Right. At least we agree on this point. If someone were to do a
MySQL 4 driver (tdbc::mysql requires at least version 5.0 of MySQL)
they'd have to handle parameter binding themselves, and do whatever
sanitization is necessary. TIP 308 strongly suggests viewing
sanitization as an infelicitous expedient to work around deficiencies
in the underlying API's, and urges using a database's native
variable binding mechanism wherever possible; in any case, for
programmer safety, it mandates that the driver worry about it and
not pass the responsibility back onto the programmer.

Finally, note that the user level API should include wrappers or other
simplification APIs which build upon the low level "reflection" APIs.
It is never expected that most developers will use the full API, but
it needs to exist to allow easy production of high-level APIs.

I remain unconvinced. Java programmers typically have no access to
the SQL/CLI level but use JDBC or SQLJ. And they contrive to lead
useful lives without ever making direct ODBC calls. Moreover, not
every TDBC driver has ODBC as an underlying layer. There are already
drivers for MySQL and SQLite3 that use the database-specific API's,
and more are expected.

To repeat myself: "reflection" which means a one-to-one mapping is an
order of magnitude easier than consolidation or some other ad-hoc
grouping of lower level API into a higher level API. It is much easier
to say "we don't support XYZ, but someone else might add it later"
than to create your own interesting collection of functionality.

I've never said that it was easy. But SQL/CLI is such an atrocious fit
to Tcl that I think we need to do a Tcl-specific language binding
anyway. And we haven't the resources to go to Geneva and ask JTC1/SC32
to do it for us.

--
73 de ke9tv/2, Kevin
.

Relevant Pages

  • Re: SQL 2005 Profiler
    ... printer Microsoft Office Document Image Writer is unknown. ... administrator to install the driver before you log in again. ... Note that if the default trace is enabled, it is directed to the SQL Server log folder. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL using C
    ... I am almost a new C programmer and now I need to use SQL within my C ... The people who are telling you to use a C API are out of their mind. ... is not less complicated than ODBC and ties the user to a single ...
    (comp.lang.c)
  • RE: XA transactions
    ... Which driver and version are you using and where did you locate these ... | I used the following set of instructions to enable XA transactions on our ... | SQL box: ... Create a registry named-value: ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Behavior of Connection.commit()
    ... Does it say "commit" and "rollback" and other SQL commands that affect the transaction state are not allowed? ... How sure can the driver ever be about the state of the transaction? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: TDBC documentation, examples, syntax?
    ... The user level API remains fixed ... Unfortunately the TDBC API, being a single level API, changes for ... If you want to change the datasource, ... distinguish SQL code and SQL data) was useless. ...
    (comp.lang.tcl)