MySQL C API: Need example for initiating DB on *Embedded* mode

Question

I work on a C tool, in which I need to manipulate and query process-internal data intensively.

Hence, I've decided to use the MySQL C API on Embedded mode, so I can have an embedded DB for each running process of my tool, and use all the SQL features on it.

All the MySQL C API tutorials I've found over the web, deal with connecting to a running server, which is not my case.

There are some examples for working with Embedded mode in Oracle's MySQL website, but they are not simple, and it's hard to make them work.

Q: Can someone please point me or write me a short example for initiating a DB using MySQL C API on Embedded mode?

Thanks!

Answer 1

After a lot of tries, finally answering to myself and sharing it with you:

• Create a central data directory that would be used for storing DBs. In this example, I'll use /tmp/mysql_embedded_data

> mkdir /tmp/mysql_embedded_data

• Create a C file, as in the following example:

#include <my_global.h>
#include <mysql.h>

int main(int argc, char **argv) {  
    static char *server_options[] = {
                                   "mysql_test", // An unused string 
                                    "--datadir=/tmp/mysql_embedded_data", // Your data dir
                                    NULL };
    int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

    static char *server_groups[] = { "libmysqld_server",
                                     "libmysqld_client", NULL };


    // Init MySQL lib and connection    
    mysql_library_init(num_elements, server_options, server_groups);
    MYSQL *con = mysql_init(NULL);

    if (con == NULL) {
        fprintf(stderr, "%s\n", mysql_error(con));
        exit(1);
    }

    mysql_options(con, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
    mysql_options(con, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

    // Connect to no host/port -> Embedded mode
    if (mysql_real_connect(con, NULL, NULL, NULL, NULL, 0, NULL, 0) == NULL) {
        fprintf(stderr, "%s\n", mysql_error(con));
        mysql_close(con);
        exit(1);
    }

    // Create a sample empty DB, named "aNewDatabase"
    if (mysql_query(con, "CREATE DATABASE aNewDatabase")) {
        fprintf(stderr, "%s\n", mysql_error(con));
        mysql_close(con);
        exit(1);
    }

    // Close connection
    mysql_close(con);
    exit(0);
}

• Compile your file (I assume in this example that my C filename is /tmp/mysql_try.c)

gcc /tmp/mysql_try.c -o /tmp/mysql_try -lz `mysql_config --include --libmysqld-libs`

• Run your compiled program:

> /tmp/mysql_try

• When your program returnes, ensure that the DB creation succeeded. We named our sample DB aNewDatabase, so we'll check if it now has a directory inside the data directory we created -> We'll check if the directory /tmp/mysql_embedded_data/aNewDatabase was created:

> ls /tmp/mysql_embedded_data/aNewDatabase db.opt

• Enjoy!