Topic : Connecting to MySQL
Author :
Page : 1 Next >>
Go to page :

MySQL (un)Torn apart

/****      1-Introduction    ****/

Good Evening!

   This what you are reading is short introductory on how to connect to MySQL database using C/C++. This text will be usefully, especially due to the fact that there are some mistakes in the official MySQL manual considering the references to the wrong types as the result from the mysql_connect() call. This should give you the basic on how to handle MySQL with C/C++ as well as how to read API documentation. At  the end of the reading, you should be able to get and put data into your database, of course, I won't handle SQL syntax here, but just MySQL API.
I personally think that you should first check SQL documentation, for example in the official MySQL documentation, or to look for some free HTML books on web that are dealing with SQL, because with this text you'll know how to connect to MySQL database, but you won't be able to handle the data. Don't worry, SQL is easy, at least basic part, it's like you're talking to the database: "give me that....", "I want only data for the people whose name starts with jo", etc. Learn it, it will be useful.

/****      2-Every tale has bad beginning    ****/

   Well, this part will be easy for all of you that were playing with PhP+MySQL before, in fact, MySQL C/C++ API is same for PhP (and, basically, for all other programming languages), the only difference is that you have to have to take care about types, and that is some cases you have to pass some pointers to the calls, rather than passing real values, but wait and you'll see...

   As you know, as any other API, this also is consisted of some "calls", of "functions" that you can refer to, as well as of some "types" that could be, for instance, some structs, objects, etc.. This is necessary to know in order to use API, because every function (except void ones) return some type/struct, or pointer to some type/struct. As well, you need to have in mind to include mysql.h in your project, so you could use this API in the first place.

   We should start listing the C API datatypes(this list you can find in MySQL documentation):  

   1) MYSQL        - This structure represents a handle to one database connection. It is used for almost all MySQL functions. You will use it to connect to a database, as well as for retrieving a data from    the database you are connected to...  

   2) 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. You just can't avoid use of this structure, of course, only if you want to get some result :).

   3) 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(). This object you will use very often. In fact, you will use it every time when you want to get some "readable" result from the database, not abstract one.

   4) MYSQL_FIELD  - This structure contains information about a field, such as the field's name, type and size. Its members are described in more detail below. 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. Check the official MySQL documentation for the members    that are contained in the MYSQL_ROW structure.

   5) MYSQL_FIELD_OFFSET -  This is a type-safe representation of an offset into a MySQL field list. (Used by
              mysql_field_seek().) Offsets are field numbers within a row, beginning at zero.

   6) my_ulonglong - The type used for the number of rows and for mysql_affected_rows(), mysql_num_rows() and mysql_insert_id(). This type provides a range of 0 to 1.84e19. On some systems, attempting to print a value of type my_ulonglong will not work. To print such a value, convert it to unsigned long and use a %lu print format. Example:

printf (Number of rows: %lu\n", (unsigned long)mysql_num_rows(result));

   Actually, in the most cases you will be able to get through by using MYSQL, MYSQL_RES and MYSQL_ROW, if you don't have any special needs, but you just want to fetch some data from known database.

/****      3-Let us actually do something    ****/

   In previous chapter we have introduced some datatypes, let us fill them in this chapter. At this moment I think that it's not bad idea to throw you some source code that I've made of pieces that can be found in MySQL manual that I could use to make my explanation easier to understand:

#include <mysql/mysql.h>
#include <stdio.h>

int main(){

   MYSQL mysql;
   MYSQL_ROW row;
   MYSQL_RES *result;
   unsigned int num_fields;
   unsigned int i;


   if (!mysql_real_connect(&mysql,"localhost","root","","MyDatabase",0,NULL,0))
      fprintf(stderr, "Failed to connect to database: Error: %s\n",
   else {
      if(mysql_query(&mysql, "SELECT * FROM my_table"));
         //here goes the error message :o)
      else {
         result = mysql_store_result(&mysql);
         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] \t", (int) lengths[i], row[i] ? row[i] : "NULL");

   return 0;


   OK, I have explained those three types that are initialized before (MYSQL, MYSQL_RES, MYSQL_ROW), but sometimes that just isn't enough, isn't it, we actually have to use the, so the first thing that we have to do is to use mysql_init() on the MYSQL object to prepare it for object that is suitable to mysql_connect(). Look the example, and you will see the ...mysql_init(&mysql);... line. After that goes the actual connection to the MySQL, and the beauty of the MySQL API is that you don't have to deal with the socket programming, but instead this will handled by the MySQL's API. For that I used mysql_real_connect(), but I could actually use mysql_connect() as well. It would be very nice of me that I explain that "weird stuff between the brackets", so I shall do so! Let me give you the definition of the mysql_real_connect() that you can find in the documentation as well:

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 int client_flag)

   The first one parameter is mysql structure that we initialized for the connection, and now we use it to perform one. Next three parameters say all for themselves, first one is the host that we want to connect to (where is MySQL server that we want to connect to, in this case I connected to the server that resides on the local machine, and I could use "" as well), second one is *user that we use to log on the server, and *pass is password that is used to autentificate the user (I used the default one for root connection, and that is "", you should change that to your password if you changed default root password, and you actually should do that anyway, this is very insecure if your machine is in the any kind of network). You should check MySQL documentation and read part about security, autentification, and ownership! Now, the three last parameters are usually, by order used - 0, NULL, and 0, but I will explain them as well. First one is the port that you connect to MySQL server, and should be left to 0 unless MySQL server that you're connecting to listens on some other port than default. Second one is unix_socket string that specifies the socket or named pipe that should be used, unless it NULL (which is usually like that). Third one is the client_flag which is usually 0, but it can take following values in cases explained below:

1) CLIENT_FOUND_ROWS  - Return the number of found (matched) rows, not the number of affected rows

2) CLIENT_NO_SCHEMA  - Don't allow the db_name.tbl_name.col_name syntax. This is for ODBC; it causes the parser    to generate an error if you use that syntax, which is useful for trapping bugs in some    ODBC programs.

3) CLIENT_COMPRESS    - Use compression protocol
4) CLIENT_ODBC - The client is an ODBC client. This changes mysqld to be more ODBC-friendly.

   As you supposed, connecting is the first thing that has to be done, and actually you must do mysql_real_connect() (or mysql_connect()) in order to use other calls from MySQL API, with the exception of mysql_get_client_info(). In case of the error, MySQL will send you callback which you can read with mysql_error(). Usage of this API function is illustrated in the

Page : 1 Next >>