When an UPDATE query causes no change in the value of a row, that row is not usually considered to be affected. However, if the CLIENT_FOUND_ROWS flag is set when connecting to the MySQL server (see the mysql_real_connect function), any rows that match the WHERE clause of the UPDATE query are considered affected.

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 */
  
/* Make any of 'HR', 'hr', 'Hr', or 'hR' into 'HR'. This is an easy way to
   force a consistent capitalization in a field.
mysql_query(&mysql, "UPDATE people SET dept = 'HR' WHERE dept LIKE 'HR'");
affected = mysql_affected_rows(&mysql);
/* By default, 'affected' will contain the number of rows that were changed.
   That is, the number of rows that had a dept value of 'hr', 'Hr' or 'hR'.
   If the CLIENT_FOUND_ROWS flag was used, 'affected' will contain the number
   of rows that matched the where. */

Example

if (! mysql_change_user( &mysql, new_user, new_pass, new_db ) ) {
      printf("Change of User unsuccessful!");
      exit(1);
}
/* At this point, the connection is operating under the access rights of the
   new username, and the new database is the default. */

Example

printf("This server uses the %s character set by default\n",
         mysql_character_set_name(&mysql));

Example

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

TIP: This function has been deprecated in the newer releases of MySQL, and the mysql_real_connect function should be used instead.

Example

/* Create a connection to the local MySQL server using the name "bob" and
   password "mypass" */
MYSQL mysql;
if(!mysql_connect(&mysql, "", "bob", "mypass")) {
                  printf("Connection error!\n");
                  exit(0);
}
/* If we've reached this point we have successfully connected to the database
   server. */

TIP: This function has been deprecated in the newer releases of MySQL. MySQL now supports the CREATE DATABASE SQL statement. This should be used, via the mysql_query function, instead.

Example

/* Create the database 'new_database' */
result = mysql_create_db(&mysql, "new_database");

Example

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

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

TIP: This function has been deprecated in the newer releases of MySQL. MySQL now supports the DROP DATABASE SQL statement. This should be used, via the mysql_query function, instead.

Example

/* Destroy the database 'old_database' */
result = mysql_drop_db(&mysql, "old_database");

Example

result = mysql_dump_debug_info(&mysql);
/* The server's logs should now contain information about this connection. 
   If something went wrong so that this is not the case, 'result' will have
   a false value.*/

TIP: This function has been deprecated in the newer releases of MySQL. The mysql_errno and mysql_error functions return more information about any errors that occur, and they are more reliable.

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

Example

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

Example

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

TIP: While not officially deprecated, this function is generally inferior to the mysql_real_escape_string function, which does everything this function does, but also takes into account the character set of the current connection, which may affect certain escape sequences.

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

Example

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

Example

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

Example

MYSQL_FIELD *field; /* A pointer to a single field */
MYSQL_FIELD *fields; /* A pointer to an array of fields */
 
/* Retrieve all the field information for the results */
fields = mysql_fetch_fields(results);
/* Assign the third field to 'field' */
field = fields[2];

TIP: This function is especially useful when reading binary data from a BLOB. Since all MySQL data is retrieved as strings (char *), it is common to use the strlen( ) function to determine the length of a data value. However, for binary data, strlen( ) returns inaccurate results, because it stops at the first null character. In these cases, you can use mysql_fetch_lengths to retrieve the accurate length for a data value.

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

Example

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

Example

MYSQL_FIELD field;
MYSQL_RES *result;
 
// A query has been executed and returned success
result = mysql_store_result( );
if (! result ) {
     // Ooops, the result pointer is null, either the query was a non-SELECT
     // query or something bad happened!
     if ( mysql_field_count(&mysql) ) {
          // The number of columns queried is greater than zero, it must have
          // been a SELECT query and an error must have occurred.
     } else {
          // Since the number of columns queried is zero, it must have been
          // a non-SELECT query, so all is well...
     }
}

Example

MYSQL_FIELD field;
 
/* result is a MYSQL_RES structure containing a result set */
/* ... do some stuff */
/* Seek back to the beginning of the row */
old_pos = mysql_field_seek(results, 0);
/* Fetch the first field of the row */
field = mysql_fetch_field(results);
/* Go back to where you were */
mysql_field_seek(results, old_pos);

Example

MYSQL_FIELD field1, field2, field3;
/* results is a MYSQL_RES structure containing a result set */
 
/* Record my current position */
old_pos = mysql_field_tell(results);
/* Fetch three more fields */
field1 = mysql_fetch_field(results);
field2 = mysql_fetch_field(results);
field3 = mysql_fetch_field(results);
/* Go back to where you where */
mysql_field_seek(results, old_pos);

Example

MYSQL_RES *results;
/* Do work with results */
/* free results... we know it's not null since we just did work with
   it, but we'll check just to be safe. */
if (results)
    mysql_free_result(results);

Example

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

Example

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

Example

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

Example

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

The format of the returned string depends on the query. If the query is INSERT INTO or ALTER TABLE, the string is:

Records: n Duplicates: n Warnings: n

If the query is LOAD DATA INFILE, the string is:

Records: n Deleted: n Skipped: n Warnings: n

If the query is UPDATE, the string is:

Rows matched: n Changed: n Warnings: n

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));
/* The printed string looks like this:
Records: 30 Deleted: 0 Skipped: 0 Warnings: 0
*/

If you pass a null pointer, mysql_init creates a MYSQL structure, initializes it, and returns a pointer to the structure. Structures created by this function are freed automatically when mysql_close is called. A null value is returned if there is not enough memory available to initialize the structure.

WARNING: As of the current release of MySQL, MySQL clients will crash on certain platforms (such as SCO Unix) when you pass in a pointer to a MYSQL structure that you allocated yourself. If this is happening to you, just pass in NULL and use the pointer created by the MySQL library. As a bonus, you don't have to worry about freeing the structure if you do this.

Example

MYSQL mysql;
 
if (!mysql_init(&mysql)) {
                printf("Error initializing MySQL client\n");
                exit(1);
}
/* Now you can call mysql_real_connect( ) to connect to a server... */
/* Alternative method: */
MYSQL *mysql;

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

TIP: The MySQL-specific SQL function LAST_INSERT_ID( ) also returns the value of the most recent auto-increment. In addition, it is not reset after each query, so it can be called at any time to retrieve that value of the last auto-increment INSERT executed during the current session.

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);
/* As soon as we run another query, mysql_insert_id will return 0 */

The process IDs are part of the process information returned by the mysql_list_processes function.

Example

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

TIP: The information obtained from this function can also be obtained through an SQL query using the statement SHOW databases.

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_free_result( databases );
/* Find all databases that start with 'projectName' */
databases = mysql_list_dbs(&mysql, "projectName%");

TIP: The information obtained from this function can also be obtained through an SQL query using the statement SHOW COLUMNS FROM table.

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_free_result( fields );

The returned result set contains the information in the following order:

The MySQL process ID

This is the ID used with mysql_kill to kill a thread.

Username

The MySQL username of the user executing a thread.

Hostname

The location of the client running a thread.

Database

The current database for the client running a thread.

Action

The type of action last run in a thread. All SQL queries of any type show up as Query, so this will be the most common value.

Time

The amount of time taken (in seconds) to execute the last action in a thread.

State

The state of the current thread. This indicates whether the thread is active (currently executing a command) or idle.

Info

Any extra information about the thread. For SQL queries, this will contain the text of the query.

TIP: The information obtained from this function can also be obtained through an SQL query using the statement SHOW PROCESSLIST.

Example

MYSQL_RES *threads;
MYSQL_ROW row
threads = mysql_list_processes(&mysql);
 
row = mysql_fetch_row( threads );
printf("The ID of the first active thread is %d\n", row[0]);

TIP: The information obtained from this function can also be obtained through an SQL query using the statement SHOW TABLES.

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' */

Example

/* 'results' is a MYSQL_RES result set structure */
num_fields = mysql_num_fields(results);
printf("There are %d fields in each row\n", num_fields);

Example

/* 'results' is a MYSQL_RES result set structure */
num_rows = mysql_num_rows(results);
printf("There were %d rows returned, that I know about\n", num_rows);

Example

char *data = "\000\002\001";
int data_length = 3;
char *result;
int result_length = 5; /* We don't want the final string to be longer than 5.
 extend_buffer( ) is a function that meets the criteria given above. */
mysql_odbc_escape_string( &mysql, result, result_length, data, data_length,
   NULL, extend_buffer );
/* 'result' now contains the string '\\\000\002\001'
 (that is, a backslash, followed by ASCII 0, then ASCII 2 then ASCII 1. */

Example

char *escaped = "\\'an escaped quoted string.\\'";
/* escaped contains the string: \' and escaped quoted string.\' */
mysql_odbc_remove_escape(&mysql, escaped);
/* escaped now contains the string: 'an escaped quoted string.' */

Example

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

Once a query has been executed using this function, the result set can be retrieved using the mysql_store_result or mysql_use_result function.

Example

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

Example

mysql_send_query(&mysql, "SELECT * INTO OUTFILE results.out FROM mytable");
/* This executes the query, but does not process the results, which is necessary
   in order to write the values into the outfile */
mysql_read_query_result(&mysql);
/* Now the results have been processed and the data written to the outfile */

• 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 indicates that the Unix login name of the person running the client should be used).

• The password used to authenticate the given user. If an empty string is used, only users with no passwords are checked for authentication. That is, if a user happened to have a password set to an empty string, he would never be authenticated.

• The initial database selected when you connect (an empty string indicates that no database should be selected).

• 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_IGNORE_SPACE

  Allows spaces after built-in MySQL functions in SQL queries. Traditionally, functions must be followed immediately by their arguments in parentheses. If this option is used, the function names become reserved words and cannot be used for names of tables, columns, or databases.

  CLIENT_INTERACTIVE

  Causes the server to wait for a longer amount of time (specified by the interactive_timeout server variable) before automatically breaking a connection. This is useful for interactive clients that do not allow the user to enter any data for significant periods of time.

  CLIENT_NO_SCHEMA

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

  CLIENT_COMPRESS

  Causes the client to use compression when communicating with the server.

  CLIENT_ODBC

  Tells the server the client is an ODBC connection.

  CLIENT_SSL

  Tells the client to use SSL encryption to secure the connection. The server must have been compiled to support SSL.

Example

MYSQL *mysql;
mysql = mysql_init( NULL );
/* 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);
}
 
/* or... */
/* Connect to the server at my.server.com using a compressed, secure protocol */
if (! mysql_real_connect(&mysql, "my.server.com", "bob", "mypass",
                          "", 0, "", CLIENT_COMPRESS|CLIENT_SSL)) {
          print "Error connecting!\n";
          exit(1);
}

TIP: This function is safe to use with binary data. The string can contain null characters or any other binary data. This is why it is necessary to include the length of the string. Otherwise, the MySQL library could not determine how long the string was if any null characters were present.

Example

# Properly escape a query that contains binary data.
char *data = "\002\001\000";
int original_length = 4 # 3 characters plus one for the null. 
char real_data[7]; # Twice as big as the original string (3) 
                   # plus one for the null.
int new_length;
 
new_length = mysql_real_escape_string(&mysql, data, real_data, original_length);
/* real_query can now be safely used in as a SQL query. */
/* The returned length is '4' since the only character that needed escaping
   was \000 (the null character) */

Once a query has been executed using this function, the result set can be retrieved using the mysql_store_result or mysql_use_result function.

Example

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

REFRESH_GRANT

Reloads the permissions tables. This is the same as mysql_reload( ) or the FLUSH PRIVILEGES SQL command.

REFRESH_LOG

Flushes the log files to disk and then closes and reopens them.

REFRESH_TABLES

Flushes any open tables to disk.

REFRESH_READ_LOCK

Reinstates the read lock on the database files stored on disk.

REFRESH_HOSTS

Reloads the internal cache MySQL keeps of known hostnames to limit DNS lookup calls.

REFRESH_STATUS

Refreshes the status of the server by checking the status of various internal operations.

REFRESH_THREADS

Clears out any dead or inactive threads from the internal cache of threads.

REFRESH_MASTER

Flushes, closes, and reopens the binary log that tracks all changes to MySQL tables. This log is sent to all slave servers for replication.

REFRESH_SLAVE

Resets the connection with any slave servers that are replicating the master MySQL server.

Example

/* Flush the log files and the table data to disk */
if (!mysql_refresh( &mysql, REFRESH_LOG|REFRESH_TABLES )) {
     printf("Error sending refresh command...\n");
}

TIP: This function is deprecated and will be removed in a future version of the API. The same functionality can be obtained by using the SQL query FLUSH PRIVILEGES.

Example

/* Make some changes to the grant tables... */
result = mysql_reload(&mysql);
/* The changes now take effect... */

This function is useful only if the result set contains all the data from the query. Therefore, it should be used in conjunction with mysql_store_result and not used with mysql_use_result.

Example

/* result is a result set pointer created with mysql_store_result( ) */
MYSQL_ROW_OFFSET where, other_place;
where = mysql_row_tell( result );
/* Do some more work with the result set... */
/* Go back to where you were before, but remember where you are now: */
other_place = mysql_row_seek( result, where );
/* Do some more work... */
/* Go back to the second marker: */
mysql_row_seek( result, other_place );

Example

/* results is a result set pointer created with mysql_store_result( ) */
MYSQL_ROW_OFFSET saved_pos = mysql_row_tell(results);
/* I can now jump back to this row at any time using mysql_row_seek( ) */

Example

if ( mysql_select_db(&mysql, "newdb") ) {
     printf("Error changing database, you probably don't have permission.\n");
}

Example

/* Quickly insert a row into a table */
mysql_send_query(&mysql, "INSERT INTO mytable VALUES ('blah', 'fnor')");

Example

if ( mysql_shutdown(&mysql) ) {
       printf("Server not shut down... Check your permissions...\n");
} else {
       printf("Server successfully shut down!\n");
}

Example

printf("This connection is using the %s cipher for security.\n",
             Mysql_ssl_cipher(&mysql));

Example

/* init a MYSQL structure and set SSL options...*/
/* Changed my mind, I don't want this connection to use SSL: */
mysql_ssl_clear(&mysql);

The function returns 0 on success and nonzero if an error occurs. The MySQL server and client both must have been compiled with SSL support for this function to work properly.

Example

/* 'key' contains an SSL public key.
   'cert' contains the filename of a certificate
   'ca' contains the name of the certificate authority
   'capath' contains the directory containing the certificate
*/
/* Create an initialized MYSQL structure using mysql_init */
mysql_ssl_set(&mysql, key, cert, ca, capath);
/* Now, when mysql_real_connect is called, the connection will use SSL for
   encryption. */

Example

printf("Server info\n-----------\n%s\n", mysql_stat(&mysql));
/* Output may look like this:
Server info
-----------
Uptime: 259044  Threads: 1  Questions: 24  Slow queries: 0  Opens: 6  
Flush tables: 1 Open tables: 0 Queries per second avg: 0.000
Everything below the row of hyphens is all on one line
*/

The function returns a null value in the case of an error. The function also returns a null value if the query was not of a type that returns data (such as an INSERT or UPDATE query). If you receive a null pointer and are not sure if the query was supposed to return data or not, you can call mysql_field_count to find the number of fields the query was supposed to return. If zero, then it was a non-SELECT statement, and the pointer should be null. Otherwise, an error has occurred.

If the query was a SELECT-type statement, but happens to contain no data, this function will still return a valid (but empty) MYSQL_RES structure (it will not be a null pointer).

Example

MYSQL_RES results;
mysql_query(&mysql, "SELECT * FROM people");
results = mysql_store_result(&mysql);
/* 'results' should now contain all of the information from the 'people' table */
if (!results) { printf("An error has occurred!\n"); }

/* 'query' is some query string we obtained elsewhere, 
    we're not sure what it is... */
mysql_query(&mysql, query);
results = mysql_store_result(&mysql);
if (!results) { /* An error might have occurred, 
                   or maybe this is just a non-SELECT statement */
     if (! mysql_field_count(&mysql) ) { /* Aha! This is zero so it was 
                                            a non-SELECT statement */
               printf("No error here, just a non-SELECT statement...\n");
     } else {
               printf("An error has occurred!\n");
     }
}

Example

thread_id = mysql_thread_id(&mysql);
/* This number can be used with mysql_kill( ) to terminate the current thread. */

Example

if (mysql_thread_safe( )) {
     printf("This library is thread safe... thread away!\n");
} else {
     printf("This library is *not* thread safe, be careful!\n");
}

The function returns a null value in the case of an error. The function also returns a null value if the query was not of a type that returns data (such as an INSERT or UPDATE query). If you receive a null pointer and are not sure if the query was supposed to return data or not, you can call mysql_field_count to find the number of fields the query was supposed to return. If zero, then it was a non-SELECT statement, and the pointer should be null. Otherwise, an error has occurred.

Example

MYSQL_RES results;
mysql_query(&mysql, "SELECT * FROM people");
results = mysql_store_result(&mysql);
/* 'results' will now allow access (using mysql_fetch_row) to the table
   data, one row at a time */