带PHP的RedHat Linux上用于SQLServer®的Microsoft®ODBC驱动程序11-与PDO的存储过程绑定参数时出现错误 [英] Microsoft® ODBC Driver 11 for SQL Server® on RedHat Linux with PHP - gives an error when bind parameter with PDO for stored procedures

问题描述

没有任何参数，它将正常工作，但是当有参数时，会出现以下错误-

SQLSTATE [42000]:语法错误或访问冲突:8018 [Microsoft] [SQL Server的ODBC驱动程序11] [SQL Server]无效的参数4(''):数据类型0x23是已弃用的大对象，或LOB，但被标记为输出参数.不支持不建议使用的类型作为输出参数.使用当前的大对象t(在/builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254上的SQLExecute [8018])

这在 Centos 6，用于SQLServer®的ODBC驱动程序11-RedHat Linux，unixODBC-2.3.0，MSSQL Server 2008 R2

连接字符串:

    $con = new PDO("odbc:dsnName", 'sa','saa');
    $con->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $con->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC); 

使用的示例存储过程:

    $stmt = $con->prepare( "SET NOCOUNT ON DECLARE  @return_value int

    EXEC    @return_value = [sp_insert_into_t_contact_test]
    @paravalue = ?

    SELECT  'returnV' = @return_value");
    $stmt->bindParam(1, $v1 = 5, PDO::PARAM_STR, 100);
    $stmt->execute(); 
    $return =$stmt->fetch();
    echo $return['returnV'];

简单的存储过程返回"123"

    [dbo].[sp_insert_into_t_contact_test]
       @paravalue varchar(100)
    AS
    return 123

其他信息-

odbc.ini文件-

   [dsnName]
   Driver=SQL Server Native Client 11.0
   Description=My Sample ODBC Database Connection
   Trace=Yes
   Server=192.168.2.60
   Port=1433
   Database=NSCDB_3

odbcinst.ini

  [SQL Server Native Client 11.0]
  Description=Microsoft ODBC Driver 11 for SQL Server
  Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
  Threading=1
  UsageCount=1

最后，我找到了解决方案，Microsoft驱动程序已经为C或C ++应用程序开发，PDO参数绑定无法正常工作.如果您在不使用PDO的情况下传递参数，则一切正常.替换为@paravalue = N'".$v1."'

例如- 常规参数绑定-SQLBindParameter PDO参数绑定-$stmt->bindParam(1, $v1 = 5, PDO::PARAM_STR, 100);

正确的鳕鱼代码段:

        $stmt = $con->prepare( "SET NOCOUNT ON DECLARE  @return_value int

        EXEC    @return_value = [sp_insert_into_t_contact_test]
        @paravalue = N'".$v1."'

        SELECT  'returnV' = @return_value");
        $stmt->execute(); 
        $return =$stmt->fetch();
        echo $return['returnV'];

Without any parameters it will works fine, but when parameters are there, gives the following error -

SQLSTATE[42000]: Syntax error or access violation: 8018 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid parameter 4 (''): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object t (SQLExecute[8018] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254)

This runs on Centos 6 , ODBC Driver 11 for SQL Server® - RedHat Linux , unixODBC-2.3.0 , MSSQL Server 2008 R2

Connection string :

    $con = new PDO("odbc:dsnName", 'sa','saa');
    $con->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $con->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC); 

Sample stored procedure used :

    $stmt = $con->prepare( "SET NOCOUNT ON DECLARE  @return_value int

    EXEC    @return_value = [sp_insert_into_t_contact_test]
    @paravalue = ?

    SELECT  'returnV' = @return_value");
    $stmt->bindParam(1, $v1 = 5, PDO::PARAM_STR, 100);
    $stmt->execute(); 
    $return =$stmt->fetch();
    echo $return['returnV'];

Simple stored procedure to return "123"

    [dbo].[sp_insert_into_t_contact_test]
       @paravalue varchar(100)
    AS
    return 123

Additional information -

odbc.ini file -

   [dsnName]
   Driver=SQL Server Native Client 11.0
   Description=My Sample ODBC Database Connection
   Trace=Yes
   Server=192.168.2.60
   Port=1433
   Database=NSCDB_3

odbcinst.ini

  [SQL Server Native Client 11.0]
  Description=Microsoft ODBC Driver 11 for SQL Server
  Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
  Threading=1
  UsageCount=1

解决方案

Finally I found the solution for this, Microsoft driver has develop for C or C++ application and the PDO parameter binding does not work as expected. If you pass parameters without using PDO everything works as expected. Replace with @paravalue = N'".$v1."'

Fore example - normal parameter binding - SQLBindParameter PDO parameter binding - $stmt->bindParam(1, $v1 = 5, PDO::PARAM_STR, 100);

Corrected cod snippet:

        $stmt = $con->prepare( "SET NOCOUNT ON DECLARE  @return_value int

        EXEC    @return_value = [sp_insert_into_t_contact_test]
        @paravalue = N'".$v1."'

        SELECT  'returnV' = @return_value");
        $stmt->execute(); 
        $return =$stmt->fetch();
        echo $return['returnV'];

这篇关于带PHP的RedHat Linux上用于SQLServer®的Microsoft®ODBC驱动程序11-与PDO的存储过程绑定参数时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！