CodeIgniter Forums
Active record, offset limit with mssql - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active record, offset limit with mssql (/showthread.php?tid=20427)

Pages: 1 2

Active record, offset limit with mssql - El Forum - 07-09-2009

[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

Active record, offset limit with mssql - El Forum - 07-09-2009

[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();
    }


Active record, offset limit with mssql - El Forum - 07-09-2009

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

Active record, offset limit with mssql - El Forum - 07-09-2009

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

Active record, offset limit with mssql - El Forum - 07-09-2009

[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..

Active record, offset limit with mssql - El Forum - 07-09-2009

[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?

Active record, offset limit with mssql - El Forum - 07-09-2009

[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??

Active record, offset limit with mssql - El Forum - 07-09-2009

[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

Active record, offset limit with mssql - El Forum - 07-09-2009

[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.

Active record, offset limit with mssql - El Forum - 07-09-2009

[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..