Hello.
I have a Laravel 9 application that must connect to 2 different sql servers, one is version 2012 and the other is 2008.
When I use the pagination in 2012 it works fine but in the 2008 version I get the following error
SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'offset'.
The reason is that Eloquent uses OFFSET ... FETCH for paginating results in SQL Server which unfortunatelly does not work for 2008 (it was introduced at a later version).
How can I tackle this? Is there a way to let eloquent know how to paginate based on the server version? Maybe create my own paginator class and work from there?
What do you suggest?
You have 3 options
@sinnbeck Thanks for the reply.
Since options 1 & 3 are not viable for various reasons, could you direct me to an example of how to proceed with option 2?
Thanks.
@achatzi I dont know of any examples. I personally read the source code and implemented it from scratch. I can give you my old now deleted code
in a service provider
public function register()
{
Connection::resolverFor('sqlsrv', function ($connection, $database, $prefix, $config) {
return new SqlServerConnectionPolyfill($connection, $database, $prefix, $config);
});
}
First polyfill
<?php
namespace App\Database;
use Illuminate\Database\SqlServerConnection;
class SqlServerConnectionPolyfill extends SqlServerConnection
{
/**
* Get the default query grammar instance.
* @return \Illuminate\Database\Query\Grammars\SqlServerGrammar
*/
protected function getDefaultQueryGrammar()
{
return $this->withTablePrefix(new SqlServerGrammarPolyfill);
}
}
and second
<?php
namespace App\Database;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\SqlServerGrammar;
class SqlServerGrammarPolyfill extends SqlServerGrammar
{
/**
* Compile a select query into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
*
* @return string
*/
public function compileSelect(Builder $query)
{
if (!$query->offset) {
return parent::compileSelect($query);
}
// If an offset is present on the query, we will need to wrap the query in
// a big "ANSI" offset syntax block. This is very nasty compared to the
// other database systems but is necessary for implementing features.
if (is_null($query->columns)) {
$query->columns = ['*'];
}
return $this->compileAnsiOffset(
$query, $this->compileComponents($query)
);
}
}
@sinnbeck Thanks for this, it is a very good start for me.
@sinnbeck Thanks for your solution! Can you upload the complete solution on github? I'm a beginner and I'm having this problem.
This is great. Thanks for sharing!
For anyone new to Laravel, you can create a service provider like this:
php artisan make:provider Sql2008CompatibilityProvider
You will need to include these files in the provider:
use App\Database\SqlServerConnectionPolyfill;
use Illuminate\Database\Connection;
Then don't forget to register it: https://laravel.com/docs/8.x/providers#registering-providers
@marichardson8008 i replay your steps but i dont know how to follow the stesps sharing from @sinnbeck , someone can i help me with this issue, where do i put polyfill ? or is a new file ? ...thank u for your help
@maxieayala You can see the folder names based on the namespace, in the files I have shown. For instance App\Database means /app/Database
@Sinnbeck thank you for your help, you mention something about databse version in database config ...can explain or show an example?
This link helps to resolve this issue https://github.com/laravel/framework/issues/7568 i edit the file : src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php. comment // if (! empty($components['orders'])) { // return parent::compileSelect($query)." offset {$query->offset} rows fetch next {$query->limit} rows only"; // }
Please or to participate in this conversation.