Open henryruhs opened 5 years ago
MSSQL fails with an SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] syntax error when using offset()...
SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
offset()
TOP
OFFSET
SELECT
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
_build_limit()
_build_offset()
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
MSSQL fails with an
SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
syntax error when usingoffset()
...TOP
andOFFSET
are not allowed to be used in oneSELECT
OFFSET 10 ROWS
withFETCH NEXT 10 ROWS ONLY
should be used_build_limit()
and_build_offset()
need some reworkThis are my approaches but I think something is missing to work properly:
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