MySQL Provider Error

May 27, 2010 at 4:56 AM

Hi

I was trying the library and i found that the TopN function doesn't get translated to proper MySql statements

This

 

DBQuery sel = DBQuery.SelectTopN(10)
                                .Fields("id")
                                .From("testtable")
                                .WhereField("Name", Compare.Like, DBConst.String("filter%"));

 

Output this

SELECT  TOP 10 `id`
        FROM `testtable`
        WHERE  (`Name` LIKE 'filter%') ;

It should be

SELECT `id`
        FROM `testtable`
        WHERE  (`Name` LIKE 'filter%') LIMIT 10;

 I then tried to modify DBMySqlStatementBuilder.cs but the TopN function gets called early in the building so i don't know how to fix that.

P.S. Sorry my english

Coordinator
May 27, 2010 at 8:04 AM
Edited May 27, 2010 at 9:16 AM

 

Thanks for the spot - your english is better than my MySQL!

I'll post an update with a fix soon, but I would suggest retaining the value for top n in the builder and use the EndSelect() function to check the value and output onto the stream then.

Actually if you really want to support it properly - a stack would be required for the BeginSelect() tracking so LIMIT is correctly appended for sub-select statements however as MySql looks as though it ignores all except the outer LIMIT value then a simple check of the statement depth would suffice.