Erlang – Running Concurrent Queries against Microsoft SQL Server 0

Running Erlang on Windows, you can connect to Microsoft SQL Server using Erlang’s own ODBC application. It’s extremely easy and straightforward (example below), you can use a DSNless connection so you don’t even need to set up an entry through the Windows ODBC Data Source Administrator.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ᦙ鄊ภErlang R16B (erts-5.10.1) [64-bit] [smp:12:12] [async-threads:10]
 
Eshell V5.10.1  (abort with ^G)
1> application:start(odbc).
ok
2> ConnStr = "DRIVER={SQL Server};SERVER=TORVERM6;Trusted_Connection=yes".
"DRIVER={SQL Server};SERVER=TORVERM6;Trusted_Connection=yes"
3> {ok, Conn} = odbc:connect( ConnStr, [] ).
{ok,}
4> odbc:sql_query( Conn, "SELECT current_timestamp as NOW" ).
{selected,["NOW"],[{{{2013,4,17},{8,19,12}}}]}
5> {_, _, NOW} = odbc:sql_query( Conn, "SELECT current_timestamp as NOW" ).
{selected,["NOW"],[{{{2013,4,17},{8,19,37}}}]}
6> 
6> NOW.
[{{{2013,4,17},{8,19,37}}}]
7>

A real power of Erlang is the ease at which you can run functions concurrently, so what better way to execute your queries but asynchronously. The example below takes a list of servers and in parallel connects and retrieves the SQL Server time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-module(sqlserverlib).
-export([sqlget_servertime/1]).
-export([sqlget_selectquery/4]).
 
% sqlserverlib:sqlget_servertime( ["TONYALIEN", "TORVERM6"]).
 
sqlget_servertime(Servers) ->	
	sqlget_servertime( Servers, "SELECT current_timestamp AS sqltime", 0 ),
	sqlget_servertime_getdata( Servers ). 
 
sqlget_servertime_getdata( [Server|TheRest] ) ->
 
	receive {Pid, Server, ServerTime, Exec} ->
		io:format( "Pid ~w ", [Pid] ),
		io:format( "Exec ~w ", [Exec] ),
		io:fwrite( "query time on ~s ", [Server] ),
		io:fwrite( "~w~n", [ServerTime] )
 
		after 2000 ->
			io:fwrite( "Timed out after 2 seconds against ~s", [Server] )
	end,
 
	sqlget_servertime_getdata( TheRest );
 
sqlget_servertime_getdata( [] ) ->
	ok.
 
sqlget_servertime([Server|TheRest], Query, Exec) when Server /= "" ->	
	spawn( sqlserverlib, sqlget_selectquery, [self(), Server, Query, Exec] ),
	sqlget_servertime( TheRest, Query, Exec + 1 );
 
sqlget_servertime([], _, _) ->
	ok.
 
%	The function that runs concurrently
 
sqlget_selectquery( Pid, Server, Query, Exec ) ->
 
	application:start( odbc ),
 
	ConnStr = "DRIVER={SQL Server};SERVER=" ++ Server ++ ";Trusted_Connection=yes",
	{ ok, Conn } = odbc:connect( ConnStr, [] ),
 
	% will only ever be 1 row/1 col, so just get the column value as ServerTime
	{ _, _, ServerTime } = odbc:sql_query( Conn, Query ),
 
	Pid ! {self(), Server, ServerTime, Exec}.

Example execution:

1
2
3
4
5
6
7
8
9
10
11
Erlang R16B (erts-5.10.1) [64-bit] [smp:12:12] [async-threads:10]
 
Eshell V5.10.1  (abort with ^G)
1> c(sqlserverlib).
{ok,sqlserverlib}
2> sqlserverlib:sqlget_servertime(["TORVERM6", "TORVERH6", "TORVERH7", "TORVERM6"] ).
Pid  Exec 3 query time on TORVERM6 [{{{2013,4,17},{8,16,57}}}]
Pid  Exec 1 query time on TORVERH6 [{{{2013,4,17},{8,16,34}}}]
Pid  Exec 2 query time on TORVERH7 [{{{2013,4,17},{8,15,31}}}]
Pid  Exec 0 query time on TORVERM6 [{{{2013,4,17},{8,16,57}}}]
ok

So that’s all there is to it! Well except for error handling which I’ll cover in another post at a later date, and the fact I just realised all my servers times are out!

The function sqlget_servertime spawns the concurrent processes – one per server in the given list.

The function sqlget_servertime_getdata waits for a response from the spawned process – that is done sequentially, I’ve put a timeout of just 2 seconds because I expect both the login and query execution time to be less than that – in reality there will be a lot more code around this to cater for errors, also the structure feels a bit clumsy because say we were dealing with 100 servers and each server took 2 seconds to complete, because this function receives one message at a time then it will take 100 x 2 seconds to complete.

Anyway, the code gives you an idea of just how easy it is to run as many concurrent queries as you want from Erlang, the simplicity of the language’s recursion just makes things so dam easy!