>> Inapoi <<
Connecting to MySQL
The first task in writing a C script to interact with MySQL is to form a connection to MySQL.
To do this, we must include a couple of header files. For our purposes, we will use stdio.h, which contains some standard
input and output definitions, and mysql.h, which is a header file containing many standard MySQL definitions. These two files
come with C and MySQL, respectively; you shouldn't have to download them from the Web.
#include
#include "/usr/include/mysql/mysql.h"
/* C to MySQL Program */
int main(int argc, char *argv[])
{
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
Because of the < and > symbols around stdio.h, C is instructed to look for it in the default location for C header files or
in the user's path. Since mysql.h is not in the default locations on my servers, I used whereis to locate it and then with
the aide of doublequotes, specified its exact location for C before compiling my script with the GNU C Compiler (gcc).
I also specified the MySQL directory and the location of mysqlclient at compile time:
gcc -o mysql_c_program mysql_c_program.c
-L/usr/include/mysql -L/usr/lib/mysql -lmysqlclient
[This is to be entered on one line.]
Within the standard main function, the connection to MySQL is established. The first line sets up the definition for MySQL,
with the variable mysql. The second line defines and names a result set based on the definitions for MYSQL_RES in mysql.h.
The results are to be stored in the array results, which will be an array of rows from MySQL. The third internal line uses
the definition for MYSQL_ROW to establish the variable row, which will be an array of columns from MySQL.
After setting up the header files and initial variables, we're ready to initialize MySQL:
if(!mysql_init(&mysql))
{
fprintf(stderr, "Cannot initialize MySQL");
exit();
}
In the if statement here, there's a reverse maneuver that could confuse beginners to programming. It's saying, "If we can't
initialize MySQL, then print a message stating that we cannot do it." The function mysql_init() initializes the connection
to MySQL using the variable mysql defined at the beginning of the main function. The exclamation point (!) just before the
mysql_init() function means "not" or "false". This is the confusing reversal that I mentioned. If C is not able to initialize
MySQL, then it is to perform the action contained in the curly braces (the statement block). In this case, it's instructed
to print or fprintf the error message that the server generates and would normally send to the standard error output
(i.e., STDERR: either a log or the screen) along with the message contained within quotes here. After printing the error
messages, C is to end the program – signified by exit(). If C is successful in initializing MySQL, however, then we can then
attempt to establish a connection to MySQL:
if(!mysql_real_connect(&mysql, "localhost",
"user", "password", "db1", 0, NULL, 0))
{
fprintf(stderr, "%d: %s \n",
mysql_errno(&mysql), mysql_error(&mysql));
exit();
}
The elements of the mysql_real_connect() function here are fairly obvious: name the database type as MySQL (as opposed to
PostgreSQL or something else) through the mysql variable, give the host name or IP address, give the user name and password,
and specify the database to use. As for the three remaining items, they are the port number, the Unix socket, and the client
flag. By giving zeros and NULL, we're essentially saying to use the defaults for these. Incidentally, we're using
mysql_real_query() here, but we could just as easily use mysql_query(). The main difference between the two is that
mysql_real_query will allow the retrieval of binary data, which may not be necessary, but it's safer to use this function.
If the script cannot connect, then it is to print the error message generated by the server (STDERR) along with the MySQL
error number (hence the %d format instruction for displaying digits or a number) and then a string (%s) containing the
MySQL error message and then a line feed or a new line (\n). The actual values to plug into the format follow, of course,
separated by commas.
SQL Statements
If the MySQL connection does not fail, the script can then confidently query the database with the mysql_query() function:
if(mysql_query(&mysql,
"SELECT rec_id, col1, col2 FROM table1"))
{
fprintf(stderr, "%d: %s\n",
mysql_errno(&mysql), mysql_error(&mysql));
}
else
{
result = mysql_store_result(&mysql);
while(row = mysql_fetch_row(result))
{
printf("%s (%s) - %s \n", row[1], row[0], row[2]);
}
mysql_free_result(result);
}
mysql_close(&mysql);
}
Here again, by using mysql_real_query(), we're using a function designed to handle binary data. If we know for sure that the
data does not contain any binary data, then we could use mysql_query() instead. Although the if statement above doesn't have
the negator (i.e., !), it still has an odd twist to its logic. Control statements usually look for the results of 0 or 1
(or something other than 0) to decide how to proceed. If the test criteria or condition returns a value of 1 when executed,
then the test is said to have succeeded. Therefore, what is contained in the statement block is to be performed. When an if
statement is coupled with an else statement, then if the test returns a 0, the else statement's block is to be performed:
if(test)
{
execute if test returns 0;
}
else
{
execute if test returns 1;
}
This is what one would normally expect from an if statement. What's odd is that the mysql_real_query() (and mysql_query())
returns 0 if it's not successful. So in this example, if the SQL statement does not succeed in selecting data from MySQL,
then 1 will be returned – which would normally mean success. Therefore, the if statement will print out of an error message.
However, if mysql_real_query() is successful, it will return a 0 (normally meaning failure) and then the contents of the
else statement will be executed. This is a little awkward. You just need to remember that the results are the opposite
for these functions.
As for the instruction within the else statement, the first line captures the results of the mysql_real_query(). That's the
role of mysql_store_result(): to get the results and to store them in an internal buffer temporarily for manipulation.
In this case, we're naming that space in memory, result. Later we free up the buffer when we issue a mysql_free_result()
with the variable name in the parenthesis.
Before we let go of the array of data, though, we need to loop through each row of it and display each for the user.
We do this with a while statement and the msqyl_fetch_row() function. This function retrieves one row at a time of the
results from MySQL and, in this particular script, it stores each row in the variable row. The while statement then prints
to the screen the value of each column in the format shown. Note that each column is extracted by typical array syntax
(i.e., array[n]). The formatting instructions for the printf are in doublequotes, the same method as with the fprintf in
the if statement earlier. Once while has gone through each row of the results, it will stop and then free up the buffer of
the data and the else statement will be concluded. We end this brief script with a mysql_close() to finish the MySQL session
and disconnect from MySQL. The final closing curly brace, of course, ends the main function.
Adding Data
To change data in MySQL with the C API, we use the mysql_query(). We can employ it either directly with an appropriate SQL
statement, or we can use a constant variable and insert it when needed within the parenthesis of mysql_query(). Below is
what an UPDATE statement might look like in a C script. Note that I've left out the usual MySQL initialization and connection
functions, as well as the error message instructions, to save space.
...
const char *sql_update =
"UPDATE table1 set col1 = 'text'
WHERE rec_id = '1000'";
... /* MySQL Initialization & Connection */
if(mysql_query(&mysql, sql_update))
{
fprintf(stderr, ...);
mysql_close(&mysql);
exit();
}
...
If the mysql_query() is successful in deploying the sql_update, then a 0 will be returned and nothing further will be done.
Otherwise, the fprintf will display an error message. If we wanted to give the user some feedback that the UPDATE statement
was successful, we could add an else statement that prints a message saying so.
To do an INSERT statement that uses variables instead of the actual values (e.g., 'text' or '1000'), we would follow the
INSERT syntax and just use variable names:
...
const char *sql_insert =
"INSERT INTO table1 (col1) VALUES (%s)";
char query[4096];
const char *colors[] = {"red", "yellow", "blue"};
uint nbr_colors = sizeof(colors)/sizeof(char *);
uint x;
... /* MySQL Initialization & Connection */
for(x = 1; x < nbr_colors; x++)
{
sprintf(query, sql_insert, colors[x]);
if(mysql_query(&mysql, query))
{
fprintf(stderr, "Could not insert row. %s %d: \%s \n",
query, mysql_errno(&mysql), mysql_error(&mysql));
mysql_close(&mysql);
exit();
}
printf("Row %d inserted \n", x);
...
In the SQL statement sql_insert, we've prepared the way with %s to indicate that a string value will be provided later as
the column's value. The next line sets up a character variable with a size of 4096 bytes or four kilobytes. This reserves
a temporary buffer that we will use later in our mysql_query(). Next, we create an array containing a list of primary
colors that are to be inserted into table1. Following this line, we declare another variable that counts the number of
elements in colors. This is unnecessary, of course, because we know that there are three colors. Nevertheless, it may be
useful if we were later to modify this script to allow the user to enter a list of colors. In a situation like this, we
would not know how many colors or elements the user will enter and would need C to count them. The final variable declaration
is for x, which we will use in a for statement later. Note that all of the variables must be declared before initializing
MySQL.
In this script excerpt, we're using a for statement to loop through the array of colors to insert them into the table one at
a time. The variable x is set to 0 to start. The test condition is to check whether x is less than the number of colors in
the array. Otherwise, the script is to execute the statement block and increment the x counter (x++) by 1 when finishing each
loop. With the sprintf statement, we're able to reuse the INSERT statement. Next, the if statement attempts to execute query.
If it's unable to do so, an error message is given and the session is closed. If the query is accomplished, a message is
displayed saying that array element number x was inserted into the table in MySQL.