Parent Elements

connection (§3.13.1)

Attributes

Description

command (Command Text)

The string containing the database command to pass to the data provider API that will interact with the external source in order to retrieve data. These strings can be constructed in a variety of ways (from simple UIs built into the spreadsheet application for browsing and choosing tables and fields, to external applications providing user interface to build up complex queries, to advanced users editing text queries). The spreadsheetML application need not understand the command syntax; it can simply pass the command string to the data provider API in order to retrieve the latest external data.

[Example:

Data connectivity can use a number of different technologies. The following is one example of an ODBC command string of commandType=2 (for a Microsoft SQL Server database):

command="SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry_x000d__x000a_FROM Northwind.dbo.Orders Orders_x000d__x000a_WHERE (Orders.ShipCountry=?)"

Some characters in this string have been escaped - for more information on the escaping scheme, please refer to the ST_Xstring type definition. end example]

[Note: the "?" syntax in the string is something that the ODBC data provider is aware of and may replace with a parameter before execution. end note]

[Example:

Data connectivity can use a number of different technologies. The following is one example of an OLE DB command string of commandType=3 (for an Oracle database):

command=""TESTDB"."ShippersTable""

end example]

[Note: Data connectivity can use a number of different technologies. A few examples of potential values stored in this attribute can be found at:

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_statements.asp

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_minimum_grammar.asp

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbusing_commands.asp

end note]

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

commandType (OLE DB Command Type)

Specifies the OLE DB command type.

Supported values are as follows:

1. Query specifies a cube name

2. Query specifies a SQL statement

3. Query specifies a table name

4. Query specifies that default information has been given, and it is up to the provider how to interpret.

5. Query is against a web based List Data Provider.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

connection (Connection String)

The connection string is used to make contact with an OLE DB or ODBC data source. These can be constructed in a variety of ways (from UI wizards built into the data provider code, to external query applications, to advanced users editing text files). The spreadsheetML application need not understand the connection syntax at all; it can simply pass the command string to the data provider API in order to re-establish a connection with the external data source.

[Example: ODBC connection string to a database:

connection="DRIVER=SQL Server;SERVER=example_server;UID=example_useralias;APP=Microsoft Office 2007;WSID=user_alias;Trusted_Connection=Yes"

end example]

[Example: of an OLE DB connection string to an Oracle database:

connection="Provider=OraOLEDB.Oracle.1;Password=example_password;Persist Security Info=True;User ID=example_useralias;Data Source=example_server;Extended Properties="""

end example]

[Note: Data connectivity can use a number of different technologies. A few examples of potential values stored in this attribute can be found at:

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview.asp

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp

• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdreforacleprovspec.asp

end note]

Connection strings syntaxes are specific to individual ODBC or OLE DB data provider drivers.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

serverCommand (Command Text)

Specifies a second command text string that is persisted when PivotTable server-based page fields are in use.

For ODBC connections, serverCommand is usually a broader query than command (no WHERE clause is present in the former). Based on these 2 commands, parameter UI can be populated and parameterized queries can be constructed.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).