Building a Store Application With MySQL++ and C/C++ - Cases 2 and 3
(Page 5 of 7 )
Case 2: The user wishes to see the total number of categories available in the database. The second case is almost identical to the first one, in fact you can use the same code and just change the SQL statement. However, since we're here to learn, let me introduce you to a slightly different way of finding out the number of rows.
Instead of using mysql_store_result() we will use mysql_use_result(). The main difference between these two functions is that mysql_store_result() retrieves all rows at once while mysql_use_result() requires that all of the rows be retrieved individually and manually by us.
Only after the rows have been retrieved individually will we be able to use mysql_num_rows(). This is illustrated in the following piece of code:
case '2':
{
mysql_query(pConnection,"SELECT * FROM category"); //query the database
pResult = mysql_use_result(pConnection);
while ((Row = mysql_fetch_row(pResult)))
{
}
//now that we have retrieved all the rows, we can use mysql_num_rows()
printf("\n\nThe Number Of Categories in the database: %d\n\n", mysql_num_rows(pResult));
mysql_free_result(pResult);
break;
}You may be wondering why would we want to use mysql_use_rows() when it requires more work -- but believe it or not, there is a very good reason for using it. The following segment is right from the MySQL++ documentation and explains it very well:
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.In other words, if your database will be very large you might want to turn to mysql_use_rows() to save memory.
Case 3: The user wants to expand his store and add a new category. Let's have a look at the code before we analyze it:
case '3':
{
//get the user input
printf("\n\nAdding New Category:\n");
printf("Enter Category Name:");
gets(newCategoryName);
printf("Enter Category ID:");
gets(newCategoryID);
printf("Enter Category Isle:");
gets(newCategoryIsle);
//format all the information into a query
sprintf(Query, "INSERT INTO category(ID,Category,CategoryID,Isle)
VALUES(NULL,'%s',%d,%d)", newCategoryName, atoi(newCategoryID), atoi(newCategoryIsle));
printf("Using: %s\n", Query);
if(mysql_query(pConnection,Query) != 0) //execute the query
{
printf("Error Executing the query: %s\n\n",mysql_error(pConnection));
}
else
{
printf("New Category Added\n\n");
}
break;
}We begin here by accepting values for the name of the category, the category ID, as well as the isle in which the products for this category can be found. At this point the queries become a bit complex and we need to format it properly -- this is where sprintf() come to the rescue. Sprintf (which is defined in stdio.h) works much like the regular printf() with the only exception is that it does not output the formatted string to the screen, but rather stores it in a char array. We store our newly created statement in the variable Query which we than pass to mysql_query():
mysql_query(pConnection,Query);Moreover, since we're dealing with insertion here, it would be wise to always check whether mysql_query() was successful or not. The function returns 0 upon success and a non zero value if an error has occurred. In order to find out the exact problem that caused the error we can use mysql_error() or mysql_errno(). Those two functions will give the last (most recent) error that took place.
The difference between mysql_error() and mysql_errno() is that mysql_error() will give the error in the form of a statement while mysql_errno() will give a numerical code for the error (all the error messages and numbers can be found in the mysqld_error.txt fiel that accompanies the MySQL++ download). Both functions require the database handle as their only parameter:
mysql_error(pConnection);Next: Cases 4, 5 and 6 >>
More C++ Articles
More By Igal Raizman
