FreeTDS Development Group

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

Nick Kew wrote:
> On Tue, 8 May 2007 20:46:26 -0400
> "James K. Lowden" <jklowden AT freetds.org> wrote:
> 
> > 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.

The idea is to make everything work.  If you look over the ChangeLog,
you'll see more attention is paid to db-lib and ODBC.  ct-lib work is more
sporadic, but ct-lib supports DBD::Sybase and sqsh.  So if it's broken we
fix it.  Yours truly is a big fan of both; he just never learned the API
very well.  
> > 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.

Well, one nice thing about FreeTDS is it has basically no dependencies. 
:-) 

> > 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?

If it's all server-side, no problem.  You declare a cursor in T-SQL and
off you go.  

If it's client-side, the update/insert has to be on a separate 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.)
> 
> 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.

create procedure PasswordGet
        @username varchar(60) 
as 
begin
        SELECT password 
        FROM authn 
        WHERE username = @username
end

        :-)

That doesn't obviate sanity checking the user-provided input, but it gets
you to the same place, no?  

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

Parameter binding in ct-lib is actually *easier* as an API than in ODBC
because the library relies on the server to describe the parameter's
metadata (whereas ODBC makes the caller do it).  For that reason, ct-lib
parameters can't be made to work with Microsoft servers: the server
doesn't provide support for it, and we don't have a T-SQL parser with
which to divine the metadata.  

If you want to try to understand both APIs, I recommend downloading
Sybase's programmer reference manuals as PDFs and reading through them,
especially the introductions, to get the gist.  That'd be much easier than
trying to understand things like ct_dynamic.  Also keep in mind we
document which functions are implemented and which not in the UG.  

HTH.  

--jkl