[SOLVED] keep MYSQL connection functional for multiple queries

Issue

I am having a very hard time finding information on this topic. I have a running application on a Raspberry Pi where I have an infinite loop with the below code. outside of it i have the MYSQL *con; to be reused. So my code works well the first time, but the second time i get the following error. I thought adding the MYSQL_close() would do the trick but it didn't.

output:

valor: d9274cb5   -651735883
valor: d9274cb5   -651735883
1
This handle is already connected. Use a separate handle for each connection.

code:

                    uint32_t intVal;
                    sscanf(&sn_str[1],"%x",&intVal);
                    fprintf(stderr, "valor: %x   %d\n", intVal, intVal);
                    if (mysql_real_connect(con, "localhost", "rfid", "******",
                            "Order2Dock", 0, NULL, 0) == NULL)
                    {
                            fprintf(stderr, "1 \n");
                            finish_with_error(con);
                    }
                    if (mysql_query(con, "INSERT INTO `Order2Dock`.`Actividad`(`TiempoInicio`,`Proceso_Id`, `Orden_Id`)     VALUES (now(),1,1)")) {
                            fprintf(stderr, "2 \n");
                            finish_with_error(con);
                    }
                    mysql_close(con);

Solution

Keep your connect command outside of the loop

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (!$mysqli) {
 fprintf(stderr, "1 \n");
 finish_with_error(con);
}

Then start the loop, you dont need to connect everytime you loop:

while (1+1 = 2){
 if (!$mysqli){ 
  $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
 }
 uint32_t intVal;
 sscanf(&sn_str[1],"%x",&intVal);
 fprintf(stderr, "valor: %x   %d\n", intVal, intVal);
 if ($mysqli->query("INSERT INTO `Order2Dock`.`Actividad`(`TiempoInicio`,`Proceso_Id`, `Orden_Id`) VALUES (now(),1,1)") === TRUE) {
  echo '<p>Yeah, another query!!</p>';
 }
}

Edit: I just added a condition to test if the link is still up, otherwise re-connect to the database.

I was just thinking that if this is an infinite loop running over a Web server like apache or IIS... then something must be configured in order to let the script run forever instead and prevent the web server from timing it out.

Cheers.

Answered By - Solrac