The MySQL C API

• All files from these note are on the linux machines.
  • Located at /home/CS/dbennett/313/C_API
  • There is also a Makefile
  • $ cp -r /home/CS/dbennett/313/DB_C_API . will give you the entire directory.
  • To build these program, type $ make
• Hidden Input
  • A system library to do prompted silent input.
  • We need this since our programs will require the database password.

  • #include <unistd.h>
    char * getpass(const char * prompt)
    	

  • Will print the prompt
  • Turn off echo (so no letters are sent back to the screen)
  • Reads up to PASS_MAX characters, or until a \n or EOF is reached
  • Turn on echo
  • Returns a pointer to the null terminated buffer holding this information
  • See hiddenInput.C
• I will focus on C since that is our language of choice.
  • Most other languages have an API
  • Support calls to C level routines.
  • There is a C++ library as well
  • There is support for this from multiple OS (But we will focus guess where?)
• The MySql C API
  • Once again, we will only touch on the interface.
  • The documentation is available.
• There are two ways to build code
  • Use a client library which communicates (perhaps over the network) with a MySQL DBMS engine.
  • Build a DBMS engine into your code.
  • We will use the first, but interestingly enough, the API is the same, it is just a matter of what library you link against.
• Building this code
  • It is normal to require additional libraries, include files when linking with an API
  • This used to make compiling a mess.
  • But modern APIs are including a configuration script.
  • mysql_config
    • mysql_config --include
    • mysql_config --libs
    • `cmd ` in bash (command substitution)
  • So my makefile contains the following lines:

    CXXFLAGS =  `mysql_config --include` 
    LDLIBS = `mysql_config --libs`
    	

  • This includes many different libraries
  • These commands are run in a subshell.
  • Then the output is stored in the variable.
  • Later things are compiled with the results.
• Include Files
  • you need to include some header files

    #include <my_global.h>
    #include <mysql.h>
    	

• Connecting to the DBMS
  • mysql_init()
  • MYSQL * mysql_init(MYSQL * mysql)
  • MYSQL is a database connection handle.
  • It should not be copied, as there is no guarantee that a proper copy will be made.
  • It is created with mysql_init and destroyed with mysql_close()
  • It can be duplicated with mysql_init too.
  • If the parameter is NULL a new connection is formed.
  • If the parameter is a valid MYSQL * , then the connection is copied.
  • A NULL return value means the initialization failed.
• Errors:
  • const char * mysql_error(MYSQL * mysql)
  • If the result is NULL (the string is of zero length) there was no error.
  • Otherwise the string contains an error message.
  • unsigned int mysql_errno(MYSQL * mysql)
  • zero means no error.
  • This is just the last error, but not necessarily from the last command.
• Options
  • There are many options to control the behavior of a connection to the DBMS
  • These can be set with mysql_options()
  • I have not yet needed any of these so I will not elaborate at this point.
  • But the options are summarized on the web page.
• Connecting
  • This must be done after a mysql_init, but before anything else.
  • 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)
    • mysql is the connection
      • This is altered in the connection process
      • If the connection is successful, a pointer to this value is returned.
      • If the connection is not successful, a NULL is returned.
    • host is the name of the host you wish to connect to
      • "localhost" or NULL for a local connection.
      • Options let you control the type of connection (pipe, socket, ...)
      • Operating system dependent stuff here.
    • user is the name of the database user.
      • Null or "" indicates use the current login name.
    • password is the user password
      • This is a plain text version of the password.
    • db is the name of the database to use.
    • port is the TCP/IP port, use 0 for localhost
    • unix_socket specifies the name of the socket or named pipe to use, NULL for default.
    • client_flag allows for some additional configuration

  •      if (NULL == mysql_real_connect(conn, "", "", password.c_str(), "dbennett", 0, NULL, 0)) {
             cout << "Connection Failed: " << endl;
             cout << mysql_error(conn) << endl;
             return -1;
         }
    	

• The next most likely thing is to perform a query
  • int mysql_query(MYSQL *mysql, const char *stmt_str)
  • int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length)
  • mysql is the connection
  • stmt_str is the string to be executed
    • It is a normal SQL command.
    • It is not terminated by a ;
  • If the stmt_string contains binary data, use mysql_real_query
  • Return is 0 on success and not 0 on failure.

  •      command = "select * from EQUIPMENT";
    
         if (!mysql_query(conn, command.c_str())) {
            cout << "Query failed: " << endl;
             cout << mysql_error(conn) << endl;
             return -1;
         }
    	
        

• Using the results of a query.
  • MYSQL_RES *mysql_store_result(MYSQL *mysql)
  • a MYSQL_RES is a structure which holds information about a result.
    • This must be freed with a mysql_free_result()
  • A NULL is returned on failure
  • or if the statement should not have had a result (INSERT)
  • But you probably don't want the mysql_store_result call there anyway.
  • This function will transfer all of the data from a query to the client when it is called.

  •      queryResult = mysql_store_result(conn);
         if (queryResult == NULL) {
         ...
         }
         ...
         mysql_free_result(queryResult);
    	

• mysql_use_result()
  • Same as mysql_store_result but does not transfer the data.
  • It is somewhat faster and uses less memory
  • But you must consume all of the data or an error will occur. (See next section)
• Getting Information about the fields:
  • MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
  • Reset with each new SELECT
  • Repeated calls loop over all of the fields in the returned table.
  • NULL is returned when no more fields are left.
  • This is a pointer to a struct.
  • There are many fields in this struct
  • A selected list:
    • char * name, name or alias from "AS"
    • char * org_name, real field name
    • char * table, name of the table.
    • char * db, the name of the database
    • unsigned long length, theoretical maximum length in bytes of the data
    • unsigned long max_length, actual maximum length of the field generated
    • unsigned int flags, information about the field (Primary, UNIQUE, NOT_NULL, ...) see table on man page.
    • enum enum_field_types type, the type of the data, see man page.
• Getting at the data
  • MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
  • Returns a MYSQL_ROW
    • Implemented as an array of counted byte strings.
    • (Because there might be binary data)
  • Returns NULL if on error or end of data.
  • unsigned int mysql_num_fields(MYSQL_RES *result)
  • Returns the number of fields in the result.

  •      fieldCount = mysql_num_fields(queryResult);
    
         cout << "There are " << fieldCount << " fields  in the result." << endl;
         while ((aRow = mysql_fetch_row(queryResult)) != NULL) {
            for(int i =0;i<fieldCount; i++) {
               if (aRow[i] !=NULL) {
                   cout << aRow[i] << "  ";
               }
            }
            cout << endl;
         }