• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MSSQL Pagination Holly Grial?

#1
[eluser]gusa[/eluser]
hi everybody!
tired of implementing an ad-hoc solution every time i want to paginate under mssql i've decided to overwrite codeigniter's mssql driver. with this solution it's possible to use such an impossible thing as active record plus pagination with mssql server.

the steps are:

1) edit database/drivers/mssql_driver.php and add these methods:

Code:
function _get_pkeys($table) {
    $stmt = @mssql_init('SP_PKEYS', $this->conn_id);
    @mssql_bind($stmt, '@table_name', $table, SQLVARCHAR, false);
    $rs = @mssql_execute($stmt);
    $n = @mssql_num_rows($rs);
    if ($n == 0) {
        return $this->display_error('db_unsuported_feature');
    }
    $result = array();
    while ($p = mssql_fetch_assoc($rs)) {
        $result[] = $p['COLUMN_NAME'];
    }
    return $result;
}

function _compile_select()
{
    if (is_numeric($this->ar_limit)) {
        $limit = $this->ar_limit;
        $offset = $this->ar_offset;
        
        $pkeys = $this->_get_pkeys($this->ar_from[0]);
        $keys = implode(', ', $pkeys);
        if (count($pkeys) > 1) {
            // convert many keys into a unique pk
            foreach ($pkeys as &$pk) {
                $pk = 'convert(varchar, '.$pk.')';
            }
            $key = implode("+':'+", $pkeys);
        } else {
            $key = $pkeys[0];
        }
        
        // select clause
        
        $select = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
        $select .= $key.', ';
        $select .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->ar_select);
        
        // from clause
        
        if (count($this->ar_from) > 0)
        {
            $from = "FROM ".implode(', ', $this->ar_from);
        }
        
        // join clause
        
        if (count($this->ar_join) > 0)
        {        
            $join = implode("\n", $this->ar_join);
        }

        // where clause
        
        if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
        {
            $where = "WHERE ";
        } else {
            $where = "WHERE 1=1 ";
        }

        $where .= implode("\n", $this->ar_where);
        
        if (count($this->ar_like) > 0)
        {
            if (count($this->ar_where) > 0)
            {
                $where .= " AND ";
            }

            $where .= implode("\n", $this->ar_like);
        }

        // order by clause
        
        if (count($this->ar_orderby) > 0)
        {
            $orderby = "ORDER BY ".implode(', ', $this->ar_orderby);
            
            if ($this->ar_order !== FALSE)
            {
                $orderby .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC';
            }
        } else {
            $orderby = 'ORDER BY '.$keys.' ASC';
        }
        
        // the hack
        
        $offset--;
        $top = $limit + $offset;

        $sql = "
            $select
            $from
            $join
            WHERE $key IN (
                SELECT TOP $limit $key
                $from
                $join
                $where AND $key NOT IN (
                    SELECT TOP $offset $key
                    $from
                    $join
                    $where
                    ORDER BY $keys
                )
                ORDER BY $keys ASC
            )
            $orderby";
    } else {
        $sql = parent::_compile_select();
    }
    return $sql;
}

ready! now you can make your active record query as always (with some limitations):

Code:
$this->db->select('blah');
$this->db->from('yara');
...
$this->db->limit(10, 20);

limitations:

1) the first table in the from clause must have a primary key defined.
2) group by clause and having clause are discarded, so if you want pagination with group by and having go back to the traditional way.

good luck!

#2
[eluser]whobutsb[/eluser]
Hi Gusa,
Thanks for posting up that MSSQL Hack, Pagination is definitely one of the biggest headaches in SQL Confusedhut:

I'm just trying to make sense of your example that you posted. How do you actually return a result from the inputs that you are using?

I tried to use:
$this->db->select('contactID');
$this->db->from('tblContact');
$this->db->limit($limit,$offset);
$query = $this->db->get();

And I guess the $this->db->get(); Method is throwing the error: Unsupported feature of the database platform you are using.

Any chance of a couple of pointers? Thanks for your help in advance!

Steve

#3
[eluser]gusa[/eluser]
[quote author="whobutsb" date="1228854820"]
And I guess the $this->db->get(); Method is throwing the error: Unsupported feature of the database platform you are using.

Any chance of a couple of pointers? Thanks for your help in advance!

Steve[/quote]

which version of ci are you using?
create a new folder under database/drivers and name it 'mssql_ext'. then copy the attached files to that folder.

please, tell me if it worked.

#4
[eluser]whobutsb[/eluser]
Thanks for the quick reply! I'm using Version 1.7.0 of CI. I'm so close with those files you sent me. But I still have the same problem with the pagination where when I go to the second group of results they are just getting appended to the first set of results.

Do I need to declare or make a change in CI to call upon those files in the mssql_ext folder?

Thanks for your help.
Steve

#5
[eluser]gusa[/eluser]
[quote author="whobutsb" date="1228856524"]
Do I need to declare or make a change in CI to call upon those files in the mssql_ext folder?
[/quote]

tell codeigniter that you are using the 'mssql_ext' driver, instead of 'mssql'. to do this, modify the $db array in the database.php file, like this:

Code:
$db['default']['dbdriver'] = 'mssql_ext';
i think that will work.

regards

#6
[eluser]whobutsb[/eluser]
No go on that one :down:

I'm getting the error: Call to a member function from() on a non-object

In the mssql_ext directory all the files are labeled as: mssql_ext_xxx.php, should I remove the _ext part?

#7
[eluser]gusa[/eluser]
[quote author="whobutsb" date="1228857800"]No go on that one :down:

I'm getting the error: Call to a member function from() on a non-object

In the mssql_ext directory all the files are labeled as: mssql_ext_xxx.php, should I remove the _ext part?[/quote]

what version of mssql are you using?
i've tested it over mssql 2000.

maybe the error comes from mssql_ext_driver's _get_pkeys method. echo a foo message before line #602 and tell me.

#8
[eluser]whobutsb[/eluser]
I'm using: Microsoft SQL Server 2000 - 8.00.2050. I tried to echo a foo message but I never got a response in the output.

#9
[eluser]gusa[/eluser]
[quote author="whobutsb" date="1228862260"]I'm using: Microsoft SQL Server 2000 - 8.00.2050. I tried to echo a foo message but I never got a response in the output.[/quote]

i'm using Microsoft SQL Server 2000 - 8.00.760... it should work!
can you trace (somehow) which method is throwing that error?

#10
[eluser]whobutsb[/eluser]
It definitely looks like the limit() method is causing the problem. I just tried this query in my model:
$this->db->select('contactID, firstname, lastname');
$this->db->limit($offset, $limit);
$query = $this->db->get('tblContact');

and it threw the error: Unsupported feature of the database platform you are using. when I added the limit command.

Possibly could you send me a sample of your code, maybe i'm writing it wrong.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.