Topic : Connecting to MySQL
Author :
Page : << Previous 2  
Go to page :

sample code.

   Let us suppose that you managed to connect to the database, which will surely happen

   if(you_have_set_you_mysql_server_properly = TRUE && you_actually_started_mysqld_daemon = TRUE &&
      database_that_you_try_to_connect_exists = TRUE && you_know_your_username_and_password = TRUE &&
      you_have_put_ennormous_quantity_of_money_on_my_bank_account = TRUE);

   So you've connect to the database, and you want to get some data, don't you? You need some database, as well as some table that is inside to see the actual effect of this code, because you won't have nowhere to connect to, and nothing to read. As you know, or maybe not, most of relational databases are organized in the groups of databases that have tables inside, and in the case of MySQL user that has permission to connect to the database, has permission to read all the tables inside. Relational databases are called so, because (logically) tables inside the database could have relations between themselves, on the level of the fields, as well as on the level of the tables, but I don't want to get inside that in this text. If you want to find out more, there is plenty of the documentation floating online, that will fit your curiosity. Now we come to the problematic of the SQL language itself. SQL (or Structured Query Language) is language that is used as herald between the use and database. You use queries that you send like messages to the database, and you're waiting for the result that database will give to you. In order to use queries in MySQL, you can use two calls:

   1) mysql_query()  -YAQC (Yet Another Query Call), you just pass the connection as MYSQL object, and query as the string.

   2) mysql_real_query() - This one is better, but I haven't used it because it wasn't really necessary in   this case. You must use mysql_real_query() rather than mysql_query() for queries
that contain binary data, since binary data may contain the `\0' character.    In addition, mysql_real_query() is faster than mysql_query() since it does not call strlen() on the query string which you can notice if I note you definition   of the mysql_real_querry() call:

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

   I have used the simplest possible query in this example "SELECT * FROM my_table" (note: you should change the values of my_database and my_table to your before you compile code if you don't want to get the error message) which will return all data from the table. After you have passed the query to the database you can store data that MySQL server returned to you inside the MYSQL_RES structure. This is being made in the following line:

result = mysql_store_result(&mysql);

There has been used one more call, mysql_num_fields() that has been used to get the number of the fields in the table, and in this case it's used for the purposes of printing data to the screen.

   And now, here goes the main thing, mysql_fetch_row(). This function is being used to retrieve the real data. MySQL returns data that was processed row-by-row, or, to be more precise, when you trigger the query, MySQL will return you all the fields you have specified (in this case all fields) row-by-row by the order that you specify (in this case order is default order in the database, so there is not any special sorting). Of course, I am talking about the case when you actually demand some data from the MySQL server, you can also INSERT or DELETE data inside the table, and this will not return data in this form, but, as I've already noticed, this is not the topic of this ASCII sheet. MYSQL_ROW structure, that is return from the mysql_fetch_row() call, is actually one-dimensional array of the char arrays (popularly known as strings) which you can see in the printf statement little bit below mysql_fetch_row():

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

If you look at the source you can see that there was also used mysql_fetch_lenghts() call that returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimization, because you can avoid calling strlen(). In addition, if the result set contains binary data, you must use this function to determine the size of the data, because strlen() returns incorrect results for any field containing null characters.

/****    4-OK, fine, I have source.. how to compile the damn thing!!!    ****/

   OK, OK, relax... just a gcc some_code.c -o some_code won't work in this case, because you're referencing to the mysqlclient library, and you have to note that to linker, so you will have to use this command line:

   gcc some_code.c -g -o some_code -L /usr/lib/mysql -lmysqlclient

note: -L gives the path to the mysqlclient library, in the case of the slackware it's like it's written above, but usually (in most linux distributions) is "localized", or in other words it's in /usr/local/lib/mysql , so be aware of that!

/****       5-Final notes    ****/

   Well, I hope that this small tutorial was useful to you.

   Caboom, 16.09.2000.

Page : << Previous 2