Online Documentation Server
 ПОИСК
ods.com.ua Web
 КАТЕГОРИИ
Home
Programming
Net technology
Unixes
Security
RFC, HOWTO
Web technology
Data bases
Other docs

 


 ПОДПИСКА

 О КОПИРАЙТАХ
Вся предоставленная на этом сервере информация собрана нами из разных источников. Если Вам кажется, что публикация каких-то документов нарушает чьи-либо авторские права, сообщите нам об этом.




MySQL Chapter Four (C API)

Documentation Version: 0.95
MySQL Version: 3.20.29

Overview

This chapter documents the C Application Programming Interface (API) supplied with the MySQL database system. The API supports a rich assortment of functions that allow complete access to the MySQL database engine from a client program, regardless of whether it is running locally or on a remote system.

Preparation

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

    #include "mysql.h"

You will also need to link in the math (for encryption) and mysqlclient libraries:

    $ cc -I/usr/include/mysql -L/usr/lib/mysql myapp.c -o myapp -lm -lmysqlclient

The include files are typically located in /usr/include/mysql, and the client libraries can be found in /usr/lib/mysql.

If you have a memory leak in your client you can compile with the --with-debug=yes option. This will cause the client code to use the 'safe_malloc' package in the MySQL client library. You would call TERMINATE(stdout) or my_end(1) in your client application before exiting to get a list of all memory leaks. Check the file mysys/safemalloc.c in the source distribution for further details.

Following is a sample MySQL client program that would simply perform a SELECT and then display all returned rows on standard output. While not all API functions are included, it should give you an idea of the typical client program layout.

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
    fprintf( stderr, "%s\n", mysql_error(&mysql) );
    exit( exitcode );
}

int main()
{
    uint i = 0;

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

    if (mysql_select_db(&mysql,"payroll"))
        exiterr(2);

    if (mysql_query(&mysql,"SELECT name,rate FROM emp_master"))
        exiterr(3);

    if (!(res = mysql_store_result(&mysql)))
        exiterr(4);

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

    if (!mysql_eof(res))
        exiterr(5);

    mysql_free_result(res);
    mysql_close(&mysql);
}


Client Functions

The MySQL API uses a MYSQL data structure (defined in mysql.h) to establish a connection with the database engine. You may set up multiple connections from a single client program, however, each connection must be assigned to its own separate MYSQL structure.

After a successful query, if data is to be returned to the client, the result set must be transferred via either the mysql_store_result or mysql_use_result functions. Both of these functions store the result set in a MYSQL_RES structure. The difference is that mysql_store_result reads the entire result set into memory on the client, where mysql_use_result instructs the client to retrieve a row dynamically from the server with each call to mysql_fetch_row. Keep in mind, however, that mysql_use_result ties up server resources and thus should not be used for interactive applications where user actions are often unpredictable and could result in extended delays. Note also that you may have only one connection open that uses mysql_user_result, and it must be the most recently created one. An additional consideration is the fact that by default the mysqld process will close the connection after thirty seconds of idle time.

Data retrieved from the result set with mysql_fetch_row is placed into a MYSQL_ROW structure, which is simply an array of pointers to the beginning of each field.


mysql_affected_rows

SYNOPSIS:

    int mysql_affected_rows(MYSQL *mysql)

DESCRIPTION:

    Retrieves the number of rows affected by the last UPDATE, DELETE or INSERT.

RETURN VALUE:

  • An integer >0 indicating the number of changed/retrieved rows.
  • Zero if no records matched the WHERE clause in an UPDATE or DELETE.
  • -1 if the query returned an error, for example and attempt was made to add a duplicate primary key during an INSERT.

EXAMPLE:

mysql_query( &mysql, "INSERT INTO gl_transact (acctnbr, amount)
VALUES (12345, 651.30)" );

if ( mysql_affected_rows( &mysql ) < 0 )
   fprintf( stderr, "Attempted to add duplicate primary key\n" );

MySQL is optimized for the 'delete all records in a table' case. A side effect of this optimization is that MySQL will return zero for the number of rows affected in this situation. Doing a 'select count(*) from the_table' before deleting all records will give you a value for the number of rows affected, though this value may change between the SELECT and and DELETE since MySQL 3.20.X does not support table locking. This is fixed in version 3.21.X


mysql_close

SYNOPSIS:

    void mysql_close(MYSQL *mysql);

DESCRIPTION:

    Closes a previously opened connection.

    mysql_close must be called after completing all operations performed via the MySQL connection. If not done, the thread created by mysql_connect will linger until the built-in timeout is reached. On a busy server this can quickly eat a lot of memory, although it should take very little CPU time.

    If a connection is closed before a running query has completed, the query will continue until it attempts to return any part of the result set to the client. It will then die upon noticing that the connection is no longer active.

    The default timeout is 30 seconds for an active query and 8 hours for an open connection.

RETURN VALUE:

    None.

mysql_connect

SYNOPSIS:

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

DESCRIPTION:

    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, 'localhost' is assumed.
    • if user is NULL, '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 alloc 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.

RETURN VALUE:

  • mysql if the connection was successful.
  • NULL if unsuccessful.

EXAMPLE:

    MYSQL *mysql;
    
    if((mysql=malloc(sizeof(MYSQL)))!=NULL)
    {
    if (!(mysql_connect(mysql,"pr_server","jqpublic","mypasswd")))
    {
    free( mysql );
    exit( 1 );
    }
    }
    free( mysql );
    

mysql_create_db

SYNOPSIS:

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

DESCRIPTION:

    Creates the database named in db on the machine pointed to by mysql. The MySQL connection must have been made with a user ID that has create privileges. (Refer to chapter 5 for more details on user privileges.)

RETURN VALUE:

  • Zero if the database was successfully created.
  • Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_data_seek

SYNOPSIS:

    void mysql_data_seek(MYSQL_RES *res, uint offset);

DESCRIPTION:

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

RETURN VALUE:

    None

mysql_drop_db

SYNOPSIS:

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

DESCRIPTION:

    Drop the database named in db from the machine pointed to by mysql. The connection must have been made with a user ID that has drop privileges for the specified database. (Refer to chapter 6 for more details on user privileges.)

RETURN VALUE:

  • Zero if the database was successfully dropped.
  • Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_eof

    SYNOPSIS:

      int mysql_eof(MYSQL_RES *)

    DESCRIPTION:

      Returns a value != 0 if the last call to mysql_fetch_row returned nothing because the end of the result set has been reached.


    mysql_error

    SYNOPSIS:

      char *mysql_error(MYSQL *mysql)

    DESCRIPTION:

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

      An empty string will be returned if no error occurred.


    mysql_fetch_field

    SYNOPSIS:

      MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle);

    DESCRIPTION:

      Find out what type a table field is.


    mysql_fetch_lengths

    SYNOPSIS:

      unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql)

    DESCRIPTION:

      Returns the length of all columns in a query result set. If you plan on retrieving data that contains a \0 you must use this function to get the actual length of the field value.


    mysql_fetch_row

    SYNOPSIS:

      MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql);

    DESCRIPTION:

      Fetch the 'next' row in the query result. Will return a NULL pointer when all rows have been retrieved.


    mysql_field_seek

    SYNOPSIS:

      void mysql_field_seek(MYSQL_RES *result, int field)

    DESCRIPTION:

      Put the column cursor on column number field, which should be within the range from 0 to mysql_num_fields(MYSQL_RES*)-1


    mysql_free_result

    SYNOPSIS:

      void mysql_free_result(MYSQL_RES *result);

    DESCRIPTION:

      Free memory used to store a query result. Should be called whenever you have finished using the results of a mysql_store_result() call


    mysql_get_client_info

    SYNOPSIS:

      char *mysql_get_client_info(void);

    DESCRIPTION:

      This function simply returns a string containing version information for the client library currently in use.


    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. MySQL implements dynamic protocols based on client capabilities. In version 3.20.X this doesn't really do anything, but in future versions it will for example allow one client to connect using the current protocol, while another connects using encryption and compression.


    mysql_get_server_info

    SYNOPSIS:

      char *mysql_get_server_info(MYSQL *mysql);

    DESCRIPTION:

      Returns the version number of the server.


    mysql_insert_id

    SYNOPSIS:

      int mysql_insert_id(MYSQL *mysql)

    DESCRIPTION:

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


    mysql_list_dbs

    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_fields

    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

    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_list_tables

    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_num_fields

    SYNOPSIS:

      int mysql_num_fields(MYSQL_RES *result);

    DESCRIPTION:

      This macro returns the number of columns (fields) in a query result.

    EXAMPLE:

      MYSQL mysql;
      MYSQL_RES *result;
      int fields;
      
      if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
      {
      	result = mysql_store_result( &mysql );
      	if ( result != NULL )
      	{
      		fields = mysql_num_fields( result );
      		printf( "Retrieved %u fields\n", fields );
      	}
      	else
      		printf( "Query failed\n" );
      } 
      else
      	abort();
      

    SEE ALSO:


    mysql_num_rows

    SYNOPSIS:

      int mysql_num_rows(MYSQL_RES *result);

    DESCRIPTION:

    EXAMPLE:

      MYSQL mysql;
      MYSQL_RES *result;
      int rows;
      
      if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
      {
      	result = mysql_store_result( &mysql );
      	if ( result != NULL )
      	{
      		rows = mysql_num_rows( result );
      		printf( "Retrieved %u rows\n", rows );
      	}
      	else
      		printf( "Query failed\n" );
      } 
      else
      	abort();
      

    SEE ALSO:


    mysql_query

    SYNOPSIS:

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

    DESCRIPTION:

      Executes the SQL query pointed to by query on the database pointed to by mysql. This function will return zero if the query executed properly. A non-zero result indicates an error. A call to mysql_error will retrieve the error message.

      Calling mysql_num_rows will give you 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 newly assigned field value by checking mysql_insert_id.


    mysql_real_query

    SYNOPSIS:

      int mysql_real_query(MYSQL *mysql, const char *query, uint length);

    DESCRIPTION:

      This function is called by mysql_query after it does a strlen function call to calculate the length of the query string. It could be used if your program allocates a fixed buffer for the query string.

      You'll have to use this function if you have data with un-escaped \0 values.


    mysql_reload

    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_select_db

    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 server will use the login and password contained in mysql to authenticate the connection.

      A successful call to mysql_connect is necessary before mysql_select_db can be used.

      In general mysql_select_db must be called successfully before attempting to query a database. The exceptions are queries such as the following.

      SHOW DATABASES like 'A%';

      SELECT 1+1; # SELECT without using tables.


    mysql_shutdown

    SYNOPSIS:

      int mysql_shutdown(MYSQL *mysql);

    DESCRIPTION:

      Shut down a MySQL database engine. User must have shutdown 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. This is essentially the same as the mysqladmin programs stat option.


    mysql_store_result

    SYNOPSIS:

      MYSQL_RES *mysql_store_result(MYSQL *mysql);

    DESCRIPTION:

      Reads the result to the client. You 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. You can check for errors with:

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

      A call to mysql_free_result() must be made when you're done to free memory.


    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.



With any suggestions or questions please feel free to contact us