Offset support for MSSQL / SQLSRV

[henryruhs]

MSSQL fails with an SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] syntax error when using offset()...

1. TOP and OFFSET are not allowed to be used in one SELECT
2. OFFSET 10 ROWS with FETCH NEXT 10 ROWS ONLY should be used
3. Therefore _build_limit() and _build_offset() need some rework

This are my approaches but I think something is missing to work properly:

protected static function _detect_limit_clause_style($connection_name) {
    switch(self::get_db($connection_name)->getAttribute(PDO::ATTR_DRIVER_NAME)) {
        case 'dblib':
            return ORM::LIMIT_STYLE_TOP_N;
        case 'sqlsrv':
        case 'mssql':
            return ORM::LIMIT_STYLE_FETCH;
        default:
            return ORM::LIMIT_STYLE_LIMIT;
    }
}

protected function _build_limit() {
    $fragment = '';
    if (!is_null($this->_limit)) {
        if (self::$_config[$this->_connection_name]['limit_clause_style'] == ORM::LIMIT_STYLE_LIMIT) {
            if (self::get_db($this->_connection_name)->getAttribute(PDO::ATTR_DRIVER_NAME) == 'firebird') {
                $fragment = 'ROWS';
            } else {
                $fragment = 'LIMIT';
            }
            $fragment .= " {$this->_limit}";
        } else if (self::$_config[$this->_connection_name]['limit_clause_style'] == ORM::LIMIT_STYLE_FETCH) {
            $fragment = 'FETCH NEXT ' . $this->_limit . ' ROWS ONLY';
        }
    }
    return $fragment;
}

protected function _build_offset() {
    if (!is_null($this->_offset)) {
        $clause = 'OFFSET';
        if (self::get_db($this->_connection_name)->getAttribute(PDO::ATTR_DRIVER_NAME) == 'firebird') {
            $clause = 'TO';
        }
        if (self::$_config[$this->_connection_name]['limit_clause_style'] == ORM::LIMIT_STYLE_FETCH) {
            return $clause . ' ' . $this->_offset . '  ROWS';
        }
        return $clause . ' ' . $this->_offset;
    }
    return '';
}

Reference: http://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ https://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server