Jump to content

pcborges

Members
  • Content count

    15
  • Joined

  • Last visited

Community Reputation

0 Neutral

About pcborges

  • Rank
    Member
  1. pcborges

    mysqli affected rows not working with INSERT

    Hi, it was not timeout... I am checking the library... Thanks
  2. pcborges

    mysqli affected rows not working with INSERT

    I will set the c++ http request to have a longer timeout, I am convinced it will solve my problem. Thanks very much you all, many heads definitively do better than one! Regards Paulo
  3. pcborges

    mysqli affected rows not working with INSERT

    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.
  4. pcborges

    mysqli affected rows not working with INSERT

    https://github.com/espressif/arduino-esp32/blob/master/libraries/HTTPClient/src/HTTPClient.h
  5. pcborges

    mysqli affected rows not working with INSERT

    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.
  6. pcborges

    mysqli affected rows not working with INSERT

    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
  7. pcborges

    mysqli affected rows not working with INSERT

    "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)
  8. pcborges

    mysqli affected rows not working with INSERT

    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?
  9. pcborges

    mysqli affected rows not working with INSERT

    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 }
  10. pcborges

    mysqli affected rows not working with INSERT

    Although the INSERT works $conn->affected_rows returns blank... Why is it happening? Is it coming from the MySQL server? Paulo
  11. pcborges

    mysqli affected rows not working with INSERT

    "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
  12. pcborges

    mysqli affected rows not working with INSERT

    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
  13. pcborges

    mysqli affected rows not working with INSERT

    NOTE of INTEREST: The requests are been sent by an Arduino C++ app. Thanks Paulo
  14. pcborges

    mysqli affected rows not working with INSERT

    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
  15. 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
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.