University of Belgrade



MySQL C API – TIPOVI PODATAKA

MYSQL

This structure represents a handle to one database connection. It is used for almost all MySQL functions.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row().

MYSQL_FIELD

This structure contains information about a field, such as the field's name, type, and size. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field() repeatedly. Field values are not part of this structure; they are contained in a MYSQL_ROW structure.

MySQL C API – FUNKCIJE

|Function |Description |

|mysql_affected_rows() |Returns the number of rows changed/deleted/inserted by the last UPDATE, DELETE, or INSERT |

| |query. |

|mysql_change_user() |Changes user and database on an open connection. |

|mysql_character_set_name() |Returns the name of the default character set for the connection. |

|mysql_close() |Closes a server connection. |

|mysql_connect() |Connects to a MySQL server. This function is deprecated; use mysql_real_connect() instead. |

|mysql_create_db() |Creates a database. This function is deprecated; use the SQL command CREATE DATABASE instead. |

|mysql_data_seek() |Seeks to an arbitrary row number in a query result set. |

|mysql_debug() |Does a DBUG_PUSH with the given string. |

|mysql_drop_db() |Drops a database. This function is deprecated; use the SQL command DROP DATABASE instead. |

|mysql_dump_debug_info() |Makes the server write debug information to the log. |

|mysql_eof() |Determines whether the last row of a result set has been read. This function is deprecated; |

| |mysql_errno() or mysql_error() may be used instead. |

|mysql_errno() |Returns the error number for the most recently invoked MySQL function. |

|mysql_error() |Returns the error message for the most recently invoked MySQL function. |

|mysql_escape_string() |Escapes special characters in a string for use in an SQL statement. |

|mysql_fetch_field() |Returns the type of the next table field. |

|mysql_fetch_field_direct() |Returns the type of a table field, given a field number. |

|mysql_fetch_fields() |Returns an array of all field structures. |

|mysql_fetch_lengths() |Returns the lengths of all columns in the current row. |

|mysql_fetch_row() |Fetches the next row from the result set. |

|mysql_field_seek() |Puts the column cursor on a specified column. |

|mysql_field_count() |Returns the number of result columns for the most recent query. |

|mysql_field_tell() |Returns the position of the field cursor used for the last mysql_fetch_field(). |

|mysql_free_result() |Frees memory used by a result set. |

|mysql_get_client_info() |Returns client version information as a string. |

|mysql_get_client_version() |Returns client version information as an integer. |

|mysql_get_host_info() |Returns a string describing the connection. |

|mysql_get_server_version() |Returns version number of server as an integer (new in 4.1). |

|mysql_get_proto_info() |Returns the protocol version used by the connection. |

|mysql_get_server_info() |Returns the server version number. |

|mysql_info() |Returns information about the most recently executed query. |

|mysql_init() |Gets or initializes a MYSQL structure. |

|mysql_insert_id() |Returns the ID generated for an AUTO_INCREMENT column by the previous query. |

|mysql_kill() |Kills a given thread. |

|mysql_list_dbs() |Returns database names matching a simple regular expression. |

|mysql_list_fields() |Returns field names matching a simple regular expression. |

|mysql_list_processes() |Returns a list of the current server threads. |

|mysql_list_tables() |Returns table names matching a simple regular expression. |

|mysql_num_fields() |Returns the number of columns in a result set. |

|mysql_num_rows() |Returns the number of rows in a result set. |

|mysql_options() |Sets connect options for mysql_connect(). |

|mysql_ping() |Checks whether the connection to the server is working, reconnecting as necessary. |

|mysql_query() |Executes an SQL query specified as a null-terminated string. |

|mysql_real_connect() |Connects to a MySQL server. |

|mysql_real_escape_string() |Escapes special characters in a string for use in an SQL statement, taking into account the |

| |current charset of the connection. |

|mysql_real_query() |Executes an SQL query specified as a counted string. |

|mysql_reload() |Tells the server to reload the grant tables. |

|mysql_row_seek() |Seeks to a row offset in a result set, using value returned from mysql_row_tell(). |

|mysql_row_tell() |Returns the row cursor position. |

|mysql_select_db() |Selects a database. |

|mysql_set_server_option() |Sets an option for the connection (like multi-statements). |

|mysql_sqlstate() |Returns the SQLSTATE error code for the last error. |

|mysql_shutdown() |Shuts down the database server. |

|mysql_stat() |Returns the server status as a string. |

|mysql_store_result() |Retrieves a complete result set to the client. |

|mysql_thread_id() |Returns the current thread ID. |

|mysql_thread_safe() |Returns 1 if the clients are compiled as thread-safe. |

|mysql_use_result() |Initiates a row-by-row result set retrieval. |

|mysql_warning_count() |Returns the warning count for the previous SQL statement. |

|mysql_commit() |Commits the transaction (new in 4.1). |

|mysql_rollback() |Rolls back the transaction (new in 4.1). |

|mysql_autocommit() |Toggles autocommit mode on/off (new in 4.1). |

|mysql_more_results() |Checks whether any more results exist (new in 4.1). |

|mysql_next_result() |Returns/Initiates the next result in multi-query executions (new in 4.1). |

To connect to the server, call mysql_init() to initialize a connection handler, then call mysql_real_connect() with that handler (along with other information such as the hostname, user name, and password). Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close() to terminate it.

While a connection is active, the client may send SQL queries to the server using mysql_query() or mysql_real_query(). The difference between the two is that mysql_query() expects the query to be specified as a null-terminated string whereas mysql_real_query() expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query().

For each non-SELECT query (for example, INSERT, UPDATE, DELETE), you can find out how many rows were changed (affected) by calling mysql_affected_rows().

For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT-like in that they return rows. These include SHOW, DESCRIBE, and EXPLAIN. They should be treated the same way as SELECT statements.)

There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result(). This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result(). This function initializes the retrieval, but does not actually get any rows from the server.

In both cases, you access rows by calling mysql_fetch_row(). With mysql_store_result(), mysql_fetch_row() accesses rows that have already been fetched from the server. With mysql_use_result(), mysql_fetch_row() actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths().

After you are done with a result set, call mysql_free_result() to free the memory used for it.

The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend to use mysql_store_result() more commonly.

An advantage of mysql_store_result() is that because the rows have all been fetched to the client, you not only can access rows sequentially, you can move back and forth in the result set using mysql_data_seek() or mysql_row_seek() to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows(). On the other hand, the memory requirements for mysql_store_result() may be very high for large result sets and you are more likely to encounter out-of-memory conditions.

An advantage of mysql_use_result() is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result() can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you don't have random access to rows within the result set (you can only access rows sequentially), and you don't know how many rows are in the result set until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary) without knowing whether or not the query is a SELECT. You can do this by calling mysql_store_result() after each mysql_query() (or mysql_real_query()). If the result set call succeeds, the query was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determine whether a result was actually to be expected. If mysql_field_count() returns zero, the query returned no data (indicating that it was an INSERT, UPDATE, DELETE, etc.), and was not expected to return rows. If mysql_field_count() is non-zero, the query should have returned rows, but didn't. This indicates that the query was a SELECT that failed. See the description for mysql_field_count() for an example of how this can be done.

Both mysql_store_result() and mysql_use_result() allow you to obtain information about the fields that make up the result set (the number of fields, their names and types, etc.). You can access field information sequentially within the row by calling mysql_fetch_field() repeatedly, or by field number within the row by calling mysql_fetch_field_direct(). The current field cursor position may be changed by calling mysql_field_seek(). Setting the field cursor affects subsequent calls to mysql_fetch_field(). You can also get information for fields all at once by calling mysql_fetch_fields().

For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno() and mysql_error() functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was.

mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

Description

Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it will be freed when mysql_close() is called to close the connection.

Return Values

An initialized MYSQL* handle. NULL if there was insufficient memory to allocate a new object.

Errors

In case of insufficient memory, NULL is returned.

mysql_real_connect()

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)

Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info().

The parameters are specified as follows:

• The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect() you must call mysql_init() to initialize the MYSQL structure. You can change a lot of connect options with the mysql_options() call.

• The value of host may be either a hostname or an IP address. If host is NULL or the string "localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.

• The user parameter contains the user's MySQL login ID. If user is NULL or the empty string "", the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current user name must be specified explicitly.

• The passwd parameter contains the password for user. If passwd is NULL, only entries in the user table for the user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether or not they have specified a password. Note: Do not attempt to encrypt the password before calling mysql_real_connect(); password encryption is handled automatically by the client API.

• db is the database name. If db is not NULL, the connection will set the default database to this value.

• If port is not 0, the value will be used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.

• If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Note that the host parameter determines the type of the connection.

• The value of client_flag is usually 0.

Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.

Errors

CR_CONN_HOST_ERROR

Failed to connect to the MySQL server.

CR_CONNECTION_ERROR

Failed to connect to the 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 a Unix socket.

CR_UNKNOWN_HOST

Failed to find the IP address for the hostname.

CR_VERSION_ERROR

A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the --old-protocol option.

CR_NAMEDPIPEOPEN_ERROR

Failed to create a named pipe on Windows.

CR_NAMEDPIPEWAIT_ERROR

Failed to wait for a named pipe on Windows.

CR_NAMEDPIPESETSTATE_ERROR

Failed to get a pipe handler on Windows.

CR_SERVER_LOST

If connect_timeout > 0 and it took longer than connect_timeout seconds to connect to the server or if the server died while executing the init-command.

Example

MYSQL mysql;

mysql_init(&mysql);

if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))

{

fprintf(stderr, "Failed to connect to database: Error: %s\n",

mysql_error(&mysql));

}

Note that upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up.

mysql_query()

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

Description

Executes the SQL query pointed to by the null-terminated string query. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement.

mysql_query() cannot be used for queries that contain binary data; you should use mysql_real_query() instead. (Binary data may contain the `\0' character, which mysql_query() interprets as the end of the query string.)

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this..

Return Values

Zero if the query was successful. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

You don't have to call mysql_store_result() or mysql_use_result() for other queries, but it will not do any harm or cause any notable performance if you call mysql_store_result() in all cases. You can detect if the query didn't have a result set by checking if mysql_store_result() returns 0 (more about this later one).

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this..

mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.

mysql_store_result() returns a null pointer if the query didn't return a result set (if the query was, for example, an INSERT statement).

mysql_store_result() also returns a null pointer if reading of the result set failed. You can check if you got an error by checking if mysql_error() doesn't return a null pointer, if mysql_errno() returns 0, or if mysql_field_count() returns 0.

An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointer as a return value.)

Once you have called mysql_store_result() and got a result back that isn't a null pointer, you may call mysql_num_rows() to find out how many rows are in the result set.

You can call mysql_fetch_row() to fetch rows from the result set, or mysql_row_seek() and mysql_row_tell() to obtain or set the current row position within the result set.

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

Return Values

A MYSQL_RES result structure with the results. NULL if an error occurred.

Errors

mysql_store_result() resets mysql_error and mysql_errno if it succeeds.

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)

Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client will only allocate memory for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows will be returned as part of the result set for your next query. The C API will give the error Commands out of sync; you can't run this command now if you forget to do this!

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queries until the mysql_use_result() has finished. (However, after you have fetched all the rows, mysql_num_rows() will accurately return the number of rows fetched.)

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

Return Values

A MYSQL_RES result structure. NULL if an error occurred.

Errors

mysql_use_result() resets mysql_error and mysql_errno if it succeeds.

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

mysql_fetch_fields()

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)

Description

Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.

Return Values

An array of MYSQL_FIELD structures for all columns of a result set.

Errors

None.

Example

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 < num_fields; i++)

{

printf("Field %u is %s\n", i, fields[i].name);

}

mysql_num_rows()

my_ulonglong mysql_num_rows(MYSQL_RES *result)

11.1.3.164 Description

Returns the number of rows in the result set.

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result(), mysql_num_rows() may be called immediately. If you use mysql_use_result(), mysql_num_rows() will not return the correct value until all the rows in the result set have been retrieved.

11.1.3.165 Return Values

The number of rows in the result set.

11.1.3.166 Errors

None.

mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

Retrieves the next row of a result set. When used after mysql_store_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred.

The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths(). Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.

Return Values

A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred.

Errors

Note that error is not reset between calls to mysql_fetch_row()

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

MYSQL_ROW row;

unsigned int num_fields;

unsigned int i;

num_fields = mysql_num_fields(result);

while ((row = mysql_fetch_row(result)))

{

unsigned long *lengths;

lengths = mysql_fetch_lengths(result);

for(i = 0; i < num_fields; i++)

{

printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");

}

printf("\n");

}

mysql_field_count()

unsigned int mysql_field_count(MYSQL *mysql)

If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned int mysql_num_fields(MYSQL *mysql) instead.

Description

Returns the number of columns for the most recent query on the connection.

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

Return Values

An unsigned integer representing the number of fields in a result set.

Errors

None.

Example

MYSQL_RES *result;

unsigned int num_fields;

unsigned int num_rows;

if (mysql_query(&mysql,query_string))

{

// error

}

else // query succeeded, process any data returned by it

{

result = mysql_store_result(&mysql);

if (result) // there are rows

{

num_fields = mysql_num_fields(result);

// retrieve rows, then call mysql_free_result(result)

}

else // mysql_store_result() returned nothing; should it have?

{

if(mysql_field_count(&mysql) == 0)

{

// query does not return data

// (it was not a SELECT)

num_rows = mysql_affected_rows(&mysql);

}

else // mysql_store_result() should have returned data

{

fprintf(stderr, "Error: %s\n", mysql_error(&mysql));

}

}

}

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case, you are checking directly for an error from mysql_store_result() rather than inferring from the value of mysql_field_count() whether the statement was a SELECT.

None.

mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

or

unsigned int mysql_num_fields(MYSQL *mysql)

The second form doesn't work on MySQL Version 3.22.24 or newer. To pass a MYSQL* argument, you must use unsigned int mysql_field_count(MYSQL *mysql) instead.

Description

Returns the number of columns in a result set.

Note that you can get the number of columns either from a pointer to a result set or to a connection handle. You would use the connection handle if mysql_store_result() or mysql_use_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

Return Values

An unsigned integer representing the number of fields in a result set.

Errors

None.

Example

MYSQL_RES *result;

unsigned int num_fields;

unsigned int num_rows;

if (mysql_query(&mysql,query_string))

{

// error

}

else // query succeeded, process any data returned by it

{

result = mysql_store_result(&mysql);

if (result) // there are rows

{

num_fields = mysql_num_fields(result);

// retrieve rows, then call mysql_free_result(result)

}

else // mysql_store_result() returned nothing; should it have?

{

if (mysql_errno(&mysql))

{

fprintf(stderr, "Error: %s\n", mysql_error(&mysql));

}

else if (mysql_field_count(&mysql) == 0)

{

// query does not return data

// (it was not a SELECT)

num_rows = mysql_affected_rows(&mysql);

}

}

}

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check if mysql_field_count(&mysql) is = 0. This will only happen if something went wrong.

mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

11.1.3.99 Description

Frees the memory allocated for a result set by mysql_store_result(), mysql_use_result(), mysql_list_dbs(), etc. When you are done with a result set, you must free the memory it uses by calling mysql_free_result().

11.1.3.100 Return Values

None.

11.1.3.101 Errors

None.

mysql_close()

void mysql_close(MYSQL *mysql)

Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect().

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download