Arduino Communications - SQLite - Revision history

Jvaughters: /* Data Slam Method */

2015-04-22T15:10:50Z

Data Slam Method

← Older revision		Revision as of 15:10, 22 April 2015
Line 142:		Line 142:
	Run the script with the following command		Run the script with the following command

−	./~~read_ardiuno~~.sh	+	./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).		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).

Jvaughters: /* Data Slam Method */

2012-01-27T13:44:47Z

Data Slam Method

← Older revision		Revision as of 13:44, 27 January 2012
Line 144:		Line 144:
	./read_ardiuno.sh		./read_ardiuno.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)~~. Now check if the SQLite database was created with the command~~.	+	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:'''		'''Sample Script Output Data:'''
Line 160:		Line 160:
	1327361513,2202,0,0,0,0,0,0,0,0,0,0,0,507,500,492,484,1023,1022		1327361513,2202,0,0,0,0,0,0,0,0,0,0,0,507,500,492,484,1023,1022
	</pre>		</pre>
		+
		+	Now check if the SQLite database was created with the command.

	ls -la arduino_data.db3		ls -la arduino_data.db3

Jvaughters: /* Linux */

2012-01-27T13:41:49Z

Linux

← Older revision		Revision as of 13:41, 27 January 2012
Line 16:		Line 16:

	==Linux==		==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.	+	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===		===Data Slam Method===
Line 76:		Line 76:
	Serial.print(analogRead(5),DEC);		Serial.print(analogRead(5),DEC);
	Serial.println("");		Serial.println("");
−	delay(45);	+	delay(45); //poll time setting can be changed
	}		}
	//***************************************************The End*********************		//***************************************************The End*********************

Jvaughters: /* Data Slam Method */

2012-01-26T15:53:05Z

Data Slam Method

← Older revision		Revision as of 15:53, 26 January 2012
Line 170:		Line 170:

	<pre>		<pre>
−		+	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
	</pre>		</pre>
−

	===Troubleshooting===		===Troubleshooting===

Jvaughters at 15:47, 26 January 2012

2012-01-26T15:47:26Z

← Older revision		Revision as of 15:47, 26 January 2012
Line 175:		Line 175:

	===Troubleshooting===		===Troubleshooting===
		+
		+	Sections not Completed
		+
	So you weren't lucky and you received errors somewhere in this process. Let's verify a few things first.		So you weren't lucky and you received errors somewhere in this process. Let's verify a few things first.

Line 231:		Line 234:

	sqlite3 arduino.db3 "select * from arduino_data"		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.
		+

	[[Category:Electronics]]		[[Category:Electronics]]

Jvaughters: /* Data Slam Method */

2012-01-26T15:46:11Z

Data Slam Method

← Older revision		Revision as of 15:46, 26 January 2012
Line 230:		Line 230:
	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.)		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'	+	sqlite3 arduino.db3 "select * from arduino_data"

	[[Category:Electronics]]		[[Category:Electronics]]

Jvaughters at 15:44, 26 January 2012

2012-01-26T15:44:39Z

← Older revision		Revision as of 15:44, 26 January 2012
Line 165:		Line 165:
	If you see the file, then run this command to see your data		If you see the file, then run this command to see your data

−	sqlite3 ~~arduino_data~~.db3 'select * from arduino_data'	+	sqlite3 arduino.db3 'select * from arduino_data'

	Sample SQLite Output Data:		Sample SQLite Output Data:
Line 230:		Line 230:
	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.)		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_data~~.db3 'select * from arduino_data'	+	sqlite3 arduino.db3 'select * from arduino_data'

	[[Category:Electronics]]		[[Category:Electronics]]

Jvaughters at 15:41, 26 January 2012

2012-01-26T15:41:56Z

← Older revision		Revision as of 15:41, 26 January 2012
Line 184:		Line 184:
	===Data Slam Method===		===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.		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.

	<source lang="TeraTerm">		<source lang="TeraTerm">
Line 215:		Line 217:
	endwhile		endwhile
	</source>		</source>
		+
		+	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_data.db3 'select * from arduino_data'

	[[Category:Electronics]]		[[Category:Electronics]]

Jvaughters at 14:58, 26 January 2012

2012-01-26T14:58:51Z

← Older revision		Revision as of 14:58, 26 January 2012
Line 146:		Line 146:
	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). Now check if the SQLite database was created with the command.		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). Now check if the SQLite database was created with the command.

−	~~ls -la arduino_data.db3~~	+	'''Sample Script Output Data:'''
−		+
−	~~If you see the file, then run this command to see your data~~	+
−		+
−	~~sqlite3 arduino_data.db3~~ '~~select * from arduino_data~~'	+
−		+
−	Sample Data:	+

	<pre>		<pre>
Line 166:		Line 160:
	1327361513,2202,0,0,0,0,0,0,0,0,0,0,0,507,500,492,484,1023,1022		1327361513,2202,0,0,0,0,0,0,0,0,0,0,0,507,500,492,484,1023,1022
	</pre>		</pre>
		+
		+	ls -la arduino_data.db3
		+
		+	If you see the file, then run this command to see your data
		+
		+	sqlite3 arduino_data.db3 'select * from arduino_data'
		+
		+	Sample SQLite Output Data:
		+
		+	<pre>
		+
		+	</pre>
		+

	===Troubleshooting===		===Troubleshooting===
Line 171:		Line 178:

	==Windows==		==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	+	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.
−	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.

−	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.~~	+	<source lang="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
		+	</source>

	[[Category:Electronics]]		[[Category:Electronics]]

Jvaughters at 23:34, 23 January 2012

2012-01-23T23:34:53Z

← Older revision		Revision as of 23:34, 23 January 2012
Line 22:		Line 22:

	<source lang="c">		<source lang="c">
		+	/*
		+	* 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);
		+	}
		+	//***************************************************The End*********************

	</source>		</source>
Line 28:		Line 85:

	<source lang="bash">		<source lang="bash">
−		+	#!/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
	</source>		</source>

Line 51:		Line 155:

	<pre>		<pre>
−		+	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
	</pre>		</pre>