SQL Server APIs | Microsoft SQL Server 2000 Administrators Companion

3 4

To communicate with SQL Server, your applications must speak SQL Server's language. One means of communication is to use one of the tools provided with SQL Server, such as command-line OSQL or the SQL Server Query Analyzer (ISQLW). These tools can be useful for simple queries, but they are not useful for day-to-day application processing. For example, the people who process inventory, accounts payable, and accounts receivable can work more productively using a graphical user interface than they can typing in SQL statements. In fact, most users of such applications don't know SQL. In general, developers use an API to write applications that connect to SQL Server. APIs provide calls that can be used to execute various database functions.

SQL Server provides a number of APIs, including DB-LIB, ODBC, and OLE DB. DB-LIB is the original SQL Server API; it is available for both Microsoft SQL Server and Sybase products. ODBC is a newer and more flexible language that can be used to communicate with any number of relational database management system (RDBMS) products. OLE DB and several other APIs are also available for programmers to use with SQL Server. This section describes the various APIs.

DB-LIB Connectivity

DB-LIB has been a part of SQL Server since the product's first release, in 1988, and DB-LIB is the original API for SQL Server programming. Although DB-LIB has always been an integral part of SQL Server, a move is on to migrate to ODBC as the primary API. C and C++ as well as Microsoft Visual Basic support DB-LIB. DB-LIB calls are made in the application code and then sent down through the net-library, to the network protocol layer, and then to the network hardware layer.

ODBC Connectivity

ODBC is a standard API developed by Microsoft to facilitate the connection of PCs running Windows to various RDBMS's. By programming to the ODBC API, you can use the same application to communicate with any number of systems. ODBC is versatile but is perhaps not the most efficient API for every RDBMS. Typically, native APIs support additional functionality and are optimized for their particular RDBMS's.

ODBC is used to support additional connectivity via the Internet using Active Server Pages (ASP). Support is also included for ActiveX, Microsoft Foundation Classes (MFC), and Extensible Markup Language (XML). The level of support for ODBC has increased dramatically in the last few years, making it a great API for products that support multiple RDBMS's.

The ODBC API has the same form regardless of the RDBMS to which you will connect, but the ODBC driver does not. You must obtain a unique ODBC driver for each RDBMS that you will use. This driver translates ODBC into the native RDBMS network protocol. Newer versions of the RDBMS typically require new ODBC drivers in order to function optimally, but backward compatibility is usually maintained. With DB-LIB, a specific net-library is typically used, whereas with ODBC, usually the multiprotocol net-library is used. This library facilitates the connection of ODBC applications to the server without requiring you to choose a specific protocol.

ODBC Connection Pooling

The ability to pool connections from within an application was introduced with ODBC 2.x. Normally, an application creates an additional connection from the application layer to the database each time a different user logs in to the application. This process can be inefficient because establishing and maintaining a connection to the database involves quite a bit of overhead.

A connection pool allows other threads within an application to use existing ODBC connections without requiring a separate connection. This capability can be especially useful for Internet applications that might repeatedly connect. Applications that require connection pooling must register themselves when they are started.

When an application requests an ODBC connection, the ODBC Connection Manager determines whether a new connection will be initiated or an existing connection reused. This determination is made outside the control of the application. The application thread then works in the usual manner.

Once the thread has finished with the ODBC connection, the application makes a call to release the connection. Again, the ODBC Connection Manager takes control of the connection. Also, if a connection has been idle for a certain amount of time, the ODBC Connection Manager will close it.

MORE INFO
For more information about ODBC connection pooling, consult the Microsoft ODBC Software Development Kit (SDK).

Other APIs

A number of other APIs can enable your applications to communicate with SQL Server. These APIs include OLE DB, ODS, SQL—Distributed Management Framework (SQL-DMF), SQL—Distributed Management Objects (SQL-DMO), and SQL-Namespace (SQL-NS). In general, each of these protocols supports a specific function or market segment that requires its own programming interface.

MORE INFO
For more information about specialized APIs, consult SQL Server 2000 Books Online.