Erlang has the ability to connect to the traditional databases such as SQL Server and Oracle. Erlang has an inbuilt odbc library that can be used to work with databases.

Database Connection

In our example, we are going to make use of the Microsoft SQL Server. Before connecting to a Microsoft SQL Server database, make sure that the following pointers are checked.

• You have created a database TESTDB.

• You have created a table EMPLOYEE in TESTDB.

• This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

• User ID “testuser” and password “test123” are set to access TESTDB.

• Ensure that you have created an ODBC DSN called usersqlserver which creates an ODBC connection to the database

Establishing a Connection

To establish a connection to the database, the following code example can be used.

Example

-module(helloworld). 
-export([start/0]). 

start() ->
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver;UID = testuser;PWD = test123", []), 
   io:fwrite("~p",[Ref]).

The output of the above program is as follows −

Output

<0.33.0>

The following things need to be noted about the above program.

• The start method of the odbc library is used to indicate the beginning of the database operation.

• The connect method requires a DSN, user name and password to connect.

Creating a Database Table

The next step after connecting to the database is to create the tables in our database. The following example shows how to create a table in the database using Erlang.

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123, []), 
   odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (FIRSTNAME char varying(20), 
   LASTNAME char varying(20), AGE integer, SEX char(1), INCOME integer)")

If you now check the database, you will see that a table called EMPLOYEE will be created.

Inserting a Record into the Database

It is required when you want to create your records into a database table.

The following example will insert a record in the employee table. If the table is successfully updated, the record and the statement will return the value of the updated record and the number of records that were updated.

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123", []), 
   io:fwrite("~p",[odbc:sql_query(Ref, 
   "INSERT INTO EMPLOYEE VALUES(''Mac'', ''Mohan'', 20, ''M'', 2000)")]).

The output of the above program will be −

Output

{updated,1}

Fetching Records from the Database

Erlang also has the capability to fetch records from the database. This is done via the sql_query method.

An example is shown in the following program −

Example

-module(helloworld). 
-export([start/0]). 

start() ->
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123", []), 
   io:fwrite("~p",[odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE") ]).

The output of the above program will be as follows −

Output

{selected,["FIRSTNAME","LASTNAME","AGE","SEX","INCOME"],
[{"Mac","Mohan",20,"M",2000}]}

So you can see that the insert command in the last section worked and the select command returned the right data.

Fetching Records from the Database Based on Parameters

Erlang also has the capability to fetch records from the database based on certain filter criteria.

An example is as follows −

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN=usersqlserver; UID=testuser;PWD=test123", []), 
   io:fwrite("~p",[ odbc:param_query(Ref, "SELECT * FROM EMPLOYEE WHERE SEX=?", 
   [{{sql_char, 1}, ["M"]}])]).

The output of the above program will be −

Output

{selected,["FIRSTNAME","LASTNAME","AGE","SEX","INCOME"],
         [{"Mac","Mohan",20,"M",2000}]}

Updating Records from the Database

Erlang also has the capability to update records from the database.

An example for the same is as follows −

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123", []), 
   
   io:fwrite("~p",[ odbc:sql_query(Ref, "
      UPDATE EMPLOYEE SET AGE = 5 WHERE INCOME= 2000")]).

The output of the above program will be −

Output

{updated,1}

Deleting Records from the Database

Erlang also has the capability to delete records from the database.

An example for the same is as follows −

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123", []), 
   io:fwrite("~p",[ odbc:sql_query(Ref, "DELETE EMPLOYEE WHERE INCOME= 2000")]).

The output of the above program will be as follows −

Output

{updated,1}

Table Structure

Erlang also has the capability to describe a table structure.

An example is as follows −

Example

-module(helloworld). 
-export([start/0]). 

start() -> 
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = testuser;PWD = test123", []), 
   io:fwrite("~p",[odbc:describe_table(Ref, "EMPLOYEE")]).

The output of the above program will be as follows −

Output

{ok,[{"FIRSTNAME",{sql_varchar,20}},
   {"LASTNAME",{sql_varchar,20}},
   {"AGE",sql_integer},
   {"SEX",{sql_char,1}},
   {"INCOME",sql_integer}]}

Record Count

Erlang also has the capability to fetch the total count of the records in a table.

An example for the same is shown in the following program.

Example

-module(helloworld). 
-export([start/0]). 

start() ->
   odbc:start(), 
   {ok, Ref} = odbc:connect("DSN = usersqlserver; UID = sa;PWD = demo123", []), 
   io:fwrite("~p",[odbc:select_count(Ref, "SELECT * FROM EMPLOYEE")]).

The output of the above program will be −

{ok,1}