mysql_query() and mysql_store_result()

Now we want to improve the class Connection, our wrapper to the MYSQL object as available through the MyQSL Connector/C.

If we use a Connection object, as described in the previous post, we could just estabilish a connection to a MySQL server on creation and close it as it is destroyed. Adding a way to execute a SQL statement on the opened connection is a natural request.

The Connector/C function mysql_query() is meant for this task; it requires the address of the MYSQL object on which operate, and the statement itself, as a c-string. The return value is an error code, or 0 in case of no error.

That is fine for SQL statements that do not return any interesting value, like CREATE TABLE, or INSERT INTO. But this is not enough for SELECT, where we actually want to see which rows are returned.

For that task we have to call another function, mysql_store_result(), that returns a pointer to the MYSQL_RES structure containg the so called resultset generated from the previous call to mysql_query().

The flag field_count in the MYSQL object is used to signal if there is a resultset to be retrieved, as result of query execution.

So, let's write a first version of a query function for the Connection class.
We change the interface adding the declaration of the new public function:

class Connection
{
   // ...
public:
   //...
   MYSQL_RES* query(const char* command);

And here is the definition:

MYSQL_RES* Connection::query(const char* command)
{
   if(mysql_query(&mysql_, command)) // 1.
       throw MySQLException(error());

   if(mysql_.field_count == 0) // 2.
       return 0;

   return mysql_store_result(&mysql_); // 3.
}

1. in case of error, we throw an exception containing error code and error message.
2. we check the field_count to see if a resultset is expected, if not, we return NULL.
3. otherwise we return the resultset. Notice that in this case the memory associated to the MYSQL_RES pointer has to be freed by a call to mysql_free_result().

Now we can start doing stuff on the MySQL server, for instance create a database:

c.query("CREATE DATABASE mytest");

Create a table:

c.query("CREATE TABLE drivers(name VARCHAR(30), champ INT)");

Or insert a row in a table:

c.query("INSERT INTO drivers VALUES('Juan Manuel Fangio', 5)");

If we check the value returned by all these call we'll find that always a NULL is returned.