FreeTDS Development Group

[Nick Kew <niq AT apache.org>]

On Tue, 8 May 2007 20:46:26 -0400
"James K. Lowden" <jklowden AT freetds.org> wrote:

> Nick Kew wrote:
> [chop]
> Pleased to be of assistance, if I can.  

:-)

> I humbly recommend the db-lib library as the most mature and easiest
> to learn.

Aha.  I saw that in one of the accompanying text files, but wasn't
sure whether it was current or long-outdated advice.

>     That said, the closest parallel to what you're doing might
> be the Perl DBD::Sybase module, which is based on ct-lib.  ODBC is an
> advantage if you already know ODBC and/or can adapt or integrate with
> other ODBC code. 

So that's three recommendations.  Sort-of.  In theory I'd love to
play with all three, but there's the time, and the three learning
curves.  I'll dig a bit deeper into db-lib today.

> Almost all the FreeTDS utilities use db-lib.  Those that don't were
> written to test the API.  

That reads like "it's the one that works".  OTOH if Perl uses ct-lib
that looks like a vote of confidence too.

> Asynchronous is not yet implemented, although it could be.  db-lib
> supports dbsetinterrupt, which might suffice.  If you need poll &
> select, I know how to add it.  

Asynchronous is only really relevant to iterating through a result
set, and what matters most is don't load a big result set into
memory all at once.

> Use a current snapshot.  If you have problems or need additional
> functions, that's where it will be addressed.  

Hmm.  I had allowed my package manager to install a 0.63 -dev version.

> Different threads cannot share a db-lib connection.  It might be OK if
> you're very careful to read all results from a query on using the same
> thread that sent the query.  

The model here is that a thread books a connection out from the pool,
does what it needs to do, then releases the connection back to the pool.
While the connection is booked out, it's reserved to the thread.
There won't be any dangling results when a connection is released.

> Bear in mind that TDS servers do not multiplex the connection: for
> each query, results must be completely processed (or cancelled)
> before the next query will be accepted by the server.  The exceptions
> to that rule are rare and arcane.  

I think we can live with that.  Does it preclude any construct like

SELECT foo ...
while (read row from results) {
   ... UPDATE row, or INSERT, or ...
}

or is it just about having multiple result sets?

> Prepared statements --  Why does anyone need them?  -- are per
> connection. db-lib has no notion of prepared statements.  The
> programmer is expected to use stored procedures, which are better in
> every way.  If you absolutely, positively must have prepared
> statements and placeholder substitution, ODBC is your only choice.
> Just don't tell anyone I recommended it.  (It works, but IMNSHO it's
> not a nice paradigm.)

A simple (and popular) form of query in Apache is authentication,
which typically uses prepared queries of the form:
  "SELECT password FROM authn WHERE username = %s"
The query is configured in the Apache configuration file (httpd.conf)
Without placeholders for that, we'd be into a game of russian roulette
with SQL injection attacks on the web.

I saw something that looks like parameter binding in ct_dynamic.c,
but it looks complex and long-winded (like Oracle, the nightmare)
and it's not obvious there whether a statement can be prepared and
re-used.

> HTH.  Sounds like an interesting project.  

Thanks:-)

-- 
Nick Kew

Application Development with Apache - the Apache Modules Book
http://www.apachetutor.org/