PDOStatement :: rowCount returns -1

Add the array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) attribute 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 specific options that may be set to prepare-time.

Translation:

  

You can usually 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 the time of "preparation" ( 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 switch:

 } catch (Exception $e) {

by:

 } catch (PDOException $e) {

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

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

Take a test, get the statement and run straight into your DBMS or through the terminal. If I return an error, it is for the reason I explained above. Anyway, the formatting of your query using PDO is correct.

Do the following:

• When executing this query , see if the data has been entered into the database.

  If the answer is yes, run rowCount after commit.

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

In order to complement the existing answers, I'll give you an example of 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 catch General /% with% error, which will help you a lot in the debug of the code.

For PDO

You can use the rowCount method that 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");

For MySQLi

You can use the affected-rows method of 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: link