SQL query error in PHP

Question

SQL query error in PHP

Navigation

#1 by (1 votes)

0

I came across a sql error, but when I run the query in SQL Server Studio it returns the normal query. Query that is below.

SELECT
CAST(REPLACE(CAST(DsXML as Nvarchar(Max)),' xmlns="http://www.portalfiscal.inf.br/nfe"','') as xml).value('(/nfeProc/NFe/infNFe/transp/vol/qVol/node())[1]', 'int') as [qVol]
FROM SPDNFE
WHERE CdIdNFe = 'NFe13161203976141000132550030000435291400513027'

Now when I transpose the same sql query into php it returns an error. As it is in php:

public function teste($nrchave) {
        $sql = "SELECT
            CAST(REPLACE(CAST(DsXML as Nvarchar(Max)),' xmlns=\"http://www.portalfiscal.inf.br/nfe\"','') as xml).value('(/nfeProc/NFe/infNFe/transp/vol/qVol/node())[1]', 'int') as [qVol]
            FROM SPDNFE
            WHERE CdIdNFe = '$nrchave'";
        $results = array();
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        if ($stmt) {
            while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $info = new Model();
                $info->setQVol($row->qVol);
                $results[] = $info;
            }
        }
        return $results;
    }

Error:

Uncaught exception 'PDOException' with message 'SQLSTATE [42000]: [Microsoft] [ODBC Driver 11 for SQL Server] [SQL Server] SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and / or indexes on computed columns and / or filtered indexes and / or query notifications and / or XML data type methods and / or spatial index operations. '

php sql sql-server

asked by anonymous 08.08.2017 / 15:09

1 answer

SQL in php script Passing value from a DialogFragment to the Fragment

score 1 · Accepted Answer

I solved by adding SET ARITHABORT ON in front of my SELECT .

Looking like this:

public function teste($nrchave) {
        $sql = "
            SET ARITHABORT ON
            SELECT
            CAST(REPLACE(CAST(DsXML as Nvarchar(Max)),' xmlns=\"http://www.portalfiscal.inf.br/nfe\"','') as xml).value('(/nfeProc/NFe/infNFe/transp/vol/qVol/node())[1]', 'int') as [qVol]
            FROM SPDNFE
            WHERE CdIdNFe = '$nrchave'";
        $results = array();
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        if ($stmt) {
            while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
                $info = new Model();
                $info->setQVol($row->qVol);
                $results[] = $info;
            }
        }
        return $results;
    }