SQL Provider:: ExecSQL

Article
04/19/2010

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).

Arguments

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: paramOptional. 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. transactionalOptional. 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. ProviderOptional. Name of the OLE DB provider to use for the connection. DriverOptional. Name of the SQL Server ODBC driver. ServerOptional. Name of the SQL Server for the connection. DatabaseOptional. Name of the SQL database for the connection. DSNOptional. Name of an existing ODBC user or system data source. FileDSNOptional. Name of an existing ODBC file data source. URLOptional. URL of the database server for the connection. uidOptional. SQL Server logon account. Not required for Microsoft Windows authentication. pwdOptional. 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. PasswordOptional. Same as pwd (password attribute varies by provider). Trusted_ConnectionEnumeration 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: nameName of the parameter. Used for SQL queries involving matching by name. Not required for parameter matching by number. typeEnumeration 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: connectOptional. Connection timeout in seconds. executeOptional. Command execution timeout in seconds.

Remarks

No remarks.

Schema Definition

Input



          <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>

Sample Code

Example XML Request

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.

Example XML Response

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.

Applies To

SQL Provider for:

Hosted Messaging and Collaboration version 4.5
Hosted Messaging and Collaboration version 4.0
Hosted Messaging and Collaboration version 3.5
Hosted Messaging and Collaboration version 3.0
Windows-based Hosting version 4.5
Windows-based Hosting version 4.0
Windows-based Hosting version 3.5
Windows-based Hosting for Applications version 1.0