Query returns empty using PHP with PDO and MS SQLServer

Question

Query returns empty using PHP with PDO and MS SQLServer

Navigation

#1 by (5 votes)
#2 by (2 votes)

3

I am migrating a system made in ASP to PHP. However, I'm having trouble making a query using the PDO:

Search.php

class Busca extends Conexao {

    private $termo;

    public function setTermo($termo) { $this->termo = $termo; }
    public function getTermo() { return $this->termo; }

    public function listarResultados() {
        try {
            $pdo = parent::getDB();
            $consulta = $pdo->prepare("SELECT D2.D2CDORG AS COD_ORGAO, D2.D2DSORG AS NME_ORGAO, D1.D1CDSRV AS COD_SERVICO, D1.D1DSSRV AS NME_SERVICO, D3.D3CDITM AS COD_ITEM, D3.D3DSITM AS NME_ITEM, D3.D3VALOR * (SELECT D7VLREAL FROM D7VRTE WHERE D7AAMMREF = 11) AS VALOR FROM D5ITMORGSRV D5, D3ITENS D3, D2ORGAOS D2, D1SERVICOS D1 WHERE (D1.D1CDSRV <> 5) AND (D5.D1CDSRV = D1.D1CDSRV) AND (D5.D2CDORG = D2.D2CDORG) AND (D5.D3CDITM = D3.D3CDITM) AND ((D3.D3DSITM LIKE :termo) OR (D1.D1DSSRV LIKE :termo)) ORDER BY NME_ORGAO, NME_SERVICO, NME_ITEM");
            $consulta->bindValue(':termo', $this->getTermo());
            $consulta->execute();
            return $consulta->fetchAll(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            echo "<p class='alert alert-danger'><b>Ocorreu um erro: </b>" . $e->getMessage() ."</p>";
        }
    }

}

Results Page:

$busca = new Busca;
$busca->setTermo('identidade');
$resultado = new ArrayIterator($busca->listarResultados());

Return is:

SQLSTATE[07002]: COUNT field incorrect: 0 [Microsoft][ODBC SQL Server Driver]Campo COUNT incorreto ou erro de sintaxe (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:133)

Searching, I saw that it is in bindValue , but I tried everything and nothing works.

I tried to use two bindValue ( :termo1 and :termo2 ). It does not give the above error, but returns ArrayIterator Object ( ) in print_r .

If instead of :termo I pass ' %identidade% ', for example, the data is shown correctly.

This works: ( LIKE '%".$this->getTermo()."%' )

$consulta = $pdo->prepare("SELECT D2.D2CDORG AS COD_ORGAO, D2.D2DSORG AS NME_ORGAO, D1.D1CDSRV AS COD_SERVICO, D1.D1DSSRV AS NME_SERVICO, D3.D3CDITM AS COD_ITEM, D3.D3DSITM AS NME_ITEM, D3.D3VALOR * (SELECT D7VLREAL FROM D7VRTE WHERE D7AAMMREF = 11) AS VALOR FROM D5ITMORGSRV D5, D3ITENS D3, D2ORGAOS D2, D1SERVICOS D1 WHERE (D1.D1CDSRV <> 5) AND (D5.D1CDSRV = D1.D1CDSRV) AND (D5.D2CDORG = D2.D2CDORG) AND (D5.D3CDITM = D3.D3CDITM) AND ((D3.D3DSITM LIKE '%".$this->getTermo()."%') OR (D1.D1DSSRV LIKE '%".$this->getTermo()."%')) ORDER BY NME_ORGAO, NME_SERVICO, NME_ITEM");

How to solve this?

php sql sql-server pdo

asked by anonymous 11.11.2015 / 19:15

2 answers

2

Missing meta-characters '%', '%' Matches any number of characters, even zero character

$busca = new Busca;
$busca->setTermo('%identidade%');
$resultado = new ArrayIterator($busca->listarResultados());

LIKE '__a%' You will find all items that have the letter a in the fourth position of the word.

LIKE '% a' Words ending in a.

LIKE 'a%' Words beginning in a.

LIKE 'a% b% c' Word must start with a, have in middle b and end with c.

NOT LIKE '% @%' not like it returns all the data that does not have the value defined in it, in case, returns everything that does not have @

field LIKE '% @%' Returns all the data that has the value defined in it, in case, returns everything that has @

Example:

SELECT * FROM% WITH% WHERE LIKE field "% a%"

11.11.2015 / 19:43

Get the number of decimal places of a decimal How to use the Primefaces p?

score 5 · Accepted Answer

Pass the wildcards in bindValue() , do not let them in the query otherwise they will be escaped and then your query will fail.

It seems that the SQL Server driver has a detail, does not allow to use the same value for more than one identifier, to solve this, define a new placeholder.

$consulta = $pdo->prepare("SELECT * FROM tabela 
                           WHERE nome like :termo1 OR descricao like :termo2");
$consulta->bindValue(':termo1', '%'.$this->getTermo().'%');
$consulta->bindValue(':termo2', '%'.$this->getTermo().'%');

Or:

$consulta = $pdo->prepare("SELECT * FROM tabela WHERE nome like ? OR descricao like ?");
$consulta->execute('%'.$this->getTermo().'%', '%'.$this->getTermo().'%');