KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

OFFSET Error with SQL Server 2008 #2734

Open jram opened this topic on on Jun 20, 2022 - 3 comments

jram commented on Jun 20, 2022

Hi,

i'm having problems with Tables and SqlSrv connection:

This is my function

 protected function onCreated()
    {
        $this->manageTable("Custtable")->inSource(Axapta::class);
    }

Axapta is a SQLServer connection:

use \koolreport\dashboard\sources\SQLServer;

class Axapta extends SQLServer
{
    protected function connection()
    {
        return [
            "connectionString"=>"sqlsrv:server=****;Database=****",
            "username"=>"alex",
            "password"=>"******",
            "charset"=>"utf8"
        ];
    }
}

And when I try to access this page, it throws this error:

Message: Query Error >> ["42000",102,"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '0'."] >> SELECT Custtable.ACCOUNTNUM, Custtable.NAMECOMER, Custtable.NAME, Custtable.weblogin, Custtable.PHONE, Custtable.zipcode FROM Custtable OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY || Sql params = [] || Search params = []

It seems that OFFSET command not works with this version of SQL Server but i don't know how can i fix it...

Sebastian Morales commented on Jun 21, 2022

Unfortunately, current Dashboard's query builder only targets SQL Server 2012 and later versions. We will pass this to the dev team to see if they can support SQL Server 2008 sql syntax.

Ellis commented on Jan 20

The reason this happens is because the query builder adds the OFFSET FETCH NEXT to the query even if there is no ORDER BY clause in the query. If you modify the file \koolreport\querybuilder\SQLServer.php and include the instructions to add the OFFSET inside the brackets of the orders condition, the problem will be gone. It is like this because OFFSET FETCH is a part of the ORDER BY instruction, cannot be used standalone like LIMIT in MySQL.

It shoul read like this: `

   if (count($this->query->orders)>0) {
        $sql.=" ORDER BY ".$this->getOrderBy($this->query->orders);
		
		if ($this->query->offset!==null) {
			$sql.=" OFFSET ".$this->query->offset." ROWS";
		}

		if ($this->query->limit!==null) {
			$sql.=" FETCH NEXT ".$this->query->limit." ROWS ONLY";
		}
    }

Sebastian Morales commented 22 hours ago

Thank you, Ellis, for your excellent suggestion.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None