mysql Chapter Four (C API)

Documentation Version: 0.80
mysql Version: 3.20.16

Overview

This chapter will document the C Application Programmers Interface (API) that comes with the mysql database system.

 The mysql database C API supports a rich assortment of functions that allow complete access to the mysql database engine, regardless of whether it is located locally or on a remote system.

 You will need to include the file mysql.h at the top of your C program:

#include "mysql.h"

You will also need to link in libmysql:

$ cc myapp.c -o myapp -lmysqlclient

By default these files are located in /usr/local/include and /usr/local/lib.

 (It would be cool to have an example of what needs to go at the top of a basic mysql application.)
 
 

Data Functions

The mysql database API uses a data structure called MYSQL to define a connection.
 
 

mysql_num_rows(res)

Number of rows in a query result.

SYNOPSIS:

int mysql_num_rows(MYSQL_RES *res)

 DESCRIPTION:

 Returns the number of rows in the query result variable 'res'.
 
 

mysql_num_fields(res)

Number of fields in a query result.

SYNOPSIS:

int mysql_num_fields(MYSQL_RES *res)

 DESCRIPTION:

 Returns the number of columns (fields) in the query result variable 'res'.
 
 

mysql_affected_rows(res)

Rows affected by a previous UPDATE, DELETE or INSERT.

SYNOPSIS:

int mysql_affected_rows(MYSQL_RES *res)

 DESCRIPTION:

 Returns the number of rows affected by the last UPDATE,DELETE or INSERT. May be zero if no records matched on UPDATE or DELETE, or when an attempt is made to enter a duplicate primary key during an INSERT.
 
 

mysql_insert_id(res)

ID generated for AUTO_INCREMENT fields.

 SYNOPSIS:

int mysql_insert_id(MYSQL *res)

 DESCRIPTION:

 Returns ID generated for AUTO_INCREMENT field on result variable 'res'.
 
 

mysql_field_seek(res,fieldnr)

Seek to a field.
 

SYNOPSIS:

void mysql_field_seek(MYSQL_RES *res, int fieldnr)

DESCRIPTION:

 Put the column cursor on column number 'fieldnr'. Fieldnr should be in the range from 0 to mysql_num_rows(res)-1
 
 

mysql_error(mysql)

SYNOPSIS:

 Error returned by last query.

*mysql_error(MYSQL *mysql)

 DESCRIPTION:

 The error message, if any, returned by the last mysql function call on connection 'mysql'.

mysql_error() will return and empty string if there was no error.
 
 

mysql_eof(res)

Detect EOF (End Of File.)

SYNOPSIS:

int mysql_eof(MYSQL_RES *res)

 DESCRIPTION:

 Will return a value != 0 if the last mysql_fetch_row() returned nothing because the end of the data has been reached.
 
 

Code Sample

EXAMPLE:

 Normal loop (Check the mysqladmin source for a 'nicer' example):
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;

if (!(mysql_connect(&mysql,"host","username","password"))) 
    abort();

if (mysql_select_db(&mysql,"argv[1]"))
    abort();

if (mysql_query(&mysql,"SELECT * FROM table"))
    abort();

if (!(res = mysql_store_result(&mysql)))
    abort();

while((row = mysql_fetch_row(res))) {
    uint i = 0;

    for (i=0 ; i < mysql_num_fields(res); i++) 
       printf("%s ",row[i]);

    fputc('\n',stdout);
}

if (!mysql_eof(res))
    abort();

Functions

mysql_connect

Connect to a database.

 SYNOPSIS:

MYSQL *mysql_connect(MYSQL *mysql,const char *host, const char *user, const char *passwd)

DESCRIPTION:

 Attempts to establish a connection to a mysqld database engine running on *host. Uses the value contained in *user as the user login, and the value contained in *passwd as the password for *user.
 
 

This function must be called successfully before any action may be taken on a database.
 
 

mysql_close

Close an existing connection to a database.

 SYNOPSIS:

void mysql_close(MYSQL *sock);

DESCRIPTION:

 Closes a previously opened connection. Must be called when you are done using a connection. If not called, the thread created by the mysql_connect() function call will linger until the built-in timeout is reached. On a busy server this can eat a lot of memory quickly, but should take very little cpu.

 If you close a connection before a query has completed the query will continue until it outputs any part of the result to the client, and the query will die upon noticing the connection has gone away.

 The default time limit is 30 seconds on an active query and 8 hours on a open connection.
 
 

mysql_get_client_info()

(Need to figure this out. -mke)
 
 

mysql_fetch_lengths()

Returns a array of of column lengths (of type uint)

SYNOPSIS:

mysql_fetch_lengths(MYSQL_RES *)

DESCRIPTION:

 Find out the length of all columns in a result query.
 
 

mysql_real_query()

DESCRIPTION:

 (Need to figure this out.)
 
 

mysql_select_db

Select what database you want to use.

SYNOPSIS:

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

 DESCRIPTION:

 Attempt to connect to the database pointed to by *db, on the machine pointed to by *mysql. The mysql database engine on the other side of the connection will use the login and password contained in *mysql to authenticate the connection.

 You must call mysql_connect() before using this function.

 You must call this function before attempting to make a query.
 
 

mysql_query

Issue a query to a database.

 SYNOPSIS:

int mysql_query(MYSQL *mysql, const char *q);

DESCRIPTION:

 Pass the SQL query pointed to by *q to the database named in *mysql. This function will return zero if the query executed properly. A non-zero result indicates an error. A call to mysql_error() will be required to find out the nature of the error.

 mysql_num_rows() will contain the number of rows returned by the query.

 If you have an AUTO_INCREMENT field in the table being updated and you are doing an INSERT, you can find the ID assigned by checking mysql_insert_id().
 
 

mysql_create_db

Create a new database.

SYNOPSIS:

int mysql_create_db(MYSQL *mysql, const char *DB);

DESCRIPTION:

 Create the database named by *DB on the SQL engine defined in *mysql. You must have connected with a user ID that has create privileges. (Check chapter 6 for more details on user privileges.) A return value != 0 indicates that an error has occurred. A call to mysql_error will be required to find out the nature of the error.
 
 

mysql_drop_db

Drop (Destroy) an existing database.

SYNOPSIS:

int mysql_drop_db(MYSQL *mysql, const char *DB);

 DESCRIPTION:

 Drop the database named by *DB on the SQL engine defined in *mysql. You must have connected with a user ID that has drop privileges for that database. (Check chapter 6 for more details on user privileges.) A return value != 0 indicates that an error has occurred. A call to mysql_error() will be required to find out the nature of the error.
 
 

mysql_shutdown

Shutdown the mysql database engine.

SYNOPSIS:

int mysql_shutdown(MYSQL *mysql);

 DESCRIPTION:

 Shutdown a mysql database engine. User must have shutdown privileges.
 
 

mysql_reload

Flush caches and reload privilege tables.

 SYNOPSIS:

int mysql_reload(MYSQL *mysql);

DESCRIPTION:

 Have the mysql database engine reload the user permissions table, flush all caches and close all open tables not in use. This should be done before running isamchk on any table.

 Requires user to have reload privileges.
 
 

mysql_stat

SYNOPSIS:

 char *mysql_stat(MYSQL *mysql);

DESCRIPTION:

 Returns the info similar to 'mysqladmin version' as a character string. This includes uptime in seconds, running threads, questions, reloads and open tables information.
 
 

mysql_get_server_info

SYNOPSIS:

char *mysql_get_server_info(MYSQL *mysql);

DESCRIPTION:

 Returns the version number of the server.
 
 

mysql_get_host_info

SYNOPSIS:

char *mysql_get_host_info(MYSQL *mysql);

DESCRIPTION:

 Returns name of host (same as the host argument to mysql_connect()).
 
 

mysql_get_proto_info

SYNOPSIS:

int mysql_get_proto_info(MYSQL *mysql);

DESCRIPTION:

 Get protocol version used by connection. Generally you won't be able to connect if the protocol versions differ.
 
 

mysql_list_dbs

List available databases.

 SYNOPSIS:

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

DESCRIPTION:

 Provided to ease porting of mSQL applications.

 Similar to doing 'SHOW databases [ LIKE wild-card ]' as a query.

mysql_list_tables

List tables available in a database.

 SYNOPSIS:

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

DESCRIPTION:

 Provided to ease porting of mSQL applications.

 Similar to doing 'SHOW tables [FROM database]' as a query.
 
 

mysql_list_fields

List fields in a table.

 B>SYNOPSIS:

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

DESCRIPTION:

 Provided to ease porting of mSQL applications.

 Similar to doing 'SHOW fields [FROM table] [FROM database] [LIKE wild-card]' in a query.
 
 

mysql_list_processes

List running threads of execution.

SYNOPSIS:

MYSQL_RES *mysql_list_processes(MYSQL *mysql);

DESCRIPTION:

 Get a list of the thread currently running on the mysql database engine. You must have process privileges.
 
 

mysql_store_result

SYNOPSIS:

MYSQL_RES *mysql_store_result(MYSQL *mysql);

DESCRIPTION:

 Reads the result to the client. One must use this or mysql_use_result() to get the result from the server. You must always use mysql_store_result() or mysql_use_result() after you have executed a successful query.

 mysql_store_result() returns NULL on error or if the statement didn't return any data. One can check for errors with:
 
 

if (!(result=mysql_store_result(&mysql)) && mysql_num_fields(&mysql))
        fputs(mysql_error(&mysql),stderr);

mysql_use_result

SYNOPSIS:

MYSQL_RES *mysql_use_result(MYSQL *mysql);

DESCRIPTION:

 The same as mysql_store_result(), except that the result is fetched dynamically from the server for each 'mysql_fetch_row()'. This should not be used on interactive applications since it ties up the server. This helps to hold down the memory usage on the client side.
 
 

mysql_free_result

Free memory holding a result.

SYNOPSIS:

void mysql_free_result(MYSQL_RES *result);

DESCRIPTION:

 Free memory used to store a query result.
 
 

mysql_data_seek

SYNOPSIS:

void mysql_data_seek(MYSQL_RES *mysql, uint offset);

DESCRIPTION:

 Used to seek to an arbitrary row returned by the query.
 
 

mysql_fetch_row

SYNOPSIS:

MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql);

DESCRIPTION:

 Fetch the 'next' row in the query result.
 
 

mysql_fetch_field

SYNOPSIS:

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle);

DESCRIPTION:

 Find out what type a table field is.