cybermonkey cybermonkey - 3 months ago 14
MySQL Question

Which MySQL statements require the use of a MySqlDataReader object?

When accessing a MySQL server programmatically, there are two types of statement:


  • A statement (such as
    SELECT
    ) that returns a resultset, and requires the use of a
    MySqlDataReader
    ;

  • A statement (such as
    UPDATE
    ) that does not return a resultset and therefore can be executed via a
    MySqlCommand
    object without a
    MySqlDataReader



Using a
MySqlCommand
on a statement that returns a resultset causes an exception from the MySQL driver, and likewise the same thing happens when a
MySqlDataReader
is used with a statement that does not return a resultset.

In order to have one function that can execute both types of statement, I need to filter what statements need to use a
MySqlCommand
and
MySqlDataReader
. My plan is to create an array which contains statements such as
SELECT
that needs to be dealt with
MySqlDataReader
, for example this pseudocode:

MySqlResultSet ExecuteStatement(string statement)
{
string[] statementTypes = { "SELECT", "SHOW", };
foreach (statementType = statementTypes)
{
if (statement.find(statementType){
//MySqlCommand
//Returns null.
}
else
{
//MySqlDataReader
//Returns the reader
}
}
}

Mjh Mjh
Answer

Since there's a big charade going on here, and your question isn't as silly as it seems - here's what returns data which is usually represented by most languages as some sort of usable construct:

  • SELECT (which you know how to use)
  • SHOW (for example, SHOW VARIABLES LIKE '%innodb%' or SHOW PROCESSLIST, SHOW CREATE TABLE yourtable)
  • DESCRIBE example: DESCRIBE yourtable;

Disclaimer: these are the ones that I can remember at this point, there could be more, please feel free to update the list if I've missed anything.