Can you imagine how to connect from some legacy system or exotic programming language to the latest business software like Microsoft Dynamics CRM, Microsoft Dynamics AX, Microsoft SharePoint, Microsoft Exchange and many others? Today I will describe how the target data sources can be accessed from legacy systems using Connect Bridge platform. I will demonstrate it using functional language – Haskell and ODBC driver using well-known SQL syntax for data manipulation.
The Connect Bridge platform is powerful integration platform that allows you to connect to latest systems using ODBC, JDBC drivers and web services. This combination enables you to connect from any environment including legacy systems.
Environment setup
First we need to prepare environment to run code. This includes 3 basic steps:
- 1. Create Connect Bridge playground
- 2. Prepare Haskell environment
- 3. Configure connection string
Create Connect Bridge playground
There is a possibility to get free demo account Connect Bridge Playground with trial access to Dynamics CRM, SharePoint and Exchange. You need to visit CB Playground page and follow the instruction to get the access.
In few minutes after request, you will receive an email with the download link. Please read the email thoroughly or visit the company page to get better overview of the Connect Bridge solution.
Download the package and extract. Start QueryAnalyzer tool using batch file in root folder. You will see preconfigured connection to CRM, SharePoint and Exchange. Feel free to play with QueryAnalyzer tool and touch target systems using SQL queries.
Prepare Haskell environment
The simplest way how to configure the package is to download and install complete Haskell Platform (https://www.haskell.org/platform/windows.html).
After installation the additional configuration is required – we need to install Haskell ODBC driver. In command line run:
cabal update
cabal install HDBC-odbc
After this your Haskell environment should be fully prepared.
Find connection string
In our sample we will use ODBC driver for connection to Connect Bridge Server. For this, you will need to know your connection string target system, that has been pre-configured for you. Open the QueryAnalyzer tool from Connect Bridge Playground, that you have downloaded. Select single connection in Connection Browser, right-click and choose Edit connection. Go to Advanced tab and copy the connection string.
Open provided CB_HaskellSample.hs file and place your connection string instead of sample connection string at line 7.
Code
To access Connect Bridge Server using you need to import packages:
import Database.HDBC import Database.HDBC.ODBC
To establish database connection to Connect Bridge server you can use connectODBC function:
conn <- connectODBC 'yourConnectionString';
To execute query that does not produce results you can call:
run conn 'INSERT INTO account (name) VALUES ('Hello world from Haskell')'
To execute query with result set you can call:
quickQuery conn 'SELECT accountid, name FROM account' []
I’ve created a sample function that demonstrates how to query data and process results in Haskell:
executeQuery :: IO ( )
executeQuery = do
{
putStr 'Enter query: ' ;
query <- getLine ;
putStrLn 'Connecting to Connect Bridge Server ...' ;
conn <- connectODBC connectionString;
putStrLn ( 'Executing query '' ++ query ++ ''' ) ;
vals <- quickQuery conn query [ ] ;
putStrLn ( 'Returned row count ' ++ show ( length vals ) ) ;
putStrLn ( convertResultSetToString vals )
}
Calling the Code
To run sample you can use any Haskell interpreter, e.g. WinGHCi that comes with package.
Open the provided file CB_HaskellSample.hs with already modified connection string.
Just type name of function you want to call. You will be prompted to enter input parameters.
Example 1:
This example shows how to create new account in Dynamics CRM from Haskell. Just call a function executeQuery and pass SQL query ‘INSERT INTO account (name) VALUES (‘Hello world from Haskell’)‘ as query. This will create immediately new account record in your Dynamics CRM.
*Main> executeQuery
Enter query: INSERT INTO account (name) VALUES (‘Hello world from Haskell’)
Connecting to Connect Bridge Server …
Executing query ‘INSERT INTO account (name) VALUES (‘Hello world from Haskell’)’
Returned row count 0
Example 2:
This example shows how to retrieve 5 newest accounts from Dynamics CRM. Call function executeQuery and pass SQL query ‘SELECT TOP 5 accountid, name, createdon FROM account ORDER BY createdon’. Optionally you can call function csvExportQuery that will write results into CSV file.
Enter query: SELECT TOP 5 accountid, name, createdon FROM account ORDER BY createdon DESC
Connecting to Connect Bridge Server …
Executing query ‘SELECT TOP 5 accountid, name, createdon FROM account ORDER BY createdon DESC’
Returned row count 5
account(e436e4d4-7d24-e411-a6b7-00155dc2040f),Hello world from Haskell 5,2014-08-15 13:12:39
account(168f7ecb-7d24-e411-a6b7-00155dc2040f),Hello world from Haskell 4,2014-08-15 13:12:29
account(158f7ecb-7d24-e411-a6b7-00155dc2040f),Hello world from Haskell 3,2014-08-15 13:12:24
account(a2079fc4-7d24-e411-a6b7-00155dc2040f),Hello world from Haskell 2,2014-08-15 13:12:18
account(a1079fc4-7d24-e411-a6b7-00155dc2040f),Hello world from Haskell 1,2014-08-15 13:12:12
Conclusion
I have demonstrated how you can easily access Dynamics CRM from Haskell with few lines of code. The same small effort is to access other systems, e.g. SharePoint, Exchange, AX, Navision, Salesforce … (see. full list of connectors: (https://test.connecting-software.com/connect-bridge/). Moreover you can access any of these systems from any language with same small effort as was described in this article.
If you enjoyed the post, get free Connect Bridge environment and start to play today!