- Posted by reggie on May 2, 2007
Connector/Net's MySqlDataAdapter class has a method named FillSchema. This method is intended to populate the metadata for a given query without actually returning any of the data from the query. The only syntax that MySQL has to help with this is SQL_SELECT_LIMIT which limits the number of rows that are returned for a given select. Indeed this is what we are using to prevent the transfer of data across the wire when we only want the metadata.
However, there is a problem. SQL_SELECT_LIMIT does not work in stored procedures and also does not affect inserts, updates, or deletes. So, the upshot of this is that calling FillSchema on a stored procedure will, in fact, execute the stored procedure and return all of the data to the client. This is a waste of bandwidth but it's worse than that. It will also execute all other DML statements in the procedure.
This is different than what happens with SQL Server and different than what people might expect. With SQL Server, a 'SET FMTONLY ON' set statement is sent to the server prior to the command. This prevents all DML from being executed and will return only metadata for any selects. MySQL has no such mechanism.
The thing to remember is that FillSchema will execute the procedure exactly the same as if Fill had been called with only one difference. INOUT and OUT parameters will not have their values set properly on return.