FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Nick Kew wrote:
> I'm looking
> at writing a FreeTDS driver under apr_dbd (where it will join
> existing drivers for PostgreSQL, MySQL, SQLite and Oracle).
> The driver will be released as open source.

Pleased to be of assistance, if I can.  

> In the light of what I
> want to do (see below), what API do I need, from the choice
> of db-lib, ct-lib or odbc?

I humbly recommend the db-lib library as the most mature and easiest to
learn.  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. 

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

$ grep -c '<sybdb.h>' src/apps/*.c
src/apps/bsqldb.c:1
src/apps/bsqlodbc.c:0
src/apps/datacopy.c:1
src/apps/defncopy.c:1
src/apps/freebcp.c:1
src/apps/tsql.c:0

> The driver will support basic SQL statements, prepared
> statements, transactions, and operations on result sets.
> It will support both synchronous and asynchronous operation
> (if the backend does - that's FreeTDS in this instance).

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.  

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

> It will work in the context of a dynamic connection pool,
> where connections are held open.  The architecture dictates
> that a connection won't switch between threads while there's
> a transaction or cursor open, but it will be used sequentially
> by different threads.  Any hints, tips, gotchas, or examples
> working with this kind of model will be welcome.  Does the
> choice of db-lib/ct-lib/odbc have a bearing on whether I'll
> be vulnerable to threading issues or memory leaks?

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.  

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.  

See also the pool server.  

> If prepared statements can be global (shared across FreeTDS
> connections rather than tied to a particular connection),

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.)

HTH.  Sounds like an interesting project.  

Regards, 

--jkl