Setting Up An Erlang and SQL Server Test Rig
We are in the beginnings of a project in which we will be building an Erlang service. The first step in being able to develop anything is to have a test rig that will allow you to mimic the final production environment.
Our Erlang system will be connecting to both a SQL Server 2012 database and also to a Riak database. In it’s final form, the Erlang service and the Riak database will be running on a Linux server and, obviously, the SQL Server will be running on a windows server. For our test rig, however, I will be running the whole set-up out of my laptop using a VM Player and an Ubuntu Linux virtual machine.
The majority of the configuration effort will be done in the Linux machine but there are some minor configuration changes that must be done on SQL Server so that it will accept the connection from the Linux machine. At a high level these are the steps:
- Set up the VM
- Install Ubuntu Linux
- Install Erlang in Linux
- Install IntelliJ IDE and Erlang plug-in in Linux
- Install ODBC drivers in Linux
- Configure MS SQL Server to accept external connections
- Test connectivity
Let’s begin the adventure…
Installing the Ubuntu
General steps are:
- Download the ISO image from the Ubuntu website
- Create a VM
- Configure (2 GB ram, 2 cores, 40 GB hd, split files)
- Boot the VM from the ISO image
Installing Erlang
Go to erlang solutions and select the Ubuntu “tab”:
Instructions:
At the time I installed it I was using the “Trusty” version of Ubuntu, which is 14.04. You can find out which version of Ubuntu you are running by using the following command:
$ lsb_release -c
In my case the result was:
user@ubuntu:/usr/bin/idea-IC-139.659.2/bin$ lsb_release -c
Codename: trusty
In the website look at the available sources definitions and add the following line to the /etc/apt/sources.list using your favorite editor. For example for me it was (Ubuntu 14.04):
deb http://packages.erlang-solutions.com/ubuntu trusty contrib
Next, add the Erlang Solutions public key for apt-secure using following commands:
$ wget http://packages.erlang-solutions.com/ubuntu/erlang_solutions.asc
$ sudo apt-key add erlang_solutions.asc</code></pre>
All the previous steps get Ubuntu ready to download the packages directly from Erlang solutions. Now we can do the actual install with these commands:
$ sudo apt-get update
$ sudo apt-get install erlang
If all is well you should be able to run in the command line erl command and get the Erlang VM. To run the shell:
$ erl
That’s it for this step.
Installing git
You will need Git to download the latest version of rebar (it’s like a make for erlang). To do that issue the following command:
$ sudo apt-get install git
That’s it for this step.
Installing Rebar
I installed rebar in the /usr/lib directory to be consistent with all the other libraries. To do that first go to the directory and then download the rebar files:
$ cd /usr/lib
$ sudo git clone git://github.com/rebar/rebar.git
$ cd rebar
$ sudo ./bootstrap #compiles rebar
$ cd /usr/bin
$ sudo ln -s /usr/lib/rebar/rebar #creates a link to the resulting rebar executable
With this Rebar should be complete.
That’s it for this step.
Installing IntelliJ and the Erlang IDE plug-in
Go to the IntelliJ site and see the instructions for downloading the install files. I put my files under the /usr/bin directory. In this example the actual install file was sitting in my home directory so:
$ cd /usr/bin
$ sudo tar xvzf /home/ilhadad/ideaIC-14.0.2.tar.gz
These commands unpack the file in the /usr/bin directory. Now we need to make it easier to start the program by creating a link in the /usr/bin directory. Like this:
$ sudo ln -s idea-IC-139.659.2/bin/idea.sh
To run the IDE we just have to run in the command line from anywhere:
idea.sh
To install the Erlang plug-in go to this site and follow the directions
That’s it for this step.
Setting up SQL Server and Windows
Enable the firewall connection by:
- Start->Run->firewall.cpl
- Right click on Inbound Rules and select “New Rule”
- Select “Port” on the radio button and click on “Next”
- Select “TCP” on the radio button, specify port 1433 and click on “Next”
- Select “Allow the connection” and click on “Next”
- Make sure all checkboxes are selected e.g. “Domain”, “Private”, and “Public”
- Give the Rule a meaninful name. e.g. “SQL Server 2012” and click on “Finish”
Once the firewall is enabled then we we need to enable SQL Server(2012) so that it will accept external connections. To do that:
- Run “SQL Server Configuration Manager”.
- In the interface on the right hand side select “SQL Server Network Configuration” -> “Protocols for SQLSERVER2012”.
- On the left pane make sure “TCP/IP” is “Enabled”.
- Right click on “TCP/IP” and in the context menu select properties.
- In the “TCP/IP Properties” dialog scroll down to find the “IP2” and ensure the IP address of the host is there.
- Scroll down to IPAll.
- Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
- Make sure that TCP Port is set to 1433. (Mine was blank.)
- Then “Apply” the changed settings.
- Run->services.msc
- Stop and Start the Microsoft SQl Server so the settings take.
That’s it for this step.
Installing ODBC
I got the information to get it up an running from these sites: www.bictor.com and www.savelono.com
This will be the ODBC system in Ubuntu.
$ sudo apt-get install unixodbc
Then you will need the MS SQL Server drivers. To get them follow these instructions to access SQL Server database install the packages:
$ sudo apt-get install freetds-bin freetds-common tdsodbc odbcinst1
This provides the libraries you need. Copy the contents of /usr/share/doc/freetds-common/examples/odbcinst.ini into /etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC layer.
Restart your webserver to load the ODBC module into PHP.
Test the connection by going back to the linux terminal and typing:
$ tsql -H 192.168.1.11 -U usernamehere -P pwdhere -p 1433
Response:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
If you get a response as shown above then you know all is well.
Instructions:
The following instructions came from www.bictor.com
Here two parameters are of primary concern to us for configuration
- freetds.conf directory
- TDS Version
As shown above freetds.conf directory is /etc/freetds and our TDS Version is 4.2
Now we need to edit /etc/odbcinst.ini but before that just find out the path of the libtdsodbc.so library
It is generally installed in /usr/lib/x86_64-linux-gnu/odbc/ directory but still you can verify by issuing the follwing command on the linux terminal
find / -name libtdsodbc.so
Now open /etc/odbcinst.ini and create a new section as show below
[FreeTDS]
Description = FreeTDS Driver v0.91
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
fileusage=1
dontdlclose=1
UsageCount=1
Now open /etc/odbc.ini and create a new connection identifier
[SSA_DB]
Driver = FreeTDS
Description = Development server
Trace = No
ServerName = mssql
Port = 1433
Database = SSA_DB
TDS_Version = 4.2
Now open /etc/freetds/freetds.conf and create a new section:
# Added this connection for SSA development
[mssql]
host = 192.168.1.11
port = 1433
tds version = 4.2
Note that the host name should be the IP or DNS resolveable name of the host which has SQL server installed.
OK! we are done. Now you can test the connection by using the isql utility
isql -v SSA_DB usernamehere pwdhere
Where usernamehere is a valid sql server user and pwdhere is the corresponding password for that user.
It should connect successfully to the MSSQL server
Getting Erlang to connect to SQL Server via ODBC
First you need to start the ERLANG ODBC Server. Here’s how:
Run Erlang VM first:
$ erl
Erlang/OTP 17 [erts-6.3] [source] [64-bit] [smp:2:2] [async-threads:10] [kernel-poll:false]
Eshell V6.3 (abort with ^G)
In the Erlang shell start the ODBC Server and establish a connection:
1> odbc:start().
ok
2> {ok,DbRef}=odbc:connect("DSN=SSA_DB;UID=SQL2005_309367_ey_user;PWD=dilly07",[]).
{ok,<0.40.0>}
Now to do some queries:
3> odbc:sql_query(DbRef, "SELECT * FROM eyProject").
{selected,["ProjectID","ClientID","ProjectName",
"PMContactID","AnalystContactID","ClientContactID",
"ContractStartDate","PeriodStartDate","PeriodEndDate",
"PeriodType","TrueUp","BaseCurrency",
"SelfAllocationNonAllocable","AllocatedWithFlexFactor",
"LastAllocatedOn","EntitiesLastChangedOn",
"DeptDescLastChangedOn","CostLastChangedOn",
"MarkUpLastChangedOn","AllocationInfoLastChangedOn"],
[{1,33,"Test 2",null,null,null,
{{2014,8,14},{0,0,0}},
{{2014,8,14},{0,0,0}},
{{2014,8,14},{0,0,0}},
null,false,null,false,false,null,null,null,null,null,null},
{2,19,"new test",null,null,null,
{{2014,8,14},{0,0,0}},
{{2014,8,14},{0,0,0}},
{{2014,8,14},{0,0,0}},
null,false,null,true,false,
{{2014,9,24},{16,38,36}},
{{2014,9,24},{16,38,19}},
null,null,null,null},
.........
{11,38,"8+4 V2 - Data From Vinay",null,null,null,
{{2014,12,9},{0,0,0}},
{{2014,12,9},{0,0,0}},
{{2014,12,9},{0,0,0}},
null,false,null,true,false,{...},...},
{12,41,"8+2",null,null,null,
{{2014,12,16},{0,0,0}},
{{2014,12,16},{0,0,0}},
{{2014,12,16},{0,0,0}},
null,false,null,true,false,...},
{13,42,"Debug",null,null,null,
{{2015,1,2},{0,0,0}},
{{2015,1,2},{0,0,0}},
{{2015,1,2},{0,0,...}},
null,false,null,true,...},
{14,42,"Debug1",null,null,null,
{{2015,1,2},{0,0,0}},
{{2015,1,2},{0,0,...}},
{{2015,1,...},{0,...}},
null,false,null,...},
{15,42,"Debug2",null,null,null,
{{2015,1,2},{0,0,...}},
{{2015,1,...},{0,...}},
{{2015,...},{...}},
null,false,...},
{16,42,"Version8",null,null,null,
{{2015,1,...},{0,...}},
{{2015,...},{...}},
{{...},...},
null,...},
{17,41,"0+12 Jan 6",null,null,null,
{{2015,...},{...}},
{{...},...},
{...},...},
{18,41,"0+12 Jan 9",null,null,null,{{...},...},{...},...}]}
4>
Success!
Erlang should now be ready to work with SQL Server via ODBC. Enjoy!
Keep coming back for more details on how to use Erlang with SQL Server.