Query returns empty using PHP with PDO and MS Sqlserver

Asked

Viewed 939 times

3

I’m migrating a system made in ASP to PHP. However, I’m having trouble making a query using 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());

The 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)

Researching, I saw that it is in the bindValue, but I’ve tried everything and nothing works.

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

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

That’s how it 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

  • When it passed :termo1 and :termo2 put the % at the consultation or on bindvalue()?

  • In the query. But it still doesn’t work. I tried to use ? instead of :termo. Changes nothing..

2 answers

5


Pass the jokers on bindValue(), do not leave 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 using the same value for more than one identifier, to resolve this set 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 else:

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

  • Right, except I have the :termo twice in the query. I will need to use bindValue(:termo1) and bindValue(:termo1) of the same value?

  • In theory if they have the same value, then make a bind only. Now return SQLSTATE[07002] This is an indication that you will need two. Test ai returns here :) @Buback

  • Yeah. That’s where the 07002. I thought I had a way to use the same bind since it has the same value. But thank you.

  • @Buback, seems to be something specific to the SQL Server driver, I did the test in Mysql(is the only one I have here) worked with a single bind. Then I recommend changing the :termo by two ? and pass the % also. $consulta = $pdo->prepare("SELECT * FROM tabela &#xA; WHERE nome like ? OR descricao like ?");&#xA;$consulta->bindValue(1, '%'.$this->getTermo().'%');&#xA;$consulta->bindValue(2, '%'.$this->getTermo().'%');

  • Yeah. That’s what I’m doing. Yeah, in Mysql I’ve also used the same bind for two Where and it worked normal. Thanks! ;)

  • It worked by repeating the ? @Buback

  • 1

    It worked, I’m using it like this. I think it’s the same Sqlserver Drive. I’ll try to update it.

Show 2 more comments

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%' All items that have the letter a in the fourth position of the word will be searched for.

LIKE Words ending in a.

LIKE Words started in a.

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

NOT LIKE %@%' not like is returned all data that does not have the value set in it, in case returns all that does not have @

LIKE field@%' Returns all data that has the value set on it, in case, returns everything you have @

Example:

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

  • Thank you very much. Since it has the same value, I thought there was a way to use the same bind.

Browser other questions tagged php sql sql-server pdo

You are not signed in. Login or sign up in order to post.