Spinneret as datalogger, htm accessible remotely?

T Chap

I am exploring using the Spinneret in an application to store some data at a remote location, but I have little info on what will be allowed as far as access to the LAN and router. Access to modify settings in the router may not be possible. I am assuming it will be easy to get the Spinneret connected to the router( maybe using a wifi go between module). The idea is to send to the SD card a time stamp, opcode for user inputs to the main board, opcodes for error conditions. Then when there is a need to review the log, instead of having to pull out the SD card, or scroll through an LCD looking at the list, just view it online. Ideally, viewing online from anywhere. But, viewing online from a locally connected computer would be second best. If it is a dynamic IP, what other options exist to view remotely from anywhere? I was considering uploading each new data event to a mysql database which the Spinneret should be able to do, then view the database instead. Any thoughts would be appreciated.

homosapien

I've grappled with this problem also (dynamic IP address). I think your idea of uploading to a database is a good idea and probably pretty bulletproof. Another possibility is to use email to send data, however obviously this causes some problems in ease of handling the data.

Another idea I have not yet tried, is to have the Spinneret do web activity or something so the IP lease is extended indefinitely. Not sure if this is allowed, and there would always be situations I'm sure where the lease lapses and you have to somehow find out what the new IP is (have Spinneret check IP address periodically, send new address via email if it changes?)


Nate

max72

The easy solution is having the router handling the IP and port forwarding. Some recent routers allow to configure a dyndns service. You require the right router and the will from the router owner.
I used the mysql approach in the past. In this case the spinenret received data via SMS, and sent the data to a remote server.
In the spinneret help there is a tutorial about using the get method to send data to a page.
The landing page would take care of it. In case I can dig my code and post it.

Mike G

This question comes up often. Folks want to log and view data but network technology (or lack of technology) makes the solution tricky. My answer is always the same. Forget the SD card and send log data, real-time, to a solution stack like LAMP (Linus, Apache, mySQL, PHP) or MS .NET. This does two things: 1) Port forwarding is not required when the Spinneret establishes a connection to a remote host. 2) The solution is extensible and can be moved from network to network with little to no configuration.

The down side is added complexity and a bit of PHP or .NET coding but very simple stuff.

Peter Jakacki

T Chap wrote: »

I am exploring using the Spinneret in an application to store some data at a remote location, but I have little info on what will be allowed as far as access to the LAN and router. Access to modify settings in the router may not be possible. I am assuming it will be easy to get the Spinneret connected to the router( maybe using a wifi go between module). The idea is to send to the SD card a time stamp, opcode for user inputs to the main board, opcodes for error conditions. Then when there is a need to review the log, instead of having to pull out the SD card, or scroll through an LCD looking at the list, just view it online. Ideally, viewing online from anywhere. But, viewing online from a locally connected computer would be second best. If it is a dynamic IP, what other options exist to view remotely from anywhere? I was considering uploading each new data event to a mysql database which the Spinneret should be able to do, then view the database instead. Any thoughts would be appreciated.

I saw your post first up but just waiting to see what suggestions you end up with. I am already doing this datalogging and internet access with my Tachyon Forth on a W5200 although I had originally experimented with the W5100. Basically the files are accessed via FTP and you can configure FTP as a virtual drive/path from your client. Web access is available for status and maybe configuration although the TELNET port can access all functions.

Mike G

Basically the files are accessed via FTP and you can configure FTP as a virtual drive/path from your client.

That works if the FTP server IP and router IP never change or you have a domain controller and DNS setup locally. Unless, I'm not understanding something with the setup.

Peter Jakacki

Mike G wrote: »

That works if the FTP server IP and router IP never change or you have a domain controller and DNS setup locally. Unless, I'm not understanding something with the setup.

First off, my previous post was to to confirm:
1. Yes, the Spinneret can be used in this way.
2. There are other protocols such as FTP or TELNET better suited for data collection but HTTP is available.

Now that we have established that we have hardware and software capabilities then routing is infrastructure dependant and really is simply a matter of implementation which is addressed by gaming and SIP networks for instance. Having a known fixed IP which may even be a third party matchmaking system is a start. So many different ways of handling this and so many different systems. The logger would obviously have to identify itself (MAC) and in the process reveal it's WAN IP and may even rely on UDP as well etc. I have the luxury of fixed IP installations globally so I don't really have to worry about this.

T Chap

Thanks for the suggestions everyone. I am leaning towards an idea I have used before, to let the Prop send Get with the needed values to a php page I have parked on a server. The php code makes posting to a mysql very simple(php is just relaying the values to the database). The idea of the php gobetween is that it allows easy change to the php code and database without requiring access to the Prop. The other suggestions seem like a bit more learning curve. I will post a version of the concept in the next few days and get your opinion. Dydns has worked before to get and forward the ip, but since port forwarding maybe be off limits, the path probably will stay uni directional.

Mike G

I am leaning towards an idea I have used before, to let the Prop send Get with the needed values to a php page I have parked on a server. The php code makes posting to a mysql very simple(php is just relaying the values to the database). The idea of the php gobetween is that it allows easy change to the php code and database without requiring access to the Prop.

That's exactly what I was trying to suggest in post #4.

Anyway, the client side GET gets around firewall and networky stuff. Add DHCP and client DNS to make the system a bit more dynamic.

Rforbes

Http POST is doable too and more data can be sent with it than GET. Using php pdo is pretty straight forward for getting the data into the database.For the FTP idea I think using EPSV (extended passive) helps with the dynamic ip problems?

T Chap

This is something I did a few years back using a wifly to write to the same location in as mysql db. This allowed me to load a php page at any time to view some parameters including the most recent time stamp. The new idea is to translate the wifly to the Spinneret, and change to a method on the db for appending. In this example, the address of the server holding the php page, the db username, password, and various values are sent to a remotely parked php page that does the work of updating the database.

Spin for wifly output:

    ser.str(1, string("**Write DB All**"))   'display on port 1
    ser.tx(1, CR)
    ser.str(2, string("open xxxxxxxxx.com 80"))  'wifly command
    ser.tx(2, CR) 
    ser.str(2, string("GET /writedb.php?"))
    ser.str(2, string("TABLE=table1"))   'DB table name
    ser.str(2, string("&ID=1"))          'system ID
    ser.str(2, string("&SERVER=xxxx.db.xxxxx.website.com"))    '' DB server
    ser.str(2, string("&DBU=myusername"))      ''DB username
    ser.str(2, string("&DBP=mypassword"))      ''DB password
    ''/// send all status parameters to mysql database
    ser.str(2, string("&REMIP="))     ''router IP 
    ser.str(2, @IP)                   'send the complete IP in one string from array
    ser.str(2, string("&TIMER="))     ''router IP (dynamic)
    ser.str(2, timer.showTimer)       '' insert the running timer value here
    ser.str(2, string("&C1="))
    ser.hex(2, C1S,1)
    ser.str(2, string("&C2="))
    ser.hex(2, C2S,1)
    ser.str(2, string("&C3="))
    ser.hex(2, C3S,1)
    ser.str(2, string("&C4="))
    ser.hex(2, C4S,1)

    ser.str(2, string("&CM1="))
    ser.hex(2, CM1S,1)
    ser.str(2, string("&CM2="))
    ser.hex(2, CM2S,1)
    ser.str(2, string("&CM3="))
    ser.hex(2, CM3S,1)
    ser.str(2, string("&CM4="))
    ser.hex(2, CM4S,1)

    ser.str(2, string("&DVS="))
    ser.hex(2, DVS,1)

    ser.str(2, string("&MIFI=")) 
    ser.hex(2, MIFIselect, 1)  

    ser.str(2, string(" HTTP/1.1"))   'http GET terminator
    ser.str(2, string("Host: xxxxxxxx.com"))
 
    ser.str(1, string("**Write DB DONE**"))  'display done
    ser.tx(1, CR)

php page

  <title>Write DB</title>
</head>
<body>
<br>
<?php $con = mysql_connect($_REQUEST[SERVER], $_REQUEST[DBU],$_REQUEST[DBP]);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

if ($con)
{
echo "WRITING DB.....     ";
}

mysql_select_db($_REQUEST[DBU], $con);
mysql_query("UPDATE table1 SET REMIP='$_REQUEST[REMIP]'
WHERE ID='$_REQUEST[ID]' ");
mysql_query("UPDATE table1 SET TIMER='$_REQUEST[TIMER]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET C1='$_REQUEST[C1]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET C2='$_REQUEST[C2]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET C3='$_REQUEST[C3]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET C4='$_REQUEST[C4]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET CM1='$_REQUEST[CM1]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET CM2='$_REQUEST[CM2]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET CM3='$_REQUEST[CM3]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET CM4='$_REQUEST[CM4]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET DVS='$_REQUEST[DVS]'
WHERE ID='$_REQUEST[ID]'");
mysql_query("UPDATE table1 SET MIFI='$_REQUEST[MIFI]'
WHERE ID='$_REQUEST[ID]'");


echo "DB WRITE DONE"   .  mysql_error();
mysql_close($con);

?>
<br>
<br>
</body>
</html>

Here is a screenshot of an html page that allows very easy updating of a database row of values. The Prop has a method that reads the database at some frequency, in which you park some values for it to read. For example, if you want to update the time, fill out the html page with the correct values, then submit to the database. The Prop will read it when it you have it set to do so, and it will parse the data for the Command(opcode for update time), then overwrite the time with your value. Obviously the seconds could be off, but the minutes will be close if you have the Prop reading once per minute. Other commands can be set in the Opcode field for the Prop to read at x frequency. Then once the command is done, the Prop will update the database with a flag value that can be read from a simple html page later to find out when and if the Prop got the command. This makes an indirect method to control the Prop without having access to the router ssid/pw, port forwarding etc( assuming you are using an ethernet cable). Hopefully these old wifly methods will adapt easily to the Spinneret, as the goal is to store data to the SD card, update to a database simultaneously, but have the option to "Upload All SD to DB", upload any particular line of a text file to DB, etc. Then, if the server or router is down, the data still goes to the SD card.