Client Development Interfaces

SQL Server provides several interfaces supporting client application development. These interfacesODBC, Remote Data Objects (RDO), OLE DB, ActiveX Data Objects (ADO), and the legacy interfaces DB-Library and Embedded SQL precompilerare described in this section. The companion CD included with this book includes a whitepaper entitled "Designing Efficient Applications for SQL Server" that compares all the interfaces described in this sectionand a few more besides.

ODBC

ODBC is an API for database access that's both a formal and a de facto industry standard. Besides being one of the most popular database interfaces used by applications today, ODBC has gained status as the formal call-level interface standard by ANSI and ISO. SQL Server provides a high-performance, native ODBC interface for all Windows-based programming environments, and it can be distributed royalty-free with any application. The SQL Server ODBC driver implements every function in the ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the highest level) conformant.

RDO

RDO is an object interface that's closely tied to ODBC, exposing all the functionality in the ODBC driver and making it easily available to Visual Basic programs. RDO supports building visual controls tied directly to SQL Server data, which greatly reduces the amount of code that must be written to display data on the screen.

OLE DB

OLE DB was first released by Microsoft in 1996 to provide a COM interface to any tabular data source, that is, data that can be represented with rows and columns . This includes data in spreadsheets, and even text files. OLE DB can be viewed as an object version of ODBC but is more powerful in that it can access data from data sources beyond those that ODBC can access. Unlike other object interfaces to SQL Server, such as RDO, OLE DB doesn't make programming a call-level interface like ODBC any easier. In addition, because OLE DB uses pointer data types extensively, it's only accessible from C and C++.

ADO

ADO is a higher-level object interface on top of OLE DB that provides much of the same functionality and performance. Because ADO is pointerless, it can be accessed from scripting languages like JScript, development software like Visual Basic, and C and C++.

ADO is the recommended and supported interface for Internet applications written using Microsoft's Visual InterDev development tool. Applications written with Visual InterDev can call ADO from Active Server Pages (ASP) and incorporate code written in VBScript or JScript.

DB-Library

DB-Library is a SQL Server_specific API that provides all the necessary macros and functions for an application to open connections, format queries, send them to the server, and process the results. You can write custom DB-Library applications using either C, C++, or Visual Basic (or any programming language that's capable of calling a C function).

DB-Library is the original SQL Server programming interface. Libraries are provided for MS-DOS, Windows 3.1, Windows 95, Windows 98, and Windows NT. (The Windows NT library for Intel computers is the same library used for Windows 95 and Windows 98, so you can write a single application that targets both environments.) Developers are granted licensed rights to redistribute the DB-Library runtimes royalty-free. Although DB-Library continues to be officially supported for SQL Server 7, it's not guaranteed to support the full-feature set.

ESQL for C

SQL Server provides an Embedded SQL precompiler (ESQL for C) that allows developers to write SQL Server applications by embedding the SQL queries directly in their C source code. Many minicomputer and mainframe developers are already accustomed to this style of programming, and ESQL might be a natural choice for that reason. In addition, Microsoft has licensed some of the ESQL run-time environment to Microfocus, the leading provider of COBOL compilers and tools. Microfocus offers an embedded SQLinterface for SQL Server directly in its COBOL development environment.

Server Development Interface

SQL Server offers an open API for developing server-based applications that work in conjunction with SQL Server. ODS is an event-driven API that is primarily used for developing extended stored procedures. ODS is actually a core part of the SQL Server architecture and benefits from the high-performance architecture. It provides all the network, connection, and thread management that SQL Server uses.

Formerly, ODS was frequently used to develop custom database gateways, data-driven event alerters, external program triggers, and request auditing, in addition to extended stored procedure DLLs. However, with the advent of SQL Server distributed queries and newer technologies such as Windows NT Component Services, the need for ODS in developing applications of this type of gateway has largely been eliminated. It is strongly recommended that you investigate other methods for developing the types of applications just mentioned, and use ODS only for developing extended store procedures.