MySQL in a Nutshell (2008)
Part IV. APIs and Connectors
This part of the book is a complete reference to database interaction using the most popular languages used with MySQL. Libraries have been created for each language that allow you to connect to a MySQL database and issue SQL statements against it. These permit MySQL to be a backend to other programs or web sites and to hide SQL behind domain-specific, friendly interfaces.
Chapter 17. C API
This chapter covers the C API provided by MySQL. The first part provides a basic tutorial on how to connect to MySQL and how to query MySQL with C and the C API. Following the tutorial is an alphabetical listing of MySQL functions in the C API with explanations and, in most cases, examples. At the end of this chapter is a listing of special data types for the C API. For the examples in this chapter, I have used a database for a fictitious computer support business. The database contains one table with client work requests (workreq) and another with client contact information (clients).
Using C with MySQL
This section presents the basic tasks you need to use the C API.
Connecting to MySQL
When writing a C program to interact with MySQL, you first need to prepare variables that will store data necessary for a MySQL connection and query results, and then you need to establish a connection to MySQL. To do this easily, you need to include a couple of C header files (as shown in the code example): 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 stdio.h is surrounded by < and > symbols, 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, the absolute path is given with the aid of double quotes. An alternative here would be <mysql/mysql.h> because the header file is in a subdirectory of the default directory.
Within the standard main function just shown, variables needed for the connection to MySQL are prepared. The first line creates a pointer to the MYSQL structure stored in the mysql variable. The next line defines and names a results set based on the definitions for MYSQL_RES in mysql.h. The results are 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 included the header files and set the initial variables, we can now 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");
return 1;
}
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, calledmysql by convention. 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 \n",
mysql_errno(mysql), mysql_error(mysql));
return 1;
}
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 (\n). 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.
Querying MySQL
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\n",
mysql_errno(mysql), mysql_error(mysql));
}
else
{
result = mysql_store_result(mysql);
while(row = mysql_fetch_row(result))
{ printf("\%d - \%s \n", row[0], row[1]); }
mysql_free_result(result);
}
mysql_close(mysql);
return 0;
}
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 is safer to use. mysql_query()returns zero if it’s successful and nonzero 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, the else statement will be executed because the if statement will have received a value of 0 from mysql_query(). 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 given.
Before letting go of the data, though, we must loop through each row of the results set and display the results from each row for the user. We’ll 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 example program, 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 enclosed within double quotes, the same method we used with the fprintf in the if statement earlier in this section. Once C has gone through each row of the results, it will stop processing and then free up the buffer of the data, concluding the else statement. This brief program ends with amysql_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 like the following from the command line:
gcc -o mysql_c_prog mysql_c_prog.c \
-I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lm -lz
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 (here, mysql_c_prog.c), it will check for syntax errors in the code. If it finds any, it will fail to compile and will display error messages. If it’s successful, the resulting compiled program (mysql_c_prog) may be executed.
Functions in Alphabetical Order
The bulk of this chapter consists of a list of C API functions in alphabetical order. Each function is given with its syntax and an explanation. For almost all functions, an example program or excerpt is provided to show how you can use the function. To save space, almost all of the excerpts are shown without the lines of code necessary to start a C program and to connect to MySQL, nor those necessary to close the connection and to end the program. For an example of how you would write opening and closing lines, see the tutorial in the previous section. The examples in this section tend to be more succinct and won’t usually include typical error checking. It’s assumed that the reader has a basic understanding of C. For the syntax of each function, the data type expected is given before each parameter or argument.
Name
mysql_affected_rows()
Synopsis
my_ulonglong mysql_affected_rows(MYSQL *mysql)
This function returns the number of rows affected by the most recent query for the current session. This function is meaningful only for INSERT, UPDATE, and DELETE statements. For SQL statements that don’t affect rows (e.g., SELECT), this function will return 0. For errors, it will return –1. Here is an example:
...
mysql_query(mysql,"UPDATE workreq
SET tech_person_id = '1015'
WHERE tech_person_id = '1012'");
my_ulonglong chg = mysql_affected_rows(mysql);
printf("Number of requests reassigned: %ul \n", chg);
...
In this example, an UPDATE statement is issued and the number of rows changed is extracted with the function and stored in the chg variable, which is then printed. For REPLACE statements, rows that are replaced are counted twice: once for the deletion and once for the insertion.
Name
mysql_autocommit()
Synopsis
my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)
Use this function to turn on or off autocommit mode. A value of 1 for the second argument of this function turns on the server’s autocommit mode. A value of 0 turns it off. The autocommit causes the server to update the database after each INSERT, UPDATE, or DELETE statement, essentially running each in its own transaction. The default is on. Here is an example:
...
mysql_autocommit(mysql, 0);
...
Name
mysql_change_user()
Synopsis
my_bool mysql_change_user(MYSQL *mysql, const char *user,
const char *password, const char *database)
Use this function to change the current user for the MySQL session to the one given as the second argument. The password of the new user is given in the third argument. Since this function will end the current session if successful, it will need to reset the default database. Therefore, a database that it should use for the new connection is to be given as the fourth argument. Here is an example:
...
mysql_real_connect(mysql,"localhost","hui","shorty","test","3306",NULL,0);
mysql_select_db(mysql,"workrequests");
...
mysql_change_user(mysql,"russell","password","workrequests");
mysql_query(mysql, "UPDATE workreq
SET tech_person_id = '1015'
WHERE tech_person_id = '1012'");
...
In this example, the program begins with one user for running SQL statements, which are replaced with ellipses. However, for changing a sensitive data column (i.e., the person assigned to perform the work requests), the user is changed to one who has been given the proper authorization to access.
Name
mysql_character_set_name()
Synopsis
const char *mysql_character_set_name(MYSQL *mysql)
This function returns the name of the default character set in use by the MySQL server. Here is an example:
...
MYSQL *mysql;
const char *char_set;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","russell","my_pwd","test","3306",
NULL,0);
char_set = mysql_character_set_name(mysql);
printf("Character Set: %s \n", char_set);
...
To get just the character set name, it’s not necessary to select a database. Here are what the results of running this program might look like:
Character Set: latin1
Name
mysql_close()
Synopsis
void mysql_close(MYSQL *mysql)
Use this function to close the connection to the MySQL server. It also deallocates the connection handle pointed to by MYSQL if the handle was allocated automatically by mysql_init() or mysql_connect(). It does not return a value. Here is an example:
...
mysql_connect(mysql,"localhost","ricky","adams");
...
mysql_close(mysql);
...
Name
mysql_commit()
Synopsis
my_bool mysql_commit(MYSQL *mysql)
Use this function to commit the current transaction. After this function is executed, INSERT, UPDATE, and DELETE statements are written to the database, and you cannot use the mysql_rollback() function to undo them. The function returns 0 if successful, a non-zero value if unsuccessful. If mysql_autocommit(mysql, 1) is used previously, this function does nothing and the return of the function is not relevant. Here is an example:
mysql_commit(mysql);
Name
mysql_connect()
Synopsis
MYSQL *mysql_connect(MYSQL *mysql, const char *host,
const char *user, cont char *password)
This function is deprecated in favor of mysql_real_connect(), described later in this chapter.
Name
mysql_create_db()
Synopsis
int mysql_create_db(MYSQL *mysql, const char *database)
This function can be used to create a new database on the MySQL server, with the new database name given as the second argument. However, this function has been deprecated. Instead, a CREATE DATABASE statement should be given with mysql_query() or mysql_real_query().
Name
mysql_data_seek()
Synopsis
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)
Use this function in conjunction with mysql_store_result() and a fetch function such as mysql_fetch_row() to change the current row being fetched to the one specified in the second argument of this function. Here is an example:
...
mysql_query(mysql, "SELECT client_id, client_name
FROM clients ORDER BY start_date");
result = mysql_store_result(mysql);
num_rows = mysql_num_rows(result);
mysql_data_seek(result, (num_rows - 8));
while((row = mysql_fetch_row(result)) != NULL)
{ printf("%s (%s) \n", row[1], row[0]); }
...
This program excerpt retrieves a list of client names along with their respective IDs. Using the mysql_data_seek() function in conjunction with mysql_fetch_row() and a while statement, the last eight clients who started with the company will be displayed.
Name
mysql_debug()
Synopsis
void mysql_debug(const char *debug)
Use this function to set debugging if the client was compiled with debugging. The set of options used by default is 'd:t:o,logname'. See Table 16-1 at the end of the list of options for the mysqldump utility in Chapter 16 for an explanation of these flags and others that may be used. Here is an example:
...
mysql_debug("d:t:o,filename");
...
The filename given could include the path to the log file where debugging information is to be written.
Name
mysql_drop_db()
Synopsis
int mysql_drop_db(MYSQL *mysql, const char *database)
This function may be used to delete the database named in the second argument of the function from the MySQL server. It returns 0 if successful and a nonzero value if not. However, this function has been deprecated. Use mysql_query() or mysql_real_query() with a DROP DATABASEstatement instead. Here is an example:
...
mysql_real_connect(mysql,host,user,password,NULL,0,NULL,0);
...
mysql_drop_db(mysql, "db5");
...
This returns a nonzero value if it fails, so a program that uses it should include error checking for the function.
Name
mysql_dump_debug_info()
Synopsis
int mysql_dump_debug_info(MYSQL *mysql)
Use this function to write debugging information about the current connection to the MySQL server’s log file. It returns 0 if successful and a nonzero value if not. The user must have administrative privileges. Here is an example:
...
if(!mysql_dump_debug_info(mysql))
{ printf("Debugging Info. Written. \n"); }
...
Name
mysql_eof()
Synopsis
my_bool mysql_eof(MYSQL *result)
Use this function to determine whether the last row of the results set has been fetched. It returns 0 until end of file is reached and a nonzero value at end of file. This function has been deprecated. Use mysql_errno() and mysql_error(), or mysql_more_results(), instead to check for an error indicating that the last row has been reached.
Name
mysql_errno()
Synopsis
unsigned int mysql_errno(MYSQL *mysql)
This function returns the error number for the last function that was run if it failed to execute. If the last function executed was successful, a value of 0 is returned by this function. Here is an example:
...
if(mysql_real_connect(mysql,host,"goofy",
password,database,0,NULL,0) == NULL)
{
printf("Error %d \n", mysql_errno(mysql));
return 1;
}
...
The program here is attempting to connect to the MySQL server for a user who is not in the mysql database.
Name
mysql_error()
Synopsis
char *mysql_error(MYSQL *mysql)
This function returns the error message for the last function that was run if it failed to execute. If the last function executed was successful, an empty string is returned by this function. Here is an example:
...
if(!mysql_real_connect(mysql,host,"goofy",
password,database,0,NULL,0))
{
printf("Error Message: %s \n", mysql_error(mysql));
return 1;
}
...
The program here is attempting to connect to the MySQL server with a user who is not in the mysql database.
Name
mysql_escape_string()
Synopsis
unsigned int mysql_escape_string(char *destination,
const char *source,
unsigned int length)
This function returns a string given as the second argument with special characters escaped by adding backslashes in front of them. However, this function is a security problem and has been deprecated. Use the mysql_real_escape_string() function instead; it does this job properly and safely.
Name
mysql_fetch_field()
Synopsis
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
This function returns a MYSQL_FIELD structure that provides information on a given field of a results set. If you use it in conjunction with a loop statement, you can extract information on each field. Here is an example:
...
MYSQL_FIELD *field;
...
mysql_query(mysql, "SELECT * FROM clients LIMIT 1");
result = mysql_store_result(mysql);
while((field = mysql_fetch_field(result)) != NULL)
{ printf("%s \n", field->name); }
...
The wildcard in the SELECT statement selects all columns in the table. The loop therefore lists the name of each column. The other possibilities are field->table for the table name and field->def for the default value of the column.
Name
mysql_fetch_field_direct()
Synopsis
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result,
unsigned int field_nbr)
This function returns a MYSQL_FIELD structure that provides information on a given field of a results set referred to in the first argument of the function. The particular field is given as the second argument. Here is an example:
...
MYSQL_FIELD *field;
...
mysql_query(mysql, "SELECT * FROM clients LIMIT 1");
result = mysql_store_result(mysql);
field = mysql_fetch_field_direct(result, 0);
printf("%s \n", field->name);
...
This function is similar to mysql_fetch_field() except that information on just one specified field can be obtained. In the example here, the name of the first field (0 being the first) will be displayed.
Name
mysql_fetch_fields()
Synopsis
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)
This function returns an array of information about the fields in a results set. Here is an example:
...
mysql_query(mysql, "SELECT * FROM clients");
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
MYSQL_FIELD *field;
field = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{ printf("%u.%s \n", i, &field[i].name); }
...
In addition to the .name key to extract the column name, a program can specify .table for the table name and .def for the default value of the column.
Name
mysql_fetch_lengths()
Synopsis
unsigned long *mysql_fetch_lengths(MYSQL *result)
This function returns the length of each column within a particular row of a results set. The values returned can vary for each row fetched, depending on the data contained in the columns. Here is an example:
...
mysql_query(mysql, "SELECT * FROM clients");
result = mysql_store_result(mysql);
row = mysql_fetch_row(result);
unsigned int num_fields = mysql_num_fields(result);
unsigned long *lengths = mysql_fetch_lengths(result);
for(i = 0; i < num_fields; i++)
{
field = mysql_fetch_field(result);
printf("%s %lu \n", field->name, lengths[i]);
}
...
This example retrieves one row of the results and checks the lengths of the fields in that row. To retrieve each field, the SELECT statement would need to be altered and a while statement would be wrapped around the for statement to loop through each row.
Name
mysql_fetch_row()
Synopsis
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
Use this function to retrieve the next row of a results set. When there are no more rows to retrieve, the function returns NULL. Here is a fairly complete example using this function:
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
int main( )
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
int i, num_fields;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","user","password",
"workrequests",0,NULL,0);
mysql_query(mysql,"SELECT * FROM users");
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{
field = mysql_fetch_field_direct(result, i);
printf("%s: %s, ", field->name, row[i]);
}
printf("\n");
}
mysql_free_result(result);
mysql_close(mysql);
return 0;
}
Although this example is a complete program, it’s missing the usual error checking methods.
Name
mysql_field_count()
Synopsis
unsigned int mysql_field_count(MYSQL *mysql)
This function returns the number of columns in a results set. You can also use it to test whether there was an error in a SELECT query. A SELECT query will return at least one blank field when there is an error, resulting in a value of 0 for the function. Here is an example:
...
if(!result)
{
if(mysql_field_count(mysql) == 0)
{
printf("Error \n");
return 1;
}
}
...
See the entry for the mysql_fetch_row() function earlier in this section for another example involving this function.
Name
mysql_field_seek()
Synopsis
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result,
MYSQL_FIELD_OFFSET offset)
Use this function in conjunction with mysql_fetch_field() to change the current field being fetched to the one specified in the second argument of this function. The function returns the offset of the field that was current before the function was invoked. A reference to the results set must be passed as the first argument. Here is an example:
...
mysql_query(mysql, sql_stmnt);
MYSQL_FIELD_OFFSET offset = 2;
mysql_field_seek(result, offset);
while((field = mysql_fetch_field(result)) != NULL)
{
printf("%d: %s \n", mysql_field_tell(result), field->name);
}
...
Using mysql_field_seek() here and an offset of 2, the first two rows of the results set are skipped. The mysql_field_tell() function is used to ascertain the index of the field being displayed within each loop of the while statement. The mysql_field_seek() function will return the offset prior to invoking the function. If you change the mysql_field_seek() call in the program to the following, the old_offset variable would contain a value of 0, the starting point for a row:
...
MYSQL_FIELD_OFFSET old_offset = mysql_field_seek(result, offset);
...
You can use this for recording a point in a results set before moving the pointer. The program can later return to that point using the old offset.
Name
mysql_field_tell()
Synopsis
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)
This function returns the value of the field pointer for the current row in use by a fetch function such as mysql_fetch_field(). The field pointer starts at 0 for the first field when a row is retrieved and advances by one as each field is retrieved in sequential order. Seemysql_field_seek() earlier in this section for an example of this function.
Name
mysql_free_result()
Synopsis
void mysql_free_result(MYSQL_RES *result)
Use this to free memory allocated by a function such as mysql_store_result() in which a MYSQL_RES element was employed to store a results set. Here is an example:
...
result = mysql_query(mysql, sql_stmnt);
...
mysql_free_result(result);
...
Not freeing allocated memory or attempting to access allocated memory after it’s freed can cause problems.
Name
mysql_get_client_info()
Synopsis
char *mysql_get_client_info(void)
This function returns the client library version. Here is an example:
...
const char *info;
info = mysql_get_client_info( );
printf("Client Library Version: %s \n", info);
...
Name
mysql_get_character_set_info()
Synopsis
void mysql_get_character_set_info(MYSQL *mysql, MY_CHARSET_INFO *cs)
This function returns the default character set information for the database given. It uses the MY_CHARSET_INFO structure, so the information may be retrieved with extensions like so:
...
if (!mysql_set_character_set(mysql, "utf8"))
{
MY_CHARSET_INFO ch_set;
mysql_get_character_set_info(mysql, &ch_set);
printf("Character Set: %s\n", ch_set.name);
printf("Collation: %s\n", ch_set.csname);
printf("Minimum Length for Multibyte Character: %d\n", ch_set.mbminlen);
printf("Maximum Length for Multibyte Character: %d\n", ch_set.mbmaxlen);
printf("Comment: %s\n", ch_set.comment);
printf("Directory: %s\n", ch_set.dir);
}
...
Here are the results of this code excerpt:
Character Set: utf8_general_ci
Collation: utf8
Minimum Length for Multibyte Character: 1
Maximum Length for Multibyte Character: 3
Comment: UTF-8 Unicode
Directory: (null)
Name
mysql_get_client_version()
Synopsis
unsigned long *mysql_get_client_version(void)
This function returns the client library version in a numeric format. For example, for version 4.1.7, the function will return 40107. Here is an example:
...
unsigned long version;
version = mysql_get_client_version( );
printf("Client Version: %d \n", version);
...
Name
mysql_get_host_info()
Synopsis
char *mysql_get_host_info(MYSQL *mysql)
This function returns the hostname and the connection type for the current connection. Here is an example:
...
MYSQL *mysql;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","marie","password",
NULL,0,NULL,0);
printf("Host Info: %s \n", mysql_get_host_info(mysql));
mysql_close(mysql);
...
The results of this program excerpt will look something like the following:
Host Info: Localhost via UNIX socket
Name
mysql_get_proto_info()
Synopsis
unsigned int mysql_get_proto_info(MYSQL *mysql)
This function returns the protocol version for the current connection. Here is an example:
...
MYSQL *mysql;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","root","password",
NULL,0,NULL,0);
printf("Protocol: %u \n", mysql_get_proto_info(mysql));
mysql_close(mysql);
...
Name
mysql_get_server_info()
Synopsis
char *mysql_get_server_info(MYSQL *mysql)
This function returns a string containing the version of MySQL running on the server for the current connection. Here is an example:
...
MYSQL *mysql;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","root","password",
NULL,0,NULL,0);
printf("Server Version: %s \n", mysql_get_server_info(mysql));
mysql_close(mysql);
...
Name
mysql_get_server_version()
Synopsis
unsigned long mysql_get_server_version(MYSQL *mysql)
This function returns the version of the server for the current connection in a numeric format. For example, for version 4.1.7, the function will return 40107. Here is an example:
...
MYSQL *mysql;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","root","password",
NULL,0,NULL,0);
printf("Server Version: %ul \n",
mysql_get_server_version(mysql));
mysql_close(mysql);
...
Name
mysql_get_ssl_cipher()
Synopsis
const char *mysql_get_ssl_cipher(MYSQL *mysql)
This function returns a string with the name of the SSL cipher that was used for the connection given. NULL is returned if there was no cipher used. This function was added as of version 5.1.11 of MySQL. Here is an example:
...
const char *cipher_name;
cipher_name = mysql_get_ssl_cipher( );
printf("Name of Cipher: %s \n", cipher_name);
...
Name
mysql_hex_string()
Synopsis
unsigned long mysql_hex_string(char *to, const char *from, unsigned long length)
This function translates a hexadecimal string to a format that can be used in an SQL statement. The hexadecimal string is to be given in the from position or variable of the function. The results are saved to the to variable named, and terminated with a NULL byte. The length is the length of bytes of the from value. The to variable needs to be the length times 2 plus 1 in length.
Name
mysql_info()
Synopsis
char *mysql_info(MYSQL *mysql)
This function returns a string containing information provided by MySQL when certain SQL statements are executed. This function works with only five types of SQL statements: INSERT INTO...SELECT..., INSERT INTO... VALUES..., LOAD DATA INFILE, ALTER TABLE, andUPDATE. For all other statements, this function typically returns NULL. Here is an example:
...
mysql_query(mysql, "UPDATE clients
SET telephone_areacode = '985'
WHERE city = 'Hammond'");
printf("Query Info: %s \n", mysql_info(mysql));
...
The results of this program excerpt will look like the following:
Query Info: Rows matched: 3 Changed: 3 Warnings: 0
Name
mysql_init()
Synopsis
MYSQL *mysql_init(MYSQL *mysql)
This function optionally allocates, and then initializes, a MYSQL object suitable for connecting to a database server and subsequently performing many of the other operations described in this chapter. If the function’s parameter is NULL, the library allocates a new object from the heap; otherwise, the user’s pointed-to local MYSQL object is initialized.
The return value is a pointer to the object, however obtained, and a NULL indicates a failure of allocation or initialization. Calling mysql_close() with this pointer not only releases the connection-related resources, but also frees the object itself if the library had allocated it in the first place.
It’s generally safer to allow the library to allocate this object rather than to do so yourself. It avoids hard-to-debug complications that can arise if certain compiler options are not in effect while building the application as they were when building the library.
Although this function prepares a handle for a database connection, no connection is attempted. Here is an example:
...
MYSQL *mysql;
if(mysql_init(mysql) == NULL)
{
printf("Could not initialize MySQL object. \n");
return 1;
}
...
Name
mysql_insert_id()
Synopsis
my_ulonglong mysql_insert_id(MYSQL *mysql)
This function returns the identification number issued to the primary key of the last record inserted using INSERT in MySQL for the current connection. This works provided the column utilizes AUTO_INCREMENT and the value was not manually set. Otherwise, a value of 0 is returned. Here is an example:
...
const char *sql_stmnt = "INSERT INTO workreq
(req_date, client_id, description)
VALUES(NOW( ), '1000', 'Net Problem')";
mysql_query(mysql, sql_stmnt);
my_ulonglong wr_id = mysql_insert_id(mysql);
printf("Work Request ID: %ld \n", wr_id);
...
Name
mysql_kill()
Synopsis
int mysql_kill(MYSQL *mysql, unsigned long identifier)
Use this function to terminate a thread on the server. The thread identifier is passed as the second argument to the function. If you’re attempting to kill the current connection, you can use the mysql_thread_id() function with the session handle. Here is an example:
...
if(!mysql_kill(mysql, mysql_thread_id(mysql)))
{ printf("Terminated Current Thread. \n"); }
...
To kill a thread other than the current one, you can use the mysql_list_processes() function to list all threads to determine which one to terminate.
Name
mysql_library_end()
Synopsis
void mysql_library_end(void)
Use this function to close the MySQL library after disconnecting from the server. It can free memory and can be used with either the normal client library or the embedded server library. It’s used in conjunction with mysql_library_init().
Name
mysql_library_init()
Synopsis
int mysql_library_init(int argc, char **argv, char **groups)
Use this function to initialize the MySQL library and any related libraries and systems before making any other MySQL function calls. It can be used with both the normal client library or the embedded server library. This function is used within a multithreaded environment. Otherwise, it’s not necessary and mysql_init() is sufficient. When finished, use mysql_library_end() to close the library. This function returns zero if successful, nonzero if not.
Here is an example:
...
static char *server_args[] = {
"--datadir='/data'",
"--key_buffer_size=32M"
};
static char *server_groups[] = {
"embedded",
"server",
(char *)NULL
};
int main(int argc, char *argv[ ]) {
if(mysql_library_init(sizeof(server_args) / sizeof(char *),
server_args, server_groups)) {
fprintf(stderr, "Cannot initialize MySQL library \n");
return 1;
}
...
mysql_library_end();
...
}
Name
mysql_list_dbs()
Synopsis
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
This function returns a results set containing a list of databases found for the current connection. An expression may be given to select databases whose names match a certain pattern. The % or _ characters may be used as wildcards. If NULL is given for the second argument, the names of all databases on the server will be selected in the results set. Here is an example:
...
MYSQL_RES *result;
MYSQL_ROW row;
...
result = mysql_list_dbs(mysql, NULL);
while((row = mysql_fetch_row(result)) != NULL)
{ printf("%s \n", row[0]); }
mysql_free_result(result);
...
This excerpt extracts a list of databases from the server using the mysql_list_dbs() function and stores the results. Using the mysql_fetch_row() function, each row of the results set is stored temporarily for printing. To extract a list of databases with “work” in the name, replace NULL with "%work%". As with all results sets, release the resources with mysql_free_result() when finished.
Name
mysql_list_fields()
Synopsis
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table,
const char *wild)
This function returns a results set containing a list of fields found for the table given as the second argument of the function. An expression may be given as the third argument to select fields whose names match a certain pattern. The % or may be used as wildcards. If NULL is given for the third argument, all fields for the table are returned. The results set must be freed when finished.
Here is an example:
...
result = mysql_list_fields(mysql, "stores", "s%");
num_rows = mysql_num_rows(result);
printf("Rows: %d \n", num_rows);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_rows; i++)
{ printf("%s \n", row[i]); }
}
mysql_free_result(result);
...
Name
mysql_list_processes()
Synopsis
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
This function returns a results set containing a list of MySQL server processes or server threads found for the handle given as the argument of the function.
Here is an example:
...
result = mysql_list_processes(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
printf("Thread ID: %s \n", row[0]);
printf("User: %s, Host: %s \n", row[1], row[2]);
printf("Database: %s, Command: %s \n", row[3], row[4]);
printf("Time: %s, State: %s, Info: %s \n\n",
row[5],row[6],row[7]);
}
mysql_free_result(result);
...
Using the mysql_fetch_row() function, each row of the results set is read and each field is displayed with its related label. The results are the same as the SHOW PROCESSES query in MySQL. It’s important to run the mysql_free_result() function when finished with a results set, as shown here.
Name
mysql_list_tables()
Synopsis
MYSQL_RES *mysql_list_tables(MYSQL *mysql,
const char *expression)
This function returns a results set containing a list of tables in the currently selected database. An expression may be given as the second argument of the function to select tables whose names match a certain pattern. The % or _ may be used as wildcards. If NULL is given for the second argument, all tables in the database will be returned. Here is an example:
...
MYSQL_RES *result;
MYSQL_ROW row;
...
result = mysql_list_tables(mysql, "w%");
while((row = mysql_fetch_row(result)) != NULL)
{ printf("%s \n", row[0]); }
mysql_free_result(result);
...
This excerpt extracts a list of tables beginning with the letter “w” using the mysql_list_tables() function and stores the results in the result variable. Using the mysql_fetch_row() function, each row of the results set is stored temporarily in the row variable for printing.
Name
mysql_more_results()
Synopsis
my_bool mysql_more_result(MYSQL *mysql)
Use this function to determine whether more results remain in a results set when using the mysql_next_result() function to retrieve data. It returns 1 if there are more results, and 0 if not.
Name
mysql_next_result()
Synopsis
int mysql_next_result(MYSQL *mysql)
Use this function to read the next row of data from a results set. It returns 0 if successful and if there are more results to retrieve, and –1 if it was successful in retrieving data, but there are no further rows to retrieve. It returns an error (or a value greater than 0) if it’s unsuccessful because the results set was not loaded with the data. You can use the mysql_more_results() function to check for more results before invoking this function.
Name
mysql_num_fields()
Synopsis
unsigned int mysql_num_fields(MYSQL_RES *result)
This function returns the number of fields in each row of a results set. It is similar to mysql_field_count() except that that function operates on the MYSQL handle and not the results set. Here is an example:
...
unsigned int num_fields = mysql_num_fields(result);
...
See mysql_fetch_lengths() earlier in this section for a more elaborate example that uses this function.
Name
mysql_num_rows()
Synopsis
int mysql_num_rows(MYSQL_RES *result)
This function returns the number of rows in the results set when issued after the mysql_store_result() function. When issued after mysql_use_result(), it returns the number of rows already fetched. Here is an example:
...
my_ulonglong num_rows = mysql_num_rows(result);
...
See mysql_list_fields() earlier in this section for a more elaborate example that uses this function.
Name
mysql_options()
Synopsis
int mysql_options(MYSQL *mysql, enum mysql_option option,
const char *value)
Use this function to set connection options before a connection has been established using a function such as mysql_real_connect() or mysql_connect(). This function may be used multiple times to set additional options before connecting. For the second argument of the function, you may give specific options for the connection. You may give a value associated with the chosen option for the third argument. Here is an example:
...
mysql = mysql_init(NULL);
mysql_options(mysql, MYSQL_OPT_COMPRESS, NULL);
mysql_real_connect(mysql,host,user,password,NULL,0,NULL,0);
...
The options permitted for the second argument of the function follow, along with the type of variable or value for the third argument in parentheses and a brief explanation of each:
MYSQL_OPT_CONNECT_TIMEOUT (unsigned int *)
This option sets the number of seconds for connection timeout.
MYSQL_OPT_READ_TIMEOUT (unsigned int *)
This option sets the timeout for reads from a Windows MySQL server.
MYSQL_OPT_WRITE_TIMEOUT (unsigned int *)
This option sets the timeout for writes to a Windows MySQL server.
MYSQL_OPT_COMPRESS (NULL)
This option compresses communications between the client and server if supported by both.
MYSQL_OPT_LOCAL_INFILE (pointer to unsigned integer)
This option runs on a file pointed to in the argument. If the pointer is NULL, the LOAD LOCAL INFILE statement is run when connecting.
MYSQL_OPT_NAMED_PIPE (NULL)
This option instructs the client to use named pipes for connecting to a Windows NT MySQL server.
MYSQL_INIT_COMMAND (char *)
This option instructs the server on connecting to execute an initial SQL statement given as the third argument to the function.
MYSQL_READ_DEFAULT_FILE (char *)
This option instructs the server to read a configuration text file named in the third argument of the function instead of the default my.cnf configuration file for the client.
MYSQL_READ_DEFAULT_GROUP (char *)
This option instructs the server to read a server section or group (e.g., [special_client]) from either the default my.cnf configuration file or the one specified by the MYSQL_READ_DEFAULT_FILE option to this function.
MYSQL_OPT_PROTOCOL (unsigned int *)
This option specifies the default protocol for communicating with the server.
MYSQL_SHARED_MEMORY_BASE_NAME (char *)
This option names the shared memory object for connecting to the server.
Name
mysql_ping()
Synopsis
int mysql_ping(MYSQL *mysql)
Use this function to determine whether the current MYSQL connection is still open. If it’s not open, the function attempts to reestablish the connection. If the connection is open or is reestablished, zero is returned. Otherwise, a nonzero value is returned. Here is an example:
...
MYSQL *mysql;
int main( )
{
...
test_connection( );
mysql_close(mysql);
test_connection( );
}
test_connection( )
{
int live;
live = mysql_ping(mysql);
if(live){ printf("Connection not alive. \n"); }
else { printf("Connection alive. \n"); }
}
This excerpt employs a user function to test for a MySQL connection.
Name
mysql_query()
Synopsis
int mysql_query(MYSQL *mysql, const char *query)
Use this function to execute the SQL query given as the second argument of the function. Only one SQL statement may be given. For queries containing binary data, use the mysql_real_query() function instead. This function will return zero if successful, and a nonzero value if not. Here is an example:
...
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
int i, num_fields;
...
mysql = mysql_init(NULL);
mysql_real_connect(mysql,host,user,password,database,0,NULL,0);
const char *sql_stmnt = "SELECT * FROM workreq";
mysql_query(mysql, sql_stmnt, bytes);
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{ printf("%s, ", row[i]); }
printf("\n");
}
mysql_free_result(result);
mysql_close(mysql);
...
Although this example is fairly complete, the lines declaring the variables containing the connection information are not shown. See the example for the msyql_real_connect() function next for those details. The SQL statement in the example is given through a variable, but it could be given within the function if enclosed in double quotes. The results of the query are stored in the result variable by way of the mysql_store_result() function. Incidentally, it’s important to free the memory allocated for the results with the mysql_free_result() function when finished.
Name
mysql_real_connect()
Synopsis
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host,
const char *user, const char *password,
const char *user, const char *password,
const char *database, uint port,
const char *user, const char *password,
const char *database, uint port,
const char *unix_socket, uint flag)
Use this to establish a connection to a MySQL server. The MYSQL structure created by mysql_init() is given as the first argument to the function. The hostname, username, and user’s password for connecting to the server are given next. The name of the database is given as the fifth argument. The port, the socket file path and name for Unix systems, and any client flags are given as the sixth, seventh, and eighth arguments, respectively. For any parameter requiring a char pointer, a value of NULL may be given to instruct the server to use the default setting. Forunsigned int variables, a value of 0 may be given to rely on the default value. Here is an example:
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
int main(void)
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
const char *host = "localhost";
const char *user = "root";
const char *password = "my_password";
const char *database = "workrequests";
unsigned int port = 3306;
const char *socket = NULL;
unsigned long flag = 0;
int i, num_fields;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,host,user,password,database,
port,socket,flag);
const char *sql_stmnt = "SELECT * FROM stores";
ulong bytes = strlen(sql_stmnt);
mysql_real_query(mysql, sql_stmnt, bytes);
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{ printf("%s, ", row[i]); }
printf("\n");
}
mysql_free_result(result);
mysql_close(mysql);
return 0;
}
This example is fairly complete. Each variable is declared at the beginning based on the type called for by the function, along with its respective values. Without having to disconnect and reconnect, you can change the database using the mysql_select_db() function.
Name
mysql_real_escape_string()
Synopsis
unsigned long mysql_real_escape_string(MYSQL *mysql,
char *result_string,
char *result_string,
char *original_string,
char *result_string,
char *original_string,
unsigned long src length)
This function writes a string given as the third argument to a string named in the second argument, but with special characters escaped by adding backslashes in front of them. The number of bytes to be copied from the source string is given for the fourth argument. When declaring the two strings, the destination string must be twice the size of the source string, plus one byte. Here is an example:
...
const char client_name[ ] = "O'Reilly Media";
ulong bytes = strlen(client_name);
char client_name_esc[(2 * bytes)+1];
mysql_real_escape_string(mysql, client_name_esc,
client_name, bytes);
char *sql_stmnt;
sprintf(sql_stmnt, "INSERT INTO clients (client_name)
VALUES('%s')", client_name_esc);
mysql_real_query(mysql, sql_stmnt, strlen(sql_stmnt));
...
After establishing the initial variable for storing the client’s name, the C function strlen() is used to determine the number of bytes contained in the string. Next, the second variable to hold the client’s name is declared with a size twice the size of the first variable, plus one byte. Themysql_real_escape_string() function is run with both variables and the size of the first. In this example, the function will place a backslash in front of the apostrophe in the client’s name so as not to cause an error when the query is run later. Using the C function sprintf(), the escaped client name is inserted into the SQL statement given. Finally, the SQL statement is run with mysql_real_query().
Name
mysql_real_query()
Synopsis
int mysql_real_query(MYSQL *mysql, const char *query,
unsigned int length)
Use this function to execute the SQL query given as the second argument of the function. Only one SQL statement may be given. Unlike mysql_query(), this function can execute queries containing binary data. Because of this feature, the number of bytes contained in the query needs to be given for the third argument. This can be determined with the C function strlen(). The function will return zero if successful, and a nonzero value if not. Here is an example:
...
mysql = mysql_init(NULL);
mysql_real_connect(mysql,host,user,password,database,port,socket,flag);
const char *sql_stmnt = "SELECT * FROM stores";
ulong bytes = strlen(sql_stmnt);
mysql_real_query(mysql, sql_stmnt, bytes);
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{ printf("%s, ", row[i]); }
printf("\n");
}
...
In this example, the number of bytes of the variable containing the SQL statement is determined with the C function strlen() and is stored in a separate variable called bytes. In turn, the bytes variable is given as the third argument to the mysql_real_query() function. As an alternative, strnlen(sql_stmnt) could be given as the third argument instead.
Name
mysql_reload()
Synopsis
int mysql_reload(MYSQL *mysql)
This function instructs the MySQL server to reload the grants table. It returns zero if successful and a nonzero value if not. This function has been deprecated. Use mysql_query() or mysql_real_query() with a FLUSH PRIVILEGES statement instead.
Name
mysql_refresh()
Synopsis
int mysql_refresh(MYSQL *mysql, unsigned int options)
Use this function to flush caches and tables. It can also be used to reset a replication server. It returns a value of zero if successful, and nonzero if not. The RELOAD privilege is required to use it. Several options may be given: REFRESH_GRANT, REFRESH_LOG, REFRESH_TABLES,REFRESH_HOSTS, REFRESH_MASTER, REFRESH_SLAVE, REFRESH_STATUS, and REFRESH_THREADS. There are four possible errors that are returned: CR_COMMANDS_OUT_OF_SYNC, CR_SERVER_GONE_ERROR, CR_SERVER_LOST, or CR_UNKNOWN_ERROR. Here is an example:
...
mysql_refresh(MYSQL mysql, unsigned int REFRESH_TABLES);
...
Name
mysql_rollback()
Synopsis
my_bool mysql_rollback(MYSQL *mysql)
Use this function to roll back or reverse the current transaction. This will not work if the mysql_commit() function has already been called for the transaction. The function returns zero if successful, and a nonzero value if not.
Name
mysql_row_seek()
Synopsis
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL *result,
MYSQL_ROW_OFFSET offset)
Use this function to move the pointer of a results set to the row given as the second argument of the function. The pointer given must use the MYSQL_ROW_OFFSET structure. Use a function such as mysql_row_tell() to determine the offset in the proper format. Here is an example:
...
MYSQL_ROW_OFFSET special_location;
while((row = mysql_fetch_row(result)) != NULL)
{
if(strcmp(row[1], "1000") == 0)
{
special_location = mysql_row_tell(result);
continue;
}
if(!mysql_more_results(mysql))
{
mysql_row_seek(result, special_location);
printf("%s (%s) \n", row[1], row[0]);
break;
}
printf("%s (%s) \n", row[1], row[0]);
}
...
In this example, a list of clients is retrieved, but the developer wants the row with a client identification number of 1000 to be displayed last. So, an if statement is used to check for the special record. When it finds the row it’s looking for, the mysql_row_tell() function is used to make a note of the point in the results set in which it was found. The remainder of the while statement in which the row is to be printed is then skipped. Using the mysql_more_results() function, another if statement watches for the end of the results set. If it determines that there are no more rows in the results set to print, it will move the pointer back to the special client using the mysql_row_seek() function and the pointer saved with mysql_row_tell(), print out that particular row’s data, and then end the while statement with break.
Name
mysql_row_tell()
Synopsis
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)
This function returns the pointer for the current position in a results set generated from the mysql_store_result() function. The value obtained can be used with mysql_row_seek() for changing the pointer while fetching rows. See the mysql_row_seek() function earlier in this section for an example of its use.
Name
mysql_select_db()
Synopsis
int mysql_select_db(MYSQL *mysql, const char *database)
Use this function to select a different database for the current connection. The name of the new database to use is given as the second argument of the function. It returns zero if successful, and a nonzero value if not. Here is an example:
...
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","ricky","adams",NULL,NULL,NULL,0);
mysql_select_db(mysql,"workrequests");
...
Name
mysql_set_character_set()
Synopsis
int mysql_set_character_set(MYSQL *mysql, const char *char_set)
Use this function to set the default character set of a connection to the character set given. It returns zero if successful, and a nonzero value if not. Here is an example:
...
if (!mysql_set_character_set(mysql, 'utf8'))
{
printf("Character Set: %s", mysql_character_set_name(mysql));
}
...
Name
mysql_set_local_infile_default()
Synopsis
void mysql_set_local_infile_default(MYSQL *mysql)
Use this function to set the handler for LOAD LOCAL DATA INFILE functions to the defaults necessary for internal use of the C client library. It is normally called automatically by the C library.
Name
mysql_set_local_infile_handler()
Synopsis
void mysql_set_local_infile_handler(MYSQL *mysql,
int (*local_infile_init)(void **, const char *, void *),
int (*local_infile_read)(void *, char *, unsigned int),
void (*local_infile_end)(void *),
int (*local_infile_error)(void *, char*, unsigned int),
void *userdata)
Use this function to enable callbacks that you will use with the LOAD DATA LOCAL INFILE statement. The callback functions must be created first:
...
int local_infile_init(void **ptr, const char *file_name, void *user_info);
int local_infile_read(void *ptr, char *buffer, unsigned int buffer_len);
void local_infile_end(void *ptr);
int local_infile_error(void *ptr, char *error_msg, unsigned int
error_msg_len);
...
Name
mysql_set_server_option()
Synopsis
int mysql_set_server_option(MYSQL *mysql,
enum mysql_set_option option)
Use this function to enable or disable a server option. The only options currently available are MYSQL_OPTION_MULTI_STATEMENTS_ON and MYSQL_OPTION_MULTI_STATEMENTS_OFF, to enable and disable multiple SQL statements, respectively. It returns 0 if successful, and a nonzero value if not.
Name
mysql_shutdown()
Synopsis
int mysql_shutdown(MYSQL *mysql)
Use this function to shut down the MySQL server. It returns zero if successful, and a nonzero value if not. Here is an example:
...
if(!mysql_ping(mysql))
{
mysql_shutdown(mysql);
printf("Shutting down server \n");
if(mysql_ping(mysql))
{ printf("MySQL server is down.\n"); }
}
...
The mysql_ping() function here checks whether the server is alive. Recall that a zero, not a TRUE, return signifies a live server.
Name
mysql_sqlstate()
Synopsis
const char *mysql_sqlstate(MYSQL *mysql)
This function returns the SQLSTATE error code for the last error that occurred for the current connection. The string will contain five characters and is terminated with a NULL character. A lack of error is signified by 00000 and unmapped errors by HY000.
Name
mysql_ssl_set()
Synopsis
my_bool mysql_ssl_set(MYSQL *mysql,
const char *key_path,
const char *cert_path, const char *ca_path,
const char *pem_path, const char *cipher)
This function makes a secure connection with SSL. OpenSSL must be enabled in order to use it. Call it before calling mysql_real_connect(). This function returns zero unless there is a problem, in which case an error will be returned when mysql_real_connect() is called. Thekey_path is the path to the key to be used; cert_path is the path to the certificate file; ca_path is the file path of the certificate authority file; pem_path is the directory with trusted SSL CA certificates, which are in the pem format; and finally, cipher contains a list of ciphers permitted for SSL encryption. You can give NULL for parameters that don’t apply.
Name
mysql_stat()
Synopsis
char * mysql_stat(MYSQL *mysql)
This function returns a character string containing information about the status of the MySQL server for the current connection. Here is an example:
...
printf("Server Status \n %s \n", mysql_stat(mysql));
...
Name
mysql_store_result()
Synopsis
MYSQL_RES *mysql_store_result(MYSQL *mysql)
Use this function to read and store all of a results set in a MYSQL_RES structure. When finished with these results, it’s necessary to use the mysql_free_result() function to free the memory allocated for storing the results set. The function returns NULL if it’s unsuccessful or if the query is not the type that would return any results (e.g., an UPDATE statement). Here is an example:
...
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","user","password",
"workrequests",0,NULL,0);
mysql_query(mysql,"SELECT * FROM users");
result = mysql_store_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{
field = mysql_fetch_field_direct(result, i);
printf("%s: %s, ", field->name, row[i]);
}
printf("\n");
}
mysql_free_result(result);
...
See the example for the mysql_fetch_row() function earlier in this chapter for an alternative method.
Name
mysql_thread_end()
Synopsis
void mysql_thread_end(void)
Use this function before calling free memory used by mysql_thread_init(). It returns nothing. It isn’t automatically called. Here is an example:
...
if(mysql_thread_safe( ))
{ printf("Safe Environment \n"); }
else{ printf("Unsafe Environment \n"); }
...
Name
mysql_thread_id()
Synopsis
unsigned long mysql_thread_id(MYSQL *mysql)
This function returns the thread identifier number for the current connection to MySQL. Thread identifiers can change if a connection is closed or restarted. Here is an example:
...
int thread = mysql_thread_id(mysql);
printf("Thread ID: %d \n", thread);
...
Name
mysql_thread_init()
Synopsis
my_bool mysql_thread_init(void)
Use this function to initialize thread specific variables. It’s automatically called by mysql_connect(), mysql_init(), mysql_library_init(), and mysql_server_init(). It returns zero if successful, and nonzero if not.
Name
mysql_thread_safe()
Synopsis
unsigned int mysql_thread_safe(void)
Use this function to determine whether the MySQL client library is safe for a threaded environment. It returns 1 if safe, 0 if not. Here is an example:
...
if(mysql_thread_safe( ))
{ printf("Safe Environment \n"); }
else{ printf("Unsafe Environment \n"); }
...
Name
mysql_use_result()
Synopsis
MYSQL_RES *mysql_use_result(MYSQL *mysql)
Use this function to read the results of a query, one row at a time. This works in a way similar to the mysql_store_result() function, except that function retrieves all of the data at once and stores it for later use. The mysql_use_result() function is best used when a results set would be large and speed of processing is a concern. With this function, processing may be started sooner without having to wait for all of the data to be retrieved. One drawback to this function is that other queries cannot be run while the results from the first query are in use. Also, functions such asmysql_data_seek() cannot be used and the return value from running mysql_num_rows() is altered, because the complete size of the results set is unknown. Here is an example:
...
mysql_query(mysql, "SELECT * FROM clients");
result = mysql_use_result(mysql);
num_fields = mysql_field_count(mysql);
while((row = mysql_fetch_row(result)) != NULL)
{
for(i = 0; i < num_fields; i++)
{
field = mysql_fetch_field_direct(result, i);
printf("%s: %s, ", field->name, row[i]);
}
printf("\n");
}
mysql_free_result(result);
...
See the example for the mysql_fetch_row() function earlier in this chapter for an alternative method.
Name
mysql_warning_count()
Synopsis
unsigned int mysql_warning_count(MYSQL *mysql)
This function returns the number of warning messages encountered from the previous query. This can be useful, for instance, when performing multiple INSERT statements with the IGNORE flag. Here is an example:
...
MYSQL *mysql;
mysql = mysql_init(NULL);
mysql_real_connect(mysql,"localhost","root","password",
"workrequests",0,NULL,0);
...
unsigned int warnings = mysql_warning_count(mysql);
printf("Number of Warnings: %d \n", warnings);
...
C API Datatypes
Here is a list of C API data types from the mysql.h header file:
MYSQL
A database handle structure created by mysql_init() and released with mysql_close().
MYSQL_RES
A structure for a results set from an SQL query. This structure is used by fetch functions and is released with mysql_free_result().
MYSQL_ROW
A structure for holding a row of data from a results set. The data is retrieved from this structure by the mysql_fetch_row() function.
MYSQL_FIELD
A structure for holding an array of information about a field of a results set. The array may be set with the mysql_fetch_field() function. The elements include name, table, and def for the default value.
MYSQL_FIELD_OFFSET
Used for recording a pointer location for a results set. The offset value can be retrieved by the mysql_row_tell() function and deployed with mysql_row_seek().
my_ulonglong
A variable type for storing the number of rows for functions such as mysql_affected_rows(), mysql_num_rows(), and mysql_insert_id(). To print the value of a variable using this type, copy the value to another variable that uses the unsigned long type.