Appendix A. Reference

MySQL Data Types

The following are the members you can find in this structure.

You may use the following macros to determine the boolean status of the flags member:

IS_PRI_KEY(n)	Is this field a primary key?
IS_NOT_NULL(n)	Is this field defined as NOT NULL?
IS_BLOB(n)	Is this field a blob?

There are additional bit fields in the flag member that you may look for:

NOT_NULL_FLAG	Field can't be NULL.
PRI_KEY_FLAG	Field is part of a primary key.
UNIQUE_KEY_FLAG	Field is part of a unique key.
MULTIPLE_KEY_FLAG	Field is part of a key.
BLOB_FLAG	Field is a blob.
UNSIGNED_FLAG	Field is unsigned.
ZEROFILL_FLAG	Field is zerofill.
BINARY_FLAG	Field is binary type.
ENUM_FLAG	Field is an enum.
AUTO_INCREMENT_FLAG	Field is a autoincrement field.
TIMESTAMP_FLAG	Field is a timestamp.

There is a macro to test if the field type is a numeric type. Pass the type member to IS_NUM(type) to return the boolean result of whether or not it is numeric.

The type field may be one of the following:

FIELD_TYPE_CHAR,FIELD_TYPE_TINY	char
FIELD_TYPE_INTERVAL,FIELD_TYPE_ENUM	enum
FIELD_TYPE_DECIMAL	decimal, numeric
FIELD_TYPE_SHORT	smallint
FIELD_TYPE_LONG	int
FIELD_TYPE_FLOAT	float
FIELD_TYPE_DOUBLE	double, real
FIELD_TYPE_NULL	NULL-type field.
FIELD_TYPE_TIMESTAMP	timestamp
FIELD_TYPE_LONGLONG	bigint
FIELD_TYPE_INT24	mediumint
FIELD_TYPE_DATE	date
FIELD_TYPE_TIME	time
FIELD_TYPE_DATETIME	datetime
FIELD_TYPE_YEAR	year
FIELD_TYPE_SET	set
FIELD_TYPE_BLOB	blob, text (use length to determine exact type)
FIELD_TYPE_STRING	A string type field.

Client Functions

unsigned long mysql_affected_rows(MYSQL * mysql)

Retrieves the number of rows affected by the last query. Must be called after mysql_store_result with a SELECT query. May be called immediately after mysql_query with INSERT, UPDATE and DELETE queries. This function may not be used with mysql_use_result.

An integer >0 indicating the number of rows affected or retrieved.

Zero if no records matched the WHERE clause in the query or no query has yet been executed.

-1 if the query returned an error or called before mysql_store_result was called on a SELECT query.

mysql_query(&mysql,"INSERT
   INTO customer (name,age) VALUES ('George',25)

num_rows = mysql_affected_rows(&mysql);
if(num_rows<0)
	fprintf(stderr,"Failed to insert record.\n");

void mysql_close(MYSQL * mysql)

Closes a previously opened connection.

None.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

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

Attempts to establish a connection to a MySQL database engine running on host. The value of host may be either a hostname or an IP address. The user parameter contains the user's MySQL login ID, and the passwd parameter contains the password for user. NOTE: Do not attempt to encrypt passwd before calling mysql_connect. Encryption is handled automatically by the client API.

• If host is NULL, then a connection to the localhost is assumed.
• If user is NULL, the current user is assumed. Under Windows ODBC, the current user must be explicitly specified. Under Unix the current login ID is assumed.
• If password is NULL then only records in the user table without a password entry will be checked for a match. This allows the db-admin to setup the MySQL privilege system in such a way that a user gets different privileges depending on whether they have specified a password or not.

mysql_connect must complete successfully before any action is allowed to be performed on a database.

You may optionally specify the first argument of mysql_connect to be (MYSQL*) 0 This will force the C API to automatically allocate memory for the connection structure and free it on close. The downside of this approach is that you can't retrieve an error message from mysql_connect when you use this option.

A MYSQL connection handle if the connection was successful.

NULL if the connection was unsuccessful.

CR_CONN_HOST_ERROR - Failed to connect to MySQL server.

CR_CONNECTION_ERROR - Failed to connect to local MySQL server.

CR_IPSOCK_ERROR - Failed to create an IP socket.

CR_OUT_OF_MEMORY - Out of memory.

CR_SOCKET_CREATE_ERROR - Failed to create UNIX socket.

CR_UNKNOWN_HOST - Failed to find the IP address for a hostname.

CR_VERSION_ERROR - Protocol mismatch. Attempted to connect to a server with a client library which uses a different protocol version.

MYSQL mysql;

if(!mysql_connect(&mysql,"host","username","password"))
	fprintf(stderr,"Failed to connect to database.\n");

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

Creates the database named in db.

Zero if the database was successfully created.

Non-zero if an error occurred.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

if(mysql_create_db(&mysql,"my_new_db"))
	fprintf(stderr,"Failed to create new database.\n");

void mysql_data_seek(MYSQL_RES *result,unsigned int offset)

Seeks to an arbitrary row in a query result set. May not be used in conjunction with mysql_use_result.

None.

void mysql_debug(char *debug)

Adds the given string to the debug stack. Uses the Fred Fish debug library. You must compile the client library to support debugging.

None.

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

Drop the database named in db from the machine pointed to by mysql.

Zero if the database was successfully dropped.

Non-zero if an error occurred.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

if(mysql_drop_db(&mysql,"some_database"))
	fprintf(stderr,"Failed to drop the database.\n");

int mysql_dump_debug_info(MYSQL *mysql)

Instructs the server to dump some debug information to the log.

Zero if the command was successful.

Non-zero if the command failed.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

User must have process privilege.

my_bool mysql_eof(MYSQL_RES *result)

Returns a non-zero value if the last call to mysql_fetch_row returned nothing because the end of the result set has been reached. May only be used with mysql_use_result. This function is usefully for checking if an error occurred with mysql_fetch_row.

The preferred method to check for errors is to use mysql_errno.

Zero if the end of the result set has not yet been reached.

Non-zero if the end of the result set has been reached.

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);

while((row = mysql_fetch_row(result))) {
	//do something with data
}

if(!mysql_eof(result)) {
	//mysql_fetch_row failed due to some error
}

char * mysql_error(MYSQL *mysql)

The error message, if any, returned by the last MySQL error on connection mysql. An empty string will be returned if no error occurred.

A character string which explains the error.

The language of the client error messages may be changed while compiling the MySQL client library.

unsigned int mysql_errno(MYSQL *mysql)

Returns the error code of the last error on connection mysql. Zero is returned if no error has occurred. Client error messages may be checked in errmsg.h. Server error messages are located in the server source code under sql/derror.h

Error code value. Zero if no error has occurred.

unsigned int mysql_escape_string(char *to,const char *from,unsigned int from_length)

Encodes the string from and stores it into to in a suitable way to send it to the server. from must be from_length bytes long. You must allocate to to be at least from_length*2+1 bytes long. to is NUL terminated.

Characters encoded are: NUL, \n, \r, \ and '

Returns the length of the value placed into to not including the NUL-terminating character.

MYSQL_FIELD * mysql_fetch_field(MYSQL_RES *result)

Returns the definition of one column as a MYSQL_FIELD structure. Call this repeatedly to retrieve all columns in the result set.

The MYSQL_FIELD structure of the current column.

NULL if no columns are left.

MYSQL_FIELD * field;

while((field = mysql_fetch_field(result))) {
	printf("field name %s\n",field->name);
}

MYSQL_FIELD * mysql_fetch_field_direct(MYSQL_RES * result,unsigned int fieldnr)

Returns the fieldnr column's field definition of a result set as a MYSQL_FIELD structure. fieldnr begins at zero. You may use this to retrieve any arbitrary field number.

The MYSQL_FIELD structure of the specified column.

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD * field;

num_fields = mysql_num_fields(result);
for(i=0;i<num_fields;i++) {
	field = mysql_fetch_field_direct(result,i);
	printf("Field %u is %s\n",i,field->name);
}

MYSQL_FIELD * mysql_fetch_fields(MYSQL_RES * result)

Returns an array of all MYSQL_FIELD structures for a result.

An array of all column definitions of a result as MYSQL_FIELD structures.

unsigned int num_fields; unsigned int i; MYSQL_FIELD * fields; num_fields = mysql_num_fields(result); fields = mysql_fetch_fields(result); for(i=0;i

unsigned int * mysql_fetch_lengths(MYSQL_RES *result)

Returns the lengths of the columns of the current row. If you have binary data, you will need to use this to determine the size of the data. This is also useful as an optimization to avoid strlen if you copy the data.

An array of unsigned integers representing the size of each column (does not include terminating NUL characters).

NULL if there is an error.

NULL will be returned if you call this before calling mysql_fetch_row or after retrieving all rows in the result.

MYSQL_ROW * row;
unsigned int * lengths;
unsigned int num_fields;
unsigned int i;

row = mysql_fetch_row(result);
if(row) {
num_fields = mysql_num_fields(result);
lengths = mysql_fetch_lengths(result);
for(i=0;i<num_fields;i++) {
printf("Column %u is %u bytes in length.\n",i,lengths[i]);
}
}

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Retrieves the next row of a result set. Will return NULL when there are no more rows to retrieve. When using with mysql_use_result, data is dynamically retrieved from the server, and thus errors may occur in this situation.

MYSQL_ROW for the next row.

NULL if there is an error or there are no more rows to retrieve.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

MYSQL_ROW * row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while((row = mysql_fetch_row(result))) {
	for(i=0;i<num_fields;i++) {
		printf("[%s] ",row[i]);
	}
	printf("\n");
}

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result,MYSQL_FIELD_OFFSET offset)

Sets the field cursor used for mysql_fetch_field to the specified offset. Returns the previous value of the cursor.

The previous value of the field cursor.

unsigned int mysql_field_tell(MYSQL_RES * result)

Returns the current position of the field cursor used for mysql_fetch_field.

Current offset of the field cursor.

void mysql_free_result(MYSQL_RES *result)

Frees the memory allocated for the result set by mysql_store_result and mysql_use_result. Should be called whenever you are done with the result set.

char * mysql_get_client_info(void)

Returns a string which represents the version of the client library.

Character string which represents the MySQL client library version.

char * mysql_get_host_info(MYSQL *mysql)

Returns the name of the host.

Character string representing the name of the host.

unsigned int mysql_get_proto_info(MYSQL *mysql)

Returns the version of the protocol used by the client.

Integer representing the version of the protocol used by the client.

char * mysql_get_server_info(MYSQL *mysql)

Returns a string which represents the version number of the server.

Character string which represents the version number of the server.

char * mysql_info(MYSQL * mysql)

Retrieves an information string under the following situations:

• INSERT INTO ... SELECT ... will return Records: 100 Duplicates: 0 Warnings: 0 with the appropriate values.
• LOAD DATA will return Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 with the appropriate values.
• ALTER TABLE will return Records: 3 Duplicates: 0 Warnings: 0 with the appropriate values.

Character string representing the query information.

NULL if no information is available.

unsigned long mysql_insert_id(MYSQL *mysql)

Returns the last AUTO_INCREMENT ID generated on the connection mysql. Use this after you have done an INSERT query into a table with an AUTO_INCREMENT column.

The value of the AUTO_INCREMENT column created.

int mysql_kill(MYSQL *mysql,unsigned long pid)

Asks the server to kill the thread specified by pid.

Zero on success.

Non-zero on failure.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

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

Retrieves a list of databases on the server as a result set.

Similar to executing the query SHOW databases [LIKE wild]

wild may be NULL to list all databases.

You are responsible to free the result set with mysql_free_result.

NULL if there is a failure.

A MYSQL_RES result set if success.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_OUT_OF_MEMORY - Out of memory.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

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

Retrieves a list of fields in the given table as a result set.

Similar to executing the query SHOW fields FROM table [LIKE wild]

wild may be NULL to list all of the fields.

You are responsible to free the result set with mysql_free_result.

NULL if there is a failure.

A MYSQL_RES result set if success.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

MYSQL_RES * mysql_list_processes(MYSQL *mysql)

Retrieves a list of threads from the server as a result set.

You are responsible to free the result set with mysql_free_result.

NULL if there is a failure.

A MYSQL_RES result set if success.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

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

Retrieves a list of tables for the current database as a result set.

Similar to executing the query SHOW tables [LIKE wild]

wild may be NULL to list all of the tables.

You are responsible to free the result set with mysql_free_result.

NULL if there is a failure.

A MYSQL_RES result set if success.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

unsigned int mysql_num_fields(MYSQL_RES *result)

Returns the number of fields in a result set.

Integer of the number of fields in a result set.

unsigned long mysql_num_rows(MYSQL_RES *result)

Returns the number of rows in the result set.

With mysql_use_result the correct result will not be returned until after all the rows have been retrieved.

Number of rows in the result set.

int mysql_query(MYSQL *mysql, const char *query)

Executes the SQL query pointed to by query.

Zero if the query was successful.

Non-zero if the query failed.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

int mysql_real_query(MYSQL *mysql, const char *q,unsigned int length)

Executes the SQL query pointed to by query which is length bytes long. This is faster than mysql_query since it does not call strlen on the query and is necessary for binary data.

Zero if the query was successful.

Non-zero if the query failed.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

int mysql_reload(MYSQL *mysql)

Asks the MySQL server to reload the permissions table.

Zero on success.

Non-zero on failure.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

User must have reload privilege.

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

Instructs that the current connection specified by mysql is to use the database specified by db. The user is authenticated to be able to use this database.

Zero on success.

Non-zero on failure.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

int mysql_shutdown(MYSQL *mysql)

Ask the database server to shutdown.

Zero on success.

Non-zero on failure.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

User must have shutdown privileges.

char * mysql_stat(MYSQL *mysql)

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

A character string of the server status.

NULL if the command failed.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

MYSQL_RES * mysql_store_result(MYSQL *mysql)

Reads the result of a query to the client, allocates a MYSQL_RES structure, and places the results into this structure. You must call this after every query which successfully retrieves data.

An empty result set will be returned if there are no rows returned.

You must call mysql_free_result once you are done with the result set.

A MYSQL_RES result structure with the results.

NULL if there was an error.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_OUT_OF_MEMORY - Out of memory.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.

unsigned long mysql_thread_id(MYSQL * mysql)

Returns the thread id of the current connection.

Thread id of the current connection.

MYSQL_RES * mysql_use_result(MYSQL *mysql)

Same as mysql_store_result except that the rows are not pre-loaded into the result structure. Instead, they are retrieved dynamically with each call to mysql_fetch_row. This helps minimize memory on the client.

You may not use mysql_affected_rows or mysql_data_seek with this function.

A MYSQL_RES result structure.

NULL if there was an error.

CR_COMMANDS_OUT_OF_SYNC - Commands have been executed in the improper order.

CR_OUT_OF_MEMORY - Out of memory.

CR_SERVER_GONE_ERROR - The MySQL server has gone away.

CR_SERVER_LOST - Lost connection to server during query.

CR_UNKNOWN_ERROR - An unknown error occurred.