Writing machine activity to a SQL database using Arduino with Ethernet Shield

We had a request to keep track of the utilization of some of our CNC presses.  The managers want to be able to look back on a machine and see how often it was in use in a 24 hour period.  An Arduino Mega (although it didn't have to be the Mega, it could have been the Uno) with an Ethernet Shield was leveraged for the task.

The first step was to find out when the machine is active.  This particular machine has a pair of open contacts that close every time the foot pedal is pressed.  I hooked it up to one of my inputs on the Arduino and sent it 5 volts. Every time those contacts close, and the voltage goes high on my input, I add to my counter.  I then pass that information to SQL.  I also flash an LED on my Arduino housing to show that it recorded an event.

Here is the Arduino code:

#include <SPI.h>
#include <Ethernet.h>

#define e116Pin 42
#define e116LEDPin 47
#define e117LEDPin 48
#define e117Pin 30 // Echo Pin
#define e118LEDPin 49
#define e118Pin 36 // Echo Pin


byte mac[] = { 0x90, 0xA2, 0xDA, 0x0E, 0x03, 0xE1};
IPAddress ip(192,168,253,251);
byte server[] = { 192, 168,   33,  54 }; 
IPAddress myDns(192,168,33,40);
IPAddress gateway(192,168,33,45);
IPAddress subnet(255,255,0,0);
EthernetClient client;

int e116cnt = 0;  
int e116LastState = 1;
int e116CurrentState = 1;

int e117cnt = 0;  
int e117LastState = 1;
int e117CurrentState = 1;

int e118cnt = 0;  
int e118LastState = 1;
int e118CurrentState = 1;

unsigned long lastUpdate = millis();

void setup(){
 Serial.begin(9600); 

 pinMode(e116Pin, INPUT);
 pinMode(e116LEDPin, OUTPUT); 
 pinMode(e117Pin, INPUT);
 pinMode(e117LEDPin, OUTPUT); 
 pinMode(e118Pin, INPUT);
 pinMode(e118LEDPin, OUTPUT); 

 delay(1000);
 Ethernet.begin(mac, ip, myDns, gateway, subnet); 
}

void loop(){ 

 e116CurrentState = digitalRead(e116Pin);
 e117CurrentState = digitalRead(e117Pin);
 e118CurrentState = digitalRead(e118Pin);



  if (e116CurrentState == HIGH && e116LastState == LOW){
    e116cnt++;
    digitalWrite(e116LEDPin, HIGH);
    Serial.print("e116 Count: ");
    Serial.println(e116cnt);
  }
  else{ digitalWrite(e116LEDPin, LOW); }
  
  if (e117CurrentState == HIGH && e117LastState == LOW){
    e117cnt++;
    digitalWrite(e117LEDPin, HIGH);
    Serial.print("e117 Count: ");
    Serial.println(e117cnt);
  }
  else{ digitalWrite(e117LEDPin, LOW); }
  
  if (e118CurrentState == HIGH && e118LastState == LOW){
    e118cnt++;
    digitalWrite(e118LEDPin, HIGH);
    Serial.print("e118 Count: ");
    Serial.println(e118cnt);
  }
  else{ digitalWrite(e118LEDPin, LOW); }

  
    if (millis() - lastUpdate > 60000){ //reset after 60 seconds
    lastUpdate = millis();
    Serial.print("60 Sec Interval: ");
    Serial.println(millis());
    if (e116cnt > 0){
     UploadData(116, e116cnt);
     e116cnt = 0;
    }
    if (e117cnt > 0){
     UploadData(117, e117cnt);
     e117cnt = 0;
    }
    if (e118cnt > 0){
     UploadData(118, e118cnt);
     e118cnt = 0;
    }
   }
  
  e116LastState = e116CurrentState;
  e117LastState = e117CurrentState;
  e118LastState = e118CurrentState;
  
delay(10);
}

void UploadData(int equipNo, int count){
  client.connect(server , 80);
  
  if (client.connected()) {
    Serial.println("Connected to server -- writing"); 
    client.print("GET /log2sql.asp?no=");
    client.print(equipNo);
    client.print("&count=");
    client.print(count);
    client.print(" HTTP/1.1\n"); 
    client.print("Host:192.168.33.54 \n"); 
    client.print("Connection: close\n");     
    client.print("Content-Type: application/x-www-form-urlencoded\n");
    client.print("Content-Length: 0 \n\n" );
  }
    client.stop();
    client.flush();
}

I could have transmitted the information every time the count incremented, but that seemed like a lot of unnecessary network traffic.  Instead, I decided to save up all the pedal presses, and then transmit the number every 60 seconds.  The Arduino is not able to write directly to a SQL server, but it is able to write to a web page.  I created a classic ASP web page that accepts two variables, the machine number (I have multiple machines writing to the database), and the count of how many events happened in that 60 seconds).

My ASP Code:

<html>
<body>

<%
   Set cn = Server.CreateObject("ADODB.Connection")
   Set cmd = Server.CreateObject("ADODB.Command")
   cn.Open "Provider=SQLOLEDB; Data Source =sqlsrv08; Initial Catalog = equipmentStats; User Id = iusr_net; Password=12ab6HG7xf"
    
   set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_InsertLog"
   cmd.CommandType = 4
   cmd.Parameters.Refresh
   cmd.Parameters(1) = Request.QueryString("no")
   cmd.Parameters(2) = Request.QueryString("count")
   cmd.Execute
%>

</body>
</html>

When the ASP page receives that information, it connects to SQL through a stored procedure and writes the information to the database. When the information is written to the database, SQL also records the current date and time of the event.  Looking back at the data, I'm able to tell up to the minute what happened on each machine.

My SQL Stored Procedure:

ALTER PROCEDURE dbo.sp_InsertLog (
@equipNo int,
@count int
)
    
AS SET NOCOUNT ON 

    insert Log (EquipmentNo, [Count], [TimeStamp])
    values       (@equipNo, @count, GETDATE())

RETURN