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.
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.
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:
The name of the field.
The name of the table containing this field. For result sets that do not correspond to real tables, this value is null.
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.
The type of the field. The type is one of the MySQL SQL datatypes.
The size of the field based on the field's type.
If accessed after calling mysql_list_fields, this contains the length of the maximum value contained in the current result set.
Zero or more option flags. The following flags are currently defined:
If defined, the field cannot contain a NULL value.
If defined, the field is a primary key.
If defined, the field is part of a unique key.
If defined, the field is part of a key.
If defined, the field is of type BLOB or TEXT.
If defined, the field is a numeric type with an unsigned value.
If defined, the field was created with the ZEROFILL flag.
If defined, the field is of type CHAR or VARCHAR with the BINARY flag.
If defined, the field is of type ENUM.
If defined, the field has the AUTO_INCREMENT attribute.
If defined, the field is of type TIMESTAMP.
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:
Returns true if the field is a primary key.
Returns true if the field is defined as NOT NULL.
Returns true if the field is of type BLOB or TEXT.
Returns true if the field type is numeric.
A numerical type indicating the position of the "cursor" within a row.
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.
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).
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.
/* 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.
mysql_close(&mysql); /* The connection should now be terminated */
mysql_connect |
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd) |
Creates a connection to a MySQL database server. The first parameter must be a predeclared MYSQL structure. The second parameter is the hostname or IP address of the MySQL server. If the host is an empty string or localhost, a connection will be made to the MySQL server on the same machine. The final two parameters are the username and password used to make the connection. The password should be entered as plain text, not encrypted in any way. The return value is the MYSQL structure passed as the first argument, or NULL if the connection failed. (Because the structure is contained as an argument, the only use for the return value is to check if the connection succeeded.)
NOTE
This function has been deprecated in the newer releases of MySQL and the mysql_real_connect function should be used instead.
/* 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. */
mysql_create_db |
int mysql_create_db(MYSQL *mysql, const char *db) |
Creates an entirely new database with the given name. The return value is zero if the operation was successful and nonzero if there was an error.
NOTE
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.
/* Create the database 'new_database' */ result = mysql_create_db(&mysql, "new_database");
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.
/* 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.
/* 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_drop_db |
int mysql_drop_db(MYSQL *mysql, const char *db) |
Destroys the database with the given name. The return value is zero if the operation was successful and nonzero if there was an error.
NOTE
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.
/* Destroy the database 'old_database' */ result = mysql_drop_db(&mysql, "old_database");
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.
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.
/* 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
/* 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.
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.
/* 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.
/* 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.
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.
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.
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.
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.
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.
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.
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.
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:
When using queries that change tables, returns the number of rows found in the table, not the number of rows affected.
Prevent the client from using the full database.table.column form to specify a column from any database.
Use compression when communicating with the server.
Tell the server the client is an ODBC connection.
/* 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.
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.
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.
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.
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.
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.
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.
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.
thread_id = mysql_thread_id(&mysql);
mysql_use_result |
MYSQL_RES *mysql_use_result(MYSQL *mysql) |
Reads the result of a query row by row and allows access to the data through a MYSQL_RES structure. Either this function or mysql_use_result must be called to access return information from a query. Because this function does not read the entire data set at once, it is faster and more memory efficient than mysql_store_result. However, when using this function you must read all of the rows of the dataset from the server or else the next query will receive the left over data. Also, you can not run any other queries until you are done with the data in this 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.
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 */
Copyright © 2001 O'Reilly & Associates. All rights reserved.
This HTML Help has been published using the chm2web software. |