locked
Connecting MySQL using BCS RRS feed

  • Question

  • For a Sharepoint 2010 application, 

    I created a ODBC connection for MySQL . When I tried connecting using BCS, the option used is

    Custom Connection String and in the connection string I entered the following inputs.

    DsnName=test;Driver={MySQL ODBC 5.1
    Driver};Server=192.168.1.5;Database=employee;User=root;Password=radhagaja;Option=3

    It is throwing a error, what could be the solution for this ?

    Tuesday, July 10, 2012 2:37 PM

Answers

  • Hi rajaramcomputers,

    Unfortunately SharePoint Designer doesn't support connecting to the database through ODBC. So you may follow one of the following approaches:

    1. Manually create a BDC Model for connecting to MySQL Databases from the beginning 
    2. Create a Web service to provide an interface to the external data exposed in the database
    3. Use Visual Studio's BDC Model project and write the code for MySQL connection. Please look at this blog post Business Data Connectivity Model – Finder Method

    You can also try out the product provided by the company where I work for, and name of that product is BCS Meta Man . It will allow you to generate both Database model and C# code automatically as soon as you drag and drop database tables to the design surface.

    It supports MySQL tables/views/stored procedures through ODBC connection.

    Please look at these blog posts:

    Please take a look at this sample BDC Model for MySQL database:

    <?xml version="1.0" encoding="utf-8"?>
    <Model Name="BCSMetaManModel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
      <LobSystems>
        <LobSystem Name="BCSMetaMan" Type="Database">
          <LobSystemInstances>
            <LobSystemInstance Name="BCSMetaManInstance">
              <Properties>
                <Property Name="ShowInSearchUI" Type="System.String"></Property>
                <Property Name="DatabaseAccessProvider" Type="System.String">Odbc</Property>
                <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
                <Property Name="RdbConnection Driver" Type="System.String">{MySQL ODBC 5.1 Driver}</Property>
                <Property Name="RdbConnection server" Type="System.String">taurus</Property>
                <Property Name="RdbConnection port" Type="System.String">3306</Property>
                <Property Name="RdbConnection database" Type="System.String">classicmodels</Property>
                <Property Name="RdbConnection uid" Type="System.String">root</Property>
                <Property Name="RdbConnection pwd" Type="System.String">12345</Property>
                <Property Name="RdbConnection Trusted_Connection" Type="System.String">yes</Property>
              </Properties>
            </LobSystemInstance>
          </LobSystemInstances>
          <Entities>
            <Entity Name="employees" Namespace="BCSMetaMan" Version="1.0.0.0">
              <Properties>
                <Property Name="OriginalTableName" Type="System.String">`employees`</Property>
                <Property Name="LeftDelimiter" Type="System.String">`</Property>
                <Property Name="RightDelimiter" Type="System.String">`</Property>
                <Property Name="Parameter" Type="System.String">?</Property>
                <Property Name="IsCustomCode" Type="System.Boolean">false</Property>
                <Property Name="Title" Type="System.String">lastName</Property>
              </Properties>
              <Identifiers>
                <Identifier Name="employeeNumber" TypeName="System.Int32" />
              </Identifiers>
              <Methods>
                <Method Name="GetAllemployeesEntitys">
                  <Properties>
                    <Property Name="BackEndObject" Type="System.String">employees</Property>
                    <Property Name="BackEndObjectType" Type="System.String">OdbcTable</Property>
                    <Property Name="RdbCommandText" Type="System.String">select `employeeNumber`, `lastName`, `firstName`, `extension`, `email`, `officeCode`, `reportsTo`, `jobTitle` from `employees`</Property>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="Schema" Type="System.String"></Property>
                  </Properties>
                  <Parameters>
                    <Parameter Name="returnparameter" Direction="Return">
                      <TypeDescriptor Name="employeesList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="employees" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                            <TypeDescriptors>
                              <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" ReadOnly="true" />
                              <TypeDescriptor Name="lastName" TypeName="System.String" />
                              <TypeDescriptor Name="firstName" TypeName="System.String" />
                              <TypeDescriptor Name="extension" TypeName="System.String" />
                              <TypeDescriptor Name="email" TypeName="System.String" />
                              <TypeDescriptor Name="officeCode" TypeName="System.Int32" />
                              <TypeDescriptor Name="reportsTo" TypeName="System.Nullable`1[[System.Int32]]" />
                              <TypeDescriptor Name="jobTitle" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="GetAllemployeesEntitys" Default="true" Type="Finder" ReturnParameterName="returnparameter" />
                  </MethodInstances>
                </Method>
                <Method Name="GetSingleemployeesEntityByID">
                  <Properties>
                    <Property Name="BackEndObject" Type="System.String">employees</Property>
                    <Property Name="BackEndObjectType" Type="System.String">OdbcTable</Property>
                    <Property Name="RdbCommandText" Type="System.String">select `employeeNumber`, `lastName`, `firstName`, `extension`, `email`, `officeCode`, `reportsTo`, `jobTitle` from `employees` where `employeeNumber`=?</Property>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="Schema" Type="System.String"></Property>
                  </Properties>
                  <Parameters>
                    <Parameter Name="?employeeNumber" Direction="In">
                      <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" />
                    </Parameter>
                    <Parameter Name="returnparameter" Direction="Return">
                      <TypeDescriptor Name="employeesList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="employees" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                            <TypeDescriptors>
                              <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" ReadOnly="true" />
                              <TypeDescriptor Name="lastName" TypeName="System.String" />
                              <TypeDescriptor Name="firstName" TypeName="System.String" />
                              <TypeDescriptor Name="extension" TypeName="System.String" />
                              <TypeDescriptor Name="email" TypeName="System.String" />
                              <TypeDescriptor Name="officeCode" TypeName="System.Int32" />
                              <TypeDescriptor Name="reportsTo" TypeName="System.Nullable`1[[System.Int32]]" />
                              <TypeDescriptor Name="jobTitle" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="GetSingleemployeesEntityByID" Default="true" Type="SpecificFinder" ReturnParameterName="returnparameter" ReturnTypeDescriptorPath="employeesList[0]" ReturnTypeDescriptorLevel="1" />
                  </MethodInstances>
                </Method>
              </Methods>
            </Entity>
          </Entities>
        </LobSystem>
      </LobSystems>
    </Model>

    Hope this makes sense.


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    Wednesday, July 11, 2012 10:34 AM

All replies

  • Will you please provide us with the error?

    Jason Warren
    Infrastructure Specialist

    Tuesday, July 10, 2012 3:06 PM
  • I am getting the following error


    Server Error: An Error Occured While retrieving the list of databases
    from: The WebServer Cannot connect to the specified data source because
    the query may contain errors the datasouce may be unavailable or
    configured improperly ,or your website is behind a proxy server or
    firewall.
    Wednesday, July 11, 2012 4:50 AM
  • Hi rajaramcomputers,

    Unfortunately SharePoint Designer doesn't support connecting to the database through ODBC. So you may follow one of the following approaches:

    1. Manually create a BDC Model for connecting to MySQL Databases from the beginning 
    2. Create a Web service to provide an interface to the external data exposed in the database
    3. Use Visual Studio's BDC Model project and write the code for MySQL connection. Please look at this blog post Business Data Connectivity Model – Finder Method

    You can also try out the product provided by the company where I work for, and name of that product is BCS Meta Man . It will allow you to generate both Database model and C# code automatically as soon as you drag and drop database tables to the design surface.

    It supports MySQL tables/views/stored procedures through ODBC connection.

    Please look at these blog posts:

    Please take a look at this sample BDC Model for MySQL database:

    <?xml version="1.0" encoding="utf-8"?>
    <Model Name="BCSMetaManModel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
      <LobSystems>
        <LobSystem Name="BCSMetaMan" Type="Database">
          <LobSystemInstances>
            <LobSystemInstance Name="BCSMetaManInstance">
              <Properties>
                <Property Name="ShowInSearchUI" Type="System.String"></Property>
                <Property Name="DatabaseAccessProvider" Type="System.String">Odbc</Property>
                <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
                <Property Name="RdbConnection Driver" Type="System.String">{MySQL ODBC 5.1 Driver}</Property>
                <Property Name="RdbConnection server" Type="System.String">taurus</Property>
                <Property Name="RdbConnection port" Type="System.String">3306</Property>
                <Property Name="RdbConnection database" Type="System.String">classicmodels</Property>
                <Property Name="RdbConnection uid" Type="System.String">root</Property>
                <Property Name="RdbConnection pwd" Type="System.String">12345</Property>
                <Property Name="RdbConnection Trusted_Connection" Type="System.String">yes</Property>
              </Properties>
            </LobSystemInstance>
          </LobSystemInstances>
          <Entities>
            <Entity Name="employees" Namespace="BCSMetaMan" Version="1.0.0.0">
              <Properties>
                <Property Name="OriginalTableName" Type="System.String">`employees`</Property>
                <Property Name="LeftDelimiter" Type="System.String">`</Property>
                <Property Name="RightDelimiter" Type="System.String">`</Property>
                <Property Name="Parameter" Type="System.String">?</Property>
                <Property Name="IsCustomCode" Type="System.Boolean">false</Property>
                <Property Name="Title" Type="System.String">lastName</Property>
              </Properties>
              <Identifiers>
                <Identifier Name="employeeNumber" TypeName="System.Int32" />
              </Identifiers>
              <Methods>
                <Method Name="GetAllemployeesEntitys">
                  <Properties>
                    <Property Name="BackEndObject" Type="System.String">employees</Property>
                    <Property Name="BackEndObjectType" Type="System.String">OdbcTable</Property>
                    <Property Name="RdbCommandText" Type="System.String">select `employeeNumber`, `lastName`, `firstName`, `extension`, `email`, `officeCode`, `reportsTo`, `jobTitle` from `employees`</Property>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="Schema" Type="System.String"></Property>
                  </Properties>
                  <Parameters>
                    <Parameter Name="returnparameter" Direction="Return">
                      <TypeDescriptor Name="employeesList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="employees" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                            <TypeDescriptors>
                              <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" ReadOnly="true" />
                              <TypeDescriptor Name="lastName" TypeName="System.String" />
                              <TypeDescriptor Name="firstName" TypeName="System.String" />
                              <TypeDescriptor Name="extension" TypeName="System.String" />
                              <TypeDescriptor Name="email" TypeName="System.String" />
                              <TypeDescriptor Name="officeCode" TypeName="System.Int32" />
                              <TypeDescriptor Name="reportsTo" TypeName="System.Nullable`1[[System.Int32]]" />
                              <TypeDescriptor Name="jobTitle" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="GetAllemployeesEntitys" Default="true" Type="Finder" ReturnParameterName="returnparameter" />
                  </MethodInstances>
                </Method>
                <Method Name="GetSingleemployeesEntityByID">
                  <Properties>
                    <Property Name="BackEndObject" Type="System.String">employees</Property>
                    <Property Name="BackEndObjectType" Type="System.String">OdbcTable</Property>
                    <Property Name="RdbCommandText" Type="System.String">select `employeeNumber`, `lastName`, `firstName`, `extension`, `email`, `officeCode`, `reportsTo`, `jobTitle` from `employees` where `employeeNumber`=?</Property>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="Schema" Type="System.String"></Property>
                  </Properties>
                  <Parameters>
                    <Parameter Name="?employeeNumber" Direction="In">
                      <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" />
                    </Parameter>
                    <Parameter Name="returnparameter" Direction="Return">
                      <TypeDescriptor Name="employeesList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                        <TypeDescriptors>
                          <TypeDescriptor Name="employees" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                            <TypeDescriptors>
                              <TypeDescriptor Name="employeeNumber" TypeName="System.Int32" IdentifierName="employeeNumber" ReadOnly="true" />
                              <TypeDescriptor Name="lastName" TypeName="System.String" />
                              <TypeDescriptor Name="firstName" TypeName="System.String" />
                              <TypeDescriptor Name="extension" TypeName="System.String" />
                              <TypeDescriptor Name="email" TypeName="System.String" />
                              <TypeDescriptor Name="officeCode" TypeName="System.Int32" />
                              <TypeDescriptor Name="reportsTo" TypeName="System.Nullable`1[[System.Int32]]" />
                              <TypeDescriptor Name="jobTitle" TypeName="System.String" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="GetSingleemployeesEntityByID" Default="true" Type="SpecificFinder" ReturnParameterName="returnparameter" ReturnTypeDescriptorPath="employeesList[0]" ReturnTypeDescriptorLevel="1" />
                  </MethodInstances>
                </Method>
              </Methods>
            </Entity>
          </Entities>
        </LobSystem>
      </LobSystems>
    </Model>

    Hope this makes sense.


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    Wednesday, July 11, 2012 10:34 AM