The blog of Rahoul Baruah from 3hv Ltd

What's going on?

My name is Rahoul Baruah (aka Baz) and I'm a software developer in Leeds (England).

This is a log of things I've discovered while writing software in Ruby on Rails. In other words, geek stuff.

However, I've decided to put this blog on ice - I would ask you to check out my business blog here (or subscribe here).

14 July, 2006

Sql Server - No Limits

I've been playing around with the Ajax Scaffold recently.

Because of the trouble I've been having with scrptxfr (meaning that Rails cannot copy my dev database structure to my test database) I've been working in MySql and relying on migrations to move over to Sql Server when I'm done.

But I've hit a problem. Ajax Scaffold uses pagination all over the shop. Pagination needs to be able to ask for records 20 to 30 out of a particular dataset. This is easy in MySql - just use the limit clause, specifying the record numbers that you want. It's not easy in Sql Server. Sql Server offers you top so you can grab the first 30 records. But nowhere does it offer you a filter returning record 20 to record 30. So the SqlServerAdapter tries to fake it - it does (select top 10 * from (select top 30 from table order by id desc) order by id asc) - (I may have got my ascs and descs the wrong way round). This works. It's nasty but it works. Until you use an :include or an :order. Then it just falls over in a crumpled heap. Whimpering.

The only way around it that I can think of is to hack the SqlServerAdapter so that it does a select top 30 * from table, disconnect the recordset (it's only ADO after all) and then delete the first 20 rows. Not nice eh?


Richard White said...

Sounds like it might be related to this bug.

Baz said...

Cheers - looks like the one. I'll give it a go.

Personal service too. Nice!

ant said...

you could try using the RowNumber function in sql 2005 to get what you want

Baz said...

I'm still on Sql 2000. None of this new-fangled technology for me Anthony.

eXTReMe Tracker