Geek Answers Handbook

Zend DB cannot perform constraint and offset emulation for MS SQL

I am using Zend Framework 1.12 to access the MSSQL 2008 server. I am using FreeTDS as the database driver.

I am using Zend_Db to create the following query.

 $obj_sel = $obj_db
          -> select ()
          -> from (array ('ps' => 'ProductStock'), array ('PLU', 'stock' => 'SUM(ps.stock)'))
          -> join (array ('pc' => 'ProductCatalogue'), 'ps.PLU = pc.PLU', NULL)
          -> join (array ('gl' => 'Gemini_Location'), 'ps.Location = gl.LocationID', array ('LocationID'))
          -> where ('ps.status = 1')
          -> where ('ps.PLU IS NOT NULL');
           > where ('pc.rootPLU >= ?', $this -> int_start_rootplu);
          -> group ('ps.PLU')
          -> group ('gl.LocationID')
          -> order (array ('ps.PLU', 'gl.LocationID'));

If I run and execute this, I get the request back, which seems to be working fine and will be correct.

SELECT "ps"."PLU", SUM(ps.stock) AS "stock", "gl"."LocationID" FROM "ProductStock" AS "ps"
 INNER JOIN "ProductCatalogue" AS "pc" ON ps.PLU = pc.PLU
 INNER JOIN "Gemini_Location" AS "gl" ON ps.Location = gl.LocationID WHERE (ps.status = 1) AND (ps.PLU IS NOT NULL) AND (pc.rootPLU >= 93838) GROUP BY "ps"."PLU",
    "gl"."LocationID" ORDER BY "ps"."PLU" ASC, "gl"."LocationID" ASC

But when I try to add a constraint or offset to the request, for example:

 $obj_sel = $obj_db
          -> select ()
          -> from (array ('ps' => 'ProductStock'), array ('PLU', 'stock' => 'SUM(ps.stock)'))
          -> join (array ('pc' => 'ProductCatalogue'), 'ps.PLU = pc.PLU', NULL)
          -> join (array ('gl' => 'Gemini_Location'), 'ps.Location = gl.LocationID', array ('LocationID'))
          -> where ('ps.status = 1')
          -> where ('ps.PLU IS NOT NULL');
           > where ('pc.rootPLU >= ?', $this -> int_start_rootplu);
          -> group ('ps.PLU')
          -> group ('gl.LocationID')
          -> order (array ('ps.PLU', 'gl.LocationID'))
          -> limit (1000,2000);

I get the following query that the SQL server refuses to execute.

SELECT * FROM (SELECT TOP 1000 * FROM (SELECT TOP 3000 "ps"."PLU", SUM(ps.stock) AS "stock", "gl"."LocationID" FROM "ProductStock" AS "ps"
 INNER JOIN "ProductCatalogue" AS "pc" ON ps.PLU = pc.PLU
 INNER JOIN "Gemini_Location" AS "gl" ON ps.Location = gl.LocationID WHERE (ps.status = 1) AND (ps.PLU IS NOT NULL) AND (pc.rootPLU >= 93838) GROUP BY "ps"."PLU",
    "gl"."LocationID" ORDER BY "ps"."PLU" ASC, "gl"."LocationID" ASC) AS inner_tbl ORDER BY "ps"."PLU" , "gl"."LocationID" DESC) AS outer_tbl ORDER BY "ps"."PLU" , "gl"."LocationID" asc

I get the following error:

SQLSTATE [HY000]: general error: 4104 General SQL Server error: check messages with SQL Server [4104] (severity 16) [(null)]

MSSQL, MySQL Postgres, , TOP . , , , SQL Zend DB , .

Zend DB? , ?

+4
1

ZF 1 SQL Server, ZF 2 .

Zend_Db_Adapter_Pdo_Mssql:

class My_Zend_Db_Adapter_Pdo_Mssql extends Zend_Db_Adapter_Pdo_Mssql
{
    /**
     * @see Zend_Db_Adapter_Pdo_Mssql::limit()
     */
    public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception('count parameter invalid: ' . $count);
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception('offset parameter invalid: ' . $count);
        }

        if (0 == $offset) {
            $sql = preg_replace('/^SELECT\s+(DISTINCT\s)?/i', 'SELECT $1TOP ' . ($count+$offset) . ' ', $sql);
            return $sql;
        }

        $selectStart = stripos($sql, 'SELECT');
        $fromStart = stripos($sql, 'FROM');

        $orderby = stristr($sql, 'ORDER BY');

        if ($orderby === false) {
            $orderby = 'ORDER BY (SELECT 1)';
        }

        $sql = rtrim(str_replace($orderby, '', $sql));

        $selectParams = trim(substr($sql, $selectStart + 6, $fromStart - $selectStart - 6));
        $selectParams .= ', ROW_NUMBER() OVER (' . $orderby . ') AS [ZEND_ROW_NUMBER]';

        $sql = substr($sql, 0, $selectStart + 6) . ' ' . $selectParams . ' ' . substr($sql, $fromStart);

        $outerSql = 'SELECT * FROM (' . $sql . ') AS [ZEND_OFFSET_EMULATION]'
                  . ' WHERE [ZEND_OFFSET_EMULATION].[ZEND_ROW_NUMBER] BETWEEN '
                  . ($offset + 1) . ' AND '
                  . ($offset + $count)
                  . ' ORDER BY [ZEND_ROW_NUMBER] ASC';

        return $outerSql;
    }
}
+1

More articles:


All Articles