URL Access

SQL statements and stored procedures can be specified directly at the URL. The SQL ISAPI extension supports the following URL syntax.

Syntax

http://iisserver/virtualroot/virtualname[/pathinfo][/XpathExpression] [?param=value[&param=value]...n]

Or

http://iisserver/virtualroot?{sql=SqlString | template=XMLTemplate} [&param=value[&param=value]...n]

Arguments

iisserver

Is the Microsoft® Internet Information Services (IIS) server.

virtualroot

Is the virtual root that contains all the properties needed to access the database server and the database.

virtualname

Is one of the virtual names already created as part of defining the virtualroot properties. A virtualname of template type indicates that a template file is being executed. A virtualname of schema type indicates that a schema file is specified. A virtualname of dbobject type refers to a database object, allowing a table/view name to be specified directly at the URL.

[/pathinfo]

Is the path to the template file or mapping schema file. If specified, pathinfo is appended to the path already specified when virtualname is created. pathinfo is not specified for virtualname of dbobject type.

[/XPathExpression]

Is specified for virtualname of dbobject or schema type. This XPath expression is specified against the mapping schema or the database object.

?sql

Denotes an SQL query string.

SqlString

Is the SQL query or a stored procedure name. If SqlString is specified, the FOR XML clause can also be specified if an XML format for the result set is required. Queries are usually specified using the FOR XML clause. However, FOR XML is not specified in cases in which the returned data is known to be compatible with the receiving application (Web browser); for example, when a stored procedure that returns an XML document is executed, or when an image (GIF or JPEG) stored in a table is retrieved by a SELECT statement.

In some cases, the browser can detect the type of the returned data based on several items, such as:

• The HTML header that is passed, which includes the content-type, provides the browser with the necessary information about the returned data.
  
  
• The returned data provides information to the browser.
  
  
• The browser also examines the file name extension when template files are executed using a URL. If a template file with the .gif extension is specified and a query in the file returns a column containing .gif files, then this provides an additional hint to the browser about the content-type.

  If the result of the query is a single value (one column, one row) or a single column (multiple rows), this is recognized as a special case, and the FOR XML clause can be omitted. In the latter case, if text data is requested, all of the row values are returned with no separator characters; and all of the data is returned as one long text stream. If nontext data, such as numbers or a date, is requested, a binary version of the fields is returned.

?template

Denotes a SQL query string formatted as an XML document.

param

Is either a parameter name or a keyword. The keyword can be contenttype, outputencoding, root, or xsl.

Keyword	Description
contenttype	Specifies the content-type of the returned document. This describes the data contained in the body fully enough that the receiving agent (Web browser) can pick an appropriate mechanism to present the data to the user. contenttype provides the content-type and the subtype, which describes the nature of the data. The content-type (such as text, image, audio, video, and so on) describes the general type of data, and the subtype specifies a specific format for that type of data. For example, the contenttype value image/jpeg indicates that the general type of the data is image and the specific format is JPEG. The contenttype setting becomes the content-type field that is sent to the browser as part of the HTTP header, which contains the MIME-type (Multipurpose Internet Mail Extensions) of the document being sent as the body. text/XML is the default value of contenttype for the document. This means that text is the general type and XML is the specific format of the data. contenttype is specified when the specific type of the return results is known. The most common examples are: When html (xhtml) is being returned, usually as a result of XSL formatting, the contenttype is set to text/html. When a query specifies a Microsoft SQL Server™ BLOB field (for example, a JPEG image), the contenttype is set to image/jpeg. Many BLOB types have corresponding MIME types. A full list of registered MIME types is maintained by IANA (Internet Assigned Numbers Authority).. Another useful contenttype setting is text/plain, which on many browsers causes the results to appear with no formatting at all.
outputencoding	Specifies the character set to use for the resulting output XML document. By default, encoding for the result is set to UTF-8 (Unicode Transformation Format). You can override the default by using outputencoding to specify the encoding you want. The XML processing instruction is then provided appropriately and the document is encoded to match. If a template is specified at the URL using keyword template=, the encoding is Unicode. For all other templates, the encoding is obtained from the template (template is a valid XML document and, therefore, has its own encoding). If an XML template is specified at the URL (instead of an SQL query) and outputencoding is also specified, the encoding specified in outputencoding overrides the template.
root	The result of a query (SQL or XPath) may not be a well-formed document and, therefore, may cause the browser to display parser errors when loading the result. SQL ISAPI supports this keyword. When this keyword is specified, the result of a query is wrapped in the given root tag to return a well-formed document.
xsl	Specifies the URL to an XSL file that is used to process the resulting XML data. By definition, XSL takes two XML documents and produces a third. One of the input documents contains the data, and the other contains the XSL processing instructions. Unless specified in the XSL document, the output document has a default encoding of UTF-8. If another encoding is wanted, it should be specified in the XSL document. In that case, the encoding specified in the XSL document is used when the document is returned. If outputencoding is specified and an XSL document is also specified in xsl, the encoding specified in outputencoding overrides the encoding of XSL document.

value

Is the text string using standard URL escape sequences.

See Also

Accessing SQL Server Using HTTP