具有MSSQL的PDO返回无效的游标 [英] PDO with MSSQL returns Invalid Cursor

问题描述

我正在使用PDO与本地MSSQL数据库建立连接.通过连接完全运行任何存储过程都不会给我任何错误.

I'm running a connection with PDO to a local MSSQL database. Running any Stored procedure at all through the connection doesn't give me any error at all.

这个单一的存储过程给了我以下错误:

This one single Stored procedure is giving me the following error:

Error in SQL: [Microsoft][SQL Server Native Client 10.0]Invalid cursor state - Query: exec sp_Get_SaldosWeb @Tipo=1, @IdDato=15368

这是我当前的PDO字符串，用于在此函数内部进行连接并返回数组:

This is my current PDO string for connecting inside this function and returning the array:

        $query = $this->db->prepare($qry, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
        $final = $query->execute();
        $this->setRows($query);
        if(!$final) {
            $this->error($qry, $query, $ret);
        } else {
            return $query->fetchAll();
        }

我尝试在执行之前和执行fetchAll之后都关闭游标，如下所示:

I've tried closing the cursor both before the execute and after fetchAll like so:

    $rows = $query->fetchAll();
    $query->closeCursor();
    return $rows;

但这也不起作用.仅供参考，在我的代码中的任何地方，对此存储过程的调用之前都不会执行任何查询.

But that also doesn't work. FYI, there are no queries executed before this call to this stored procedure anywhere in my code.

不确定为什么此存储过程会出现这么多错误.如果我从MSSQL管理控制台运行此完全相同的SP，它将正常运行并返回3行.

Not sure why this stored procedure is giving so many errors. If I run this exact same SP from MSSQL Management console it runs fine and returns 3 rows.

这是存储过程:

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Get_SaldosWeb]          
@Tipo int ,  --1 = Alumno 2 = Familia          
@IdDato int           

as          


 if @Tipo = 1           
 begin          

  select SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos as [Nombre],          
   SC_Moneda.Nombre as Moneda, upper(replace(replace(replace(replace(replace( replace(lower(SC_TipoCargo.Nombre),char(237),'i'), char(243),'o'), char(233),'e') , char(225),'a') ,char(250),'u'),char(241),'ñ'))  as [Tipo de Cargo]            
  , cast(round(Sum(SC_CargoxAlumno.Debe),2) as decimal(18,2)) as Monto      ,    
  SC_Alumno.Codigo as Codigo2    
  from SC_CargoxAlumno          
  inner join SC_Moneda on SC_CargoxAlumno.IdSC_Moneda = SC_Moneda.IdSC_Moneda          
  inner join SC_TipoCargo on SC_CargoxAlumno.IdSC_TipoCargo = SC_TipoCargo.IdSC_TipoCargo          
  inner join SC_Alumno on SC_Alumno.IdSC_Alumno = SC_CargoxAlumno.IdSC_Alumno           
  inner join SC_Familia on SC_Alumno.IdSC_Familia = SC_Familia.IdSC_Familia          
  where          
  SC_Alumno.IdSC_Alumno = @IdDato          
  and SC_CargoxAlumno.Debe <> 0          
  group by           
  SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos ,          
   SC_Moneda.Nombre , SC_TipoCargo.Nombre        

 end          
 else          
 begin          

  select SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos as [Nombre],          
   SC_Moneda.Nombre as Moneda, upper(replace(replace(replace(replace(replace( replace(lower(SC_TipoCargo.Nombre),char(237),'i'), char(243),'o'), char(233),'e') , char(225),'a') ,char(250),'u'),char(241),'ñ'))  as [Tipo de Cargo]  ,          
  cast(round(Sum(SC_CargoxAlumno.Debe),2) as decimal(18,2)) as Monto      ,    
  SC_Alumno.Codigo as Codigo2    
  from SC_CargoxAlumno          
  inner join SC_Moneda on SC_CargoxAlumno.IdSC_Moneda = SC_Moneda.IdSC_Moneda          
  inner join SC_TipoCargo on SC_CargoxAlumno.IdSC_TipoCargo = SC_TipoCargo.IdSC_TipoCargo          
  inner join SC_Alumno on SC_Alumno.IdSC_Alumno = SC_CargoxAlumno.IdSC_Alumno           
  inner join SC_Familia on SC_Alumno.IdSC_Familia = SC_Familia.IdSC_Familia          
  where          
  SC_Familia.IdSC_Familia = @IdDato          
  and SC_CargoxAlumno.Debe <> 0          
  group by           
  SC_Alumno.Codigo ,           
  SC_Alumno.Nombres + ' ' + SC_Alumno.Apellidos ,          
   SC_Moneda.Nombre , SC_TipoCargo.Nombre        

 end

推荐答案

在过程的开头添加"SET NOCOUNT ON".

Add "SET NOCOUNT ON" to the beginning of your procedure.

您可能会在这里找到参考文献:

You might find references here :

PHP数据对象

我存储的程序最佳做法"清单

这篇关于具有MSSQL的PDO返回无效的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！