C API Functions
|
|
The C API functions are for opening and closing connections to the server, performing queries, analyzing query results, debugging, and performing administrative tasks. You'll need to know them well and know how they interact with the C API data types to master the C API.
mysql_affected_rows
my_ulonglong mysql_affected_rows(MYSQL *mysql)
Returns the number of rows affected by the last query (for example, the number of rows removed with a DELETE statement or the number of rows returned from a SELECT statement, in which case it's the same as the mysql_num_rows function.). It returns –1 on error.
With UPDATE statements, the row is not counted as affected if it matched the condition but no changes were made, unless the CLIENT_FOUND_ROWS flag is set when connecting with mysql_ real_connect().
For example:
/* Update the customer table, and return the number of records affected */ mysql_query(&mysql, "UPDATE customer SET first_name='Jackie' WHERE– surname='Wood')"; affected_rows = mysql_affected_rows(&mysql);
mysql_change_user
my_bool mysql_change_user(MYSQL *mysql, char *username, char *password, char *database)
Changes the current MySQL user (the one that logged in) to another user (specifying that user's username and password). You can also change database at the same time or open a new connection; otherwise, the current connection and database will be used. This returns true if successful or false if not, in which case the existing user and details are maintained.
For example:
if (! mysql_change_user(&mysql, 'guru2b', 'g00r002b', 'firstdb')) { printf("Unable to change user and or database!"); }
mysql_character_set_name
char *mysql_character_set_name(MYSQL *mysql)
Returns the name of the default character set (usually ISO-8859-1, or Latin1).
For example:
printf("The default character set is: %s \n", mysql_character_set_name(&mysql));
mysql_close
void mysql_close(MYSQL *mysql)
Closes the connection and frees the resources.
For example:
mysql_close(&mysql);
mysql_connect
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
For connecting to MySQL. The function has been deprecated, so instead use mysql_real_ connect().
mysql_create_db
int mysql_create_db(MYSQL *mysql, const char *db)
For creating a database. The function has been deprecated, so instead use mysql_query().
mysql_data_seek
void mysql_data_seek(MYSQL_RES *res, unsigned int offset)
Moves the internal row pointer (0 is the first row) associated with the results returned from
mysql_store_result() to a new position. The offset is the row to move to, starting at 0.
For example:
mysql_data_seek(results, mysql_num_rows(results)-1);
mysql_debug
mysql_debug(char *debug)
To use, the MySQL client needs to have been compiled with debugging enabled. Uses the Fred Fish debug library.
For example:
/* Traces application 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)
For dropping a database. The function has been deprecated, so instead use mysql_query().
mysql_dump_debug_info
int mysql_dump_debug_info(MYSQL *mysql)
Writes connection debug information into the log. The connection needs the SUPER privilege to be able to do this. This returns 0 if it succeeded; otherwise it returns a nonzero result.
For example:
result = mysql_dump_debug_info(&mysql);
mysql_eof
my_bool mysql_eof(MYSQL_RES *result)
Checks whether the last row has been read. The function has been deprecated, so instead use mysql_err() or mysql_errno().
mysql_errno
unsigned int mysql_errno(MYSQL *mysql)
Returns the error code for the most recent API function or 0 if there have been no errors. You can retrieve the actual text of the error using the mysql_error() function.
For example:
error = mysql_errno(&mysql);
mysql_error
char *mysql_error(MYSQL *mysql)
Returns the error message (in the current server language) for the most recent API function or an empty string if there was no error. If there have been no errors in the connection, the function returns 0.
For example:
printf("Error: '%s'\n", mysql_error(&mysql));
mysql_escape_string
unsigned int mysql_escape_string(char *to, const char *from, unsigned int length)
Returns a string with all characters that could break the query escaped (a backslash placed before them). Instead, use mysql_real_escape_string() because it respects the current character set.
mysql_fetch_field
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
Returns the field data of the current field. You can call this function repeatedly to return data of the following fields in the result. This returns a null value when there are no more fields to return.
For example:
while((field = mysql_fetch_field(results))) { /* .. process results by accessing field->name, field->length etc */ }
mysql_fetch_field_direct
MYSQL_FIELD * mysql_fetch_field_direct(MYSQL_RES * result,– unsigned int field_number)
Returns field data of the specified field (which starts at 0).
For example:
/* Return the second field */ field = mysql_fetch_field_direct(results, 1);
mysql_fetch_fields
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES * result)
Returns an array of field data from each field in the result.
For example:
unsigned int num_fields; unsigned int i; MYSQL_FIELD *fields; /* Returns the number of fields in the result */ num_fields = mysql_num_fields(result); /* Returns an array of field data */ fields = mysql_fetch_fields(result); /* ... Access field data as fields[0].name, fields[1].table and so on */
mysql_fetch_lengths
unsigned long *mysql_fetch_lengths(MYSQL_RES *result)
Returns an array of the lengths of the fields from the current row (called with mysql_fetch_ row()) in the result set, or null if there was an error.
This is the only function that correctly returns the length of binary fields (for example, BLOBs).
For example:
unsigned long *lengths; /* Return the next row of data */ row = mysql_fetch_row(results); /* Return the array of lengths */ length_array = mysql_fetch_lengths(results); /* ... Access lengths as length_array[0], length_array[1] and so on */
mysql_fetch_row
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
Returns the next row from the result or null if there are no more rows or an error.
For example:
MYSQL_ROW row; row = mysql_fetch_row(results); /* Access the row data as row[0], row[1] and so on
mysql_field_count
unsigned int mysql_field_count(MYSQL *mysql)
Returns the number of fields in the last executed query. It allows you to determine whether a NULL returned from mysql_use_result() or mysql_store_result() is because of an error or because it shouldn't return a result (a non-SELECT type query). For checking the number of fields in a successful result set, use mysql_num_fields().
For example:
results = mysql_store_result(&mysql); /* test if no result set found */ if (results == NULL) { /* if no result, test whether the field count was zero or not. if (mysql_field_count(&mysql) > 0) { /* the query was a SELECT-type, so the null store_result is an error */ } else { /* no error, as the query was an INSERT-type, which returns no fields */ } }
mysql_field_seek
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)
Moves the internal field pointer (which starts at 0) to the specified field. The next call to mysql_fetch_field() will the specified field. This returns the previous field position pointer.
mysql_field_tell
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)
Returns the current position of the field pointer.
For example:
/* Record the current position */ current_pos = mysql_field_tell(results);
mysql_free_result
void mysql_free_result(MYSQL_RES *result)
Frees the resources allocated to a result set.
mysql_get_client_info
char *mysql_get_client_info(void)
Returns a string containing the client's MySQL library version.
For example:
/* Displays - Client library version is: 4.0.2 (for example) */ printf("Client library version is: %s\n", mysql_get_client_info());
mysql_get_host_info
char *mysql_get_host_info(MYSQL *mysql)
Returns a string containing the connection information.
For example:
/* Displays - Type of connection: Localhost via UNIX socket (for example) */ printf("Type of connection: %s", mysql_get_host_info(&mysql));
mysql_get_proto_info
unsigned int mysql_get_proto_info(MYSQL *mysql)
Returns an integer containing the protocol version (for example, 10) used by the connection.
For example:
/* displays - Protocol version: 10 (for example) */ printf("Protocol version: %d\n", mysql_get_proto_info(&mysql));
mysql_get_server_info
char *mysql_get_server_info(MYSQL *mysql)
Returns a string containing the MySQL server version (for example, 4.0.3).
For example:
/* displays - Server version: 4.0.3-beta-log (for example) */ printf("Server version: %s\n", mysql_get_server_info(&mysql));
mysql_info
char *mysql_info(MYSQL *mysql)
Returns a string containing detailed information about the most recent query. Detailed information includes records, rows matched, changes, and warnings.
For example:
/* Query info String format: Rows matched: 19 Changed: 19 Warnings: 0 (for example) */ printf("Query info: %s\n", mysql_info(&mysql));
mysql_init
MYSQL *mysql_init(MYSQL *mysql)
Returns an initialized MySQL handle, ready for a mysql_real_connect().
The argument can be a null pointer, in which case a structure will be created, or a pointer to an existing MYSQL structure. The mysql_close() function frees the resources from the structure if it created it. If you passed an existing structure, you'll need to free the resources yourself after the connection is closed.
mysql_insert_id
my_ulonglong mysql_insert_id(MYSQL *mysql)
Returns a value containing the most recently inserted AUTO_INCREMENT value or 0 if the most recent query did insert an auto incremented value.
For example:
last_auto_increment = mysql_insert_id(&mysql);
mysql_kill
int mysql_kill(MYSQL *mysql, unsigned long process_id)
Requests that MySQL kill the thread specified by the process_id. This returns 0 if operation was successful or a nonzero value if it failed.
Requires that you have the PROCESS privilege.
For example:
kill = mysql_kill(&mysql, 1293);
mysql_list_dbs
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
Returns a result set containing the names of the databases on the server that match the wild regular expression (equivalent to the SQL statement SHOW DATABASES LIKE 'wild') or null if there was an error. This returns all databases if passed a null pointer.
For example:
MYSQL_RES database_names; /* returns a list of all databases with 'db' in the name */ database_names = mysql_list_dbs(&mysql, "%db%"); /* ... Don't forget to free the resources at a later stage mysql_free_result(database_names);
mysql_list_fields
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
Returns a result set containing the names of the fields in the specified table that match the wild regular expression (equivalent to the SQL statement SHOW COLUMNS FROM tablename LIKE 'wild') or null if there was an error. This returns all fields if passed a null pointer.
For example:
MYSQL_RES field_names; /* returns a list of all fields with 'name' in the name */ field_names = mysql_list_fields(&mysql, "customer", "%name%"); /* ... Don't forget to free the resources at a later stage mysql_free_result(field_names);
mysql_list_processes
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
Returns a result set containing a description of the currently running database server threads or null if there was an error. Returns the same information as returned through the SHOW PROCESSLIST statement (that is, the process ID, username, hostname, database, action, time, state, and info). You can then as usual pass the result to mysql_fetch_row() to access the results.
For example:
MYSQL_RES *threadlist; MYSQL_ROW row threadlist = mysql_list_processes(&mysql); row = mysql_fetch_row(threadlist); /* Access the thread data as row[0], row[1] and so on /* ... Don't forget to free the resources at a later stage mysql_free_result(threadlist);
mysql_list_tables
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
Returns a result set containing the names of the tables in the current database that match the wild regular expression (equivalent to the SQL statement SHOW TABLES LIKE 'wild') or null if there was an error. This returns all fields if passed a null pointer.
For example:
MYSQL_RES tablelist; /* returns a list of all tables with 'customer' in the name */ tablelist = mysql_list_tables(&mysql, "%customer%"); /* ... Don't forget to free the resources at a later stage mysql_free_result(tablelist);
mysql_num_fields
unsigned int mysql_num_fields(MYSQL_RES *result)
Returns the number of fields in the query result. Use the mysql_field_count() function to check for errors, and use this function to check number of fields in a successful result set.
For example:
num_fields = mysql_num_fields(results);
mysql_num_rows
int mysql_num_rows(MYSQL_RES *result)
Returns the number of rows in a query result (only the results to date if mysql_use_result() was used to get the result set).
For example:
num_rows = mysql_num_rows(results);
mysql_options
int mysql_options(MYSQL *mysql, enum mysql_option option, void *value)
Sets extra connect options for the connection about to be made. It can be called multiple times and is called after mysql_init() and before mysql_real_connect(). This returns 0 if successful or a nonzero value if it was passed an invalid option. The options are as follows:
MYSQL_OPT_CONNECT_TIMEOUT An unsigned int *, specifies the connect timeout in seconds.
MYSQL_OPT_COMPRESS Uses the compressed client/server protocol.
MYSQL_OPT_LOCAL_INFILE An optional pointer to uint. LOAD DATA LOCAL is enabled if the pointer points to nonzero unsigned integer or none is supplied.
MYSQL_OPT_NAMED_PIPE On Windows NT, uses named pipes to connect to the server.
MYSQL_INIT_COMMAND A char *, specifies a query to run as soon as the connection is established (including an automatic reconnection).
MYSQL_READ_DEFAULT_FILE A char *, causes the options to be from the name file, instead of the usual configuration file (my.cnf or my.ini).
MYSQL_READ_DEFAULT_GROUP A char *, causes the options to be read from the named group inside the configuration file (my.cnf or my.ini, or as set by MYSQL_READ_DEFAULT_ FILE).
For example:
MYSQL mysql; mysql_init(&mysql); /* use compression, and flush the tables upon connecting */ mysql_options(&mysql, MYSQL_OPT_COMPRESS, 0 ); mysql_options(&mysql, MYSQL_INIT_COMMAND, "FLUSH TABLES" ); /* ... continue and connect */ if(!mysql_real_connect(&mysql, "localhost", "guru2b", "g00r002b",– "firstdb", 0,NULL,0)) { printf("The following connection error occurred %s\n", mysql_error(&mysql)); }
mysql_ping
int mysql_ping(MYSQL *mysql)
Returns 0 if the MySQL server is up or a nonzero value if not. If the ping fails, the program will try to reconnect.
mysql_query
int mysql_query(MYSQL *mysql, const char *query)
Performs the specified query. It returns 0 if successful or a nonzero value if there was an error. To perform a query with binary data (which could contain the null character), you need to use
mysql_real_query() instead. You should use this function for dropping and creating databases as well, replacing the deprecated mysql_create_db() and mysql_drop_db() functions.
You can then retrieve the result, if applicable, with the mysql_store_result() or mysql_use_result() functions.
For example:
query_result = mysql_query(&mysql, "CREATE DATABASE seconddb");
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)
Establishes a connection to the MySQL server with the specified arguments, as follows:
MYSQL *mysql An existing MYSQL structure, created when you called mysql_init().
const char *host The hostname or IP address of the MySQL server. It can be an empty string, same as when you connect to MySQL from a client on the same machine.
const char *user The username.
const char *passwd The password for the specified user.
const char *db The database to connect to (it can be null).
uint port Specifies the port for the TCP/IP connection (if used). 0 is the default port.
const char *unix_socket Specifies the filename for the Unix socket, or name pipe, for connecting locally. This can be null to accept the default.
You can also pass any of the following optional flags:
CLIENT_FOUND_ROWS Specifies that mysql_affected_rows() will return the number of rows that matches the query condition, not the number of rows that were actually changed.
CLIENT_IGNORE_SPACE Allows you to place spaces after function names (which has the consequence of making all functions reserved words).
CLIENT_INTERACTIVE Specifies that MySQL drops the connection after interactive_ timeout seconds rather than wait_timeout seconds (two mysql variables). This is usually used when the client will wait a longer period of time for interactive user input before running a query.
CLIENT_NO_SCHEMA Mainly used for ODBC and disallows the database_name.tablename
.fieldname syntax in a query.CLIENT_COMPRESS Ensures that the connection uses compression.
CLIENT_ODBC Tells MySQL that the client is an ODBC client, causing some behavioral changes.
CLIENT_SSL Ensures that the connection uses Secure Sockets Layer (SSL) encryption as long as this has been compiled into the server.
For example:
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql, MYSQL_OPT_COMPRESS, 0 ); mysql_options(&mysql, MYSQL_INIT_COMMAND, "FLUSH TABLES" ); if(!mysql_real_connect(&mysql, "localhost", "guru2b", "g00r002b",– "firstdb", 0, NULL,0)) { printf("The following connection error occurred %s\n", mysql_error(&mysql)); }
mysql_real_escape_string
unsigned long mysql_real_escape_string(MYSQL *mysql, char *new_string,– char *old_string, unsigned long old_string_length)
Escapes a string (old_string of length string_length) so that you can use it in a MySQL query, placing the result in new_string (which should be at least 1 byte larger than twice as large as the original string, in case every character needs escaping, as well as taking into account the null string). The characters escaped are NUL (ASCII 0), \n, \r, \, ', ", and Ctrl+Z. It returns the length of the new string.
For example:
/* the original query is 4 bytes (a,b,c and the null character) */ char *old_query = "abc\000"; /*the new length must be at least4*2+1 byted */ char new_query[9]; int new_length; /* returns the new length */ new_query_length = mysql_real_escape_string(&mysql, new_query, old_query, 4);
mysql_real_query
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
Executes the query (which can also use binary data), specifying the length as well (excluding a null character). You can then retrieve the result, if applicable, with the mysql_ store_result() or mysql_use_result() functions.
For example:
query_result = mysql_real_query(&mysql, "CREATE DATABASE seconddb");
mysql_reload
int mysql_reload(MYSQL *mysql)
A deprecated function that reloads the grant tables, assuming the connected user has Reload permission. Rather, use the mysql_query() function.
mysql_row_seek
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)
Moves the internal row pointer to the specified row, returning the original row pointer. The
MYSQL_ROW_OFFSET should be the structure returned from either the mysql_row_tell() function or another mysql_row_seek() function, not just a row number (in which case you'd use the
mysql_data_seek() function).
For example:
current_location = mysql_row_seek(result,row_offset);
mysql_row_tell
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)
Returns the current position of the row pointer. You can use this with mysql_row_seek() to move to the specified row. Use after mysql_store_result(), not mysql_use_result().
For example:
MYSQL_ROW_OFFSET current_position = mysql_row_tell(results); /* A little later..., move back to this position */ moved_position = mysql_row_seek(result,current_position);
mysql_select_db
int mysql_select_db(MYSQL *mysql, const char *db)
Changes the current database to the specified database (assuming the user has permission to change). It returns 0 if successful or a nonzero value if there was an error.
For example:
mysql_select_db(&mysql, "seconddb");
mysql_shutdown
int mysql_shutdown(MYSQL *mysql)
Requests that the MySQL server shut down. The user must have the SHUTDOWN privilege for this to work. Returns 0 if successful or a nonzero value if there was an error.
For example:
mysql_shutdown(&mysql);
mysql_stat
char *mysql_stat(MYSQL *mysql)
Returns a string containing the server status. This contains uptime, threads, questions, slow queries, opens, flush tables, open tables, and queries per second average.
For example:
/* displays (for example): Uptime: 109 Threads: 2 Questions: 199 Slow queries: 1 Opens: 4 Flush tables: 1 Open tables: 2 Queries per second avg: 1.826b */ printf("Server status: %s\n", mysql_stat(&mysql));
mysql_store_result
MYSQL_RES *mysql_store_result(MYSQL *mysql)
For all queries that return data, you need to call either this function or mysql_use_result(). This stores the query results into the MYSQL_RES structure, or returns null in case of an error or if the query did not return data (such as after a CREATE DATABASE of an INSERT). You should use mysql_field_count() to count the number of fields expected from the query. If it's not zero (when the query was not expected to return any data), then an error has occurred.
Always free up the resources afterward.
For example:
MYSQL_RES results; mysql_query(&mysql, "SELECT first_name, surname FROM customers"); results = mysql_store_result(&mysql); /* later ... */ mysql_free_result(results);
mysql_thread_id
unsigned long mysql_thread_id(MYSQL * mysql)
Returns the current thread ID of the connection, usually in order to kill it with mysql_kill().
For example:
thread_id = mysql_thread_id(&mysql);
mysql_use_result
MYSQL_RES *mysql_use_result(MYSQL *mysql)
For all queries that return data, you need to call either this function or mysql_store_result(). This function reads the data row by row, not all at once as does mysql_store_result(). It is therefore faster, but it does not allow other queries to be run until all the data has been returned, making locking more of a problem than usual. It returns a null value in case of an error or if the query did not return data (such as after a CREATE DATABASE of an INSERT). You should use mysql_field_count() to count the number of fields expected from the query. If it's not zero (when the query was not expected to return any data), then an error has occurred.
For example:
MYSQL_RES results; mysql_query(&mysql, "SELECT first_name,surname FROM customer"); results = mysql_use_result(&mysql); /* can now use mysql_fetch_row() to access data one row at a time * /* later ... */ mysql_free_result(results);
|
|
