MySQL and NTP on a Ethernet Arduino

I have the need to use both NTP and MySQL on an Ethernet Arduino. I started out by having separate NTP and MySQL programs. Both work fine until I combine them. I first made several revisions to reduce the memory needs. I was almost 85% now it is down to 79%. Still the memory warning shows itself but I have confidence that the problem is in using both UDP and TCP.

UDP is needed for NTP and TCP is needed for MySQL. The NTP aspect is basically the example whittled down to extract time only when a MySQL UPDATE needs to occur.

The MySQL UPDATE event occurs when an input pin goes low and defined by pinMode(7, INPUT_PULLUP);
I also need to use the following libraries which is part of the memory usage
#include <Time.h>
#include <SPI.h>
#include <Ethernet.h>
#include <EthernetUdp.h>
#include <sha1.h>
#include <mysql.h>
#include <String.h>
#include <PString.h>

FYI: I needed to go into sha1.cpp and sha256.cpp and modify uint8_t sha1InitState PROGMEM = {
to
const uint8_t sha1InitState PROGMEM = {
By doing so it stopped the compile error of sha1InitState

When A0 goes low a long string is created after parsing the date and hour string and several other values.

As identified above each work independently but not together. It appears that UDP and TCP cannot run concurrently.

If this is accurate how does one close a UDP port, then enable the ethernet for use with MySQL, then close TCP so I can start over to enable UDP and so on?

It also appears I may need to look at another device that has ethernet and more memory as this is the beginning of larger project that will have many more SQL statements, input checks and other code aspects.

Development is on a MAC running 10.10

Any suggestions are welcome

Thanks

As identified above each work independently but not together. It appears that UDP and TCP cannot run concurrently.

Yes, they work together. I have client and server code that both use NTP.

I have a Mega 2560 though. The server code I use is right on the border of exceeding what an Uno or Arduino Ethernet would have.

Opening and closing the UDP socket doesn't really help with memory. If you are using TCP client and NTP code together, that should not be a problem.

Maybe a problem with MySQL and NTP together. I have tried several different ways to organize the code to get both to function in a single sketch but not working at this time.

Maybe a problem with MySQL and NTP together.

Only if you have a SRAM problem. Are you running out of SRAM?

The problem was I did this //Start UDP Udp.begin(localPort);

But not this // Start Ethernet Ethernet.begin(mac_addr, ip);

Makes a difference if you do not enable the ethernet :-)

Next problem is String length when using MySQL. I have a long sql UPDATE statement that requires year(), month(), day(), hour(), minute(), second() with several other parameters.

This String mysql_str; enables creating the full string as needed however when I do this my_conn.cmd_query(mysql_str); the compiler sends an error

iSecure_Input2.ino: In function 'void Read_Inputs()': iSecure_Input2.ino:274:32: error: no matching function for call to 'Connector::cmd_query(String&)' iSecure_Input2.ino:274:32: note: candidate is: In file included from iSecure_Input2.ino:18:0: /Users/carlfitzsimmons/Documents/Arduino/libraries/mysql_connector/mysql.h:110:13: note: boolean Connector::cmd_query(const char*) boolean cmd_query(const char query); ^ /Users/carlfitzsimmons/Documents/Arduino/libraries/mysql_connector/mysql.h:110:13: note: no known conversion for argument 1 from 'String' to 'const char' Error compiling.

Looks like a problem in the mysql.h file row 110. Not sure what needs to be added. Appears that 'char' is needed and 'String' is not defined as proper type

Doing this my_conn.cmd_query(mysql_str.c_str()); and the complier does not complain but the code goes into never never land when this is executed

The problem was I did this //Start UDP Udp.begin(localPort);

But not this // Start Ethernet Ethernet.begin(mac_addr, ip);

This is why we normally require you to post your code.

I don't use the String data type. I use character arrays.

I modified to use char and it appears I have hit a limit to the ethernet Arduino. The char needs to be
char my_str[360];

There are the UDP, Ethernet and NTP libraries plus 14 variables that need to be included in the INSERT string and after correctly assigning 10 values the program becomes non responsive. I was doing some testing to see if I could use this as a quick turn around proto board but it appears I found a limit to the ethernet Arduino for my needs. I have used this in several other projects but I need to find another platform to perform this work

Any suggestions welcomed

Thanks to everyone for their feedback

You are probably running out of SRAM. There are ways to reduce the amount of SRAM you use, but without seeing your code, it is difficult to tell which you have already implemented. Two that come to mind is the use of the F() function and reducing the size of the url variable names. Even using 14 variables in a url request, I can't imagine needing 360 characters to hold it.