Mike's Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======= Programing in C++ with MySQL =======

==== Intro ====

Coding in C++ with embedded MySQL calls is extremely easy once you get your baseline right. Add in the standard “std” namespace, and things get even more easy. There are some “gotcha’s” to look our for and I’ll document below.

==== Environment ====

  * Linux (all flavors of Debian or RedHat)
  * GNU gcc compiler (gcc 4.8.5)
  * MySQL Development Libraries

Intro
I do most of my web development (Yes, I’m a DBA) in either bash or C++.  Anything CGI will be done with fastCGI, but even when doing straight CGI (Old School) fork-exec is costly, but my programs usually run faster than the equivalent Php or Python programs. 

Why ? I’ve seen seasoned developers opening/closing cursors to retrieve thousands of records in a “do” loop or opening/closing connections to the database.. Which are incredibly expensive operations (there’s a lot of hand-shaking going on when examining the packets). 

Let’s get into it, and I’ll explain what each piece of the code does:

First, let’s create a throwaway table in our database.

create table foo (id integer not null auto_increment, bar varchar(120), primary key (id));

NOTE: we’ve created an auto-increment field for this demo.

<code>
#include <iostream>
#include <mysql.h>
#include <string>
#include <string.h>
#include <stdio.h>

MYSQL *conn;

char host[128];
char database[30];
char username[30];
char password[30];

int main(int argc, char* argv[])
{

    memset(host,'\0', sizeof(host));
    memset(username,'\0', sizeof(username));
    memset(password,'\0', sizeof(password));
    memset(database,'\0', sizeof(database));

    strcpy(host,     "db_or_ip_address");
    strcpy(username, "username");
    strcpy(password, "password");
    strcpy(database, "schema_aka_database");

    //--------------------------------------------------------------------------
    // MySql Connect Section
    //--------------------------------------------------------------------------
    conn = mysql_init(NULL);
    conn = mysql_real_connect(conn,host,username,password,database,0,NULL,0);

    if(conn == NULL) {
        std::cerr << "RDBMS not available or invalid credentials";
        exit(1);
    }

    if(mysql_select_db(conn, database)) {
        std::cerr << "NOT CONNECTED: " << mysql_error(conn);
        exit(1);
    }

    MYSQL_RES *res_set;       // Think of this a a file descriptor or pointer
    MYSQL_ROW row;            // This of this as a pointer to the columns 

    std::string sql_string = std::string("select now()");  // Your query

     //------------------------------------------------------------------
     // STATEMENT
     //------------------------------------------------------------------
     sqlerrc=mysql_query(conn, sql_string.c_str());        // Tell MySQL to invoke  
     if(sqlerrc != 0 ) {
                    std::cerr << "DB Error: " << sqlerrc << std::endl
                              <<  mysql_error(conn)      << std::endl;
                    exit(1);
     }

     res_set = mysql_store_result(conn);                    // Let MySQL setup the storage  

     std::string hv_foobar;                                 // Can be anything, arrays, etc.
     while ( (row = mysql_fetch_row(res_set)) != NULL ) {              //  for each row….
         for (unsigned int i=0; i < mysql_num_fields(res_set); i++) {  // and for each column
             hv_foobar     = std::string(row[i]); i++;      // store in variable
             // some var = row[i]; i++;                     // if you had more than 1 column
         }
    }

    std::cout << hv_foobar << std::endl;                    // display the results
    //--------------------------------------------------------------------------
    // Let’s insert into our new table 
    // it will return the value of the auto-incremented field
    //--------------------------------------------------------------------------
    std::string dml=std::string("insert into foo (bar) values ('This is foobar')");
    sqlerrc=mysql_query(conn, dml.c_str());
    if(sqlerrc != 0 ) {
        std::cerr << "DB Error: " << sqlerrc << std::endl
                   <<  mysql_error(conn)      << std::endl;
         exit(1);
    }
    std::cout << mysql_insert_id(conn) << std::endl;     // last insert id, can be used
                                                         // for child tables to reference  
                                                         // the parent ID (if FK constraint)
    mysql_close(conn);
}

</code>

Compile

<code>
 g++ -std=c++11  -Wall -Wno-write-strings -I/usr/include/mysql -L/usr/lib/mysql test.cpp -o test.bin `mysql_config --cflags --libs`
</code>

Verification
And finally, Let’s check our data

<code>
$ /test.bin
2024-04-25 15:30:28
1

MySQL [test_db]> select * from foo;
+----+----------------+
| id | bar            |
+----+----------------+
|  1 | This is foobar |
+----+----------------+

</code>

**Gotchas**


When doing “stuff” like outer joins, remember that there’s a good chance that some columns will come back with nulls, and one of the biggest complaints about any of the “C” variants is how nulls are handled. In this case, you want to wrap possible NULL columns with an “ifnull()” in your SQL statement.

Again: WRAP ANY POSSIBLE NULLS COMING FROM THE DB WITH IFNULL

**Conclusion**

Coding in any of ‘c’ languages is pretty easy when interfacing with any RDBMS. You can take the code above and start modifying it. In this example, I hardcoded the database connection, but more than likely you will store these values in a configuration file and read it, so you won’t have to recompile every time you change the password.