Omnichannel Services – Access a Database

Today I’m going to show you how to setup and access a simple database. When building omnichannel services, this could be a way to access a back-end database or to store data that is not available in the back-end systems. If the data in the back-end system change very seldom and need to be aggregated, a database for the omnichannel services can offer an efficient cache.

In this example, I will use a MySQL database that was installed when you set up your cloud server as I showed in one of my previous videos, Omnichannel Services – Cloud Server, and a similar approach can be used to access any of the other major databases like PostgreSQL, Oracle, SQL Server, or DB2.

To make the database accessible from PHP, we open a terminal (or PuTTY on Windows), login to the virtual server, and enter the following commands:

sudo apt-get install mysql-client
sudo apt-get install php5-mysql
sudo apt-get install php5-mysqlnd
sudo service apache2 restart

To make the database accessible remotely, you need to edit the MySQL configuration file with…

sudo nano /etc/mysql/my.cnf

…and comment (put a # at the beginning of the line with) the bind-address (exit and save with Ctrl+X, Y and Enter). Then login to MySQL…

mysql -u root -p

…enter the password for the root account (that you chose during the installation of MySQL) and grant remote access…

grant all on *.* to root identified by ‘';

…where you replace with a suitable password (exit with the “quit” command), and restart MySQL:

sudo service mysql restart

You also need to open the default MySQL port in the virtual server’s firewall (on Amazon it’s called security group), which you can see how to do on http://docs.aws.amazon.com/gettingstarted/latest/computebasics-linux/getting-started-security-group.html.
Let’s start by creating a simple table, and you can do that using Coda (https://panic.com/coda), but here I’m going to show you how it’s done using the terminal and by entering the following commands (don’t enter what’s in brackets, that’s just the explanation):

mysql -u root –p [login to MySQL by entering the password]
use test; [change to the sample database]
create table person (id int, name varchar(30)); [create table]
insert person (id,name) values(1,'Chris'); [insert row]
insert person (id,name) values(2,'Eliza'); [insert another row]
select * from person; [to check the contents of the table]
quit; [exit]

With the table in place, let’s create a service that access it and return its contents. First we connect to the database (line 2) and select the database (line 3). Then we query the database (line 4), and for each found row (line 6), the columns are inserted into an entity (line 8-10) that is added to an array (line 11). Then a root object (line 14) and headers (line 15-16) are added to the response before it’s converted to JSON and returned to the app (line 17).
To put this code on your server (using https://panic.com/coda), I just create a new file, and then it can be called with (replace 0.0.0.0 with the IP address of your server):

http://0.0.0.0/getFromDatabase

There you have an omnichannel service that query a database and transform the data to JSON that can be easily consumed by your apps or webs.

You can download the source code.