How to: Adapt the Samples to Connect to Oracle and Other Databases
The Business Data Catalog can connect to and provide business data from the following databases:
Microsoft SQL Server
Oracle
OLE DB
ODBC
The AdventureWorks2000 sample explains the basic way to work with the Business Data Catalog to show business data from a SQL Server database. When you work with other databases such as Oracle, OLE DB, and ODBC you follow exactly the same steps, and we recommend that you start with the AdventureWorks 2000 sample metadata and adapt it for these systems. However, when you adapt the AdventureWorks metadata or write new metadata for these systems, you should remember the following things:
The wildcard character may differ in these systems. SQL Server uses the asterisk (*). In Oracle and other systems, the wildcard character is usually the percent sign (%). Determine the wildcard character used by your system and change it in the WildcardCharacter property of the <LobSystem> tag.
The Oracle SQL syntax requires you to specify parameters in your query by prefixing them with a colon (:) rather than the at sign (@). Be sure to set these correctly in the SQL statements in your metadata.
Oracle requires explicit user id and password parameters in the connection string, which means:
You have to set up an enterprise definition in SSO with the Oracle credentials.
Use AuthenticationMode of RdbCredentials.
Make sure the Microsoft SSO service is started on all farm servers and set to automatic startup.
You cannot use the "RdbConnection User ID" and "RdbConnection Password" properties, as these values are supplied by SSO. If you specify them, they are simply ignored. You must use SSO to supply the Oracle credentials.
SSO requires RPC connectivity between servers. Make sure the necessary ports (135, etc.) are open. You can use KB 167260 as a guide on how to verify RPC connectivity using the rpings.exe and rpingc.exe utilities which are in the Microsoft Windows 2003 Server resource kit.
Example
This example shows you how to set the LobSystemInstance properties for an Oracle database that is connecting by using single sign-on (SSO). FEEDSTORE must match the TNS net service name. OracleDb must match the name of the enterprise application definition you set up in SSO.
<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database" Version="2.1.1.5" Name="Oracle" xmlns="https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
<LobSystemInstances>
<LobSystemInstance Name="Oracle Sys Instance">
<Properties>
<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RdbCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">Oracle</Property>
<Property Name="RdbConnection Data Source" Type="System.String">FEEDSTORE</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
<Property Name="SsoApplicationId" Type="System.String">OracleDb</Property>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity EstimatedInstanceCount="10000" Name="Employee">
<Properties>
<Property Name="Title" Type="System.String">EName</Property>
</Properties>
<Identifiers>
<Identifier TypeName="System.String" Name="EmployeeName" />
</Identifiers>
<Methods>
<Method Name="EmployeeFinder">
<Properties>
<Property Name="RdbCommandText" Type="System.String">SELECT * FROM EMP WHERE ENAME LIKE :Name ORDER BY EMPNO</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Wildcard" Name="EmployeeName" />
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name=":Name">
<TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" AssociatedFilter="EmployeeName" Name="EmployeeName">
<DefaultValues>
<DefaultValue MethodInstanceName="IdEnumeratorInstance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Employees">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Employees">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Employee">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Decimal" Name="EmpNO" />
<TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" Name="EName" />
<TypeDescriptor TypeName="System.String" Name="JOB" />
<TypeDescriptor TypeName="System.String" Name="MGR" />
<TypeDescriptor TypeName="System.String" Name="Job" />
<TypeDescriptor TypeName="System.DateTime" Name="HireDate" />
<TypeDescriptor TypeName="System.Decimal" Name="SAL" />
<TypeDescriptor TypeName="System.Decimal" Name="COMM" />
<TypeDescriptor TypeName="System.Decimal" Name="DEPTNo" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Finder" ReturnParameterName="Employees" ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0" Name="EmployeeFinderInstance" />
<MethodInstance Type="SpecificFinder" ReturnParameterName="Employees" ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0" Name="EmployeeSpecificFinderInstance" />
<MethodInstance Type="IdEnumerator" ReturnParameterName="Employees" ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0" Name="IdEnumeratorInstance" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>