I need the metadata from a mysql resultset, is this possible?

Question

I'd like to obtain the metadata from the results of a mysql query so I can put it into a datatable in c#. I primarily need the column name and the data type. Here's a simple example.

show columns from (
 select sum(subtotal) as subtotal
 , sum(tax1+tax2+tax3+tax4) as tax
 , sum(subtotal)+sum(tax1+tax2+tax3+tax4) as total

 from tbltransmaster 
 where batchnum in (
  SELECT BatchNum
  FROM tblshiftmaster

  WHERE ClosingTS <> -1

  ORDER BY OpeningTS ASC
 )
) as x

It generates this error though

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(

select sum(subtotal) as subtotal

, sum(tax1+tax2+tax3+tax4) as tax

, sum' at line 1

Could someone provide me with some advice, or perhaps a link to assist with this?

Answer 1

show columns only works for actual tables and views, not for queries.

Since you mention C#, you're probably using ADO.NET ? You can directly fill a DataTable which figure out the data types and column names.

DbConnection conn = null;
DbConnection conn = null;
DbCommand cmd = null;
DbDataAdapter da = null;
DataTable tbl = new DataTable();

conn = new MySqlConnection(connectionString);
conn.Open();

cmd = conn.CreateCommand();
cmd.CommandText = query;

da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(tbl);

If you want to get the colum names only, you could fetch the result using a DataReader instead:

DbDataReader dbReader = cmd.ExecuteReader();
for (int i = 0; i < dbReader.FieldCount; i++) {
  Type colType = dbReader.GetFieldType(i);
  String colName = dbReader.GetName(i);
}

DbDataReader also have the .GetSchemaTable() which returns a DataTable describing the metadata of the result.