Application Development Architecture

Applications use two components to access a database:

• An application programming interface (API) or Uniform Resource Locator (URL).

  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, and a C or Microsoft® Visual Basic® API defines a set of functions for applications written in C, C++, or Visual Basic.

  A Uniform Resource Locator is a string, or stream, that an Internet application can use to access resources on the Internet or an intranet. Microsoft SQL Server™ 2000 provides an ISAPI dynamic-link library (DLL) that Microsoft Internet Information Services (IIS) applications use to build URLs that reference instances of SQL Server 2000.

• 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. SQL Server 2000 supports two languages:

  • Internet applications running on IIS can use XPath queries with mapping schemas.
    
    
  • The Transact-SQL language.
• The topics in this section provide information about the APIs supported by SQL Server 2000 and the issues to consider when choosing which API to use in an application.

Transact-SQL

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

The ODBC specification defines extensions to the SQL defined in the SQL-92 standard. The 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 layer over ODBC. The ODBC SQL extensions are not supported from applications that use the DB-Library or Embedded SQL APIs.

XPath

SQL Server 2000 supports a subset of the XPath language defined by the World Wide Web Consortium (W3C). XPath is a graph navigation language used to select nodes from XML documents. You first use a mapping schema to define an XML-based view of the data in one or more SQL Server tables and views. You can then use XPath queries to retrieve data from that mapping schema.

You usually use XPath queries in either URLs or the ADO API, XPath queries are also supported by the OLE DB API.

APIs Supported by SQL Server

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

• These open APIs with publicly defined specifications supported by several database vendors:
  • ActiveX Data Objects (ADO)
    
    
  • OLE DB
    
    
  • Open Database Connectivity (ODBC) and the object APIs built over ODBC: Remote Data Objects (RDO) and Data Access Objects (DAO)
    
    
  • Embedded SQL for C (ESQL)
• The legacy DB-Library for C API that was developed specifically to be used with earlier versions of SQL Server that predate the SQL-92 standard.

Internet applications can also use URLs that specify IIS virtual roots that reference an instance of SQL Server. The URL can contain an XPath query, a Transact-SQL statement, or a template. In addition to using URLs, Internet applications can also use ADO or OLE DB to work with data in the form of XML documents.