Error executing query sql server + php + pdo

Question

Error executing query sql server + php + pdo

Navigation

#1 by (2 votes)

2

Follow the code:

$conexao = new PDO("odbc:Driver={SQL Server};Server=127.0.0.1;Database=MASTERDB; Uid=admin;Pwd=admin123;");


$select = $conexao->query("IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;


SET DATEFIRST 7;  -- domingo

with
Consulta as (
SELECT 
    F.FILIAL  AS  FILIAL,
  F.COLIGADA  AS  COLIGADA,
  F.CHAPA   AS  CHAPA,
  F.SECAO   AS  SECAO,
  F.SITUACAO  AS  SITUACAO,
  F.NOME    AS  NOME,
    V.DATA,
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
  FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN vwFUNC  AS F ON V.CHAPA = F.CHAPA
  WHERE
        DATEPART(dw,V.DATA) = 1  -- domingo
        AND BATIDA IS NOT NULL 
    AND  V.DATA BETWEEN '$v_datainicio' AND '$v_datafinal'
        AND V.CODCOLIGADA = 1 

  GROUP BY V.CHAPA,V.DATA,
      F.FILIAL,F.COLIGADA,F.CHAPA,F.SECAO,F.FUNCAO,F.NOME,F.SITUACAO 
)
SELECT * 
  into #TabBatidas
  from Consulta;

CREATE clustered INDEX I1_TB on #TabBatidas (CHAPA, DATA);

SELECT 
  T1.CHAPA AS CHAPA, 
  T1.NOME AS NOME, 
  T1.FILIAL AS FILIAL,
  T1.SECAO AS SECAO,
  T1.SITUACAO AS SITUACAO, 
       Cast(T1.DATA as date) as [DATA 1],
       Cast(T2.DATA as date) as [DATA 2],
       Cast(T3.DATA as date) as [DATA 3],
     '04 - TESTE DOMINGO' AS OCORRENCIA
  from #TabBatidas as T1
       inner join #TabBatidas as T2 on T2.CHAPA = T1.CHAPA
       inner join #TabBatidas as T3 on T3.CHAPA = T1.CHAPA
  where T2.DATA = DateAdd(day, +7, T1.DATA)
        and T3.DATA = DateAdd(day, +14, T1.DATA);    

--
IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;
go");
?>
<div class="panel panel-primary">
  <div class="panel-heading">
    <h3 class="panel-title">04 - TESTE DE DOMINGO</h3>
  </div>
  <div class="panel-body">
<table class='datatable table table-hover table-bordered table-responsiv'>
   <thead>
     <tr>
        <th>CHAPA</th>
        <th>NOME</th>
        <th>FILIAL</th>
        <th>SECAO</th>
        <th>SITUACAO</th>
        <th>1° DOMINGO</th>
        <th>2° DOMINGO</th>
        <th>3° DOMINGO</th>
     </tr>
    </thead>
  <?php
     echo"<tbody>";   
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
     echo" <tr>";
        echo"<td>".$row['CHAPA']."</td>";
        echo"<td>".$row['NOME']."</td>";
        echo"<td>".$row['FILIAL']."</td>";
        echo"<td>".$row['SECAO']."</td>";
        echo"<td>".$row['SITUACAO']."</td>";
        echo"<td>".$row['DATA 1']."</td>";
        echo"<td>".$row['DATA 2']."</td>";
        echo"<td>".$row['DATA 3']."</td>";
     echo" </tr>";

   }   
  echo"  </tbody>";
 echo" </table>";

?>
  </div>
</div>

Turning me back:

Fatal error: Call to a member function fetch () on a non-object in G: \ php \ systems \ reports \ main.php on line 411

Line 411 is:

while ($row = $select->fetch(PDO::FETCH_ASSOC)) {

If I run the query in sql server it runs normally, and if I enterate another query in php it will also, it is only in this one that is giving this error.

php sql-server pdo

asked by anonymous 01.11.2016 / 22:26

1 answer

Separate clickable item in android layout How to import the current $ scope into $ uibModal?

score 2 · Accepted Answer

Run the following function in your database:

if object_id('dbo.batidas', 'TF') is null
begin
  exec('create function dbo.batidas() returns @retorno table(T int) as begin return end');
end;
go

alter function dbo.batidas(@data_inicio date,
                           @data_final date)
returns @retorno table(chapa      varchar(100),
                       nome       varchar(100),
                       filial     varchar(100),
                       secao      varchar(100),
                       situacao   varchar(100),
                       [data 1]   date,
                       [data 2]   date,
                       [data 3]   date,
                       ocorrencia varchar(100))
as
begin
  declare @batidas table(filial    varchar(100),
                         coligada  varchar(100),
                         chapa     varchar(100),
                         secao     varchar(100),
                         situacao  varchar(100),
                         nome      varchar(100),
                         data      datetime,
                         dia       varchar(100),
                         batida    int);

  insert into @batidas(filial,
                       coligada,
                       chapa,
                       secao,
                       situacao,
                       nome,
                       data,
                       dia,
                       batida)
  select f.filial,
         f.coligada,
         f.chapa,
         f.secao,
         f.situacao,
         f.nome,
         v.data,
         datename(dw,v.data),
         max(v.sequencialbatida)
    from arelbatidatransitoview as v
    left join vwfunc  as f on v.chapa = f.chapa
   where datepart(dw,v.data) = 1  -- domingo
     and batida is not null
     and  v.data between @data_inicio and @data_final
     and v.codcoligada = 1
   group by v.chapa,
            v.data,
            f.filial,
            f.coligada,
            f.chapa,
            f.secao,
            f.funcao,
            f.nome,
            f.situacao;

   insert into @retorno (chapa,
                         nome,
                         filial,
                         secao,
                         situacao,
                         [data 1],
                         [data 2],
                         [data 3],
                         ocorrencia)
  select t1.chapa,
         t1.nome,
         t1.filial,
         t1.secao,
         t1.situacao,
         cast(t1.data as date),
         cast(t2.data as date),
         cast(t3.data as date),
         '04 - TESTE DOMINGO'
  from @batidas as t1
 inner join @batidas as t2 on t2.chapa = t1.chapa
 inner join @batidas as t3 on t3.chapa = t1.chapa
 where t2.data = dateadd(day, + 7, t1.data)
   and t3.data = dateadd(day, + 14, t1.data);

  return;
end;
go

This function is based on your previous select. Note that it is not necessary to create temporary tables when we can only use variables of type table. The purpose of the function is exactly what you wanted to do in the query: to search and organize data in a systematic way. After running it in the database, replace the select below in the query:

select bat.CHAPA,
       bat.NOME,
       bat.FILIAL,
       bat.SECAO,
       bat.SITUACAO,
       bat.[DATA 1],
       bat.[DATA 2],
       bat.[DATA 3],
       bat.OCORRENCIA
  from dbo.batidas('$v_datainicio', '$v_datafinal') bat