Keep MySQL connection in static variable in function (avoid global variables) in C

Question

I'm creating a MySQL layer in C (to be run in GWAN) and are trying to avoid to keep the MySQL connection in a global variable.

My simplified code looks like this:

    #include <mysql.h>

    const char *DB_HOST = "localhost";
    const char *DB_USER = "user";
    const char *DB_PASSWORD = "pass";
    const char *DB_DATABASE = "xxx";
    const unsigned int DB_PORT = 3306;
    const char *DB_CHARACTER_SET = "utf8";
    const char *DB_SOCKET = 0;
    const unsigned long DB_CLIENT_FLAG=0;

    typedef struct DB_Data {
        //database returned data
    } DB_Data;

    //connect to database and return the connection
    MYSQL *db_connect() {

        static MYSQL *db_connection = NULL;

        if(db_connection == NULL) {

            printf(" NEW CONN \n");

            db_connection = (MYSQL *)mysql_init(NULL);

            if(!db_connection) {
                return NULL;
            }

            //connect to the database using settings
            if(mysql_real_connect(db_connection, DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG) == NULL) {
                return NULL;
            }

            //set character set
            if (mysql_set_character_set(db_connection, DB_CHARACTER_SET)) {
                return NULL;
            }

        }else{
            printf(" OLD CONN \n");
        }

        return db_connection;
    }

    //close the database connection
    void db_close() {

        //get connection
        MYSQL *db_connection = db_connect();

        if(db_connection != NULL) {

            printf("\n closing... \n");

            //close connection
            mysql_close(db_connection);

            //set pointer to null
            //Fail: this does not effect the static variable in db_connect()
            db_connection = NULL;
        }
    }

    //query the database
    DB_Data *db_query(char *sql, bool getResult) {

        DB_Data *rtn;
        MYSQL_ROW row = NULL;

        //create connection if not already done
        MYSQL *db_connection = db_connect();
        //db_connect();
        if(db_connection == NULL) {
            return NULL;
        }

        if(mysql_query(db_connection, sql) != 0) {
            return NULL;
        }

        if(getResult != true) {
            return NULL;
        }

        MYSQL_RES *result = mysql_store_result(db_connection);

      if(result == NULL) {
            return NULL;
      }

        while ((row = mysql_fetch_row(result))) {
            //process data
            printf("Process data \n");
        }

        mysql_free_result(result);

        return rtn;
    }

Everything works until I start to use db_close() to close the connection. What seem to fail in my code is that in db_close() db_connection is not set to NULL. After running db_close() and then again db_connect() the value for db_connection is not NULL but the same value it got the first time db_connect() was run.

There is no problem closing the connection with mysql_close(db_connection) or executing queries, so db_connection (returned by db_connect()) is valid.

If I put db_connection as a global variable then db_close() also works but I'm trying to avoid global variables.

The above code is the simplified version. I have tried a couple of things like passing around a MYSQL ** as db_connection instead but I have not gotten it to work.

Info and/or some example code would be great!

Answer 1

First of all - a simple but a bit "crooked" solution might be to only use one function called "db_action" that receives an integer variable that acts as a kind of flag: When the flag is "0" you try to connect to the DB and when the flag is "1" you disconnect from it:

    MYSQL *db_action(int flag) {

    static MYSQL *db_connection = NULL;

    if(flag == 0 && db_connection == NULL) {

        printf(" NEW CONN \n");

        db_connection = (MYSQL *)mysql_init(NULL);

         ...
    }
    else if(flag == 1 && db_connection != NULL){
        printf("\n closing... \n");

        //close connection
        mysql_close(db_connection);

        //set pointer to null
        db_connection = NULL;
    }
    else{           
        printf(" OLD CONN \n");
    }

    return db_connection;
}

This is if you really want to a void using a global variable. In my opinion it makes sense that your connection variable should be global, because it will probably be used for the entire run time of the program and be used by different functions. In many basic mysql tutorial for c/php/ruby etc. they usually use a global variable.

I want you to understand why you cannot set your db_connection variable to NULL in the db_close function.

When you fist declare static MYSQL *db_connection = NULL; in the db_connect function you are creating a new static variable (which means it is initialized only once) and set it to NULL. You can read more here: wikipedia static_variable

Then you exit (your code exits...) the db_connect function. Now the db_connection variable is out of scope - you can't "use" it (read its data or change it). You can only do that inside the db_connect func.

In db_close you get the previous db connection, MYSQL *db_connection = db_connect(); but notice since you declared *db_connection to be a new variable of type MYSQL (it's a pointer to a MYSQL variable) it is now in the scope of the db_close function - and it cannot affect the static variable you created earlier - they are two completely different variables - they only share the same data when you call db_connect().

The scope and/or heap/stack mechanism is something you have to understand when you develop any type of software. Especially C/C++ or similar languages when you as a programmer have more responsibility to manage the memory allocation of the different variables in your programs. Read more about it here: Memory allocation, Heap and Stack

Tom.

Answer 2

There are two problems to take into account with your script:

• you need to keep the MySQL socket global to make several requests and finally close the connection;
• you need to make an asynchonous SQL-server request to avoid blocking the worker thread while waiting for the DB server to reply.

The first problem is resolved by the persistence.c G-WAN example.

The second problem is resolved by the stream3.c example.

stream4.c merges the two solutions in another example but it is distributed with the latest stable release for registered users (v4.12.19).

Hope it helps.