MYSQL Examples

Installing MySQL

MySQL can be installed in most modern OS (Windows, Linux, MacOS) please check the installation instructions here: https://dev.mysql.com/doc/refman/5.7/en/installing.html

Setting up remote access

MySQL is installed with restricted access from other machines, to open access one must follow these instructions: https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/

Working with the database

To work with the database MySQL installs also an SQL client that allows connecting to the database. In windows you can find it under the name MySQL 5.7 - Command line client if you search it in the start menu. In linux the client can be accessed in the terminal under the command "mysql -h <host_ip> -u <user> -p<password>"

Database creation

Execute

CREATE DATABASE advannet;

Table creation

A good example of where store the data gathered from the reader is two tables, one for inventory data and the other for events. In this case execute these two instructions in the mysql client to create two new, empty tables:

CREATE TABLE IF NOT EXISTS advannet.inventory (id bigint(20) NOT NULL AUTO_INCREMENT,ip varchar(128) DEFAULT NULL,data_source varchar(128) DEFAULT NULL,epc_hex varchar(128) DEFAULT NULL,epc_decoded varchar(128) DEFAULT NULL,epc_serial bigint(20) DEFAULT 0,time bigint DEFAULT 0,date_time timestamp NOT NULL,reader_port int(2) DEFAULT NULL,mux1_port int(2) DEFAULT NULL,mux2_port int(2) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS advannet.events (id bigint(20) NOT NULL AUTO_INCREMENT, ip varchar(128) DEFAULT NULL, data_source varchar(128) DEFAULT NULL, epc_hex varchar(128) DEFAULT NULL, epc_decoded varchar(128) DEFAULT NULL, epc_serial bigint(20) DEFAULT 0, time bigint DEFAULT 0, event varchar(128) DEFAULT NULL, event_param1 varchar(128) DEFAULT NULL, event_param2 varchar(128) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Data insertion

Data insertion in a relational database like MySQL is done by executing INSERT queries. Here there are two examples:

Below you can find examples.

Example of configuration of the SQL Service

Using the examples provided in this page a working configuration would be:

Example with native query.