Pdostatement::rowCount returns -1

Asked

Viewed 183 times

0

I need to know how many records were entered in the query below.

public function CadastraCliente() {
    $this->conn->beginTransaction();
    try {
        $stmt = $this->conn->prepare("
            INSERT INTO SISCli (CdInscricao,DsEntidade,DsApelido,InInscricao,InCadastro,DsEndereco,NrCEP,NrInscricaoEstadual,NrCGCCPF,NrTelefone,DtCadastro,CdEmpresa,DsEMail,DsUSuarioInc,InClassificacaoFiscal,DsBairro)
            SELECT
            right( ('00000000' + cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) as [CdInscricao],
            ESP353_XML.DES_DsEntidade AS [DsEntidade],
            ESP353_XML.DES_DsApelido AS [DsApelido],
            ESP353_XML.DES_InInscricao,
            0 AS [InCadastro],
            ESP353_XML.DES_DsEndereco AS [DsEndereco],
            ESP353_XML.DES_NrCEP AS [NrCEP],
            ESP353_XML.DES_NrInscricaoEstadual AS [NrInscricaoEstadual],
            case when LEN(ESP353_XML.DES_NrCGCCPF) > 11
            then right( ('00'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) 
            else right( ('00000'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 11) 
                end as [NrCGCCPF],
            ESP353_XML.DES_NrTelefone AS [NrTelefone],
            GETDATE() AS [DtCadastro],
            case when SISCEP.DsUF='SP' then '4' 
            when SISCEP.DsUF='PR' then '3'
            when SISCEP.DsUF='SC' then '12'
            ELSE '1' END
            as [CdEmpresa], 
            ESP353_XML.DES_DsEMail as [DsEMail],
            'EdiXML' AS [DsUSuarioInc],
            ESP353_XML.DES_InClassificacaoFiscal,
            substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
            FROM ESP353_XML
            LEFT JOIN SISCEP ON SISCEP.NrCep = ESP353_XML.DES_NrCEP
            where not exists (select 1 from SISCli A WHERE cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric))
            ");
        $stmt->execute();
        $this->conn->commit();
    } catch (Exception $e) {
        $this->conn->rollback();
        echo $e;
    }
}

What I’ve already tried:

$stmt->execute();
$count = $stmt->rowCount();
print_r($count);
php sql-server pdo

  • Always returns zero? what’s the problem with the code?

  • have you tried to make count() ? What $stmt->rowCount(); returns ?

  • Returns -1 when I give the print_r in the $count

  • You want the number of queries executed or the number of Updates and Inserts executed?

  • De update’s and Insert’s executed. How many lines have been affected, the same return it shows when running directly in sgbd.

  • @Kevin. F try to run rowCount after the commit and tell me what happened.

  • It keeps returning -1, after I run this function call it in the view, so I wanted to show how many were executed (in my case registered).

Show 2 more comments

4 answers

2

According to your comment, you are getting -1 when running $stmt->rowCount().

This indicates, that the query - in your case a INSERT - was invalid. What does this mean? Probably some parameter (name of a column for example) is incorrect.

Take a test, take the statement and run directly through your DBMS or terminal. If you return any errors, it is for the reason I explained above. Anyway, the formatting of your query using PDO is correct.

Do the following:

  • In executing that query, see if the data were entered in the database.

  • If the answer is yes, run the rowCount after the commit.

  • If the answer is no, please use $this->conn->errorInfo() to check if there really is an error in the syntax.

  • It is strange, because I already I went straight to the sgbd and it worked normal. And even if I returned something would enter the catch.

  • @Kevin. F is likely to have wrong/syntax values(a) since the query is huge it increases the chance of errors

  • @13dev but ae would go into Exception isn’t it? I think the problem might be in commit and not in query (I’m not sure).

  • @Guilhermenascimento Not because he’s captured from the class Exception and not of PDOException, (in this case you should capture the 2), @Kevin. F tries to capture both classes

  • 1

    @13dev yes I had noticed this, as I put it in my reply, but I’m not quite sure of the PHP behavior in Try/catch, so I said I wasn’t sure

  • @Kevin. F Take a look at the response issue.

  • 2

    @Kevin. F How can I not answer other comments for lack of reputation... The reason to use the attribute array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) is because it creates a cursor for the query, which allows you to iterate over the result set without fetching the entire result at once... So that the rowCount.

Show 2 more comments

2


Add the attribute array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) as described in documentation:

You would Most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time.

Translation:

You can normally use this to set the PDO::ATTR_CURSOR with the PDO::CURSOR_SCROLL value to request a "scrollable pointer". Some drivers have options that can be set at "preparation time" (prepare(...))

An example would look like this:

 $sql = "
        INSERT INTO SISCli (CdInscricao,DsEntidade,DsApelido,InInscricao,InCadastro,DsEndereco,NrCEP,NrInscricaoEstadual,NrCGCCPF,NrTelefone,DtCadastro,CdEmpresa,DsEMail,DsUSuarioInc,InClassificacaoFiscal,DsBairro)
        SELECT
        right( ('00000000' + cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) as [CdInscricao],
        ESP353_XML.DES_DsEntidade AS [DsEntidade],
        ESP353_XML.DES_DsApelido AS [DsApelido],
        ESP353_XML.DES_InInscricao,
        0 AS [InCadastro],
        ESP353_XML.DES_DsEndereco AS [DsEndereco],
        ESP353_XML.DES_NrCEP AS [NrCEP],
        ESP353_XML.DES_NrInscricaoEstadual AS [NrInscricaoEstadual],
        case when LEN(ESP353_XML.DES_NrCGCCPF) > 11
        then right( ('00'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) 
        else right( ('00000'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 11) 
            end as [NrCGCCPF],
        ESP353_XML.DES_NrTelefone AS [NrTelefone],
        GETDATE() AS [DtCadastro],
        case when SISCEP.DsUF='SP' then '4' 
        when SISCEP.DsUF='PR' then '3'
        when SISCEP.DsUF='SC' then '12'
        ELSE '1' END
        as [CdEmpresa], 
        ESP353_XML.DES_DsEMail as [DsEMail],
        'EdiXML' AS [DsUSuarioInc],
        ESP353_XML.DES_InClassificacaoFiscal,
        substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
        FROM ESP353_XML
        LEFT JOIN SISCEP ON SISCEP.NrCep = ESP353_XML.DES_NrCEP
        where not exists (select 1 from SISCli A WHERE cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric))
        ";

$stmt = $this->conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

And change:

 } catch (Exception $e) {

for:

 } catch (PDOException $e) {

  • Now, what does that attribute do array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) ?

  • @Kevin. F is because PDO is a class used for different types of banks and by default this is not configured, so as the link http://php.net/manualen/pdo.prepare.php#refsect1-Pdo.prepare-Parameters explains that it is usually necessary to set it like this, but if it’s another type of database maybe it’s not necessary or works differently, I can’t say precisely because I don’t know the source of PHP.

0

To complement the existing answers I will give an example, how you can capture the two classes (PDOException, Exception) in the same Try:

<?php
try {
  $stmt = $db->prepare("INSERT INTO minha_tabela (id, name, password)    VALUES (NULL, :name, :password)");
  $stmt->bindValue(":name", $_POST['name']);
  $stmt->bindValue(":password", $_POST['password']);
  $stmt->execute();
  $count = $stmt->rowCount();
  echo $count . " Utilizador adicionado com sucesso ";

} catch (PDOException $e) {
  echo "DataBase Error: O utilizador não pode ser adicionado.<br>".$e->getMessage();
} catch (Exception $e) {
  echo "General Error: O utilizador não pode ser adicionado.<br>".$e->getMessage();
}
?>

So you can capture General / Database error, which will help you greatly in debug of the code.

-1

For PDO

You can use the method rowCount which returns the number of rows affected by the last DELETE, INSERT or UPDATE running by the last object PDOStatement.

Definition:

public int PDOStatement::rowCount ( void )

Example:

/* Exclui todos registros da tabela 'frutas' */
$del = $dbh->prepare('DELETE FROM frutas');
$del->execute();

/* Retorna o número de linhas que foram excluídas */
$count = $del->rowCount();
print("Excluídas $count linhas.\n");

To Mysqli

You can use the method affected-rowsof mysqli:

Definition:

int mysqli_affected_rows ( mysqli $link )

Example:

$mysqli->query("UPDATE Language SET Status=1 WHERE Percentage > 50");
printf("Linhas afetadas (UPDATE): %d\n", $mysqli->affected_rows);

Reference: http://php.net/manual/en/mysqli.affected-rows.php

  • 1

    the author is using PDO

  • I put the example to PDO

  • 2

    but still does not answer my question

Browser other questions tagged php sql-server pdo

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