Collect sensor data into SQL using Robot events

In conveyor line systems and industrial robots, various events can trigger data collection procedures. Saving data is often tied to a detected object or a sensor reading. In this lecture you will build a robot that will read the value of the color sensor when a button is pressed. After the value is read, it will store the color name in a database. This lecture teaches you the most common set up for robot data acquisition systems.

Figure 1 - This example shows how your robot stores color data to the database

Requirements

Ozeki 10 installed: Installed Ozeki 10 Robot OS
Lego connection installed: Connect the Lego robot to Ozeki 10
MySQL database server installed: http://www.ozeki.hu/index.php?owpn=6150
Touch Sensor: http://www.ozeki.hu/index.php?owpn=6040
Color Sensor: http://www.ozeki.hu/index.php?owpn=6086

Task #1:

Create table for color data!

Before you would like to collect sensor data into database, you should create a new database table for these data. First please open the Control Panel in your Ozeki 10 and click on your database connection from the connections list. After that please click on the SQL tab, because you will create a new database table by typing and executing the SQL command. So please type 'CREATE TABLE Color_data ( Color VARCHAR(255) )' SQL command and click on the 'Execute' button to create a new table for the measured color sensor data (Figure 2). You will fill this table later.

Figure 2 - Creating the table to store color data

Task #2:

Write code to detect each time you press the Touch sensor!

After that you created a database table to store the collected sensor data, you should create a SNAP program which is able to detect Robot events. Please open the SNAP and place a 'when [key] key pressed' block from the Control tab as an entry point for your program. Set [key] as you wish. Then place a 'forever' block, because you should infinitely detect the events. With your program you will measure if the Touche Sensor's button is pressed, so please place an 'if [true/false]' block inside the 'forever' block, where [true/false] is the detected event. To define this event in your SNAP, place a '[value_1] = [value_2]' block, where [value] is 'Button on [connection]' block from the Lego tab, where [connection] is your Touch Sensor's name and [value_2] is the expected state of the Touch Sensor, so please type 'on' (Figure 3).

Figure 3 - The program can detect button presses

Task #3:

Insert data from the Color sensor into the table!

After that you created a database table and you can detect a Robot event, you should collect and store sensor data in your program. First please place a 'Use database [name]' block from the Database tab between the entry point and the forever block, where [name] is your database's connection name to define, which database would you like to use. Then create a variable named 'read' by clicking on the 'Make variable' button in the Variables tab, to store the sensor data. After that please place a 'set [variable] to [value]' block inside the 'if ...' block, where [variable] is ‘read’ from the list and [value] is the 'color sensor connection' block from the Lego tab. Finally please place the 'Insert into [table_name] ([attribute]) values [value]' block, where [table_name] is the target table in the database (Color_data), [attribute] is the column (Color) and [value] is the inserted new value (read) (Figure 4), so the code will be able to fill the Color_data table with Color -s.

Figure 4 - It stores the color values in the 'Color_data' table

Task #4:

Check the collected data from the SQL Console by using the 'SELECT' statement!

After that you finished your program which collects sensor data using Robot events, you should test how it works. If you start your program by pressing your selected key, you should see that the ‘read’ variable's value sometimes changes if you press the Touch Sensor. After that you should check that the measured data is stored in your database table. To check it, please open your database connection in your Control Panel and select the SQL tab. Then type 'SELECT * FROM Color_data' to get all stored data in this table (Figure 5).

Figure 5 - Query of all data from this table

Task #5:

Delete data from the created table with 'DELETE' SQL command!

After that you successfully collected sensor data using Robot events, finally you will learn how can you delete data from your database table. The SQL command to delete data is called 'DELETE FROM'. You have to start the command with this and after that you have to type the name of the table from where you would like delete data. If you execute this command all data will deleted from that table (Figure 6). If you type the 'WHERE' keyword and after that type a condition, executing it will delete only those records where the condition is met.

Figure 6 - 'DELETE' command in SQL Console

Program code

Figure 7 - The program code can fill a table with detected color values

It inserts the detected color values
Collects and inserts colors into a database from the robot using Robot events.
It is easier to follow the guide if you have the codes in front of you:
Download the SNAP code files