Using MySQL - steinsoft.net

If you have ever tried programming with PHP or at least know a little about this open source scripting language, you should also have heard from MySQL, the "world's most famous open source database". MySQL is very fast, free and its databases can be accessed from within many languages - C/C++ of course included. I will show you a short example how to connect to a MySQL server using plain C. There's a real C++ port too but that's something different.

First you need a MySQL server on your workstation or have access to a remote one. You'll also need the client libraries and headers installed so please check mysql.com. Under linux you simply have to install the necessary packages.

Step one: connect to the MySQL server.

#include &lt;mysql.h&gt;

//our mysql object for later on
MYSQL mysql;
//initialize MYSQL object
mysql_init(&mysql);

//try to connect to mysql server; might be remote too
if (! mysql_real_connect(&mysql,"localhost",
      "user","passwd","my_database",0,NULL,0))
{
   fprintf(stderr, "Failed to connect to database: Error: %sn",
          mysql_error(&mysql));
}

We now have a connection to our MySQL server, either local or remote, and chosen the database we would like to work on. The next step is to retrieve data from a table in this database:

//send query string to our open server connection
mysql_query(&mysql,"SELECT * FROM books WHERE author='king'");
//get result resource
MYSQL_RES result = mysql_use_result(&mysql);
MYSQL_ROW row;


//get number of fields (columns)
numFields = mysql_num_fields(result);

//go through all result rows and "play" with them
while((row = mysql_fetch_row(result)))
{
   //get length of every field
   lengths = mysql_fetch_lengths(result);
   
   //got through columns
   for(i = 0; i &lt; numFields; i++)
   {
        printf("Column %u is %lu bytes in length.n", i, lengths[i]);
   }
}

MySQL row's are represented as arrays of byte strings. But like getting data from the server we can also modify tables in the database - if we have the appropriate writing permission. That's even easier than getting data:

//send query string to update our fictive books database
mysql_query(&mysql,"UPDATE books SET author='stephen' WHERE author='king'");
//mysql_affected_rows(..) shows how many rows have been updated
printf("%ld books updated",(long) mysql_affected_rows(&mysql));

After this basic walkthrough you should be able to discover all other functions of the MySQL C API. If you have used PHP before you will see that most functions are very similar. If something's unclear, don't hesitate to ask! Happy C0ding!

[ Using MySQL ]