Database in Erlang

Erlang can connect to traditional databases such as SQL Server and Oracle. Erlang has ainbuilt odbc libraryTools available for database processing.

Database connection

In our example, we will use Microsoft SQL Server. Before connecting to the Microsoft SQL Server database, please ensure that the following pointers have been checked.

• You have created the database TESTDB.

• You have created a table EMPLOYEE in TESTDB.

• The table contains fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.

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

• Make sure you have created an ODBC DSN named usersqlserver that creates an ODBC connection to the database

Establish connection

To establish a connection with the database, you can use the following code example.

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 points should be noted about the above program.

• The startup method of the odbc library is used to indicate the start of database operations.

• The connection method requires DSN, username, and password to connect.

Create database table

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

-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 check the database now, you will see that a record namedEMPLOYEEtable.

Inserting records into the database

It is needed when creating records in a database table.

The following example will insert a record into the employee table. If the table is updated successfully, the record and the statement will return the values of the updated record and the number of updated records.

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

{updated,1}

Retrieve records from the database

The following program shows an 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 is as follows: }}

Output

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

Therefore, you can see that the insert command in the previous section worked, and the select command returned the correct data.

Retrieve records from the database by parameters

Erlang also has the function to retrieve records from the database based on certain filter conditions.

An example is as follows:

-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:

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

Update records in the database

Erlang also has the function to update records in the database.

Here is a similar 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:

{updated,1}

Delete records from the database

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

Here is a similar 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 is as follows: }}

{updated,1}

Table structure

Erlang also has the function to describe table structure.

An example is as follows

-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 is as follows: }}

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

Number of records

Erlang also has the function to get the total number of records in the table.

The same example is shown in the following program.

-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}