home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Book HomeMySQL and mSQLSearch this book

Chapter 19. C Reference

19.1. MySQL C API

The MySQL C API uses several defined datatypes beyond the standard C types. These types are defined in the `mysql.h' header file that must be included when compiling any program that uses the MySQL library.

19.1.1. Datatypes

MYSQL

A structure representing a connection to the database server. The elements of the structure contain the name of the current database and information about the client connection among other things.

MYSQL_FIELD

A structure containing all of the information concerning a specific field in the table. Of all of the types created for MySQL, this is the only one whose member variables are directly accessed from client programs. Therefore it is necessary to know the layout of the structure:

char *name

The name of the field.

char *table

The name of the table containing this field. For result sets that do not correspond to real tables, this value is null.

char *def

The default value of this field, if one exists. This value will always be null unless mysql_list_fields is called, after which this will have the correct value for fields that have defaults.

enum enum_field_types type

The type of the field. The type is one of the MySQL SQL datatypes.

unsigned int length

The size of the field based on the field's type.

unsigned int max_length

If accessed after calling mysql_list_fields, this contains the length of the maximum value contained in the current result set.

unsigned int flags

Zero or more option flags. The following flags are currently defined:

NOT_NULL_FLAG

If defined, the field cannot contain a NULL value.

PRI_KEY_FLAG

If defined, the field is a primary key.

UNIQUE_KEY_FLAG

If defined, the field is part of a unique key.

MULTIPLE_KEY_FLAG

If defined, the field is part of a key.

BLOB_FLAG

If defined, the field is of type BLOB or TEXT.

UNSIGNED_FLAG

If defined, the field is a numeric type with an unsigned value.

ZEROFILL_FLAG

If defined, the field was created with the ZEROFILL flag.

BINARY_FLAG

If defined, the field is of type CHAR or VARCHAR with the BINARY flag.

ENUM_FLAG

If defined, the field is of type ENUM.

AUTO_INCREMENT_FLAG

If defined, the field has the AUTO_INCREMENT attribute.

TIMESTAMP_FLAG

If defined, the field is of type TIMESTAMP.

unsigned int decimals

When used with a numeric field, it lists the number of decimals used in the field.

The following macros are provided to help examine the MYSQL_FIELD data:

IS_PRI_KEY(flags)

Returns true if the field is a primary key.

IS_NOT_NULL(flags)

Returns true if the field is defined as NOT NULL.

IS_BLOB(flags)

Returns true if the field is of type BLOB or TEXT.

IS_NUM(type)

Returns true if the field type is numeric.

MYSQL_FIELD_OFFSET

A numerical type indicating the position of the "cursor" within a row.

MYSQL_RES

A structure containing the results of a SELECT (or SHOW) statement. The actual output of the query must be accesses through MYSQL_ROW elements of this structure.

MYSQL_ROW

A single row of data returned from a SELECT query. Output of all MySQL data types are stored in this type (as an array of character strings).

my_ulonglong

A numerical type used for MySQL return values. The value ranges from to 1.8E19, with -1 used to indicate errors.

mysql_affected_rows

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

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

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

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

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

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

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);
mysql_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));
mysql_escape_string

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_fetch_field

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_fetch_field_direct

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_fetch_fields

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

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_fetch_row

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_seek

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_tell

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

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

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()));
mysql_get_host_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));
mysql_get_proto_info

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());
mysql_get_server_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);
mysql_info

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_init

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);
}
mysql_insert_id

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

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_list_dbs

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_list_fields

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_list_processes

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_list_tables

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

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

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

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

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_real_connect

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);
}
mysql_real_query

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);
}
mysql_reload

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

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

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

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

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_store_result

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

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


Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.