C API Data Types
Data types for the MySQL client library are designed to represent the
entities you deal with in the course of a session with the server. There are
types for the connection itself, for results from a query, for a row within
a result, and for metadata (descriptive information about the columns making
up a result). The terms "column" and "field" are synonymous in the following discussion.



Scalar Data Types
MySQL's scalar data types represent values such as very large integers,
boolean values, and field or row offsets.

my_bool
A boolean type, used for the return value of mysql_change_user() and
mysql_thread_init().

my_ulonglong
A long integer type, used for the return value of functions that return row
counts or other potentially large numbers, such as mysql_affected_rows(),
mysql_num_rows(), and mysql_insert_id(). To print a my_ulonglong value, cast
it to unsigned long and use a format of %lu. For example:
	printf ("Row count = %lu\n", (unsigned long) mysql_affected_rows (conn));

The value will not print correctly on some systems if you don't do this,
because there is no standard for printing long long values with printf().
However, if the value to be printed might actually exceed the maximum
allowed by unsigned long (2321), %lu won't work, either. You'll need to
check your printf() documentation to see if there is some implementation
-specific means of printing the value. For example, a %llu format specifier
might be available.

MYSQL_FIELD_OFFSET
This data type is used by the mysql_field_seek() and mysql_field_tell()
functions to represent offsets within the set of MYSQL_FIELD structures for
the current result set.

MYSQL_ROW_OFFSET
This data type is used by the mysql_row_seek() and mysql_row_tell()
functions to represent offsets within the set of rows for the current result
set. 



Non-Scalar Data Types
MySQL's non-scalar types represent structures or arrays. Any instance of a MYSQL,
MYSQL_RES, or MYSQL_STMT structure should be considered a "black box." That is,
you should refer only to the structure itself, not to members within the
structure. The MYSQL_ROW, MYSQL_FIELD, MYSQL_BIND, and MYSQL_TIME types do not
have the same restriction. Each of these structures has members that you can 
access freely to obtain data and metadata returned as a result of a query. The
MYSQL_BIND and MYSQL_TIME structures also are used both for transmitting data to
the server and receiving results from the server.

MYSQL
The primary client library type is the MYSQL structure, which is used for
connection handlers. A handler contains information about the state of a
connection with a server. To open a session with the server, initialize a
MYSQL structure with mysql_init() and then pass it to mysql_real_connect().
After you've established the connection, use the handler to issue SQL
statements, generate result sets, get error information, and so forth. When
you're done with the connection, pass the handler to mysql_close(), after
which you should no longer use it.


MYSQL_FIELD
 The client library uses MYSQL_FIELD structures to represent metadata about
the columns in the result set, one structure per column. The number of
MYSQL_FIELD structures in the set may be determined by calling
mysql_num_fields(). You can access successive field structures by calling
mysql_fetch_field() or move back and forth among structures with
mysql_field_tell() and mysql_field_seek().

 The MYSQL_FIELD structure is useful for presenting or interpreting the
contents of data rows. It looks like this:

typedef struct st_mysql_field 
{
	 char                   *name;
	 char                   *org_name;
	 char                   *table;
	 char                   *org_table;
	 char                   *db;
	 char                   *catalog;
	 char                   *def;
	 unsigned long           length;
	 unsigned long           max_length;
	 unsigned int            name_length;
	 unsigned int            org_name_length;
	 unsigned int            table_length;
	 unsigned int            org_table_length;
	 unsigned int            db_length;
	 unsigned int            catalog_length;
	 unsigned int            def_length;
	 unsigned int            flags;
	 unsigned int            decimals;
	 unsigned int            charsetnr;
	 enum enum_field_types   type;
} MYSQL_FIELD;



MYSQL_FIELD structure members have the 
following meanings:

name
The column name, as a null-terminated string. For a column that is
calculated as the result of an expression, name is that expression in string
form. If a column or expression is given an alias, name is the alias name.
For example, the following query results in name values of "mycol"
, "4*(mycol+1)", "mc", and "myexpr":
	SELECT mycol, 4*(mycol+1), mycol AS mc, 4*(mycol+1) AS myexpr ...

org_name
This member is like name, except that column aliases are ignored. That is,
org_name represents the original column name. For a column that is
calculated as the result of an expression, org_name is an empty string.

table
The name of the table that the column comes from, as a null-terminated
string. If the table was given an alias, table is the alias name. For a
column that is calculated as the result of an expression, table is an empty
string. For example, if you issue a query like the following, the table name
for the first column is mytbl, whereas the table name for the second column
is the empty string:
	SELECT mycol, mycol+0 FROM mytbl ...

org_table
This member is like table, except that table aliases are ignored. That is,
org_table represents the original table name. For a column that is
calculated as the result of an expression, org_table is an empty string.

db
The database in which the table containing the column is located, as a 
null-terminated string. For a column that is calculated as the result of 
an expression, db is an empty string.

catalog
The catalog name. Currently, this value is always "def". This member was
introduced in MySQL 4.1.1.

def
The default value for the column, as a null-terminated string. This member
of the MYSQL_FIELD structure is set only for result sets obtained by calling
mysql_list_fields(), a deprecated function, and is NULL otherwise.

Default values for table columns also can be obtained by issuing a DESCRIBE
tbl_name or SHOW COLUMNS FROM tbl_name query and examining the result set.

length
expression, the length is determined from the elements in the expression.

max_length
The length of the longest column value actually present in the result set.
For example, if a string column in a result set contains the values "Bill"
, "Jack", and "Belvidere", the value of max_length for the column will be 9.

Because the max_length value can be determined only after all the rows have
been seen, it is meaningful only for result sets created with
mysql_store_result(). max_length is 0 for result sets created with
mysql_use_result().

name_length
The length of the name member.

org_name_length
The length of the org_name member.

table_length
The length of the table member.

org_table_length
The length of the org_table member.

db_length
The length of the db member.

catalog_length
The length of the catalog member. This member was introduced in MySQL
4.1.1.

def_length
The length of the def member.

flags
The flags member specifies attributes for the columns. Within the flags
value, attributes are represented by individual bits, which may be tested
via the bitmask constants shown in Table G.1. For example, to determine
whether a column's values are UNSIGNED, test the flags value like this:
	if (field->flags & UNSIGNED_FLAG)
 printf ("%s values are UNSIGNED\n", field->name);

 Table G.1. MYSQL_FIELD flags Member Values


flags Value
Meaning
 AUTO_INCREMENT_FLAG 
 Column has the AUTO_INCREMENT attribute
 BINARY_FLAG 
 Column has the BINARY attribute
 MULTIPLE_KEY_FLAG 
 Column is a part of a non-unique index
 NOT_NULL_FLAG 
 Column cannot contain NULL values
 PRI_KEY_FLAG 
 Column is a part of a PRIMARY KEY
 UNIQUE_KEY_FLAG 
 Column is a part of a UNIQUE index
 UNSIGNED_FLAG 
 Column has the UNSIGNED attribute
 ZEROFILL_FLAG 
 Column has the ZEROFILL attribute


BINARY_FLAG is set for columns that contain binary strings. This includes
columns for which the BINARY keyword is specified explicitly (such as CHAR 
BINARY), as well as BLOB columns.

A few flags constants indicate column data types rather than column
attributes;
they are now deprecated because you should use field->type to determine the
data
type. Table G.2 lists these deprecated constants.

 Table G.2. Deprecated MYSQL_FIELD flags Member Values


flags Value
 Meaning
 BLOB_FLAG Column contains BLOB or TEXT values
 ENUM_FLAG 
 Column contains ENUM values
 SET_FLAG 
 Column contains SET values
 TIMESTAMP_FLAG
 Column contains TIMESTAMP values


 decimals
The number of decimals for numeric columns, zero for non-numeric columns.
For example, the decimals value is 3 for a DECIMAL(8,3) column, but 0 for a
BLOB column.

charsetnr
The character set number.

type
The data type. For a column that is calculated as the result of an
expression, 
the type is determined from the types of the elements in the expression. For

example, if mycol is a VARCHAR(20) column, type is MYSQL_TYPE_VAR_STRING, 
whereas type for LENGTH(mycol) is MYSQL_TYPE_LONGLONG. The possible type values
are listed in mysql_com.h and shown in Table G.3.

 Table G.3. MYSQL_FIELD type Member Values


type Value
SQL Data Type
MYSQL_TYPE_TINY 
TINYINT
MYSQL_TYPE_SHORT 
SMALLINT
MYSQL_TYPE_INT24 
MEDIUMINT
MYSQL_TYPE_LONG 
INT
MYSQL_TYPE_LONGLONG 
BIGINT
MYSQL_TYPE_DECIMAL 
DECIMAL, NUMERIC
MYSQL_TYPE_DOUBLE 
DOUBLE, REAL
MYSQL_TYPE_FLOAT 
FLOAT
MYSQL_TYPE_STRING 
CHAR
MYSQL_TYPE_VAR_STRING 
VARCHAR
MYSQL_TYPE_BLOB 
BLOB, TEXT
MYSQL_TYPE_ENUM 
ENUM
MYSQL_TYPE_SET 
SET
MYSQL_TYPE_DATE 
DATE
MYSQL_TYPE_DATETIME 
DATETIME
MYSQL_TYPE_TIME 
TIME
MYSQL_TYPE_TIMESTAMP 
TIMESTAMP
MYSQL_TYPE_YEAR 
YEAR
MYSQL_TYPE_GEOMETRY 
Spatial type
MYSQL_TYPE_BIT 
BIT
MYSQL_TYPE_NULL 
NULL


 MYSQL_TYPE_BIT is available as of MySQL 5.0.3.

 Before MySQL 4.1, the MYSQL_TYPE_xxx flag names were known as
FIELD_TYPE_xxx. The older names still are recognized for backward
compatibility.

 You might see references to FIELD_TYPE_CHAR in older source files; that was
a one-byte type that is now called MYSQL_TYPE_TINY. Similarly,
FIELD_TYPE_INTERVAL is now called MYSQL_TYPE_ENUM.

MYSQL_RES
Statements such as SELECT or SHOW that return data to the client do so by
means of a result set, represented as a MYSQL_RES structure. This structure
contains information about the rows returned by the query.

After you have a result set, you can call API functions to get result set
data (the data values in each row of the set) or metadata (information about
the result, such as how many columns there are, their types, their lengths,
and so forth).


MYSQL_ROW
The MYSQL_ROW type contains the values for one row of data, represented as
an array of strings. All values are returned in string form (even numbers),
except that if a value in a row is NULL, it is represented in the MYSQL_ROW
structure by a C NULL pointer.

The number of values in a row is given by mysql_num_fields(). The i-th
column value in a row is given by row[i]. Values of i range from 0 to
mysql_num_fields(res_set)1, where res_set is a pointer to a MYSQL_RES result
set.

Note that the MYSQL_ROW type is already a pointer, so you should define a
row variable like this:
	MYSQL_ROW row; /* correct */

Not like this:
	MYSQL_ROW *row; /* incorrect */

Values in a MYSQL_ROW array have terminating nulls, so non-binary values may
be treated as null-terminated strings. However, data values that may contain
binary data might contain null bytes internally and should be treated as
counted strings. To get a pointer to an array that contains the lengths of
the values in the row, call mysql_fetch_lengths() like this:
	unsigned long *length;
length = mysql_fetch_lengths (res_set);

 The length of the i-th column value in a row is given by length[i]. If the
column value is NULL, the length will be zero.

MYSQL_STMT
A prepared statement handler. To create a handler, call mysql_stmt_init().
This function returns a pointer to the new handler, which can be used to
prepare a statement, execute it, and so on. When you're done with the
handler, pass it to mysql_stmt_close(), after which it should no longer be
used.

MYSQL_BIND
This structure is used with prepared statements and serves two purposes:

For input, MYSQL_BIND structures contain data to be transmitted to the
server to be bound to the parameters of a prepared statement before the
statement is executed. You set up an array of structures, and then bind them
to the statement by calling mysql_stmt_bind_param() before calling
mysql_stmt_execute() to execute the statement. The array should contain one
MYSQL_BIND structure per parameter.

Input strings are assumed to be represented in the character set indicated
by the character_set_client system variable. If this differs from the
character set of the column into which the value is stored, conversion into
the column character set occurs on the server side.

For output, after a prepared statement that produces a result set is
executed, MYSQL_BIND structures are used to fetch data values from the
result set. You set up an array of structures, and then bind them to the
statement by calling mysql_stmt_bind_result() before fetching result set
rows with mysql_stmt_fetch(). The array should contain one MYSQL_BIND
structure per column of the result set.

Output strings are assumed to be represented in the character set indicated
by the character_set_results system variable.

The MYSQL_BIND structure contains several members, but only some of them
should be considered public. Those members are shown here:

 typedef struct st_mysql_bind
 {
	 unsigned long         *length;
	 my_bool               *is_null;
	 void                  *buffer;
	 my_bool                error;
	 enum enum_field_types  buffer_type;
	 unsigned               long buffer_length;
	 my_bool                is_unsigned;
	 ...
 } MYSQL_BIND;


The following list describes the purpose of each MYSQL_BIND member, for both
input and output. True indicates a non-zero value; false indicates a zero
value.


buffer_type
The data type. This member always must be set.

For input, this is the type of value that you are sending to the server.

For output, this is the type of value that you expect the server to return.

is_unsigned
A flag that indicates whether an integer data value corresponds to an
UNSIGNED data value. This member is used only for integer data types.

For input, set this member to true if the value being sent to the server is
UNSIGNED.

For output, set this member to true if you expect the server to return an
UNSIGNED value.

is_null
A pointer to a variable that indicates whether the data value corresponds to
a NULL value. The variable should be of type my_bool.

For input, the variable pointed to should be set to true or false to
indicate that the value being sent to the server is NULL or NOT NULL. As a
special case, if the value bound to this parameter never will be NULL, you
can set is_null to zero rather than to the address of a my_bool variable.

For output, the variable pointed to will be set to true or false to indicate
that the value returned by the server is NULL or NOT NULL.

error
For output, this is a flag that indicates whether an input value was
acceptable as given without truncation. It is false if there was no error,
true if there was data truncation such as for a numeric value that is out of
range or a string value that is too long. To enable truncation checks, you
must call mysql_options() with the MYSQL_REPORT_DATA_TRUNCATION option.

The error member was introduced in MySQL 5.0.3.

buffer
The data storage area.

For input, this is a pointer to the variable that holds the data value to be
sent to the server.

For output, this is a pointer to the variable where the value returned by
the server should be stored.

buffer is always the address of the corresponding variable. For numeric
types, buffer points to a scalar variable. For string types, it points to a
char buffer. For temporal types, it points to a MYSQL_TIME structure. Table
G.4 shows how the type flags correspond to SQL data types and C types. If an
integer type corresponds to an UNSIGNED SQL type, the variable should be
defined unsigned.

Table G.4. MYSQL_BIND buffer_type Member Values


buffer_type Value
SQL Data Type
 C Type
MYSQL_TYPE_TINY 
TINYINT 
char
MYSQL_TYPE_SHORT 
SMALLINT 
short int
MYSQL_TYPE_LONG 
INT 
int
MYSQL_TYPE_LONGLONG 
BIGINT 
long long int
MYSQL_TYPE_FLOAT 
FLOAT 
float
MYSQL_TYPE_DOUBLE 
DOUBLE 
double
MYSQL_TYPE_STRING 
CHAR, VARCHAR 
char[]
MYSQL_TYPE_BLOB 
BLOB, TEXT 
char[]
MYSQL_TYPE_DATE 
DATE 
MYSQL_TIME
MYSQL_TYPE_DATETIME 
DATETIME 
MYSQL_TIME
MYSQL_TYPE_TIME 
TIME 
MYSQL_TIME
MYSQL_TYPE_TIMESTAMP 
TIMESTAMP 
MYSQL_TIME
MYSQL_TYPE_NULL 
NULL


buffer_length
The actual size in bytes of the buffer pointed to by buffer, both for input
and output. This applies only to string types (either binary or non-binary),
which can vary in length. For other data types, the length is determined by
the buffer_type value.

length
A pointer to a variable that indicates the number of bytes to be
transferred. The variable should be of type unsigned long. Like
buffer_length, this member needs to be set only for string types. For
numeric and temporal types, the length is determined from the data type.

For input, the variable pointed to should be set to indicate the number of
bytes to be sent to the server.

For output, the variable pointed to will be set by mysql_stmt_fetch() to
indicate the number of bytes actually retrieved from the server.

MYSQL_TYPE_NULL should be used only when a parameter is always NULL.
Otherwise, set the is_null member appropriately each time you execute the
statement to indicate whether the parameter is NULL.

MYSQL_TIME
This structure is used to send temporal values to the server or receive them
from the server. To associate a MYSQL_TIME structure with a MYSQL_BIND
structure, set the buffer member of the MYSQL_BIND to the address of the
MYSQL_TIME.

MYSQL_TIME is used for DATETIME, TIMESTAMP, DATE, and TIME types, but the
structure members that do not apply to a given type are ignored. For
example, the month, year, and day members do not apply to TIME values, and
the hour, minute, and second members do not apply to DATE values.

The MYSQL_TIME structure contains several members, but only some of them
should be considered public. Those members are shown here:

 typedef struct st_mysql_time
 {
	 unsigned int year;
	 unsigned int month;
	 unsigned int day;
	 unsigned int hour;
	 unsigned int minute;
	 unsigned int second;
	 unsigned long second_part;
	 my_bool neg;
	 ...
 } MYSQL_TIME


The members are used as follows:

year, month, day
The year, month, and day parts of temporal values that contain a date part.

hour, minute, second, second_part

The hour, minute, second, and fractional second parts of temporal values
that contain a time part.

neg
A flag that indicates whether the temporal value contained in the MYSQL_TIME
structure is negative.



Accessor Macros
mysql.h contains a few macros that allow you to test MYSQL_FIELD members
more conveniently. IS_NUM() tests the type member; the others listed here
test the flags member.


IS_NUM()
 is true (non-zero) if values in the column have a numeric type:
	if (IS_NUM (field->type))
printf ("Field %s is numeric\n", field->name);


IS_PRI_KEY()
 is true if the column is part of a PRIMARY KEY:
	if (IS_PRI_KEY (field->flags))
printf ("Field %s is part of primary key\n", field->name);


IS_NOT_NULL()
 is true if the column cannot contain NULL values:
	if (IS_NOT_NULL (field->flags))
printf ("Field %s values cannot be NULL\n", field->name);


IS_BLOB()
 is true if the column is a BLOB or TEXT. However, this macro
tests the deprecated BLOB_FLAG bit of the flags member, so IS_BLOB() is
deprecated as well.