Chapter 13 - Application Development Architecture

  • Article

Applications use two components to access a database:

  • API 

    A database API defines how to code an application to connect to a database and pass commands to the database. An object model API is usually language independent and defines a set of objects, properties, and interfaces, while a C or Microsoft Visual Basic API defines a set of functions for applications written in C, C++, or Visual Basic. 

  • Database language 

    A database language defines the syntax of the commands sent to the database. The commands sent through the API allow the application to access and modify data. They also allow the application to create and modify objects in the database. All commands are subject to the permissions granted to the user. 

This section discusses:

  • The database language supported by Microsoft SQL Server, Transact-SQL. 

  • The APIs supported by SQL Server and the issues to consider when choosing which API to use in an application. 

Transact-SQL 

The database language supported by SQL Server is Transact-SQL. Transact-SQL complies with the Entry Level of the SQL-92 standard, but also supports several features from the Intermediate and Full Levels. It also supports some powerful extensions to the SQL-92 standard. For more information, see "Transact-SQL" in Microsoft SQL Server Transact-SQL and Utilities Reference.

The ODBC specification defined extensions to the SQL defined in the SQL-92 standard. These ODBC SQL extensions are also supported by OLE DB. Transact-SQL supports the ODBC extensions from applications using the Microsoft ActiveX Data Objects (ADO), OLE DB, or ODBC APIs, or the APIs that map over ODBC.

APIs Supported by SQL Server 

SQL Server supports a number of APIs for building general-purpose database applications:

  • The legacy DB-Library API developed specifically to be used with earlier versions of SQL Server that predate the SQL-92 standard. 

Choosing an API

The general-purpose APIs recommended for use in new applications using Microsoft SQL Server 7.0 are:

  • Microsoft ActiveX Data Objects (ADO) for most database applications. ADO supports rapid development of robust applications and has access to most SQL Server features. The SQL Server features needed by most applications are supported by ADO when using the Microsoft OLE DB Provider for SQL Server. 

  • OLE DB for COM-based tools and utilities, or COM-based system level development needing either top performance or access to SQL Server features not exposed through ADO. The OLE DB Provider for SQL Server uses provider-specific properties, interfaces, and methods to expose SQL Server features not covered by the OLE DB specification. Most of these provider-specific features are not exposed through ADO. 

  • ODBC for the same class of applications as are listed above for OLE DB, but which are not based on COM. 

There are several factors to consider when selecting which general-purpose APIs to use in a SQL Server application:

  • Degree of developer control.

    APIs vary in their overall feature set. Simple APIs like ADO are easy to learn, program, and maintain, but they do not support all of the capabilities of the more complex APIs such as OLE DB and ODBC. You can take advantage of the ease-of-use advantages of the APIs such as ADO, RDO, and ESQL if they provide the functionality the application needs. 

  • Access to SQL Server features. 

    Some APIs have limitations on the numbers or types of SQL Server features they can use. 

  • Access to Microsoft SQL Server OLAP Services features. 

    OLAP Services, ADO MD, and OLE DB for OLAP offer support for online analytical processing. These services can be integrated with ADO and OLE DB applications using the OLE DB Provider for SQL Server. 

  • Programming language and tool support for the API. 

The following table maps the general-purpose database APIs supported by SQL Server to the issues listed earlier.

API

Maturity

Overhead

Degree of developer control

SQL Server 7.0 feature support

OLAP Services feature support

Language support

ADO

Emerging

Object Model over OLE DB

Moderate

Most

Yes

Microsoft Visual Basic
Microsoft Visual C++
Microsoft Visual J++®

OLE DB

Emerging

Native

High

All

Yes

Visual C++

ODBC

Existing

Native

High

All

No

Visual C++

RDO

Existing

Object Model over ODBC

Moderate

Most

No

Visual Basic
Visual J++

DAO

Legacy

Object Model over ODBC

Low

Limited

No

Visual Basic
Visual C++

ESQL

Legacy

Hosted over DB-Library

Low

Limited

No

Visual C++
COBOL

DB-Library for C

Legacy

Native

High

Limited

No

Visual C++

Additional APIs 

SQL Server also supports a number of interfaces that allow applications to make full use of all SQL Server features:

  • SQL Distributed Management Objects (SQL-DMO API) 

    A set of COM interfaces for managing and administering SQL Server. 

  • Replication components (Replication Component Programming API) 

    A set of COM interfaces for defining and managing replication between SQL Server databases. You can also replicate data from heterogeneous databases to SQL Server. 

  • Data Transformation Services (Data Transformation Services API) 

    A set of COM interfaces (based on OLE DB) for defining and executing complex data transformations between OLE DB data providers. 

  • Open Data Services (Open Data Services API) 

    A C API for writing SQL Server extended stored procedures. 

See Also 

In Other Volumes 

"Overview of Building SQL Server Applications" in Microsoft SQL Server Building Applications 

SQL Server and ADO

Microsoft ActiveX Data Objects are a set of Automation objects that consume the OLE DB API and allow applications to consume data from OLE DB data sources. This includes data stored in many different formats, not just SQL databases. The ActiveX Data Object (ADO) API can be used from applications written in any Automation-enabled language, such as Microsoft Visual Basic, Microsoft Visual C++, Microsoft Visual J++, and Microsoft Visual FoxPro®.

ADO applications access data through OLE DB providers. Microsoft SQL Server version 7.0 introduces a native Microsoft OLE DB Provider for SQL Server that can be used by ADO applications to access the data in SQL Server. In earlier versions of SQL Server, ADO applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. While ADO applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to only use the OLE DB Provider for SQL Server.

ADO is the API most recommended for general purpose data access from SQL Server. ADO:

  • Is easy to learn and program. 

  • Has the feature set needed by most general-purpose applications. 

  • Allows programmers to produce robust applications quickly with good performance. 

The core capabilities of the OLE DB specification provides all the data access functionality needed by most applications. In addition, OLE DB allows individual providers to define provider-specific mechanisms to support additional features of the data engine accessed by the provider. ADO exposes the core capabilities of OLE DB, but does not expose provider-specific features. ADO applications cannot access a few SQL Server features exposed through provider-specific features of the OLE DB Provider for SQL Server, such as the IRowsetFastLoad bulk copy methods, SQL Server-specific extended diagnostic information, and auto-fetch cursors.

ADO has evolved from the earlier, ODBC-based Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. RDO and DAO applications can be converted to ADO, and RDO and DAO application programmers quickly learn ADO. ADO is used extensively in Active Server Pages (ASP).

See Also 

In This Volume 

Programming ADO

In Other Volumes 

"SQL Server and ADO" in Microsoft SQL Server Building Applications 

SQL Server and OLE DB

OLE DB is an API that allows COM applications to consume data from OLE DB data sources. OLE DB data sources include data stored in many different formats, not just SQL databases. An application uses an OLE DB provider to access an OLE DB data source. An OLE DB provider is a COM component that accepts calls to the OLE DB API and does whatever is necessary to process that request against the data source.

Microsoft SQL Server version 7.0 introduces a native Microsoft OLE DB Provider for SQL Server that can be used by OLE DB applications to access the data in SQL Server. The OLE DB Provider for SQL Server complies with the OLE DB 2.0 specification. Each OLE DB provider supports a command language; the OLE DB Provider for SQL Server accepts the command syntax specified as DBGUID_SQL. DBGUID_SQL syntax is primarily SQL-92 syntax with ODBC escape sequences.

In earlier versions of SQL Server, OLE DB applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. While OLE DB applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to use only the OLE DB Provider for SQL Server.

OLE DB is the API recommended for tools, utilities, or system level development needing either top performance or access to SQL Server features not exposed through ADO. The core capabilities of the OLE DB specification provides all the data access functionality needed by most applications. In addition, OLE DB allows individual providers to define provider-specific mechanisms to support additional features of the data engine accessed by the provider. ADO applications cannot access some SQL Server features exposed through provider-specific features of the OLE DB Provider for SQL Server, so applications needing to use the provider-specific features of the OLE DB Provider for SQL Server must use the OLE DB API. These features include:

  • An IRowsetFastLoad interface to the SQL Server bulk copy component. 

  • An ISQLServerErrorInfo interface to get SQL Server-specific information from messages and errors. 

  • A LINKEDSERVERS rowset that exposes catalog information from the linked servers used in SQL Server distributed queries. 

  • Various provider-specific properties to control SQL Server-specific behaviors. 

See Also 

In Other Volumes 

"SQL Server and OLE DB" in Microsoft SQL Server Building Applications 

SQL Server and ODBC

Open Database Connectivity (ODBC) is a Call-Level Interface (CLI) that allows C and C++ applications to access data from ODBC data sources. A CLI is an API consisting of functions an application calls to obtain a set of services. ODBC data sources include data stored in different formats, not just SQL databases. An application uses an ODBC driver to access a data source. An ODBC driver is a DLL that accepts calls to the ODBC API functions and does whatever is necessary to process that request against the data source.

ODBC is aligned with these specifications and standards defining a CLI for data access:

  • The X/Open CAE Specification "Data Management: SQL Call-Level Interface (CLI)" 

  • ISO/IEC 9075-3:1995(E) Call-Level Interface (SQL/CLI) 

ODBC has been widely accepted by database programmers, and several database vendors or third-party companies supply ODBC drivers. Several other Microsoft data access APIs were defined as simplified object models over ODBC:

  • Remote Data Objects (RDO) 

  • Data Access Objects (DAO) 

  • Microsoft Foundation Classes (MFC) Database Classes 

Microsoft SQL Server includes a native Microsoft SQL Server ODBC driver that can be used by ODBC applications to access the data in SQL Server. The SQL Server ODBC Driver complies with Level 2 of the ODBC 3.51 specification and exposes all the features of SQL Server. In SQL Server version 7.0 all of the SQL Server utilities except isql use the ODBC API and the SQL Server ODBC Driver.

ODBC can be used in tools, utilities, or system level development needing either top performance or access to SQL Server features, and which are not COM applications. ODBC, like OLE DB, allows individual drivers to define driver-specific mechanisms to support additional features of the data engine accessed by the driver. These features include:

  • A set of bulk copy functions based on the earlier DB-Library bulk copy functions. 

  • Extensions to the ODBC diagnostic functions and records to get SQL Server-specific information from messages and errors. 

  • A set of functions that exposes catalog information from the linked servers used in SQL Server distributed queries. 

  • Various driver-specific attributes and connection string keywords to control SQL Server-specific behaviors. 

See Also 

In Other Volumes 

"SQL Server and ODBC" in Microsoft SQL Server Building Applications 

SQL-DMO API

SQL Distributed Management Objects (SQL-DMO) encapsulate the objects found in Microsoft SQL Server databases. SQL-DMO allows applications written in languages that support OLE Automation or COM to administer all parts of a SQL Server installation. SQL-DMO is the API used by the SQL Server version 7.0 SQL Server Enterprise Manager, so applications using SQL-DMO can perform all functions performed by SQL Server Enterprise Manager.

SQL-DMO is intended for any OLE Automation or COM application that needs to incorporate SQL Server administration. Examples include:

  • Applications that encapsulate SQL Server as their data store and want to shield users from as much SQL Server administration as possible. 

  • Applications that have specialized administrative logic incorporated the application itself. 

  • Applications that want to integrate SQL Server administrative tasks in their own user interface. 

See Also 

In This Volume 

Programming ADO

In Other Volumes 

"Developing SQL-DMO Applications" in Microsoft SQL Server Distributed Management Objects 

SQL Namespace API

The SQL Namespace (SQL-NS) API is a set of objects that encapsulate the SQL Server Enterprise Manager user interface. SQL-NS allows applications written in languages that support OLE Automation or COM to include parts of the SQL Server Enterprise Manager user interface in their own user interface.

Using SQL-NS, an application can incorporate the following SQL Server Enterprise Manager elements into its user interface:

  • Wizards

  • Dialog boxes (including property dialog boxes) 

When an application uses the SQL-NS objects, SQL Server Enterprise Manager must be installed on any client that attempts to run the SQL-NS application.

See Also 

In This Volume 

Administration Architecture

In Other Volumes 

"Programming SQL-NS Applications" in Microsoft SQL Server Building Applications 

Replication Component Programming API

Microsoft SQL Server version 7.0 includes a set of replication objects in addition to the replication objects found in SQL-DMO. These objects include:

  • The Replication Distributor Interface, which allows you to programmatically implement and manage heterogeneous transactional replication, in conjunction with third-party programs that can perform change tracking at heterogeneous data sources. 

  • Microsoft ActiveX controls that allow you to provide the functionality of the Distribution Agent or the Merge Agent in custom programs. 

See Also 

In This Volume 

Administration Architecture

In Other Volumes 

"Programming Replication ActiveX Controls" in Microsoft SQL Server Building Applications 

"Programming Transactional and Snapshot Replication from Heterogeneous Data Sources" in Microsoft SQL Server Building Applications 

"Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication 

"Developing SQL-DMO Applications" in Microsoft SQL Server Distributed Management Objects 

Data Transformation Services API

The Data Transformation Services (DTS) API is a set of objects encapsulating services that assist with building a data warehouse. DTS can be used in applications written in languages that support OLE Automation or COM. DTS:

  • Transfers data between heterogeneous OLE DB data sources. 

  • Performs customized transformations that can convert detailed online transaction processing (OLTP) data to a summarized form for easy analysis of trend information. 

See Also 

In Other Volumes 

"Overview of Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication 

"Programming DTS Applications" in Microsoft SQL Server Building Applications 

SQL Server and English Query

English Query provides an OLE Automation API that lets users resolve natural language questions about the information in a Microsoft SQL Server database.

Given a definition of the entities and relationships associated with a SQL Server database, English Query translates a natural-language question about data in the database to a set of SQL SELECT statements that can then be executed against the SQL Server database to get the answer.

For example, given a car sales database, an application can send English Query a string containing the question, "How many blue Fords were sold in 1996?"

English Query returns to the application an SQL statement such as:

SELECT COUNT(*)
FROM CarSales
WHERE Make = 'Ford'
AND Color = 'Blue'
AND DATEPART(yy, SalesDate) = '1996'

The application can then execute the SQL statement against the SQL Server database to get a number it can return to the user.

English Query works best with a normalized database. There are two parts to using English Query in an application:

  1. An administrator defines an English Query domain for the database and uses that to compile what is called an English Query application file. The English Query application file is what defines the structure of the database to the English Query run-time engine. 

    An English Query domain contains:

    • Definitions of entities, which are usually associated with tables and columns of the database. 

    • Definitions of the relationships between the entities. 

  2. The domain is defined with a domain editor and tested with a test tool. The result is saved as a domain project (or *.eqp file). This file is compiled to form the English Query application file (*.eqd). 

Cc917549.eqart1(en-us,TechNet.10).gif

English Query is an OLE Automation server that can be called from any OLE Automation application. The OLE Automation server, or run-time engine, exposes an object model API. An OLE Automation application uses the API to load the English Query application file and then send the run-time engine natural-language questions. The run-time engine usually returns an SQL statement or batch that will retrieve the needed information from the SQL Server database. Sometimes the run-time engine can provide the answer directly, request clarification, or return an error.

Cc917549.eqart2(en-us,TechNet.10).gif

See Also 

In This Volume 

Installing Microsoft English Query

Open Data Services API

The Open Data Services API is a server-based API specific to Microsoft SQL Server. It can be used to produce two types of applications:

  • Extended stored procedures 

    An extended stored procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL stored procedure. Extended stored procedures are a way to extend the capabilities of Transact-SQL to include any resources or services available to Microsoft Win32 applications. 

  • Gateways

    Gateways are server applications that allow applications written to access SQL Server to work with other databases. The need for gateway applications has largely been made obsolete by newer technologies such as SQL Server distributed queries, Windows NT Component Services, and general database APIs such as ADO and OLE DB. 

See Also 

In Other Volumes

"Programming Open Data Services" in SQL Server Books Online

SQL Server and Embedded SQL

Embedded SQL (ESQL) is a SQL-92 standard API for SQL database access. ESQL requires a two step compilation process:

  • A precompiler translates Embedded SQL statements into commands in the programming language used to write the application. The generated statements are specific to the database that supplied the precompiler, so while the original source is generic to ESQL, the generated statements and the final executable file are specific to one database vendor. 

  • The source generated by the precompiler is then compiled using the compiler for the application programming language. 

Embedded SQL has a simpler syntax than COM APIs such as OLE DB or Call Level Interfaces such as ODBC, so it is easier to learn and program. It is less flexible than OLE DB or ODBC, where well-written applications can switch from one DBMS to another by simply switching drivers or providers. OLE DB and ODBC are also better at dealing with environments where the SQL statements are not known when the application is compiled, such as when developing ad hoc query tools.

Microsoft SQL Server provides an Embedded SQL precompiler for C applications. The SQL Server precompiler translates Embedded SQL statements as calls to the appropriate DB-Library API functions. The Microsoft implementation of ESQL has the same restrictions as DB-Library applications.

SQL Server is designed such that it can support COBOL Embedded-SQL applications compiled with third-party Embedded SQL precompilers that support Microsoft SQL Server.

See Also 

In Other Volumes 

"Programming Embedded SQL for C" in SQL Server Books Online

DB-Library API

DB-Library is a Call Level Interface that allows C applications to access Microsoft SQL Server. Microsoft also provides a closely matched API that provides access to the DB-Library functions from Microsoft Visual Basic applications. DB-Library for Visual Basic supports a subset of the functions in DB-Library for C. DB-Library was the original API that allowed applications to access SQL Server, and remains specific to SQL Server.

The DB-Library API has not been enhanced beyond the level of SQL Server 6.5. All DB-Library applications can work with SQL Server 7.0, but only as 6.5 level clients. New features introduced in SQL Server 7.0 are not supported for DB-Library applications.

SQL Server 7.0 does not include a programming environment for DB-Library for Visual Basic. Existing DB-Library for Visual Basic applications can run against SQL Server 7.0, but must be maintained using the software development tools from SQL Server 6.5. All development of new Visual Basic applications that access SQL Server should use the Visual Basic data APIs such as ActiveX Data Objects (ADO) and Remote Data Objects (RDO).

See Also 

In This Volume 

DB-Library for C Reference

SQL Syntax Recommendations

The Microsoft SQL Server version 7.0 Transact-SQL version complies with the Entry level of the SQL-92 standard, and supports many additional features from the Intermediate and Full levels of the standard.

The OLE DB and ODBC APIs were developed with the understanding that applications would use:

  • SQL-92 syntax whenever it provides the functionality needed by the application. Because the SQL dialects of most databases now comply with the Entry level of SQL-92 and support many features in the Intermediate and Full levels, this means many OLE DB providers and ODBC drivers can simply pass through most SQL-92 syntax without having to transform it to something accepted by the database. 

  • Use the ODBC extensions to SQL-92 whenever they provide functionality needed by the application that SQL-92 does not support. 

  • Use the native SQL syntax of the database engine whenever it provides functionality needed by the application that SQL-92 and the ODBC extensions do not support. 

This approach minimizes the overhead of OLE DB providers and ODBC drivers. The providers and drivers only have to parse incoming SQL statements for ODBC escape sequences or SQL-92 syntax not accepted by the database. Any ODBC escape sequences and unsupported SQL-92 syntax are transformed into the corresponding SQL syntax accepted by the database engine. All other SQL syntax is passed through to the database engine.

SQL Server 7.0 applications using OLE DB, ODBC, or one of the other APIs that encapsulate these two, should follow these guidelines and:

  • Use SQL-92 syntax whenever it provides the functionality needed by the application. 

  • Use ODBC escape sequences when they provide functionality needed by the application but not provided by SQL-92. 

  • Use Transact-SQL syntax when it provides functionality needed by the application but not provided by SQL-92 or the ODBC escape sequences. 

Using SQL with DB-Library and Embedded SQL 

DB-Library supports only Transact-SQL. DB-Library does not support the ODBC escape sequences.

ESQL/C supports only the SQL syntax defined in Embedded SQL for C and SQL Server.