0

How can I create OFFSET and FETCH NEXT in Zf2 logic? The result should be like the following

    SELECT * FROM mytable
  ORDER BY id DESC
    OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

as described here. When using the following code

$select = $this->tableGateway->getSql()->select();
$select->order('id DESC');
$select->offset(100);
$select->limit(10);
$resultSet = $this->tableGateway->selectWith($select);

then the output is

SELECT [mytable].* 
FROM  [mytable] 
ORDER BY [id] DESC 
LIMIT '10' OFFSET '100'

which does not work for SQL Server. The output I need is the one from above.

The config in db.config.php looks like the following

'db_sql_server' => array(
        'driver'    => 'pdo',
        'dsn'       => 'dblib:host=myhost;dbname=mydatabase',
        'username'  => 'myusername',
        'password'  => 'mypasswort',
),

I skiped the 'db' config for mysql here. And in global.php

return array(
  'service_manager' => array(
    'factories' => array(
        /**
         * Adapter SQL Server
         */
        'Application\Db\AdapterSQLServer' => function($sm) {
            $config = $sm->get('Config');
            return new Adapter($config['db_sql_server']);
        },
    ),
  ),
)

to which I refere in Module.php with Table Gateway.

Community
  • 1
  • 1
Gemane
  • 43
  • 8
  • Did you install it correctly? [SO Link](http://stackoverflow.com/q/19596234/3893182) | [samsonasik](https://samsonasik.wordpress.com/2013/03/08/zend-framework-2-working-with-sql-server/) – danopz Jul 17 '15 at 10:04
  • Yes, the connection to the SQL Server works. Other statements can be executed. The problem is just that ZF2 translates "offset" and "limit" to the eg MySQL statement and not to the SQL server statement as described above. – Gemane Jul 17 '15 at 10:25
  • Could you provide Config and your whole Query from `select` till some kind of `getResult` – danopz Jul 17 '15 at 12:11
  • I updated the description – Gemane Jul 17 '15 at 14:25
  • Which instance is your adapter? `get_class($tableGateway->getAdapter())` – danopz Jul 20 '15 at 06:39
  • This yields `Zend\Db\Adapter\Adapter` – Gemane Jul 20 '15 at 10:33
  • I found another way to get the adapter by using `$this->tableGateway->getAdapter()->platform` where I receive `Zend\Db\Adapter\Platform\SqlServer` – Gemane Jul 27 '15 at 08:10

1 Answers1

0

I believe you need to change the driver name in the 'db_sql_server' config array from

'driver' => 'pdo'

to

'driver' => 'sqlsrv'
rastusik
  • 123
  • 9
  • 'sqlsrv' only works on Windows with DLLs but I am using a Linux system with FreeTDS. – Gemane Jul 27 '15 at 08:07
  • well, you will have to implement an own FreeTds adapter then ... or maybe try this: http://stackoverflow.com/a/15594853/1558846 – rastusik Jul 28 '15 at 18:58