Experiment 001

Databases
In another part of this website I outlined what databases are. I also stated there that nowadays there are widely used Internet databases with which you can communicate via the Internet by knowing their API interface. Before you start this exercise, I recommend that you read this information - you can find it here..

Experiment environment
Imagine that you have 6 devices in different places around the world that are connected to temperature sensors. Each device is connected to four such sensors. The measured temperature values are saved to the database. This database is installed on a server located somewhere in the so-called cloud. The server has a set of data stored in one table, such as in the figure below. The server is equipped with an appropriate application acting as a DBSM database management system..

database

As you can see, the database contains a table with six records (0 - 5). Each database record (table row) consists of 7 fields (table columns 0 - 6). There are fields for storing numeric data (ID, Temp1, Temp2, Temp3, Temp4) and text data (Pass, Localisation). The Pass field is dedicated to storing the password.

The data can be accessed via the Internet, because the DBSM system has an appropriate API interface (part of the server software) developed for this purpose, which is made public for authorized users of this system. We are users who only have read rights to the data. We don't know how to save information to the database, this is a normally encountered situation.
We were informed that the Web API of this database allows HTTP clients to query data from a table using two functions. Here they are.

1.

http://db.noerrors.pl/Ex001/read.php?id=x&pass=y
where: id - record identification number, pass - password for a given record.
The function returns data from five fields of the record, from 2 to 6.

2.
http://db.noerrors.pl/Ex001/readf.php?id=x&pass=y&field=z
where: id - record identification number, pass - password, field - field no (2 - 6).
The function returns data from one record field indicated in the query, from 2 to 6.


Task to do
   Read all data from the table.



Solving the task

From the command to call the first API function, for example:

http://db.noerrors.pl/Ex001/read.php?id=101&pass=data1

we can read that the service request is to be sent using the HTTP Internet protocol and is to be directed to a host with the domain name noerrors.pl.
So we have the following situation. Using a client application implemented on a host (machine), we should also send a request via the HTTP protocol to the server on which the database system we are interested in is installed.

Web App

To perform such an operation, we must use a numeric IP address, not a DNS domain address as noerrors.pl.

Therefore, you need to send a query to the appropriate DNS server. This server will send us the IP number corresponding to the IP address and then we will be able to properly direct our data reading request. To summarize, the client application must perform the following operations:
• send IP queries to the DNS server.
• wait for the DNS server to respond with an IP address (for example, IP = 195.78.67.46).
• send a query to the database.
The procedure may seem quite complicated to us now, but it will soon become clear that it is simple.
DNS server

Let's look at our API call again:

http://db.noerrors.pl/Ex001/read.php?id=101&pass=data1

It contains information for the server that it should find the location specified as db in its resources. This is some folder on the server's disk, we don't know its name, but the server knows it. Within this folder there is a subfolder called Ex001 and within it there is a read.php resource. Actually, we don't have to be particularly interested in this, but I will explain what it means. This subfolder simply contains the read.php file, which contains the code of the application written in PHP. Maybe you're wondering where this file got there? Well, I wrote this code and saved it there, because I have access to this server.

• • •

I have already presented you the entire database access mechanism. But you're probably already thinking about how it can be practically implemented. So it's time for practical exercises.


Experiment 1

Let's start with the fact that you don't have to write any application. You can use a ready-made tool that you probably have. I'm thinking about your web browser. She will perform all the operations I described above.
Just enter the following into its address bar:

http://db.noerrors.pl/Ex001/read.php?id=101&pass=data1

If you are lazy, just click on the link above :)
Web browser

The browser will contact the DNS server and then send a query to the database server and when the response comes, it will display it to you. You will see on the screen the contents of six fields of record 101, i.e. the location of the device coupled with four temperature sensors and the values measured by these sensors.
Such a long description of the mechanism of operation, but so simple to implement :)
Now read the remaining records of this mini database (change only the function arguments, i.e. id and pass values, in the browser address bar).


Experiment 2

You may have found the previous experiment a bit too simple. We can now expand on it a bit. If you use even a little bit of HTML HTML, you know what so-called forms are. They are often used on websites to give the user the ability to send data or queries to servers. What application could this be? I have prepared two versions of the code:
Form 1 (calling function read(), more precisely script read.php)
Form 2 (calling function readf(), more precisely script readf.php)
You can easily view the code of each of these applications because most web browsers have the "View page source" option. The code is very simple, but requires some knowledge of HTML.


Experiment 3

We may be in a situation where we would like to read some data from an Internet database server and process it appropriately. Let's imagine that the data in the database table used here specifies temperature on the Fahrenheit scale, and we want to display it on the Celsius scale. Then we will not be satisfied with the solutions shown in the experiments above.
We need a more advanced application that, at the user's request, will download data from an online database and process it appropriately. What to do then?
It depends on what programming language you know. Most high-level languages (C++, C#, Java, Python, etc.) have appropriate tools to accomplish this task. I most often use JavaScript on this website, which, combined with HTML code, gives quite a lot of opportunities to create interesting applications. I assume you have some knowledge of this programming language.
I decided to use examples of an applications written in HTML and JavaScript.
Application 1,
Application 2.
In these applications I used the AJAX technique (AJAX = Asynchronous JavaScript And XML). In the tutorial about this technique for accessing Internet databases, it is written: "AJAX is a programmer's dream." I will write differently: AJAX greatly facilitates communication with online databases.
Look and analyze the codes of these applications. Maybe create your own, more interesting application.



Next experiments

I suggest you practice as much as possible with both API functions presented above.

• • •

There are many websites on the Internet with a public API. Many of them are free to experiment with. Their creators provide available functions and the syntax to be used in communication. It does not always have to be the syntax presented above.
Therefore, I suggest you experiment with another database using its API.
Let's imagine a database with one table as below.

Calendar

As you can see, this virtual table contains data for all calendar days from January 1, 1930 to December 31, 2050. That's over 44,000 records. For each day (in each record) there are fields specifying: "Day of the week" (column 2), "Name of the day of the week" (column 3), "What day is it in a given year" (column 4) and "What week is it in a given year " (column 5).
The API for this database is just one function:

http://db.noerrors.pl/Ex001/Calendar.php?pass=x&m=y&d=y-m-d
where:
pass - password ( student or studentka),
m - query type (allowed values: 0 - 3),
y-m-d - date in the year-month-day format (for example: 1930-01-01).
So an example query might look like this:

http://db.noerrors.pl/Ex001/Calendar.php?pass=student&m=0&d=2024-03-21

So we have one password (student) giving access to all database records. Depending on the query mode we choose, this is what we will receive in response:
for m = 0 - day of the week and name of the day,
for m = 1 - week number in a given year for a given date,
for m = 2 - day number in a given year for a given date,
for m = 3 - a set of data in JSON format.
The format JSON is very common in data exchange and is easily understandable by humans (JSON = JavaScript Object Notation).

Reading information from the database is as follows. We send a query to the database. The DBSM (Database Management System) software searches column 1 and when it finds a record with a date corresponding to our query, it will generate a response. The response will include data stored in the appropriate fields of the record we are interested in.
How to send queries to such a database? You can do it exactly the same as in the previous experiments. You can enter your query in the address bar of your browser.

Web browser

You can also develop a form application in HTML, such as the one here.
I think these exercises will help you become freely using the APIs of various online databases.

Note for those who know PHP
PHP has a very good set of functions that operate on date and time. Therefore, you can easily simulate a database identical to the one presented above. However, it does not matter whether the database from which you read data is a simulation or a real database, because my goal is to give you the opportunity to practice using the API interface.
.

★ ★ ★

Maybe you feel unsatisfied because you could only read the data in the proposed exercises. You may have ideas for developing applications that require reading and writing data. If you want to experiment with a database that also allows you to save data, I recommend my website "Experiment 002".