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 (2³²1), %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
The length of the column, as specified in the CREATE TABLE statement used to create the table. For a column that is calculated as the result of an 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.

C API Data Types

Scalar Data Types

Non-Scalar Data Types

Table G.1. `MYSQL_FIELD flags` Member Values

Table G.2. Deprecated `MYSQL_FIELD flags` Member Values

Table G.3. `MYSQL_FIELD type` Member Values

Table G.4. `MYSQL_BIND buffer_type` Member Values

Accessor Macros

C API Data Types

Scalar Data Types

Non-Scalar Data Types

Table G.1. MYSQL_FIELD flags Member Values

Table G.2. Deprecated MYSQL_FIELD flags Member Values

Table G.3. MYSQL_FIELD type Member Values

Table G.4. MYSQL_BIND buffer_type Member Values

Accessor Macros

Table G.1. `MYSQL_FIELD flags` Member Values

Table G.2. Deprecated `MYSQL_FIELD flags` Member Values

Table G.3. `MYSQL_FIELD type` Member Values

Table G.4. `MYSQL_BIND buffer_type` Member Values