 |
There are three APIs. Any will work with any server.
db-lib is the oldest and simplest. It's the only API supported by both vendors, which is important to people porting applications that use the vendors' libraries. db-lib was the first API implemented by FreeTDS, and is still the best supported, for what that's worth. Anything that can be done in FreeTDS can be done through db-lib. Among other places, db-lib is used by PHP.
ct-lib is Sybase's second-generation API. People who really know Sybase rely on ct-lib because it fixes a bunch of implementation and conceptual gaps in db-lib. That's why sqsh and Perl's DBD::Sybase use ct-lib. ct-lib is not our most complete implementation, but is getting some attention from the developers. We're committed to making it work perfectly with DBD::Sybase.
ODBC is our most recent addition. Its chief advantage is that it makes FreeTDS servers look like other ODBC servers, a big help to people who know ODBC and/or write applications for several kinds of servers. Recent releases of FreeTDS have included large expansions of functionality in the ODBC library, and the git version almost always includes still more features.
There is no bottom line. The shortest path lies between two points that only you, dear reader, know: where you are and where you're going.
I like db-lib and think it's easy to get to know; it has always done what I need. But the real experts out there believe in ct-lib, so it's probably the right answer for people just starting out, unless you want compatibility with Microsoft's libraries. FreeTDS swings both ways: you can use any client library API to connect to either vendor's server.
Another consideration is what you have for a reference platform. If you have access to vendor libraries, it can be very handy to use their docs and write small tests against their implementation.
Links to the vendors' library docs are in the FreeTDS FAQ.
"bcp" stands for "bulk copy". It is the name Sybase gave to a feature of their server and TDS protocol.
Normal INSERT statements are first applied to the transaction log and then applied to the table. A bcp insertion, on the other hand, never1 touches the transaction log. It's applied directly to the table and committed wholesale. bcp operations are often an order of magnitude faster than ordinary inserts, and are easier on the server, too.
To make bcp possible, Sybase included a feature in the TDS protocol to (1) alert the server and (2) structure the incoming data such that it's easy for the server to accept them. This feature is made accessible to client applications via a set of bcp functions in db-lib and ct-lib. (Microsoft's ODBC driver supports bcp as an extension; it's not part of the ODBC specification.) The APIs allow the programmer to indicate the name of a file to load, or to send rows of data from variables inside the application. The latter feature is especially useful to the kind of numerical/quantitative programming I do for a living; it's rare that I'm working with under a million rows.
Sybase and Microsoft both include a bcp utility to load/unload the server. It's the standard way to import data from another server, for example. FreeTDS hadn't had one (we supply the library, you supply the application), but Bill Thompson has written freebcp (Yay, Bill!) so that gap is filled.
One sometimes hears of "bcp out" i.e., extracting data to a file with the bcp utility. From the server's and protocol's point of view, there's no (or almost no) such thing. The real advantage of bcp is bypassing the transaction log and its concomitant locks and I/O, elements that aren't significant when reading a table.
As far as I know, bcp is a unique feature in the database world. Other databases have ways to load data, of course, but not via the client protocol.
RTFM for more.
1 Actually—to be pedantic about it—the table allocations are still logged (i.e. which pages get allocated during the insert). And you can only run the non-logged (aka "fast") bcp if the target table has no indexes and there are no triggers.
--jkl, with clarification from M. Peppler