Network Data Logging Suite

This suite of programs takes data from a sensor and saves it to a text file on a network. Each sensor reading is time stamped. The suite illustrates the basic principles involved in sending sensor data to a networked file or database.
The first program involved is a microcontroller program, written in PicBasic Pro, tested on a PIC18F258. It waits for serial input from an external program. Then it reads its analog sensor, and sends the result out in two bytes.
The second program is the same microcontroller code in Wiring/Arduino, thanks to Jamie Allen for the cleanup.
The third program involved is a desktop computer program, written in Processing. It requests data via its serial port from the microprocessor and sends that data to a CGI program on a web server. It passes the data into the CGI using an HTTP GET request. This program only sends every three seconds, so as not to overwhelm the server with hits.
The fourth program is a CGI (common gareway interface) program, written in PHP. It takes in data from an HTTP GET request and appends it to a text file, along with the time the request was received. Note that this program does not check to see how big the file is, or whether the incoming data is properly formatted, so it isn’t terribly secure.
The fifth program is another PHP script that logs the data to a mySQL database. Running this doesn’t require any change in the microcontroller code, but it does require a slight change in the Processing code. The change is in the sentToNet() method, and is noted below.

Technorati Tags: ,



Microcontroller code, written in PicBasic Pro, tested on a PIC18F258:

' call and response serial example for picBasic Pro.
' By Tom Igoe, 2003
' updated 25 Oct. 2005

' This example waits for a byte on the incoming serial connection,
' It then sends the value of a sensor on RA0, as a two-byte value.

' serial RX is on pin RC7
' serial TX is on pin RC6

 ' Define ADCIN parameters
DEFINE  ADC_BITS        10     ' Set number of bits in result
DEFINE  ADC_CLOCK       3         ' Set clock source (3=rc)
DEFINE  ADC_SAMPLEUS    50        ' Set sampling time in uS

' constant to set the baud rate:
inv9600 con 16468

' define variables:
adcVar var word
inByte var byte

TRISA = %11111111       ' Set PORTA to all input
ADCON1 = %10000010      ' Set PORTA analog and right justify result
 main:

  ' read sensors, convert to bytes:
      adcin 0, adcVar
 
  ' read serial data in:
  serin2 portc.7, inv9600, [inByte]
  
  ' if you got the message from the remote device, send out data:
    serout2 portc.6, inv9600, [adcVar.lowByte, adcVar.highByte]
goto main

Wiring Arduino code, written and tested on an Arduino and Arduino NG:


/*
Analog Call-and-Response for 10 bit sensor readings
by Tom Igoe

Created 26 Sept. 2005
Updated 30 May 2006
Cleaned up for use with the Network Data Logging Suite
by Jamie Allen, Feb 2007

Waits for serial input.  If the incoming value
is a valid byte (i.e. "A"), the program then
reads two analog inputs and one digital input.
It slices up the data into Most and Least Significant
BYTES and sends them out sequentially to the
serial port

Arduino hardware connections:
A0: analog sensor on analog in 0
*/

int firstSensor = 0;    // first analog sensor
int inByte = 0;         // incoming serial byte
byte firstSensorLSB;    //LSB for the analog sensor value
byte firstSensorMSB;    //MSB for the analog sensor value

void setup()
{
  // start serial port at 9600 bps:
  Serial.begin(9600);
}

void loop()
{
  // if we get a valid byte, read analog ins:
  if (Serial.available() > 0) {
    // get incoming byte:
    inByte = Serial.read();

    if (inByte == 65)  //i.e.: it's an ASCII "A"
    {
    firstSensor = analogRead(0);
    firstSensorLSB = firstSensor;
    firstSensorMSB = firstSensor >> 8;

    Serial.print(firstSensorLSB, BYTE);
    Serial.print(firstSensorMSB, BYTE);
    }
  }
}

Desktop computer code, written in Processing, tested on mac OSX:

/* datalogging client
 by Tom Igoe
 
  Communicates between a microcontroller reading a 10-bit analog sensor and
  a CGI script that timestamps the reading and writes it to a file.
  
  The program starts by sending to the sensor for an initial value.
  When a good sensor value is obtained, the program checks to see if there's 
  a net send in progress.  If there's not, it sends the sensor reading to the net
  by starting a network client that connects to a server on port 80,
  sends an HTTP 1.1 GET request, and prints the results. 
   
  Once the sensor request is done, the client waits 3 seconds before the next send
  so as not to overwhelm the server.

 created 18 March 2005
 updated 24 Oct. 2005
 */
 
import processing.net.*;
import processing.serial.*;

Serial port;                            // The serial port
Client client;                          // the net client
int[] serialInArray = new int[2];       // Where we'll put what we receive
int serialCount = 0;                    // A count of how many bytes we receive
int sensorValue = 0;                    // value of the sensor
boolean firstContact = false;           // whether we've heard from the microcontroller
boolean netSendInProgress = false;      // whether or not the last net request is finished
boolean newSensorData = false;          // whether or not we have new sensor data



void setup()
{
  size(200, 200);
  // Print a list of the serial ports, for debugging purposes:
  println(Serial.list());

  // I know that the first port in the serial list on my mac
  // is always my  Keyspan adaptor, so I open Serial.list()[0].
  // On Windows machines, this generally opens COM1.
  // Open whatever port is the one you're using.
  port = new Serial(this, Serial.list()[0], 9600);
  port.write(65);    // Send a capital A to start the microcontroller sending
}

void draw()
{
  background(0);

// if there's any serial data available, get it:
  if (port.available() > 0) {
    serialEvent();
    // Note that we heard from the microntroller at least once:
    firstContact = true;
  }
  // If there's no serial data, send again until we get some incoming data.
  // (in case you tend to start Processing before you start your 
  // external device):
  if (firstContact == false) {
    delay(300);
    port.write(65);
  }

  // if we have new sensor data, check to see that there's no open
  // net connections. If there aren't, send the data.
  if (newSensorData) {
    if (!netSendInProgress) {
      sendToNet(sensorValue);
    }
  }


  // print the results of the net send:
  if (netSendInProgress) {
    if (client.available() > 0) {
      int inByte = client.read();
      print((char)inByte);
      // when we get a byte of value 0, it's the end of the response
      // from the server.  Stop listening and get some more data:
      if (inByte == 0) {
      netSendInProgress = false;
      // don't overwhelm the server:
      delay(3000);
      // Send a capital A to request new sensor readings:
      port.write(65);
      }
    }
  }
}


void serialEvent() {
  // Add the latest byte from the serial port to array:
  serialInArray[serialCount] = port.read();
  serialCount++;
  // If we have 2 bytes, combine them into one value:
  if (serialCount > 1 ) {
    sensorValue = serialInArray[1] * 256 + serialInArray[0];
    newSensorData = true;
    
    // Reset serialCount:
    serialCount = 0;
  } 
  else {
  // if we have only one byte, don't let the main loop 
  // send out yet:
    newSensorData = false;
  }
}

void sendToNet(int sensorValue) {
  // open a TCP socket to the host:
  client = new Client(this, "myserver.com", 80);

  //print the IP address of the host:
  println(client.ip());
  // send the HTTP GET request:
  client.write("GET /~someaccount/logger.php?tempValue=" + sensorValue + " HTTP/1.1\n");
  client.write("HOST: myserver.com\n\n");
  netSendInProgress = true;
}

CGI program, written in PHP, tested using an Apache web server running on a Redhat Linux machine:

<?php
$filename = 'datalog.txt';
// compose the data string from the date and the incoming value:
  $dataString = date("Y-m-d h:i:s\t");
  $dataString = $dataString.$_REQUEST['tempValue'];
  // add a linefeed and carriage return
  $dataString = $dataString."\r\n";

//  make sure the file exists and is writable first:
if (is_writable($filename)) {

   // Open $filename in append mode. anything you add 
   // will be appended to the end of the file:
   if (!$handle = fopen($filename, 'a')) {
         echo "Can't open file $filename";
         exit;
   }

   // Write $dataString to the opened file.
   if (fwrite($handle, $dataString) == FALSE) {
       echo "Can't write to file $filename";
       exit;
   }
  // data successfully written:
   echo "Wrote $dataString to file $filename";
   // send a 0 to tell the remote side we're done:
  echo "\0";
  // close the file
   fclose($handle);

} else {
   echo "The file $filename is not write-enabled.";
}
end;
?>

This PHP script saves the sensor value to a mySQL database and timestamps it. It requires a change to the Processing code, which follows after the script:

<?php
// get username & pwd info:
include "secret.php";

// initialize variables:
$sensorValue = -1;        // value from the sensor
$date = -1;                    // date string: YYYY-MM-DD
$time = -1;                    // time string: HH:mm:ss in 24-hour clock
$recordNumber = -1;     // which record to delete
$list = 0;                        // whether or not to list results in HTML format
$databaseName = 'mydatabaseName';
$tableName = 'myTableName';

// open the database:
$link = open_database('localhost', $databaseName, $username, $password);

// process all the HTTP Request variables:
foreach ($_REQUEST as $key => $value)
{ 
    // action is the SQL action: insert, delete, select, etc.
    if ($key == "action") {
        $action = $value;
    }
    // sensorValue is the result from the remote sensor system
    if ($key == "sensorValue") {
        $sensorValue = $value;
    }
    // date that the sensor reading was taken
    if ($key == "date") {
        $date = $value;    
    }
    // time that the sensor reading was taken
    if ($key == "time") {    
        $time = $value;
    }
    // database record number (for deleting only):
    if ($key == "recNum") {    
        $recordNumber = $value;
    }
    // whether or not to print out results in HTML:
    if ($key == "list") {    
        $list = $value;
    }

}

// insert a new record in the database:
if ($action == "insert") {
    // make sure date and time have values:
    if ($date == -1 || $time == -1) {
        // if not values, generate them from the server time
        // (I should probably properly check for valid date and time strings here):
        list($date, $time) = split(" ", date("Y-m-d H:i:s"));
    }

    // Only insert if we got a sensor value from the GET:
    if (sensorValue != -1) {
        insert_record($tableName, $sensorValue, $date, $time);
    }
}

// if we're supposed to delete, delete:
if ($action == "delete") {
    // only delete if we got a record number from the GET:
    if ($recordNumber != -1) {
        delete_record($tableName, $recordNumber);
    }
}

// if we should list in HTML format, list the whole table:
if ($list == 1) {
    echo "<html><head></head><body>";
    // browse the whole table:
    browse_table($tableName);
    echo "</body></html>";
}

// close the database:
close_database($link);

// end with a 0 to close the session to the client:
echo "\0";
end;

//    Functions    -------------------------------

// Connect to a server and open a database:
function open_database($myServer, $myDatabase, $myUser, $myPwd) {
    $myLink = mysql_connect($myServer, $myUser, $myPwd)
       or die('Could not connect: ' . mysql_error());
    if ($list == 1) {
        echo 'Connected successfully';
    }
    mysql_select_db($myDatabase) or die('Could not select database');
    return $myLink;
}

// close an open database:
function close_database($myLink) {
    mysql_close($myLink);
}

// select all from a table:
function browse_table($myTable) {
    $query = "SELECT * FROM `$myTable`";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    // Printing results in HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
           foreach ($line as $col_value) {
               echo "\t\t<td>$col_value</td>\n";
           }
           echo "\t</tr>\n";
    }
    echo "</table>\n";
    // Free resultset
    mysql_free_result($result);
}

// insert a new record in the table:
function insert_record($myTable, $recValue, $recDate, $recTime) {
    $query = "INSERT INTO `$myTable` (`Value`, `Date`, `Timestamp`) VALUES ('$recValue', '$recDate','$recTime')";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // Free resultset
    mysql_free_result($result);

}

// delete a record from the table:
function delete_record($myTable, $recNum) {
    $query = "DELETE FROM `$myTable` WHERE `ID` = $recNum  LIMIT 1";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // Free resultset
    mysql_free_result($result);
}

?>

Here’s the modified sendToNet() method for the Processing code above, to work with the mySQL PHP script:

void sendToNet(int sensorValue) {
  // open a TCP socket to the host:
  client = new Client(this, "itp.nyu.edu", 80);

  //print the IP address of the host:
  println(client.ip());
  // send the HTTP GET request:
  String sensorString = Integer.toString(sensorValue);
  client.write("GET /~myAccount/sql_datalog.php?action=insert&sensorValue=" + sensorValue + " HTTP/1.1\n");
  client.write("HOST: myserver.com\n\n");
  netSendInProgress = true;
}
This entry was posted in arduino/wiring, PHP, PicBasic Pro, Processing. Bookmark the permalink.

Comments are closed.