Do-More to MySQL Server

[Bolt]

Hey Bolt,

Just a friendly word of caution.  I'd consider changing your insert from your concatenated/interpolated string to using a prepared statement.  It may seem a bit tedious at first but is a good habit to get into as it will help prevent SQL injection.  When you get into inserting strings it also helps by handling escape codes for quotes and things like that.  The following link provides some details.  Where this becomes useful is when inserting batches of records.  The SQL server doesn't have to redo the query plan for each insert.

https://www.w3schools.com/php/php_mysql_prepared_statements.asp

Once I get all this working simply, I will go back and clean up things like that. I like that website, I have learned a lot lot there the last few days.

[plcnut]

Try leaving out the HTTP/1.0 and just do a single $0D%0A immediately after the query string.

Still no luck. Bad request. You do mean $0D$0A right?

Yes, I corrected my post.

I wish I had a way to test from here. I have worked with WAMP in the past, and don't remember any issues like this.

I will be thinking on it.

[plcnut]

I did some testing this morning using Fiddler from here: https://www.telerik.com/fiddler

Using this simple debugger, you can see what the request and response headers actually look like (Even in Hex).

I was using Do-more to open this thread on forum.hosteng using this request:

Code: [Select]

GET http://50.240.102.59/index.php/topic,2214.0.html HTTP/1.1$0D$0AUser-Agent: DoMore/2.1$0D$0AHost: forum.hosteng.com$0D$0A$0D$0A

Using Fiddler, you will find that each line of the request header is separated by a CRLF, and that the header is terminated by a double CRLF.
I am guessing that your server is looking for something in the request header that Do-more is not providing. Maybe all you need to do is add is User-Agent, or maybe the Host.
Either way, I would suggest that you use Fiddler, and enter your request string into the "Composer" tab, and then run the query. You can then click on the response shown in the left pane and view the RAW or the Hex of the Request as well as the Response, and find what makes the server happy.

[Bolt]

Thank you for your continued help in my projects.

I have the GET command working from the PLC.  It is back to the first statement, GET http://192.168.0.10/post.php?TimeStamp=1515510728&T0Temp=63.33&P0Temp=147.4<CR><LF>

I'm not sure what changed now, I am pretty sure I tried all these same iterations yesterday.  Even after I took the / out infront of the ? My last attempts last night were via remote PC access on my phone, so I might have missed something there.

I can't get a response to the PLC from the server.  After sending, I jump to a Response Timer.   When .InQueue != 0, it jumps to a STREAMIN Stage, and that STREAMIN command Jumps to my Error Stage on Fail every time (and leaves the string empty).  Is there something I can do for the server to keep connection open longer, or what is causing the data in the queue to "disappear"?

[plcnut]

On your STREAMIN, change the 'Length' to '$MariaDB.InQueue'.

[Bolt]

Thanks.  Made all the difference.

[plcnut]

Cool!
It is pretty grand when you get to watch your code do it's thing!

[Bolt]

How would one go about logging edge triggered boolean data to MySQL?  It seems silly to log continuously while bit is ON or OFF.  But triggering it with a delta contact would get me a timestamp at bit ON, and a timestamp at bit OFF.  How could I digest this in MySQL?  I would like to display it graphically, like trend view does.  I've googled it but am coming up empty on how to deal with the ON/OFF transition.  In my database I would need to log an OFF bit right before before the transition to ON, and log an ON bit right before it turns OFF.  Am I looking at this wrong?

I've got it working really well.  I have built a website (on local server) to pull up all the data, it's working well, both on phone and desktop.  Drop down menus to pull up various graphs.  Here's a screenshot.  I found a javascript charting solution that works nice, scrolls/zooms/pans well with lots of data.  I can easily load 14 days of 1 minute data onto the webpage without taking very long to load.  That is enough data on one graph for my purposes.  I can reload 14 days older or newer with the arrow buttons.

P.S.  building website on server is MUCH easier than on Do-More...

[plcnut]

How would one go about logging edge triggered boolean data to MySQL?  It seems silly to log continuously while bit is ON or OFF.  But triggering it with a delta contact would get me a timestamp at bit ON, and a timestamp at bit OFF.  How could I digest this in MySQL?  I would like to display it graphically, like trend view does.  I've googled it but am coming up empty on how to deal with the ON/OFF transition.  In my database I would need to log an OFF bit right before before the transition to ON, and log an ON bit right before it turns OFF.  Am I looking at this wrong?

You could just do 2 INSERT queries with the inverse of the bit as the first insert.

Code: [Select]

DECLARE @MyBit bool,
SET @MyBit =
CASE(IF BitValue = 1
THEN 0
ELSE 1);

INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(@MyBit, DATEADD(SECOND, GETDATE(), -1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(BitValue, GETDATE());


I've got it working really well.  I have built a website (on local server) to pull up all the data, it's working well, both on phone and desktop.  Drop down menus to pull up various graphs.  Here's a screenshot.  I found a javascript charting solution that works nice, scrolls/zooms/pans well with lots of data.  I can easily load 14 days of 1 minute data onto the webpage without taking very long to load.  That is enough data on one graph for my purposes.  I can reload 14 days older or newer with the arrow buttons.

Nice!

P.S.  building website on server is MUCH easier than on Do-More...

It is pretty amazing that a PLC can do it to begin with, and then to have so much flexibility is pretty cool!

[BobO]

It is pretty amazing that a PLC can do it to begin with, and then to have so much flexibility is pretty cool!

And pretty sure that server won't run 19 axes of closed loop motion or 34 concurrent Modbus/RTU comm sessions, all while slapping 1K of relays at 100Hz. 

[Bolt]

You could just do 2 INSERT queries with the inverse of the bit as the first insert.

Code: [Select]

DECLARE @MyBit bool,
SET @MyBit =
CASE(IF BitValue = 1
THEN 0
ELSE 1);

INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(@MyBit, DATEADD(SECOND, GETDATE(), -1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(BitValue, GETDATE());


I did just that this morning.  Only I'm using the PLC's $UTC-1 for queuing purposes.

On that note, is there an easy way to build a queue?  Say I have a few events that triggered simultaneously.  I'm not smart enough to write my PHP to digest them all at once, so I send them separately.  I'm finding my manual queuing (stages, etc) to be getting cumbersome.  Is there a way I can dump my created strings into a queue, and then have my transmit program send them one at a time?  Or do I just use copy to move my table around manually?  I've searched the forums but haven't found any examples.  I'm just starting to wrap my head around building an actual queue.

[BobO]

On that note, is there an easy way to build a queue? 

We have had a table feature request in the database (with a Queue being an option) since version 1.0, but it just keeps getting pushed.

If it were me, I'd do a simple ring buffer, but many people brute force it with copies.

[Bolt]

And what might a ring buffer be?

[Bolt]

Okay, I understand the concept behind a ring buffer, have some string registers, 0-x "slots", and as they are (successfully) transmitted, it will clear them out.  On the writing to the slots, how do I (cleanly) determine which slot to write into?

I like how the Do-More has many ways to accomplish things.  However, the OCD in me struggles with accomplishing it "properly".  I don't like kludging something together to later have to rework it to make room for something else.  I'm learning as I go here, and am learning a lot.

[BobO]

Head and Tail index....write to Head, then increment...read from Tail, then increment. Empty when Head == Tail, full when Adjusted(Tail+1) == Head. I generally make the wrap happen at powers of two (mask off the wrapped value), but it is arbitrary.