Developing MySQL Applications with Eclipse CDT

Introduction

In September, the Developer Zone featured Chapter 6 of Paul DuBois' MySQL, 3rd edition on Writing C Programs with the MySQL C API. This month, we continue in this series on developing MySQL applications in C with a tutorial from Mark Schoonover about using the Eclipse CDT (C Development Toolkit) as an IDE for developing C applications for MySQL.

Using Eclipse, we'll develop an example application that will document key structures, and demonstrate several API calls. This article will also cover configuring Ubuntu and Eclipse for MySQL client development. It's important that your development environment is tested and working properly before developing your MySQL client software.

Ubuntu

You'll need to install the build-essential, Eclipse and the Eclipse CDT packages:

MySQL

In order to develop an application with the API, you'll need libmysqlclient and all the header files installed.

For testing, you'll also need MySQL running with some test data. I've been experimenting and have been very happy with XAMPP. Outside of DBA work, I'm involved in web development and really enjoyed how quickly a development environment can be created under XAMPP. We'll use the sample world database for our program which can be downloaded from the document page of mysql.com

Eclipse

You'll need to create a project to store your configuration and source files. First, create a Managed Make C Project with File->New->Project->C->Managed Make C Project, and call it mysqlapidemo.

The Type of Project will be Executable (Gnu).

Click Finish. Eclipse will setup your workspace, and it should look like this:

Before you can compile our code, you need to include the path to the mysql header files, Project->Properties->C/C++ Build->Directories

and the mysqlclient, and Z libraries: Project->Properties->C/C++ Build->GCC C Linker->Libraries

Once that's done, we need to add a source file called - mysqlapidemo.c. Right click on the folder mysqlapidemo, then New->Source File

Make sure the file extension ends in c.

Data Structures

There are five data structures involved when writing your own clients using the API, but I'll discuss only three of them: MYSQL, MYSQL_ROW, and MYSQL_RES.

MYSQL: This data structure can be found in mysql.h, by searching for typdef struct st_mysql. It's a structure for storing all the details about MYSQL like communication parameters, server status, host, user, password, socket, server version, host info, etc. Understanding this structure will help greatly in developing your own clients. To browse this structure, open the /usr/include/mysql folder in the C/C++ Projects tab on the left of the

MYSQL_RES: Structure represents the rows returned from SQL statements that can return multiple rows of data. Search on typedef struct st_mysql_res in mysql.h for all the details.

MYSQL_ROW: A very small, but important structure that can handle a single binary row of data. Use the same technique as the previous structures to view the member variables. Search on typedef struct st_mysql_row for further information.

Overview

Our sample program needs to initialize struct MYSQL , connect to the MySQL server, call API functions, use the result, then fetch and display the data. Those are pretty much all the steps you need your application to do - intialize, connect, use, fetch, and close the connection. You can copy and paste the mysqlapidemo code below into the source file you created above.

mysqlapidemo Program:

#include 
#include 

int main()
{
    MYSQL            *connection;
    MYSQL_RES        *resource;
    MYSQL_ROW          result;
  
    char            *servername     = "localhost";
    char            *user            = "root";
    char            *password        = "";
    char            *database        = "world";
    char            *socket            = "/opt/lampp/var/mysql/mysql.sock";
    char            *hostinfo;
    char            *serverinfo;
    int               protoinfo;
  
    /* Intitialize connection to database, and MYSQL structure. */
    connection = mysql_init(NULL);
  
    /* Connect to database */
    if(!mysql_real_connect(connection, servername, user,
        password, database, 0, socket, 0)) {
            printf("%s\n", mysql_error(connection));
        }
    /* Get host info */
    hostinfo = mysql_get_host_info(connection);
  
    /* Get server info */
    serverinfo = mysql_get_server_info(connection);
  
    /* Get protocol info */
    protoinfo = mysql_get_proto_info(connection);
  
    /* Output get info */
    printf("Host: %s\n", hostinfo);
    printf("Server: %s\n", serverinfo);
    printf("Protocol: %d\n", protoinfo);
  
    /* Execute our SHOW DATABASES statement */
    mysql_query(connection, "SHOW DATABASES");
  
    /* Resource structure with the rows of data from SHOW DATABASES */
    resource = mysql_use_result(connection);
  
    printf("Databases:\n\n");
  
    /* Fetch & print each row */
    while((result = mysql_fetch_row(resource))) {
        printf("%s\n", result[0]);
    }
  
    /* SELECT more data from world database. */
    mysql_query(connection, "SELECT Name, Population FROM City ORDER BY Name");
  
    /* Resource struct with rows of returned data. */
    resource = mysql_use_result(connection);
  
    printf("\n\nCities and populations in City table:\n\n");
  
    /* Print out all our cities with populations */
    while((result = mysql_fetch_row(resource))) {
        printf("%s %s\n",result[0], result[1]);
    }
   
    /* UPDATE San Diego population */   
    mysql_query(connection, \
        "UPDATE City SET Population=\"1300000\" WHERE ID=\"3799\"");
       
    printf("%ld Row(s) Updated!\n", (long) mysql_affected_rows(connection));
       
    /* SELECT newly inserted record. */
    mysql_query(connection, \
        "SELECT Name, Population FROM City WHERE Name = 'San Diego'");
       
    /* Resource struct with rows of returned data. */
    resource = mysql_use_result(connection);
       
    /* Fetch single result */
    result = mysql_fetch_row(resource);
   
    /* Display San Diego's new population */
    printf("%s %s\n",result[0], result[1]);
  
    /* Free memory used by resource */
    mysql_free_result(resource);
  
    /* Closes connection to database, frees memory used by connection. */
    mysql_close(connection);
  
    /* Frees up other memory used by the libmysqlclient. */
    mysql_library_end();
  
    return 0;
  
}

Conclusion

You have the basics on creating your own MySQL client using the C API. I've kept things simple, there's no error checking on most of the API functions. In a real application, you'd want to check return values from API functions using mysql_errono() function after the API call. You can see an example of mysql_error() when trying to connect to the database.

The best way to learn any new API is to build small example programs to see how it all works, and ask questions!

Resources

MySQL C API Docs
Eclipse
Eclipse CDT