pcborges Posted December 6, 2018 Share Posted December 6, 2018 Hi, I have an app that access a MySQL database via a php script. I noticed that INSERT was returning error but the data was being recorded successfully. I finally traced the problem to be related with the line echo $conn->affected_rows; For some reason when it is an SQL INSERT it returns -11 but as I said the INSERTS executes successfully. The app requesting the service sends in sequence: char* txtSQL[]={"INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "UPDATE activity set value=333 where value=130", "SELECT sum(value) from activity where mac='a9c4952de6b4'", "DELETE from activity WHERE value=333"}; I set a monitor to check what was being returned and got: query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130) HttpResponse: - httpResponseCode: -11 query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130) HttpResponse: - httpResponseCode: -11 query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130) HttpResponse: - httpResponseCode: -11 query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130) HttpResponse: - httpResponseCode: -11 query=UPDATE activity set value=333 where value=130 HttpResponse: 4 * httpResponseCode: 201 -------------------------------------->As can be seem the INSERTS above returned error but worked OK query=SELECT sum(value) from activity where mac='a9c4952de6b4' HttpResponse: 1379 * httpResponseCode: 200 query=DELETE from activity WHERE value=333 HttpResponse: 4 * httpResponseCode: 201 The PHP script do ing the job i s as be low : <?php include('connection.php'); //these are just in case setting headers forcing it to always expire header('Cache-Control: no-cache, must-revalidate'); error_log(print_r($_POST,TRUE)); if( isset($_POST['query']) && isset($_POST['key']) ){ //checks if the tag post is there and if its been a proper form post header('Content-type: application/x-www-form-urlencoded'); if($_POST['key']==$SQLKEY){ //validates the SQL key $query=urldecode($_POST['query']); if(get_magic_quotes_gpc()){ //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query $query=stripslashes($query); } $conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME); //connect if($conn->connect_error){ //checks connection header("HTTP/1.0 400 Bad Request"); echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR; //reports a DB connection failure } else { $result=$conn->query($query); //runs the posted query if($result === false){ header("HTTP/1.0 400 Bad Request"); //sends back a bad request error echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR; //errors if the query is bad and spits the error back to the client } else { if (strlen(stristr($query,"SELECT"))>0) { //tests if it's a SELECT statement $csv = ''; // bug fix Undefined variable: csv while ($fieldinfo = $result->fetch_field()) { $csv .= $fieldinfo->name.","; } $csv = rtrim($csv, ",")."\n"; //******************************** echo $csv; //prints header row $csv = ''; $result->data_seek(0); while($row = $result->fetch_assoc()){ foreach ($row as $key => $value) { $csv .= $value.","; } $csv = rtrim($csv, ","); //."\n"; } echo $csv; //prints all data rows } else { header("HTTP/1.0 201 Rows"); echo $conn->affected_rows; //if the query is anything but a SELECT, it will return the number of affected rows (INSERT IS RETURNING -11) } } $conn->close(); //closes the DB } } else { header("HTTP/1.0 400 Bad Request"); echo "-Bad Request"; //reports if the secret key was bad } } else { header("HTTP/1.0 400 Bad Request"); echo "*Bad Request"; } ?> Any help will be much appreciated. Thanks Paulo Borges Quote Link to comment Share on other sites More sharing options...
requinix Posted December 6, 2018 Share Posted December 6, 2018 First thing that I see is the "httpResponseCode: -11". Why is that happening? Because there's no way the script returned -11. Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 Also I see the OP is not using prepared statements. Quote Link to comment Share on other sites More sharing options...
requinix Posted December 6, 2018 Share Posted December 6, 2018 1 minute ago, gw1500se said: Also I see the OP is not using prepared statements. Which makes sense given the nature of this. Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 Hi requinix, I do not know, it just prints out as I posted above. gw1500se, I am not strong on PHP and just got this php script that I am using with an APP Inventor Android app and it works fine, including with the INSERTS. Thanks Paulo Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 I agree with requinix. Where is that error coming from? To what "monitor" are you referring? It looks like you may not have posted all your code. Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 NOTE of INTEREST: The requests are been sent by an Arduino C++ app. Thanks Paulo Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 Did not post all the code because it is C++. The "monitor" is just a serial monitor provided by Arduino that prints to the screen everything the PHP script sends back. Paulo Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 "I agree with requinix. Where is that error coming from?" I have no idea, I believe when I find the answer to that I will have the problem solved... Or will be close to it. Paulo Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 Although the INSERT works $conn->affected_rows returns blank... Why is it happening? Is it coming from the MySQL server? Paulo Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 That method returns an integer. If it is returning a "blank" then something is interfering with the myslqi calls. Is there a call-back function somewhere? Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 When I say "blank" I am referring to what is printed on the monitor. The serial monitor prints whatever is sent back from the php script. Here is the C++ code that is sending the requests. May be it will help. #include <HTTPClient.h> const char* ssid = "Skynet"; const char* password = "babacabaca"; char* txtSQL[]={"INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)", "UPDATE activity set value=333 where value=130", "SELECT sum(value) from activity where mac='a9c4952de6b4'", "DELETE from activity WHERE value=333"}; String SQLKEY = "73163"; int sqlArray = 6; int count=0; void setup() { Serial.begin(115200); delay(4000); //Delay needed before calling the WiFi.begin WiFi.begin(ssid, password); Serial.print("Connecting to WiFi"); while (WiFi.status() != WL_CONNECTED) { //Check for the connection delay(1000); Serial.print("."); } Serial.println("\nConnected to the WiFi network"); } void loop() { if(WiFi.status()== WL_CONNECTED){ //Check WiFi connection status HTTPClient http; http.begin("http://www.infotecservices.com.br/ESP32web/hydroflux.php"); http.addHeader("Content-Type", "application/x-www-form-urlencoded"); String _post = "query="; _post += txtSQL[count]; _post += "&key="; _post += SQLKEY; int httpResponseCode = http.POST(_post); //Serial.print("Count= "); //Serial.println(count); if (count==sqlArray){ count=0; } else { count+=1; } if(httpResponseCode>0){ String response = http.getString(); //Get the response to the request Serial.println(_post); Serial.print("HttpResponse: "); Serial.print(response); Serial.print(" * "); Serial.print(" httpResponseCode: "); Serial.println(httpResponseCode); //Print return code }else{ String response = http.getString(); //Get the response to the request Serial.println(_post); Serial.print("HttpResponse: "); Serial.print(response); Serial.print(" - "); Serial.print(" httpResponseCode: "); Serial.println(httpResponseCode); //Print return code } http.end(); //Free resources }else{ Serial.println("Error in WiFi connection"); } delay(2000); //Send a request every 10 seconds } Quote Link to comment Share on other sites More sharing options...
requinix Posted December 6, 2018 Share Posted December 6, 2018 Your HttpClient library is returning -11. Not PHP. You have to find out why. What library are you using? Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 My C++ is very rusty but the response codes are not coming from the PHP script per se. I think you need to investigate the C++ code. It obviously not really outputting what comes back from the PHP script. The -11 may mean it cannot properly interpret what comes back. If it is expecting HTML in return you may have some headers messed up in the PHP script. Keep in mind that nothing, not even white space can be output before headers. 1 Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 This is a library provided by the Arduino developers. The -11 is something strange because when the request fails it should return -1 and for a successful request 0 correct? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 6, 2018 Share Posted December 6, 2018 An error code perhaps? Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 "Keep in mind that nothing, not even white space can be output before headers." That is important tip for me. In the PHP script above, assuming INSERT is working (which it is) , I understand the execution comes the code below. So, do you suggest the C++ side is getting it and messing something at this point? } else { header("HTTP/1.0 201 Rows"); echo $conn->affected_rows; //if the query is anything but a SELECT, it will return the number of affected rows (INSERT IS RETURNING -11) Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 (edited) You need to look at the HTML source being returned by the PHP script to the C++ code. If it is blank or just -1 or 0 then the headers are not being output and that is why the C++ code cannot interpret it. Thus, perhaps, the -11. Edited December 6, 2018 by gw1500se Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 (edited) Look to the line: if (strlen(stristr($query,"SELECT"))>0) { //tests if it's a SELECT statement It does not send a header but it still works. One the request: "SELECT sum(value) from activity where mac='a9c4952de6b4'", It is the one who returns: HttpResponse: 1379 * httpResponseCode: 200 Edited December 6, 2018 by pcborges Quote Link to comment Share on other sites More sharing options...
gw1500se Posted December 6, 2018 Share Posted December 6, 2018 You are missing the point. The PHP script is working with respect to MySQL. But it is not outputting a valid HTML page that the C++ code can interpret. I highly suspect that is what the -11 is trying to tell you. 1 Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 Yes, you may well be correct. But all the c++ code in this micro-controller does is relay whatever it gets from the php script to the serial monitor, the http driver is the one who hides the protocol. I will probably have to dive deeper into its internals to understand what might be going wrong. The reason I came here is that it is not working ONLY with INSERTS and when it is a POST because with GET requests it works fine. Quote Link to comment Share on other sites More sharing options...
requinix Posted December 6, 2018 Share Posted December 6, 2018 33 minutes ago, pcborges said: This is a library provided by the Arduino developers. What is the name of it? Where can it be viewed online? Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 https://github.com/espressif/arduino-esp32/blob/master/libraries/HTTPClient/src/HTTPClient.h Quote Link to comment Share on other sites More sharing options...
requinix Posted December 6, 2018 Share Posted December 6, 2018 #define HTTPC_ERROR_READ_TIMEOUT (-11) 1 Quote Link to comment Share on other sites More sharing options...
pcborges Posted December 6, 2018 Author Share Posted December 6, 2018 (edited) Dear requinix, your call to see the library provided a good tip. I saw that line as well! I believe the php script may be taking too long to process the INSERT and the c++ application gets tired of waiting... I have seen that before a good time ago and that may well be my problem now. Edited December 6, 2018 by pcborges Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.