0

I can execute query like this against sql server:

DECLARE @ROWS INT = 2, @PAGE INT = 2
SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY ID) AS _NO
    FROM [Reports].[dbo].[Cycle] 
) AS SOD
WHERE SOD._NO BETWEEN ((@PAGE-1)*@ROWS)+1
AND @ROWS*(@PAGE)
GO

and i got result:

result of executing query

but if i try to do so in pdo through dblib it doesn't work:

            $stmt = $pdo->query("
    DECLARE @ROWS INT = 2, @PAGE INT = 2
    SELECT *
    FROM (
        SELECT *,
        ROW_NUMBER() OVER (ORDER BY ID) AS _NO
        FROM [Reports].[dbo].[Cycle] 
    ) AS SOD
    WHERE SOD._NO BETWEEN ((@PAGE-1)*@ROWS)+1
    AND @ROWS*(@PAGE)
    GO
    ");

            var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

error code:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15)

description of error according error mesages: http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

is: 

Incorrect syntax near '%.*ls'.
monstersmart
  • 39
  • 3
  • You are declaring variables, declaring a query and executing it. While SQL Server is able to parse these 3 commands sequentially, PDO is trying to send all 3 of them as a single sentece. Moreover, you're using a query operator. You could try separating these 3 sections with semicolons and using exec operator. – ffflabs Jun 12 '14 at 12:55
  • ->exec() changes nothing because it's only return numbers of rows instead of statemetn object, executing is in the same way like in query. $stmt = $pdo->exec(" DECLARE \@ROWS INT = 2, \@PAGE INT = 2; SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS _NO FROM [Reports].[dbo].[Cycle] ) AS SOD WHERE SOD._NO BETWEEN ((\@PAGE-1)*\@ROWS)+1 AND \@ROWS*(\@PAGE); GO "); print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); error: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 102 General SQL ... – monstersmart Jun 12 '14 at 13:03
  • You have a point. I still believe that query won't be able to parse 3 sequential commands. Did you try the semicolons? – ffflabs Jun 12 '14 at 13:04

0 Answers0