- Erlang - Web Programming
- Erlang - Drivers
- Erlang - Performance
- Erlang - Concurrency
- Erlang - OTP
- Erlang - Distributed Programming
- Erlang - Ports
- Erlang - Databases
- Erlang - Emails
- Erlang - Processes
- Erlang - Funs
- Erlang - Binaries
- Erlang - BIFS
- Erlang - Guards
- Erlang - Pattern Matching
- Erlang - Preprocessors
- Erlang - Header Files
- Erlang - Macros
- Erlang - Exceptions
- Erlang - Records
- Erlang - Tuples
- Erlang - Maps
- Erlang - Atoms
- Erlang - File I/O
- Erlang - Lists
- Erlang - Strings
- Erlang - Numbers
- Erlang - Recursion
- Erlang - Modules
- Erlang - Functions
- Erlang - Decision Making
- Erlang - Loops
- Erlang - Operators
- Erlang - Variables
- Erlang - Data Types
- Erlang - Shell
- Erlang - Basic Syntax
- Erlang - Environment
- Erlang - Overview
- Erlang - Home
Erlang Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Erlang - Databases
Erlang has the abipty to connect to the traditional databases such as SQL Server and Oracle. Erlang has an inbuilt odbc pbrary 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
Estabpshing a Connection
To estabpsh 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 pbrary 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 capabipty 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 capabipty 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 capabipty 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 capabipty 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 capabipty 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 capabipty 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}Advertisements