Get type of the returned data in MySQL query

c++, mysql, mysql-connector

Solution

The article on the MySQL site titled Developing Database Applications Using MySQL Connector/C++ contains the following subsection:

Accessing Result Set Metadata

When the SQL statement being processed is unknown until runtime, the `ResultSetMetaData` interface can be used to determine the methods to be used to retrieve the data from the result set. `ResultSetMetaData` provides information about the structure of a given result set. Data provided by the `ResultSetMetaData` object includes the number of columns in the result set, the names or labels and the types of those columns along with the attributes of each column and the names of the table, schema and the catalog the designated column's table belongs to.

When `getMetaData()` is called on a `ResultSet` object, it returns a `ResultSetMetaData` object describing the columns of that `ResultSet` object.

Signatures of some of the relevant methods are shown below. For the complete list of methods supported by `ResultSetMetaData` interface, check the resultset_metadata.h header in your Connector/C++ installation.

/* resultset.h */
ResultSetMetaData * ResultSet::getMetaData() const;

/* prepared_statement.h */
ResultSetMetaData * PreparedStatement::getMetaData() const;

/* resultset_metadata.h */
std::string ResultSetMetaData::getCatalogName(unsigned int columnIndex);
std::string ResultSetMetaData::getSchemaName(unsigned int columnIndex);
std::string ResultSetMetaData::getTableName(unsigned int columnIndex);

unsigned int ResultSetMetaData::getColumnCount();
unsigned int ResultSetMetaData::getColumnDisplaySize(unsigned int columnIndex);
std::string ResultSetMetaData::getColumnLabel(unsigned int columnIndex);
std::string ResultSetMetaData::getColumnName(unsigned int columnIndex);
int ResultSetMetaData::getColumnType(unsigned int columnIndex);
std::string ResultSetMetaData::getColumnTypeName(unsigned int columnIndex);

int ResultSetMetaData::isNullable(unsigned int columnIndex);
bool ResultSetMetaData::isReadOnly(unsigned int columnIndex);
bool ResultSetMetaData::isWritable(unsigned int columnIndex);

The following code fragment demonstrates how to retrieve all the column names or labels, their data types and the sizes along with the table name and the schema names to which they belong.

ResultSet *rs;
ResultSetMetaData *res_meta;

res_meta = rs -> getMetaData();

int numcols = res_meta -> getColumnCount();
cout << "\nNumber of columns in the result set = " << numcols << endl;

cout.width(20);
cout << "Column Name/Label";

cout.width(20);
cout << "Column Type";

cout.width(20);
cout << "Column Size" << endl;

for (int i = 0; i < numcols; ++i) {
  cout.width(20);
  cout << res_meta -> getColumnLabel (i+1);

  cout.width(20); 
  cout << res_meta -> getColumnTypeName (i+1);

  cout.width(20); 
  cout << res_meta -> getColumnDisplaySize (i+1) << endl;
}

cout << "\nColumn \"" << res_meta -> getColumnLabel(1);
cout << "\" belongs to the Table: \"" << res_meta -> getTableName(1);
cout << "\" which belongs to the Schema: \"" << res_meta -> getSchemaName(1) << "\"" << endl;

//delete res_meta;
delete rs;

From release 1.0.5 onwards, the connector takes care of cleaning the `ResultSetMetaData` objects automatically when they go out of scope. This will relieve the clients from deleting the `ResultSetMetaData` objects explicitly. Due to the implicit destruction of the metadata objects, clients won't be able to delete the `ResultSetMetaData` objects directly. Any attempt to delete the `ResultSetMetaData` object results in compile time error. Similarly using `auto_ptr` template class to instantiate an object of type `ResultSetMetaData` results in compile time error. For example, compilation of the above code fails with Connector/C++ 1.0.5 and later versions, when the statement `delete res_meta;` is uncommented.

Prepared Statements and the Result Set Metadata

`PreparedStatement::getMetaData()` retrieves a `ResultSetMetaData` object that contains information about the columns of the `ResultSet` object, which will be returned when the `PreparedStatement` object is executed.

Because a `PreparedStatement` object is precompiled, it is possible to know about the `ResultSet` object that it will return without having to execute it. Consequently, it is possible to invoke the method `getMetaData` on a `PreparedStatement` object rather than waiting to execute it and then invoking the `ResultSet::getMetaData` method on the `ResultSet` object that is returned.

The method `PreparedStatement::getMetaData` is supported only in Connector/C++ 1.0.4 and later versions.

Problem

Original source