Chapter 19. C Reference

my_ulonglong mysql_affected_rows(MYSQL *mysql)

Returns the number of rows affected by the most recent query. When used with a non-SELECT query, it can be used after the mysql_query call that sent the query. With SELECT, this function is identical to mysql_num_rows.

Example

/* Insert a row into the people table */
mysql_query(&mysql, "INSERT INTO people VALUES ('', 'Illyana Rasputin', 16)";
num = mysql_affected_rows(&mysql);
/* num should be 1 if the INSERT (of a single row) was successful, and -1 if
   there was an error */

void mysql_close(MYSQL *mysql)

Ends a connection to the database server. If there is a problem when the connection is broken, the error can be retrieved from the mysql_err function.

Example

mysql_close(&mysql);
/* The connection should now be terminated */

void mysql_data_seek(MYSQL_RES *res, unsigned int offset)

Moves to a specific row in a group a results. The first argument is the MYSQL_RES structure that contains the results. The second argument is the row number you wish to seek to. The first row is 0. This function only works if the data was retrieved using mysql_store_result.

Example

/* Jump to the last row of the results */
mysql_data_seek(results, mysql_num_rows(results)-1);

mysql_debug(char *debug)

Manipulates the debugging functions if the client has been compiled with debugging enabled. MySQL uses the Fred Fish debugging library, which has far too many features and options to detail here.

Example

/* This is a common use of the debugging library. It keeps a trace of the
   client program's activity in the file "debug.out" */
mysql_debug("d:t:O,debug.out");

int mysql_dump_debug_info(MYSQL *mysql)

This function causes the database server to enter debugging information about the current connection into its logs. You must have Process privilege in the current connection to use this function. The return value is zero if the operation succeeded and nonzero in the case of an error.

Example

result = mysql_dump_debug_info(&mysql);
/* The server's logs should now contain information about this connection */

my_bool mysql_eof(MYSQL_RES *result)

Returns a nonzero value if there is no more data in the group of results being examined. If there is an error in the result set, zero is returned. This function only works of the result set was retrieved with the mysql_use_result function.

Example

/* Read through the results until no more data comes out */
while((row = mysql_fetch_row(results)))
{
       /* Do work */
}

if(!mysql_eof(results))
{
   printf("Error. End of results not reached.\n");

unsigned int mysql_errno(MYSQL *mysql) 

Returns the error number of the last error associated with the current connection. If there have been no errors in the connection, the function returns zero.

Example

error = mysql_errno(&mysql);
printf("The last error was number %d\n", error);

char *mysql_error(MYSQL *mysql)

Returns the error message of the last error associated with the current connection. If there have been no errors in the connection, the function returns an empty string.

Example

printf("The last error was '%s'\n", mysql_error(&mysql));

unsigned int mysql_escape_string(char *to, const char *from, unsigned int length)
unsigned int mysql_escape_string(char *to, const char *from)

Encodes a string so that it is safe to insert it into a MySQL table. The first argument is the receiving string, which must be at least one character greater than twice the length of the second argument, the original string. (That is, to >= from*2+1.) If a third argument is present, only that many bytes are copied from the originating string before encoding it. The function returns the number of bytes in the encoded string, not including the terminating null character.

Example

char name[15] = "Bob Marley's";
char enc_name[31];
mysql_escape_string(enc_name, name);
/* enc_name will now contain "Bob Marley\'s" (the single quote is escaped).

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

Returns a MYSQL_FIELD structure describing the current field of the given result set. Repeated calls to this function will return information about each field in the result set until there are no more fields left, and then it will return a null value.

Example

MYSQL_FIELD *field;

while((field = mysql_fetch_field(results)))
{
    /* You can examine the field information here */
}

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

This function is the same as mysql_fetch_field, except that you specify which field you wish to examine, instead of cycling through them. The first field in a result set is 0.

Example

MYSQL_FIELD *field;

/* Retrieve the third field in the result set for examination */
field = mysql_fetch_field_direct(results, 2);

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES * result)

The function is the same as mysql_fetch_field, except that it returns an array of MYSQL_FIELD structures containing the information for every field in the result set.

Example

MYSQL_FIELD *field;
MYSQL_FIELD *fields;

/* Retrieve all the field information for the results */
fields = mysql_fetch_fields(results);
/* Assign the third field to 'field' */
field = fields[2];

unsigned long *mysql_fetch_lengths(MYSQL_RES *result)

Returns an array of the lengths of each field in the current row. A null value is returned in the case of an error. You must have fetch at least one row (with mysql_fetch_row) before you can call this function. This function is the only way to determine the lengths of variable length fields, such as BLOB and VARCHAR, before you use the data.

Example

unsigned long *lengths;

row = mysql_fetch_row(results);
lengths = mysql_fetch_lengths(results);
printf("The third field is %d bytes long\n", lengths[2]);

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Retrieves the next row of the result and returns it as a MYSQL_ROW structure. A null value is returned if there are no more rows or there is an error. In the current implementation, the MYSQL_ROW structure is an array of character strings that can be used to represent any data.

Example

MYSQL_ROW row;

row = mysql_fetch_row(results);
printf("The data in the third field of this row is: %s\n", row[2]);

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

Seeks to the given field of the current row of the result set. The position set by this function is used when mysql_fetch_field is called. The MYSQL_FIELD_OFFSET value passed should be the return value of a mysql_field_tell call (or another mysql_field_seek). Using the value will seek to the beginning of the row. The return value is the position of the cursor before the function was called.

Example

MYSQL_FIELD field;

/* Seek back to the beginning of the row */
old_pos = mysql_field_seek(results, 0);
/* Fetch the first field of the row */
field = mysql_field_field(results);
/* Go back to where you where */
mysql_field_seek(results, old_pos);

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

Returns the value of the current field position within the current row of the result set. This value is used with mysql_field_seek.

Example

MYSQL_FIELD field1, field2, field3;

/* Record my current position */
old_pos = mysql_field_tell(results);
/* Fetch three more fields */
field1 = mysql_field_field(results);
field2 = mysql_field_field(results);
field3 = mysql_field_field(results);
/* Go back to where you where */
mysql_field_seek(results, old_pos);

void mysql_free_result(MYSQL_RES *result) 

Frees the memory associated with a MYSQL_RES structure. This must be called whenever you are finished using this type of structure or else memory problems will occur.

Example

MYSQL_RES *results;
/* Do work with results */
mysql_free_result(results);

char *mysql_get_client_info(void)

Returns a string with the MySQL library version used by the client program.

Example

printf("This program uses MySQL client library version %s\n", 
       mysql_get_client_info()));

char *mysql_get_host_info(MYSQL *mysql) 

Returns a string with the hostname of the MySQL database server and the type of connection used (e.g., Unix socket or TCP).

Example

printf("Connection info: %s", mysql_get_host_info(&mysql));

unsigned int mysql_get_proto_info(MYSQL *mysql)

Returns the MySQL protocol version used in the current connection as an integer.

Example

printf("This connection is using MySQL connection protocol ver. %d\n",
        mysql_get_proto_info());

char *mysql_get_server_info(MYSQL *mysql)

Returns a string with the version number of the MySQL database server used by the current connection.

Example

printf("You are currently connection to MySQL server version %s\n",
        mysql_get_server_info(&mysql);

char *mysql_info(MYSQL *mysql)

Returns a string containing information about the most recent query, if the query was of a certain type. Currently, the following SQL queries supply extra information via this function: INSERT INTO (when used with a SELECT clause); LOAD DATA INFILE; ALTER TABLE; INSERT INTO TABLE (when used with multiple records). If the last query had no additional information (e.g., it was not one of the above queries), this function returns a null value.

Example

/* We just sent LOAD DATA INFILE query reading a set of record from a file into
   an existing table */
printf("Results of data load: %s\n", mysql_info(&mysql));

MYSQL *mysql_init(MYSQL *mysql)

Initializes a MYSQL structure used to create a connection to a MySQL database server. This, along with mysql_real_connect, is currently the approved way to initialize a server connection. You pass this function a MYSQL structure that you declared, or a null pointer, in which case a MYSQL structure will be created and returned. Structures created by this function will be properly freed when mysql_close is called. A null value is returned if there is not enough memory to initialize the structure.

Example

MYSQL mysql;

if (!mysql_init(&mysql)) {
			printf("Error initializing MySQL client\n");
			exit(1);
}

my_ulonglong mysql_insert_id(MYSQL *mysql)

Returns the last number generated for an AUTO_INCREMENT field. This function is usually used immediately after a value is inserted into an AUTO_INCREMENT field, to determine the value that was inserted.

Example

/* We just inserted an employee record with automatically generated ID into
   a table */
id = mysql_insert_id(&mysql);
printf("The new employee has ID %d\n", id);

int mysql_kill(MYSQL *mysql, unsigned long pid) 

Attempts to kill the MySQL server thread with the specified Process ID. This function returns zero if the operation was successful and nonzero on failure. You must have Process privileges in the current connection to use this function.

Example

/* Kill thread 4 */
result = mysql_kill(&mysql, 4);

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

Returns a MYSQL_RES structure containing the names of all existing databases that match the pattern given by the second argument. This argument may be any standard SQL regular expression. If a null pointer is passed instead, all databases are listed. Like all MYSQL_RES structures, the return value of this function must be freed with mysql_free_result. This function returns a null value in the case of an error.

Example

MYSQL_RES databases;
databases = mysql_list_dbs(&mysql, (char *)NULL);
/* 'databases' now contains the names of all of the databases in the
   MySQL server */

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

Returns a MYSQL_RES structure containing the names of all existing fields in the given table that match the pattern given by the third argument. This argument may be any standard SQL regular expression. If a null pointer is passed instead, all fields are listed. Like all MYSQL_RES structures, the return value of this function must be freed with mysql_free_result . This function returns a null value in the case of an error.

Example

MYSQL_RES fields;
fields = mysql_list_fields(&mysql, "people", "address%");
/* 'fields' now contains the names of all fields in the 'people' table
    that start with 'address' */

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

Returns a MYSQL_RES structure containing the information on all of the threads currently running on the MySQL database server. This information contained here can be used with mysql_kill to remove faulty threads. Like all MYSQL_RES structures, the return value of this function must be freed with mysql_free_result. This function returns a null value in the case of an error.

Example

MYSQL_RES threads;
threads = mysql_list_processes(&mysql);

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

Returns a MYSQL_RES structure containing the names of all existing tables in the current database that match the pattern given by the second argument. This argument may be any standard SQL regular expression. If a null pointer is passed instead, all tables are listed. Like all MYSQL_RES structures, the return value of this function must be freed with mysql_free_result. This function returns a null value in the case of an error.

Example

MYSQL_RES tables;
tables = mysql_list_tables(&mysql, "p%");
/* 'tables' now contains the names of all tables in the current database
    that start with 'p' */

unsigned int mysql_num_fields(MYSQL_RES *result)

Returns the number of fields contained in each row of the given result set.

Example

num_fields = mysql_num_fields(results);
printf("There are %d fields in each row\n", num_fields);

int mysql_num_rows(MYSQL_RES *result)

Returns the number of rows of data in the result set. This function is only accurate if the result set was retrieved with mysql_store_result. If mysql_use_result was used, the value returned by this function will be the number of rows accessed so far.

Example

num_rows = mysql_num_rows(results);
printf("There were %d rows returned\n", num_rows);

int mysql_ping(MYSQL *mysql)

Checks to see if the connection to the MySQL server is still alive. If it is not, the client will attempt to reconnect automatically. This function returns zero if the connection is alive and nonzero in the case of an error.

Example

while(mysql_ping(&mysql)) printf("Error, attempting reconnection...\n");

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

Executes the SQL query given in the second argument. If the query contains any binary data (particularly the null character), this function cannot be used and mysql_real_query should be used instead. The function returns zero if the query was successful and nonzero in the case of an error.

Example

error = mysql_query(&mysql, "SELECT * FROM people WHERE name like 'Bill%'");
if (error) { 
     printf("Error with query!\n"); 
     exit(1);
}

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user,
const char *passwd, const char *db, uint port, const char *unix_socket,
uint client_flag)

Creates a connection with a MySQL database server. There are eight arguments to this function:

• An initialized MYSQL structure, created with mysql_init.

• The hostname or IP address of the MySQL database server (use an empty string or localhost to connect to the local MySQL server over a Unix socket).

• The username used to connect to the database server (an empty string may be used assuming the Unix login name of the person running the client).

• The password used to authenticate the given user. If an empty string is used, only users with no passwords are checked for authentication.

• The initial database selected when you connect (an empty string may be used to not initially choose a database).

• The port used to remotely connect to a MySQL database server over TCP (0 may be used to accept the default port).

• The filename of the Unix socket used to connect to a MySQL server on the local machine (an empty string may be used to accept the default socket).

• Zero or more of a set of flags used under special circumstances:

  CLIENT_FOUND_ROWS

  When using queries that change tables, returns the number of rows found in the table, not the number of rows affected.

  CLIENT_NO_SCHEMA

  Prevent the client from using the full database.table.column form to specify a column from any database.

  CLIENT_COMPRESS

  Use compression when communicating with the server.

  CLIENT_ODBC

  Tell the server the client is an ODBC connection.

Example

/* Connect to the server on the local host with standard options. */
if (! mysql_real_connect(&mysql, "localhost", "bob", "mypass", "", 0, "", 0))
{ print "Error connecting!\n";
  exit(1);
}

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

Executes the SQL query given in the second argument. The length of the query must be given in the third argument. By supplying the length, you can use binary data, including null characters, in the query. This function is also faster than mysql_query. The function returns zero if the query was successful and nonzero in the case of an error.

Example

error = mysql_real_query(&mysql, "SELECT * FROM people WHERE name like 'Bill%'",
        44);
if (error) { 
     printf("Error with query!\n"); 
     exit(1);
}

int mysql_reload(MYSQL *mysql)

Reloads the permission tables on the MySQL database server. You must have Reload permissions on the current connection to use this function. If the operation is successful, zero is returned otherwise a nonzero value is returned.

Example

result = mysql_reload(&mysql);

unsigned int mysql_row_tell(MYSQL_RES *result)

Returns the value of the cursor used as mysql_fetch_row reads the rows of a result set. The return value of this function can used with mysql_row_seek to jump to a specific row in the result set.

Example

saved_pos = mysql_row_tell(results);
/* I can now jump back to this row at any time */

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

Changes the current database. The user must have permission to access the new database. The function returns zero if the operation was successful and nonzero in the case of an error.

Example

result = mysql_select_db(&mysql, "newdb");

int mysql_shutdown(MYSQL *mysql)

Shutdown the MySQL database server. The user must have Shutdown privileges on the current connection to use this function. The function returns zero if the operation was successful and nonzero in the case of an error.

Example

result = mysql_shutdown(&mysql);

char *mysql_stat(MYSQL *mysql)

Returns information about the current operating status of the database server. This includes the uptime, the number of running threads, and the number of queries being processed, among other information.

Example

printf("Server info\n-----------\n%s\n", mysql_stat(&mysql));

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Reads the entire result of a query and stores in a MYSQL_RES structure. Either this function or mysql_use_result must be called to access return information from a query. You must call mysql_free_result to free the MYSQL_RES structure when you are done with it. The function returns a null value in the case of an error.

Example

MYSQL_RES results;
mysql_query(&mysql, "SELECT * FROM people");
results = mysql_store_result(&mysql);
/* 'results' now contains all of the information from the 'people' table */

unsigned long mysql_thread_id(MYSQL * mysql)

Returns the thread ID of the current connection. This value can be used with mysql_kill to terminate the thread in case of an error.

Example

thread_id = mysql_thread_id(&mysql);