Using a MySQL database in C is not very complicated, but I know that when I was starting out on it, I could have used some example code with documentation. With that fact in mind, here's some code:
#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
int main()
{
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char query[80];
mysql_init(&mysql);
mysql_real_connect(&mysql,"some.host.com","ipaudit","letmein","audit",0,NULL,0);
sprintf(query,"SELECT src,dst FROM ipaudit");
mysql_real_query(&mysql,query,(unsigned int)strlen(query));
res = mysql_use_result(&mysql);
while(row = mysql_fetch_row(res))
printf("%s %sn",row[0],row[1]);
mysql_free_result(res);
return 0;
}
And there you have it: a program that will connect to the audit database on some.host.com with username audit and password letmein, execute a query, and print out the results.
Let's start at the beginning, shall we?
#include <mysql/mysql.h>
Hopefully you get the gist of this line. I only highlight it to show you where the header file is located.
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
MySQL has quite a few custom structs it uses; these are the three you'll probably see most often.
- MYSQL: this is a structure to hold information about a unique MySQL connection. Any functions which involve interaction with the server expect one of these to be passed by reference.
- MYSQL_RES: this is a structure for holding a result set from a query. They can grow to be quite large, depending on which method you use to fetch your results. More on that later.
- MYSQL_ROW: this holds one row from a result set. It can be accessed as an array of character pointers (strings) as seen later in the program.
mysql_init(&mysql);
This function MUST be called on all MYSQL objects. The name pretty much says it all; just your basic initialization function.
mysql_real_connect(&mysql,"some.host.com","ipaudit","letmein","audit",0,NULL,0);
Ah, now the nitty-gritty. This is probably the longest prototype in the API, but it's really not that complicated...so long as you remember the order of arguments.
The function takes(in order):
- A reference to a MYSQL object (the one to be used for the connection, obviously)
- A const char * that specifies a hostname or IP to connect to. Passing nothing or NULL causes the function to default to "localhost"
- A username to connect to the server with. Blank or NULL values cause the function to default to the user running the program
- The user's password. You can have blank passwords in MySQL, in which case this can be NULL or ""
- The default database for queries. If you specify one here (optional), all queries will be presumed to operate on this database, instead of you having to use the database.table format
- Port, socket, and client flags. Passing 0 and NULL (you guessed it) cause the API to use the default values(3306,new connection, and 0, respectively).
So in our example code, we're connecting with ipaudit:letmein@some.host.com:3306, and chosing the audit database as the default.
Now, the moment you've all been waiting for: The Mighty Query
mysql_real_query(&mysql,query,(unsigned int)strlen(query));
Okay. Kind of anti-climatic, I know. The three paramaters here are very self-explanitory: MYSQL *db_connection,const char *query,unsigned int query_length (my compiler complains about the return type of strlen, hence the type cast). Note that the format of the query is exactly like every other client interface for SQL ever invented (except for TrollTech's rumored C++ interface, that is)
Once this command has been executed, the server executes your query and stores the results locally (on the server, that is). Your next task is to retrieve them:
res = mysql_use_result(&mysql);
This line points your MYSQL_RES variable at the result set from the query you just executed. The API allocates a bit of memory for it, but it does not actually pull any rows until you request them with mysql_fetch_row(). This uses very little RAM, but relies more on a good network connection.
Now, if your network connection sucks (or you just want faster access to the results within a loop), you should consider using mysql_store_result() instead. On the buggy network I have to use at work, the use of store_result has saved me countless headaches, simply because I only have to have a good connection long enough to send the result set, not send and parse it (yes, it gets that bad where I work).
Now, to use that very creatively named row variable:
while(row = mysql_fetch_row(res))
mysql_fetch_row() returns one row at a time from a result set, or NULL when there are no more rows in the set. An empty result set will return NULL on the first call (this is different than the behavior in the case of an error; see below for error checking). We assign the returned value to a MYSQL_ROW structure, and we can (at last) get our data.
printf("%s %sn",row[0],row[1]);
That's all there is to pulling the data. ROWs are accessed as arrays of character pointers, and MySQL is nice enough to order the elements in the same order you list fields in your SELECT statement. So, in the example above, row[0] is the src field of a row, and row[1] is the dst field. One caveat: all data is returned as strings in this model, so you'll learn to love string to integer conversions quickly.
Last but not least, we free the memory used by the result set:
mysql_fetch_row(res);
This must be done before re-using the MYSQL_RES structure for another query. It's especially important if you use mysql_store_result().
To compile the application, you'll have to link it against the appropriate libraries. On GNU/Linux, this is usually done with the following command:
gcc sql_prog.c -o sql_prog -lz -lmysqlclient -L /usr/lib/mysql/
There you have it! Everything you need to know to use a SQL database remotely. As long as nothing goes wrong. Ever.
Knowing how likely that is, let me show you a couple of things you can do to catch errors:
if(!mysql_real_connect(....))
{
printf("Error connecting to database: %sn",mysql_error(&mysql));
//exit gracefully here...
}
mysql_real_connect() returns a pointer to a MYSQL structure (which you'll probably never use) or NULL in the case of an error (which you'll use a lot). Check the return value and then call mysql_error(&connection) to get a very detailed and helpful error message (No, that's not sarcasm).
You may also call mysql_error() after a function who's success you wish to check. This will return non-zero if an error occurred during the most recent API call (with some exceptions; but the exceptional functions usually provide the ability to check for a NULL return value in any case)
mysql_error() will also return errors from the database server, so if your mysql_real_query() call fails, you can get the same error message you'd likely get if you were connected through a command-line client.
That's about it for the basics. The MYSQL API is, in my opinion, pretty well thought out and has very good error checking built in. My only gripe is the _real_ in their function names, which is there for historical reasons so as not to deprecate old code.
You should definately check out MySQL.com's documentation, as well:
On a side note, this is my first public technical article. I look forward to hearing your comments and suggestions on this article, and encourage questions pertaining to it - I'll do my best to answer them here or in a future article |
Copyright Open Source Institute, 2006 
