We will start by establishing a connection with the MySQL server and for that, we need to invoke the mysql_real_connect function. But we have to pass a MYSQL object to the mysql_real_connect function and we have to invoke the mysql_init function to create the MYSQL object. Hence, the mysql_init function is first invoked to initialize a MYSQL object by the name conn.
We will then supply the MYSQL object conn to the mysql_real_connect function, along with the valid user ID, password, and the host details. The mysql_real_connect function will establish a connection to the MySQL server running at the specified host. Besides this, the function will link to the supplied mysql database and will declare conn as the connection handler. This means that conn will be used in the rest of the program whenever we want to perform some action to the specified MySQL server and the mysql database.
If any error occurs in establishing the connection to the MySQL database engine, the program will terminate after displaying an error message. If the connection to the MySQL database engine is established successfully, the mysql_query function is invoked and the SQL statement show tables and the connection handler conn are supplied to it. The mysql_query function will execute the supplied SQL statement. To save the resulting table information of the mysql database, the mysql_use_result function is invoked. The table information that's received from the mysql_use_result function will be assigned to resultset res.
Next, we will invoke the mysql _fetch_row function in a while loop that will extract one row at a time from the resultset res; that is, one table detail will be fetched at a time from the resultset and assigned to the array row. The array row will contain complete information of one table at a time. The table name stored in the row[0] subscript is displayed on the screen. With every iteration of the while loop, the next piece of table information is extracted from resultset res and assigned to the array row. Consequently, all the table names in the mysql database will be displayed on the screen.
Then, we will invoke the mysql_free_result function to free up the memory that is allocated to resultset res and, finally, we will invoke the mysql_close function to close the opened connection handler conn.
Let's use GCC to compile the mysql1.c program, as shown here:
$ gcc mysql1.c -o mysql1 -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient
If you get no errors or warnings, that means the mysql1.c program has compiled into an executable file, mysql1.exe. Let's run this executable file:
$ ./mysql1
MySQL Tables in mysql database: columns_priv db
engine_cost event
func
general_log
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
Voila! As you can see, the output shows the list of built-in tables in the mysql database. Now, let's move on to the next recipe!