How to connect BDC with Sybase Database

  • Article

Using BDC Editor Tool we can generate metadata xml file for connecting BDC with Sybase database. For this we can either use ODBC or OLEDB connection type. To use ODBC we need to first create DSN. The DSN name along with login credentials needs to be used in the connection string. Once this is done the connection gets established with Sybase database and the tables get populated and subsequently create an instance out of it. But, when we try to execute any of the method instances it doesn't work. It fails saying "The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Trusted_Connection' in the connection string". The same error is shown even if we add Trusted_Connection in the connection string. The same application definition file when used in SharePoint also doesn't work.

 The issue is because of the XML file that is generated through the editor tool. As we are using ODBC connection, we need to manually make few changes in the XML file in order to make it work.

- First to connect BDC with Sybase Database using BDC editor tool, we need to use ODBC / OLEDB as connection type.

- The connection string should essentially look like this - Dsn=dsnname;uid=username;pwd=password;Trusted_Connection=yes;integrated security=true

- Create an instance of it and then export the file and save it locally as a XML file.

The following changes has to be done manually in the Application Definition file

1. As ODBC is used, do not put quotation marks on the SQL query. The quotation marks are generated by BDC Editor and we need to remove them.

For e.g.: Instead of Select "ProductId", "ProductName" from Products - use Select ProductId, ProductName from Products.

2. Do not put parameters like @pk. This is not accepted by ODBC Driver. It only accepts parameter using ?

For e.g.: Instead of Select "ProductName" from Products where ProductId=@pk use Select ProductName from Products where ProductId=?

3. Trusted connection should be in title case. It should be Trusted_Connection and not trusted_connection.

For e.g.: Instead of <Property Name="rdbconnection trusted_connection" Type="System.String">yes</Property> use

<Property Name="rdbconnection Trusted_Connection" Type="System.String">yes</Property>

4. If the Application definition file is to be used in BDC Data List web part then make sure that there is at least one method instance of type "Finder". By default, BDC editor tool doesn't generate method instance of finder. It generates method instance of type "IdEnumerator". So, change "IdEnumerator" to "Finder".

5. Possibilities are there when we map the Application definition file with BDC web part, it might throw an error saying "unable to connect to abc_instance". In this case change User DSN to System DSN as System DSN is globally available to all users.