Wednesday, May 2, 2007

The issue with FillSchema

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.

8 comments:

  1. busby seo test caseJanuary 16, 2009 at 7:31 PM

    never use fillSchema.. hope the issue solved..

    ReplyDelete
  2. u made my day
    love it
    so reat

    ReplyDelete
  3. thank u
    great post
    we love u all

    ReplyDelete
  4. knockoff handbagsJune 15, 2009 at 4:10 PM

    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.

    ReplyDelete
  5. I don't like your template but your posts are quite good so I will check back!

    ReplyDelete
  6. I hope you will countinue your work. I want a blogengie blog as well. Try to implemet it.

    ReplyDelete
  7. I learned a lot of about blogs, and your is a grreat of piece of them. Thanks to share

    ReplyDelete
  8. I loved the way you exlained things. Much better many here


    Regards

    Niki

    ReplyDelete