14.2. 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, as well as to close the connection and to end the program. For an example on how you would write opening and closing lines, see the tutorial in the previous section. The examples in this section will 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 datatype expected is given before each parameter or argument.

mysql_affected_rows( )

my_ulonglong mysql_affected_rows(MYSQL *mysql)

This 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.

... 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.

mysql_autocommit( )

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)

Use this to set auto-commit mode on the MySQL server. A value of 1 for the second argument of this function sets the server's auto-commit mode to on. A value of sets it to 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.

... mysql_autocommit(mysql, 0); ...

mysql_change_user( )

my_bool mysql_change_user(MYSQL *mysql, const char *user,                            const char *password,                            const char *password,                            const char *database)

Use this 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. This function will end the current session if successful, so it will need to issue a new USE statement. Therefore, a database to use is to be given as the fourth argument.

... mysql = mysql_init(NULL); mysql_connect(mysql,"localhost","hui","shorty"); 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 the proper authority.

mysql_character_set_name( )

const char *mysql_character_set_name(MYSQL *mysql)

This returns the name of the default character set in use by the MySQL server.

... MYSQL *mysql; const char *char_set; mysql = mysql_init(NULL); mysql_connect(mysql,"localhost","russell","password"); 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

mysql_close( )

void mysql_close(MYSQL *mysql)

Use this to close the connection to the MySQL serve. It releases the MYSQL object if allocated by mysql_init( ). It does not return a value.

... mysql = mysql_init(NULL); mysql_connect(mysql,"localhost","russell","password"); ... mysql_close(mysql); ...

mysql_commit( )

my_bool mysql_commit(MYSQL *mysql)

Use this 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, nonzero if unsuccessful.

 mysql_commit(mysql);

mysql_connect( )

MYSQL *mysql_connect(MYSQL *mysql, const char *host,                       const char *user, cont char *password)

Use this to establish a connection to a MySQL server. The first argument is an object by mysql_init( ). The second argument for the function is the hostname. If NULL is given, the default of localhost is used. The third argument is the username for connecting and the fourth is the user's password in plain text. The return value for the function, if successful, is a MYSQL structure and is saved to the variable named in the first argument. This variable should be used in subsequent functions to access the server. NULL is returned if the connection fails.

This function is deprecated in favor of mysql_real_connect( ). The mysql_close( ) function is used to end a connection:

int main( ) {    MYSQL *mysql;    const char *host = "localhost";    const char *user = "russell";    const char *password = "password";    mysql = mysql_init(NULL);    mysql_connect(mysql,host,user,password);    ...    mysql_close(mysql);    exit(EXIT SUCCESS); }

Notice that datatypes for the variables set up in the first few lines coincide with the data types shown in the function prototype. Instead of variables, the actual values may be given within the function. Each value must be contained within double quotes.

mysql_create_db( )

int mysql_create_db(MYSQL *mysql, const char *database)

Use this to create a new database on the MySQL server. The new database's name is given as the second argument. This function has been deprecated. Instead, a CREATE DATABASE statement should be given with mysql_query( ) or mysql_real_query().

... mysql_real_connect(mysql,host,user,password,NULL,0,NULL,0); mysql_create_db(mysql, "new_database"); mysql_select_db(mysql, "new_database"); ...

This program excerpt creates a database named new_database. The parameters for the mysql_real_connect( ) function are variables declared earlier in the program. Notice that the fifth argument (the parameter for the initial database to use) is set to NULL.

mysql_data_seek( )

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)

Use this 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.

... 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.

mysql_debug( )

void mysql_debug(const char *debug)

Use this to set debugging if the client was compiled with debugging.

... mysql_debug("d:t:o,filename"); ...

The filename given could include the path to the logfile where debugging information is to be written.

mysql_drop_db( )

int mysql_drop_db(MYSQL *mysql, const char *database)

Use this to delete the database named in the second argument of the function from the MySQL server. It returns 0 if successful and nonzero if not. This function has been deprecated. Use mysql_query( ) or mysql_real_query( ) with a DROP DATABASE statement instead.

... mysql = mysql_init(NULL); mysql_real_connect(mysql,host,user,password,NULL,0,NULL,0); ... mysql_drop_db(mysql, "db5"); ...

This returns a nonzero if it fails, so a program that uses it should include error checking for the function. Otherwise, the program may hang if the function is unsuccessful.

mysql_dump_debug_info( )

int mysql_dump_debug_info(MYSQL *mysql)

Use this to write debugging information about the current connection to the MySQL server's logfile. The user must have administrative privileges. It returns 0 if successful, nonzero if unsuccessful.

... if(!mysql_dump_debug_info(mysql))    { printf("Debugging Info. Written. \n"); } ...

mysql_eof( )

my_bool mysql_eof(MYSQL *result)

Use this to determine whether the last row of the results set has been fetched. It returns 0 until end of file is reached and nonzero 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.

mysql_errno( )

unsigned int mysql_errno(MYSQL *mysql)

This returns the error number for the last function executed that failed. After a successfully executed function, 0 is returned from this function.

... if(mysql_real_connect(mysql,host,"goofy",                        password,database,0,NULL,0) =  = NULL)    {     printf("Error %d \n", mysql_errno(mysql));     exit(EXIT FAILURE);    } ...

In this example, the program is attempting to connect to the MySQL server with a user who is not in the mysql database.

mysql_error( )

char *mysql_error(MYSQL *mysql)

This returns the error message for the last function executed that failed. After a successfully executed function, an empty string is returned from this function.

... if(!mysql_real_connect(mysql,host,"goofy",                        password,database,0,NULL,0))    {     printf("Error Message: %s \n", mysql_error(mysql));     exit(EXIT FAILURE);    } ...

In this example, the program is attempting to connect to the MySQL server with a user who is not in the mysql database.

mysql_escape_string( )

unsigned int mysql_escape_string(char *destination,                                   const char *source,                                  const char *source,                                  unsigned int length)

This returns a string given as the second argument 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 third. When declaring the two strings, the destination must be double the source string, plus one byte. This function does not include a MYSQL object (which includes knowledge of the current character set), so it may not be comprehensive. This is a security problem. Use the mysql_real_escape_string() function instead, which does this job properly and safely.

... char client_name[  ] = "O'Reilly Media"; unsigned int bytes = strlen(client_name); char client_name_esc[(2 * bytes) + 1]; mysql_escape_string(client_name_esc, client_name, bytes); char *sql_stmnt; sprintf(sql_stmnt, "INSERT INTO clients (client_name)                     VALUES('%s')", client_name_esc); printf("SQL Statement:\n%s", sql_stmnt); mysql = mysql_init(NULL); mysql_real_connect(mysql,host,user,password,database,                    port,socket,flag); mysql_real_query(mysql, sql_stmnt, strlen(sql_stmnt)); ...

In this example, the client name is first stored in the client_name variable. Next, the number of bytes contained in the variable is calculated with the C function strlen( ) and stored in the bytes variable. Then the client_name_esc variable is declared with a size of the value contained in bytes doubled, plus one, per the requirements of the mysql_escape_string( ) function. Then the mysql_escape_string( ) is run to convert the string contained in client_name and save it to client_name_esc. Then the program creates an SQL statement, inserting the escaped client name where the %s is shown. To see the results, a print statement is issued before the SQL statement is executed.

SQL Statement: INSERT INTO clients (client_name) VALUES('O\'Reilly Media')

mysql_fetch_field( )

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

This 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.

... 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.

mysql_fetch_field_direct( )

MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result,                                        unsigned int field_nbr)

This 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.

... 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.

mysql_fetch_fields( )

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)

This returns an array of information about the fields in a results set.

... 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.

mysql_fetch_lengths( )

unsigned long *mysql_fetch_lengths(MYSQL *result)

This 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.

... 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.

mysql_fetch_row( )

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Use this 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);     exit(EXIT SUCCESS);    }

Although this example is a complete program, it's missing the usual error checking.

mysql_field_count( )

unsigned int mysql_field_count(MYSQL *mysql)

This returns the number of columns in a results set. You can also use this function 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.

... if(!result)    {     if(mysql_field_count(mysql) =  = 0)        {         printf("Error \n");         exit(EXIT FAILURE);        }    } ...

See the entry for the mysql_fetch_row( ) function for another example involving this function.

mysql_field_seek( )

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result,                                      MYSQL_FIELD_OFFSET offset)

Use this 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.

... 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 0, the starting point for a row.

... MYSQL_FIELD_OFFSET old_offset = mysql_field_seek(result, offset); ...

You can use this for notating a point in a results set before moving the pointer. The program can later return to that point using the old offset.

mysql_field_tell( )

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

This 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 order. See mysql_field_seek( ) for an example of this function.

mysql_free_result( )

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.

... 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.

mysql_get_client_info( )

char *mysql_get_client_info(void)

This returns the client library version.

... const char *info; info = mysql_get_client_info( ); printf("Client Library Version: %s \n", info); ...

mysql_get_client_version( )

unsigned long *mysql_get_client_version(void)

This returns the client library version in a numeric format. For example, for Version 4.1.7, the function will return 40107.

... unsigned long version; version = mysql_get_client_version( ); printf("Client Version: %d \n", version); ...

mysql_get_host_info( )

char *mysql_get_host_info(MYSQL *mysql)

This returns the hostname and the connection type for the current connection.

... 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

mysql_get_proto_info( )

unsigned int mysql_get_proto_info(MYSQL *mysql)

This returns the protocol version for the current connection.

... 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); ...

mysql_get_server_info( )

char *mysql_get_server_info(MYSQL *mysql)

This returns a string containing the version of MySQL running on the server for the current connection.

... 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); ...

mysql_get_server_version( )

unsigned long mysql_get_server_version(MYSQL *mysql)

This 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.

... 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); ...

mysql_info( )

char *mysql_info(MYSQL *mysql)

This returns a string containing information provided by MySQL when certain SQL statements are executed. This function works only with five types of SQL statements: INSERT INTO...SELECT..., INSERT INTO... VALUES..., LOAD DATA INFILE, ALTER TABLE, and UPDATE. For all other statements, this function typically returns NULL.

... mysql_query(mysql, "UPDATE clients                      SET telephone_areacode = '985'                      WHERE city = 'Hammond'"); printf("Query Info: %s \n", mysql_info(mysql)); ...

The results of the previous program excerpt will look like the following:

Query Info: Rows matched: 3  Changed: 3  Warnings: 0

mysql_init( )

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 were when building the library.

Though this function prepares a handle for a database connection, no connection is attempted.

... MYSQL *mysql; if(mysql_init(mysql) =  = NULL)       {         printf("Could not initialize MySQL object. \n");         exit(EXIT FAILURE);       } ...

mysql_insert_id( )

my_ulonglong mysql_insert_id(MYSQL *mysql)

This 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, 0 is returned.

... 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); ...

mysql_kill( )

int mysql_kill(MYSQL *mysql, unsigned long identifier)

Use this 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.

... 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.

mysql_list_dbs( )

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)

This 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.

... 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, NULL would be replaced with "%work%". As with all results sets, release the resources with mysql_free_result( ) when finished.

mysql_list_fields( )

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table,                              const char *wild)

This returns a results set containing a list of fields found for the table given as the second argument to 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.

... 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); ...

mysql_list_processes( )

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

This returns a results set containing a list of MySQL server processes or server threads found for the handle given as the argument to the function.

... 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 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.

mysql_list_tables( )

MYSQL_RES *mysql_list_tables(MYSQL *mysql,                               const char *expression)

This 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.

... 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.

mysql_more_results( )

my_bool mysql_more_result(MYSQL *mysql)

Use this 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, 0 if not.

mysql_next_result( )

int mysql_next_result(MYSQL *mysql)

Use this 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.

mysql_num_fields( )

unsigned int mysql_num_fields(MYSQL_RES *result)

This returns the number of fields in each row of a results set. This function is similar to mysql_field_count( ) except that function operates on the MYSQL handle and not the results set.

... unsigned int num_fields = mysql_num_fields(result); ...

See mysql_fetch_lengths( ) for a more elaborate example that uses this function.

mysql_num_rows( )

int mysql_num_rows(MYSQL_RES *result)

This 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.

... my_ulonglong num_rows = mysql_num_rows(result); ...

See mysql_list_fields( ) for a more elaborate example that uses this function.

mysql_options( )

int mysql_options(MYSQL *mysql, enum mysql_option option,                    const char *value)

Use this to set connection options before a connection has been established with 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, specific options for the connection may be given. A value associated with the chosen option may be given for the third argument.

... 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 *)

Sets the number of seconds for connection timeout.


MYSQL_OPT_READ_TIMEOUT (unsigned int *)

Sets the timeout for reads from a Windows MySQL server.


MYSQL_OPT_WRITE_TIMEOUT (unsigned int *)

Sets the timeout for writes to a Windows MySQL server.


MYSQL_OPT_COMPRESS (NULL)

Compresses communications between the client and server if supported by both.


MYSQL_OPT_LOCAL_INFILE (pointer to unsigned integer)

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)

Instructs the client to use named pipes for connecting to a Windows NT MySQL server.


MYSQL_INIT_COMMAND (char *)

Instructs the server on connecting to execute an initial SQL statement given as the third argument to the function.


MYSQL_READ_DEFAULT_FILE (char *)

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 *)

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 *)

Specifies the default protocol for communicating with the server.


MYSQL_SHARED_MEMORY_BASE_NAME (char *)

Names the shared memory object for connecting to the server.

mysql_ping( )

int mysql_ping(MYSQL *mysql)

Use this 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, 0 is returned. Otherwise, nonzero is returned.

... 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.

mysql_query( )

int mysql_query(MYSQL *mysql, const char *query)

Use this 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. If successful, this function will return 0; otherwise, nonzero.

... 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 for those details. The SQL statement in the previous example is given through a variable, but could be given within the function if enclosed within 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.

mysql_real_connect( )

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. For unsigned int variables, a value of 0 may be given to rely on the default value.

#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);   exit(EXIT_SUCCESS); }

This example is fairly complete. Each variable is declared at the beginning based on the type called for by the function, along with their respective values. Without having to disconnect and reconnect, you can change the database with the mysql_select_db() function.

mysql_real_escape_string( )

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 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 double the size of the source string, plus one byte.

... 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 double the size of the first variable, plus one byte. The mysql_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( ).

mysql_real_query( )

int mysql_real_query(MYSQL *mysql, const char *query,                       unsigned int length)

Use this 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( ). If successful, the function will return 0; otherwise, nonzero.

... 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.

mysql_reload( )

int mysql_reload(MYSQL *mysql)

This instructs the MySQL server to reload the grants table. It returns 0 if successful and nonzero if unsuccessful. This function has been deprecated. Use mysql_query( ) or mysql_real_query() with a FLUSH PRIVILEGES statement instead.

mysql_rollback( )

my_bool mysql_rollback(MYSQL *mysql)

Use this 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 0 if successful, nonzero if unsuccessful.

mysql_row_seek( )

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL *result,                                  MYSQL_ROW_OFFSET offset)

Use this to move the pointer of a result 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.

...  MYSQL_ROW_OFFSET special_location;  while((row = mysql_fetch_row(result)) != NULL)    {     if(strcmp(row[1], "1000") =  = 0)       {        special_location = mysql_tell_row(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 has been 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 for which it's looking, 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.

mysql_row_tell( )

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)

This 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 for an example of its use.

mysql_select_db( )

int mysql_select_db(MYSQL *mysql, const char *database)

Use this 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 0 if successful, nonzero if unsuccessful.

... mysql = mysql_init(NULL); mysql_connect(mysql,"localhost","hui","shorty"); mysql_select_db(mysql,"workrequests"); ...

mysql_set_server_option( )

int mysql_set_server_option(MYSQL *mysql,                              enum mysql_set_option option)

Use this 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, nonzero if not successful.

mysql_shutdown( )

int mysql_shutdown(MYSQL *mysql)

Use this to shut down the MySQL server. It returns 0 if successful and nonzero if unsuccessful.

... 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 in the example checks if the server is alive. Recall that a zero, not a TRUE, return signifies a live server.

mysql_sqlstate( )

const char *mysql_sqlstate(MYSQL *mysql)

This 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".

mysql_stat( )

char * mysql_stat(MYSQL *mysql)

This returns a character string containing information about the status of the MySQL server for the current connection.

... printf("Server Status \n %s \n", mysql_stat(mysql)); ...

mysql_store_result( )

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Use this 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).

... 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 for an alternative method.

mysql_thread_id( )

unsigned long mysql_thread_id(MYSQL *mysql)

This returns the thread identifier number for the current connection to MySQL. Thread identifiers can change if a connection is closed or restarted.

... int thread = mysql_thread_id(mysql); printf("Thread ID: %d \n", thread); ...

mysql_thread_safe( )

unsigned int mysql_thread_safe(void)

Use this to determine whether the MySQL client library is safe for a threaded environment. It returns 1 if safe, 0 if not.

... if(mysql_thread_safe( ))    { printf("Safe Environment \n"); } else{ printf("Unsafe Environment \n"); } ...

mysql_use_result( )

MYSQL_RES *mysql_use_result(MYSQL *mysql)

Use this to read the results of a query, one row at a time. This functions 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 without finishing with the results that are in use from the first query. Also, functions such as mysql_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.

... 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 for an alternative method.

mysql_warning_count( )

unsigned int mysql_warning_count(MYSQL *mysql)

This 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.

... 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); ...



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net