<dbPr> (Database Properties)

This element stores all properties associated with an ODBC or OLE DB external data connection.

example:

Data connectivity can use a number of different technologies. The following is one example XML fragment defining an OLE DB <connection> and the associated <dbPr> element:

<connection id="2"
  odcFile="C:\My Documents\My Data Sources\Northwind Orders.odc" keepAlive="1" 
  name="Northwind Orders" description="northwind" type="5" refreshedVersion="3">
  <dbPr connection="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
    Security Info=True;Initial Catalog=Northwind;Data Source=dataserver1;Use 
    Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation 
    ID=LOCAL_MACHINE_NAME;Use Encryption for Data=False;Tag with column 
    collation when possible=False" 
    command="&quot;Northwind&quot;.&quot;dbo&quot;.&quot;Orders&quot;" 
    commandType="3"/>
</connection>

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

Note:

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="&quot;TESTDB&quot;.&quot;ShippersTable&quot;"

Note:

  • 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

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: 
connection="DRIVER=SQL Server;SERVER=example_server;UID=example_useralias;APP=Microsoft Office 2007;WSID=user_alias;Trusted_Connection=Yes"

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

Note:

  • 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

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

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_DbPr">
	<attribute name="connection" use="required" type="ST_Xstring"/>
	<attribute name="command" use="optional" type="ST_Xstring"/>
	<attribute name="serverCommand" use="optional" type="ST_Xstring"/>
	<attribute name="commandType" use="optional" type="xsd:unsignedInt" default="2"/>
</complexType>