This section presents the basic tasks you need to perform use the C API.
When writing a C program to interact with MySQL, first make a connection to MySQL. To do this easily, you need to include a couple of C header files: stdio.h for basic C functions and variables, and mysql.h for special MySQL functions and definitions. These two files come with C and MySQL, respectively; you shouldn’t have to download them from the Web if both were installed properly.
#include <stdio.h> #include "/usr/include/mysql/mysql.h" int main(int argc, char *argv[ ]) { MYSQL *mysql; MYSQL_RES *result; MYSQL_ROW row;
Because of the < and > symbols surrounding stdio.h, C is instructed to look for it in the default location for C header files (e.g., /usr/include), or in the user’s path. Because mysql.h may not be in the default locations, with the aide of double quotes, the absolute path is given. An alternative here would have been <mysql/mysql.h>, because the header file is in a subdirectory of the default directory.
Within the standard main
function just shown, the
connection to MySQL is established. The first line sets up the
definition for MySQL, with mysql
, which will be
referenced by mysql
later. The second line defines
and names a results set based on the definitions for
MYSQL_RES
in mysql.h. The
results are to be stored in the result
array, which will be an array of rows from MySQL. The
third line of main
uses the definition for
MYSQL_ROW
to establish the
row
variable, which will be
used later to contain an array of columns from MySQL.
Having set up the header files and initial
variables, set up an object in memory for interacting with the MySQL
server using mysql_init( )
:
if(mysql_init(mysql) = = NULL) { fprintf(stderr, "Cannot initialize MySQL"); exit( ); }
The if
statement here is testing whether a MySQL
object can be initialized. If the initialization fails, a message is
printed and the program ends. The mysql_init( )
function initializes the MySQL object using the MYSQL
structure declared at the beginning of the main function called by
convention, mysql
. If C is successful in
initializing the object, it will go on to attempt to establish a
connection to MySQL:
if(!mysql_real_connect(mysql, "localhost", "user", "password", "db1", 0, NULL, 0)) { fprintf(stderr, "%d: %s ", mysql_errno(mysql), mysql_error(mysql)); exit(EXIT FAILURE); }
The elements of the
mysql_real_connect( )
function here are fairly
obvious: first the MySQL object is referenced; next the hostname or
IP address; then the username and password; and finally the database
to use. The three remaining items are the port number, the Unix
socket filename, and a client flag, if any. Passing zeros and NULL
tells the function to use the defaults for these. If the program
cannot connect, it is to print the error message generated by the
server to the standard error stream, along with the MySQL error
number (hence the %d
format instruction for
displaying digits or a number) and finally a string
(%s
) containing the MySQL error message and then a
line feed or a newline (
). The actual values to
plug into the format follow, separated by commas.
The program so far only makes a connection to MySQL. Now let’s look at how you can add code to the program to run an SQL statement with the C API.
If the
MySQL connection portion of the program is successful, the program
can query the MySQL server with a query function such as
mysql_query( )
:
if(mysql_query(mysql, "SELECT col1, col2 FROM table1")) { fprintf(stderr, "%d: %s ", mysql_errno(mysql), mysql_error(mysql)); } else { result = mysql_store_result(mysql); while(row = mysql_fetch_row(result)) { printf("\%d - \%s ", row[0], row[1]]); } mysql_free_result(result); } mysql_close(mysql); exit(EXIT SUCCESS); }
Incidentally, this excerpt is using
mysql_query( )
, but you could use the
mysql_real_query( )
function instead. The main
difference between the two is that mysql_real_query()
allows the retrieval of binary data, which may not be
necessary, but it’s safer to use.
mysql_query( )
returns 0 if it’s
not successful, so if the preceding SQL statement does not succeed in
selecting data from MySQL, an error message will be printed. However,
if the query is successful, it will return a 0 and the
else
statement to be executed. In the
else
statement block, the first line captures the
results of the query and stores them in memory with the use of the
mysql_store_result( )
function. Later, the memory
will be freed when mysql_free_result( )
is issued
with the variable name result
in the parentheses.
Before
letting go of the data, though, you must loop through each row of it
and display results from each row for the user. Do this with a
while
statement and the mysql_fetch_row()
function. This function retrieves one row of the results
at a time and in this particular program, it stores each row in the
row
variable. Then the
printf
statement prints to the screen the value of
each field in the format shown. Notice that each field is extracted
by typical array syntax (i.e., array [n]). The
formatting instructions for printf
are in double
quotes, the same method as with fprintf
in the
if
statement earlier. Once C has gone through each
row of the results, it will stop and then free up the buffer of the
data, concluding the else
statement. This brief
program ends with a mysql_close( )
call to finish
the MySQL session and to disconnect from MySQL. The final closing
curly brace ends the main
function.
To compile the program with the GNU C Compiler (gcc), you can enter something such as the following from the command line:
gcc -o mysql_c_prog mysql_c_prog.c -L/usr/include/mysql -L/usr/lib/mysql -lmysqlclient
Notice that the paths to the MySQL header file and the MySQL data directory are given as well, and the name of the client library, mysqlclient, is also given. These paths may be different on your system. When the compiler attempts to compile the program (mysql_c_prog.c here), it will check for syntax errors in the code. If it finds any, it will fail to compile and will display error messages. If its successful, the resulting compiled program (mysql_c_prog) may be executed.
3.138.125.2