Active record, offset limit with mssql

[eluser]0plus1[/eluser]
I encountered a small problem, I have a methiod inside a model:

Code:

function lista($limit="0",$offset="0")
    {
        $query = $this->db->get('AN_ANAGRAFICA_AZIENDE', $limit, $offset);
        return $query->result();
    }

When I call it from the controller and then push the result from the list I get incremental results:

Code:

$data['lista'] = $this->Mana->lista(10,120);

Theoretically I would start from record 120 and limit results by ten, in reality it returns 130 rows.. How come? The same thing happens the other way around..

Can you explain what I'm doing wrong/missing?

Thank you

[eluser]Cro_Crx[/eluser]
That does seem a bit odd. I dont' have ms sql anywhere so can't test, although have you tried doing the limit and offset outside of the get by using $this->db->limit, would be something like this:

Code:

function lista($limit="0",$offset="0")
    {
        $this->db->limit($limit, $offset);
        $query = $this->db->get('AN_ANAGRAFICA_AZIENDE');
        return $query->result();
    }

[eluser]0plus1[/eluser]
Thank you, I get the same result even with you alternative code..

[eluser]Dam1an[/eluser]
Try enabling the profiler and see what the generated SQL is

[eluser]0plus1[/eluser]
uhm apparently CI simply ignore the parameters:

Code:

SELECT  TOP 40 *
FROM AN_ANAGRAFICA_AZIENDE

I understand that mssql doesn't have any LIMIT function. Still you can pull this off: the Zend frameword does this, I was hoping that CI would also..

[eluser]Dam1an[/eluser]
wtf! How can any self respecting database not support limit?

A very wasteful way to do it would be to return the full set, and then just trim the result array to the records you want, but it just seems wrong

So I guess the real solution is to not use MSSQL?

[eluser]0plus1[/eluser]
[quote author="Dam1an" date="1247167131"]So I guess the real solution is to not use MSSQL?[/quote]

A LOT of companies in the real world use it, no one wants to switch to the "free" solution, so I'm stuck with this thing.

I was hoping that using a layer like Active Record would help me avoid using low level mssql, guess I was wrong.

Can anyone here know how to handle mssql with CI? Stuff like pointer can be achieved with Active Record??

[eluser]0plus1[/eluser]
I may have found a solution that can be used:

Code:

$this->db->limit($limit, $offset);
$query = $this->db->get('AN_ANAGRAFICA_AZIENDE');
return $query->result();

Somewhere must be used:

$query = mssql_data_seek($query, $offset);

Problem is can this be even used with active record?

Thanks

[eluser]kgill[/eluser]
[quote author="Dam1an" date="1247167131"]wtf! How can any self respecting database not support limit?

[/quote]

None of the enterprise level DB's support limit, the best you can hope for from Oracle, DB2 & MS SQL is selecting the first X rows, no offset without doing some SQL trickery.

[eluser]0plus1[/eluser]
I just tried scaffolding on the table and it actually divides result page by page, I guess I will be using the pagination class..