Arduino Communications - SQLite

Latest revision as of 15:10, 22 April 2015

Welcome to Combustory

Any questions or comments:

• Send them to - combustor@combustory.com

Overview

When writing code these days it is silly to not use a database to manage your data. It significantly cuts the amount of code needed to manage data. So why not just collect the Arduino output directly into a database and allow any software access to the database?

This article shows you how to use a database to collect Arduino data directly from the serial port and place the data into an SQLite database using scripts. This article will provide examples for Linux and Windows.

Technical Assumptions

• Users are familiar with programming the Arduino
• Users know how to install and use SQLite (Most Linux computers have it by default)
• It would be helpful to be knowledgeable about bash scripting

Linux

Ok, well I am starting with Linux. Why? Well.... Linux is smooth! It treats all devices like files. Meaning once you know how to open a file in Linux, you basically know how to access any device. How great is that? Someone was thinking when they created that idea. The operating system drivers are made to make this possible. So in the case of a serial port all you have to do is an stty command to set the transmission rates and the communications parameters and BOOM... like magic a serial port can be treated like a file. This allows you to use common commands that are used on files on a serial port. For instance, you can use echo to send commands to the serial device, just as you would use it to send to a file.

Data Slam Method

The goal here is to push out all the data of the Arduino as fast as we can and load it into the database. To do that use this Arduino Sketch:

/*
 * Data Slam v.01
 * by <http://www.combustory.com> John Vaughters
*/
#include "Wire.h"
// Global Variables
unsigned int diff_mil_time;
long last_mil_time;
 
void setup() {
  Wire.begin();
  Serial.begin(115200);
}
 
void loop() {
//  diff_mil_time = (int) (millis() - last_mil_time);
  diff_mil_time = (unsigned int) (millis() - 1 +1);
//  last_mil_time = millis();
  Serial.print(diff_mil_time);
  Serial.print(",");
  Serial.print(digitalRead(2),DEC);
  Serial.print(",");
  Serial.print(digitalRead(3),DEC);
  Serial.print(",");
  Serial.print(digitalRead(4),DEC);
  Serial.print(",");
  Serial.print(digitalRead(5),DEC);
  Serial.print(",");
  Serial.print(digitalRead(6),DEC);
  Serial.print(",");
  Serial.print(digitalRead(7),DEC);
  Serial.print(",");
  Serial.print(digitalRead(8),DEC);
  Serial.print(",");
  Serial.print(digitalRead(9),DEC);
  Serial.print(",");
  Serial.print(digitalRead(10),DEC);
  Serial.print(",");
  Serial.print(digitalRead(11),DEC);
  Serial.print(",");
  Serial.print(digitalRead(12),DEC);
  Serial.print(",");
  Serial.print(analogRead(0),DEC);
  Serial.print(",");
  Serial.print(analogRead(1),DEC);
  Serial.print(",");
  Serial.print(analogRead(2),DEC);
  Serial.print(",");
  Serial.print(analogRead(3),DEC);
  Serial.print(",");
  Serial.print(analogRead(4),DEC);
  Serial.print(",");
  Serial.print(analogRead(5),DEC);
  Serial.println("");
  delay(45);                   //poll time setting can be changed
}
//*****************************************************The End***********************

Once the Arduino sketch is loaded, then use the code below to create a BASH script with a file named "read_arduino.sh".

#!/bin/bash
# Data Slam Script v0.1
# User define Function (UDF)
LogLine(){
  echo -E "`date +%s`,${line}"
 sqlite3 arduino.db3 "insert into arduino_data(time_stamp,millis,di2,di3,di4,di5,di6,di7,di8,di9,di10,di11,di12,ai1,ai2,ai3,ai4,ai5,ai6) values (`date +%s`,${line})"
} 
### Main script stars here ###
# Store file name
FILE=""
 
# Make sure we get file name as command line argument
# Else read it from standard input device
stty -F /dev/ttyUSB0 cs8 115200 ignbrk -brkint -icrnl -imaxbel -opost -onlcr -isig -icanon -iexten -echo -echoe -echok -echoctl -echoke noflsh -ixon -crtscts
if [ "$1" == "" ]; then
   FILE="/dev/ttyUSB0"
else
   FILE="$1"
   # make sure file (serial device) exist and is readable
   if [ ! -f $FILE ]; then
  	echo "$FILE : does not exists"
  	exit 1
   elif [ ! -r $FILE ]; then
  	echo "$FILE: can not read"
  	exit 2
   fi
fi
# Create Database if it does not exist
 
if [ ! -f "arduino.db3" ]; then
  	echo "Creating database"
        sqlite3 arduino.db3 "CREATE TABLE arduino_data (time_stamp integer ,millis integer, di2 integer, di3 integer, di4 integer, di5 integer, di6 integer,
                          di7 integer, di8 integer, di9 integer, di10 integer, di11 integer, di12 integer,
                          ai1 integer, ai2 integer, ai3 integer, ai4 integer, ai5 integer, ai6 integer);"
fi
exec 3<&0
exec 0<"$FILE"
while true
do
 
	# use $line variable to process line in processLine() function
	while read -r line
        do
              LogLine $line
        done
       usleep 50000 # This delay can be changed to match the delay of the Arduino
done
exec 0<&3
exit 0

Now make the file executable with the following command.

chmod 755 read_arduino.sh

Run the script with the following command

./read_arduino.sh

At this point, if you are lucky, you are seeing data separated by commas spit out on your screen. This is the data being loaded into SQLite. To exit the script, hit ctrl-c (^c).

Sample Script Output Data:

1327361512,1752,0,0,0,0,0,0,0,0,0,0,0,506,499,491,483,1023,1023
1327361512,1802,0,0,0,0,0,0,0,0,0,0,0,506,500,491,482,1023,1023
1327361512,1852,0,0,0,0,0,0,0,0,0,0,0,507,500,490,482,1023,1023
1327361512,1901,0,0,0,0,0,0,0,0,0,0,0,507,501,491,482,1023,1023
1327361513,1951,0,0,0,0,0,0,0,0,0,0,0,507,499,491,482,1023,1023
1327361513,2001,0,0,0,0,0,0,0,0,0,0,0,507,501,491,483,1023,1023
1327361513,2052,0,0,0,0,0,0,0,0,0,0,0,506,499,492,484,1023,1023
1327361513,2102,0,0,0,0,0,0,0,0,0,0,0,506,500,492,484,1023,1023
1327361513,2152,0,0,0,0,0,0,0,0,0,0,0,507,500,491,483,1023,1023
1327361513,2202,0,0,0,0,0,0,0,0,0,0,0,507,500,492,484,1023,1022

Now check if the SQLite database was created with the command.

ls -la arduino_data.db3

If you see the file, then run this command to see your data

sqlite3 arduino.db3 'select * from arduino_data'

Sample SQLite Output Data:

1327361513|2001|0|0|0|0|0|0|0|0|0|0|0|507|501|491|483|1023|1023
1327361513|2052|0|0|0|0|0|0|0|0|0|0|0|506|499|492|484|1023|1023
1327361513|2102|0|0|0|0|0|0|0|0|0|0|0|506|500|492|484|1023|1023
1327361513|2152|0|0|0|0|0|0|0|0|0|0|0|507|500|491|483|1023|1023
1327361513|2202|0|0|0|0|0|0|0|0|0|0|0|507|500|492|484|1023|1022

Troubleshooting

Sections not Completed

So you weren't lucky and you received errors somewhere in this process. Let's verify a few things first.

Windows

If you jumped straight to this section, I recommend that you read the Linux section and understand the overall concept. The problem with Windows is that the communication with serial ports is not as easy. The only way to communicate through software that you write is to have a license to the software objects that provide communication to serial ports. Well.... we cannot have that, so we will have to resort to an open source solution.

TeraTerm Pro is a terminal emulator that can communicate with serial ports as well as telnet and ssh. It also has a very rich macro language for automating terminal sessions that we will use to talk with the Arduino. There are many great applications for this software, like using it to control or configure devices that are accessed via ssh or telnet. So if you are new to TeraTerm Pro, it should significantly improve your toolbox of software utilities.

Data Slam Method

The goal here is to push out all the data of the Arduino as fast as we can and load it into the database. To do that use the same Arduino Sketch found in the Linux section of this article.

Create a text file named read_arduino.ttl with the code below. Place it in the c:\Program Files\teraterm directory. If this directory does not exist, then you need to install TeraTerm.

;Read Arduino Macro v.01
;by <http://combustory.com> John Vaughters
filesearch 'arduino.db3' ;Check if database exists and create if it doesn't 
if result=0 then
   fileopen fhandle 'create_db.bat' 0 ;The create table string was too long for script strings so I made a bat file and exec the bat file
   filewrite fhandle 'sqlite3 arduino.db3 "CREATE TABLE arduino_data (time_stamp char, millis integer, di2 integer, di3 integer, di4 integer,'
   filewrite fhandle ' di5 integer, di6 integer, di7 integer, di8 integer, di9 integer, di10 integer, di11 integer, di12 integer, ai1 integer,'
   filewrite fhandle 'ai2 integer, ai3 integer, ai4 integer, ai5 integer, ai6 integer)"'
   fileclose fhandle
   exec 'create_db.bat' "HIDE" 1
   filedelete 'create_db.bat'
endif
;connect '/V /C=18' ;Com Port 18 - Start with hidden terminal
connect '/C=18'     ;Com Port 18 - Start with visible terminal
setbaud 115200
setsync 1
while 1 ; endless loop to read Arduino
  ; receive one line
  recvln
   ; build SQLite command string
   command = 'sqlite3 arduino.db3 "insert into arduino_data(time_stamp,millis,di2,di3,di4,di5,di6,di7,di8,di9,di10,di11,di12,ai1,ai2,ai3,ai4,ai5,ai6) values ('
   gettime time_status "%Y%m%d%H%M%S,"
   strconcat command time_status
   strconcat command inputstr ;inputstr is the arduino output from recvln 
   strconcat command ');"'
   ; write it to SQLite
   exec command "HIDE"
endwhile

Change this line to match your com port. My com port was 18, so just change the 18 to match your number. (Hint: Use Arduino programming environment to find the Arduino com port ie. Menu Item: Tools>Serial Port)

connect '/C=18'

Make sure you have the sqlite3.exe and sqlite.dll in the c:\Program Files\teraterm directory. These are the SQLite windows files needed for SQLite to work. It really is that simple with SQLite and Windows. You only need those two files. I normally have a path to those files, but they are small enough that I put them in the directory for applications that I create for others, to make sure the applications work.

Use this command to run the Macro. (Create a batch file to reduce typing)

"C:\Program Files\teraterm\ttpmacro.exe" read_arduino.ttl

If you are lucky, you will see output coming across a TeraTerm console. To stop the data, just delete the console window and end the macro via the pop up boxes. Now check if you have a file named arduino.db3 in the c:\Program Files\teraterm directory. If so run this command to see the data in SQLite. (Make sure you are in the c:\Program Files\teraterm directory when running this command.)

sqlite3 arduino.db3 "select * from arduino_data" 

Troubleshooting

Sections not Completed

So you weren't lucky and you received errors somewhere in this process. Let's verify a few things first.