Did you know you could use Haskell to access data that is stored in 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 the 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 a powerful integration platform that allows you to connect to different target 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 the environment to run the code. This includes 3 basic steps:
-
- 1. Install Connect Bridge
-
- 2. Prepare Haskell environment
-
- 3. Configure connection string
Install Connect Bridge
There is a possibility to get a free trial of Connect Bridge so that you can try all this in your environment and then decide if it suits you.
You should start by requesting the free trial here. We will get back to you within 24 working hours, and then you can proceed with the installation as described in this short video.
Prepare the Haskell environment
The simplest way to get Haskell working is to follow the instructions at https://www.haskell.org/get-started/
After installation, you will need to install Haskell ODBC driver. In the command line, run:
cabal update
cabal install HDBC-odbc
After this your Haskell environment should be fully prepared.
Find the connection string
In our sample, we will use the ODBC driver with Connect Bridge. 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 a single connection in the Connection Browser, right-click, and choose Edit connection. Go to the Advanced tab and copy the connection string.
Open the provided CB_HaskellSample.hs file and place your connection string instead of the 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 Microsoft Dynamics CRM from Haskell with a few lines of code. The same small effort is to access other systems, e.g., SharePoint, Exchange, AX, Navision, and Salesforce …
There is a full list of connectors at https://www.connecting-software.com/connectors/). Moreover, you can access any of these systems from any language with the same small effort as was described in this article.
If you enjoyed the post, get your free Connect Bridge trial and start to play with it today!
Original article & code by Tomas Olejnik, Dec 10, 2015
Last updated on May4, 2023