c++ - 获取MySQL查询返回数据的类型

Question

我知道如何获取标准表中列的类型(例如 SHOW FIELDS FROM ...)，但是有什么方法可以从自定义表中获取返回数据的类型使用各种列选择和不同表的连接进行查询(例如 SELECT table1.var1,table2.var2 FROM table1 JOIN table2)？

Answer 1

MySQL 站点上标题为 Developing Database Applications Using MySQL Connector/C++ 的文章包含 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.