SQL Provider:: ExecSQL

Passes SQL and ADO commands to an OLE DB provider on a database server. Binds input and output parameters to the SQL command and returns an XML response. Used by Microsoft® Provisioning Framework (MPF).

XML Input Schema

The following code fragment shows the format for sending data to this procedure. For more information on individual elements and attributes, see the Elements and Attributes table.

<executeData>1..1
  <connect>1..1
	<param name="..">0..unbounded</param>
  </connect>
  <timeout connect="..." execute=".."/>0..1
  <command param=".." transactional = "..">1..1</command>
  <param name=".." type=".." explicitNulls="..">0..unbounded
	<null/>0..1
   </param>
</executeData>

XML Output Schema

ADO recordsets are returned as /executeData/xml nodes. Output parameters returned by calls to stored procedures are returned as /executeData/param nodes. The Microsoft® XML Parser (MSXML) automatically escapes any return values that would conflict with XML usage.

Elements and Attributes

The following table describes the XML schema elements and attributes. Unless otherwise indicated, the data type is string.

Element Description, relationships, and attributes
command Description:
 Command string for the SQL statement. 

Whenever the SQL statement contains characters such as "<" and ">" whose SQL usage conflicts with the XML parser, encapsulate the command string in an XML CDATA node. Alternately, you can replace the problematic characters with their corresponding escape sequences, but the resulting code will be harder to read.

If the SQL statement has parameters, they are represented by param nodes that follow the command node. For calls to stored procedures, if a param node is not specified, ExecSQL still passes the parameter but assumes it takes a default value. Otherwise, every parameter in the statement has a param node.

The SQL Provider supports two types of parameter matching: by order and by name. In the following examples, the SQL parameters are simply embedded in the executeData node. In actual procedures, these parameters can be derived from XSL transformations.

  • Matching by order

    Every SQL parameter is represented by a question mark and a corresponding unnamed param node. In the following example, the state parameter binds to the first param node and the au_lname parameter binds to the second param node.

    <executeData>
  <connect Provider="SQLOLEDB" Server="(local)" Database="pubs" Trusted_Connection="yes" /> 
  <command>
	<![CDATA[ 
	SELECT au_fname, au_lname, phone AS Telephone FROM authors 
	WHERE state=? and au_lname<>?
	]]>
  </command>
  <param>CA</param> 
  <param>McBadden</param> 
</executeData>
  • Matching by name

    Every SQL parameter is represented by a named mark and a corresponding named param node. This example is the same as the previous one except that matching is now by name.

    <executeData>
  <connect Provider="SQLOLEDB" Server="(local)" Database="pubs" Trusted_Connection="yes" /> 
  <command>
	<![CDATA[ 
	SELECT au_fname, au_lname, phone AS Telephone FROM authors 
	WHERE state=@state and au_lname<>@lname
	]]>
  </command>
  <param name="state">CA</param> 
  <param name="lname">McBadden</param> 
</executeData>

Parent: 
 executeData

Attributes:

param Optional. Enumeration that specifies how ExecSQL executes parameter matching: "auto" (default), "named", "unnamed", or "none". For "auto", it automatically binds all parameters. For "named", it binds only named (@) parameters. For "unnamed", it binds only ordered ("?") parameters. For "none", it binds no parameters.
transactional Optional. Boolean that identifies whether the SQL command is non-transactional. Some commands such as CREATE DATABASE and DROP TABLE are not transactional; that is, they cannot be reversed by a rollback if there is a failure. Including the transactional attribute with a value of "0" enables you to execute such commands.
connect Description:
 ADO connection parameters required for communication with the SQL database. Most commonly, these parameters are represented in XML with a param node for every property/value pair, as in the following example: 
<connect>
  <param name="Provider">sqloledb</param>
  <param name="Server">myserver</param>
  <param name="Database">pubs</param>
  <param name="Trusted_Connection">yes</param>
</connect>

ExecSQL concatenates these parameters into the ADO connect string as follows:

Provider=sqloledb;Server=myserver;Database=pubs;Trusted_Connection=yes

Alternately, you can represent some parameters simply as attributes of the connect node. The following example is functionally equivalent to the previous example:

<connect Provider="sqloledb" Server="myserver" Database="pubs" Trusted_Connection="yes"/>

The first format supports all attribute names and is the more common usage. The second can be used only with the ADO parameters that are defined in the input schema for ExecSQL. (See the list following "Atributes.") Both formats can be used in the same request.

Parent: 
 executeData

Child:
param (minOccurs="0" maxOccurs="*")

Attributes:

The following ADO attributes are defined in the input schema for ExecSQL:

Note  These are the only attributes that can be specified as attributes of connect. Any other ADO attributes must be specified as \connect\param nodes.

Provider Optional. Name of the OLE DB provider to use for the connection.
Driver Optional. Name of the SQL Server ODBC driver.
Server Optional. Name of the SQL Server for the connection.
Database Optional. Name of the SQL database for the connection.
DSN Optional. Name of an existing ODBC user or system data source.
FileDSN Optional. Name of an existing ODBC file data source.
URL Optional. URL of the database server for the connection.
uid Optional. SQL Server logon account. Not required for Microsoft® Windows® authentication.
pwd Optional. Password for the SQL Server logon account specified in uid. Not required if the logon has a NULL password or if Trusted_Connection is Yes.
Password Optional. Same as pwd (password attribute varies by provider).
Trusted_Connection Enumeration that specifies how the ODBC driver performs logon validation. Values are yes or no (default). If yes, the ODBC driver uses Windows authentication and the uid and pwd attributes are optional. If no, ODBC driver uses the SQL Server username and password, and uid and pwd are required.

Attributes required for a particular usage vary depending on the requirements of the specific OLE DB driver. For more 
information, see the ADO documentation.

One ADO attribute, Maximum Rows, corresponds to the SQL Provider's MaxRows registry property, which defines the maximum number of rows that can be returned for a recordset from a SQL table. Specifying a new value for Maximum Rows enables you to override the default MaxRows value for a specific SQL command, as in the following example:

<param name="Maximum Rows">value</param>
executeData Description:
 Encapsulates the procedure's input and output data.

Children:
command (minOccurs="1" maxOccurs="1")
connect (minOccurs="1" maxOccurs="1")
param (minOccurs="0" maxOccurs="*")
timeout (minOccurs="0" maxOccurs="1")
null Description:
 NULL value for a parameter. Cannot be nested in a connect node or used in a param node that already includes a non-null value.

Parent: 
 param
param Description:
 Parameter for the SQL statement.

Parent: 
 connect, executeData

Child:
null (minOccurs="0" maxOccurs="1")

Attributes:

name Name of the parameter. Used for SQL queries involving matching by name. Not required for parameter matching by number.
type Enumeration that specifies parameter type for calls to stored procedures. Values are "input" (default), "output", "inout", or "return". Not used if param is a child of connect.
explicitNulls Boolean that determines whether SQL Provider distinguishes between empty strings and NULL values. Examples:
 
<param type="input" explicitNulls="0"></param> Passes NULL.
<param type="input" explicitNulls="1"></param> Passes empty string.
<param type="input" explicitNulls=".."><null/></param> Ignores explicitNulls setting and passes NULL.
<param type="output" explicitNulls="0"></param> Returns empty string regardless of whether returned string is empty string or NULL.
<param type="output" explicitNulls="1"></param> Returns empty string for empty string and <null/> node for NULL.
timeout Description:
 Timeouts for database connection and command execution. If not specified, the default ADO values are used: 15 seconds for connecting and 30 seconds for command execution. Normally, the default values are suitable.

Parent: 
 executeData

Attributes:

connect Optional. Connection timeout in seconds.
execute Optional. Command execution timeout in seconds.

See Also

SQL Provider

Up Top of Page
© 1999-2002 Microsoft Corporation. All rights reserved.