douyuanqia665858 2019-01-28 08:45
浏览 105

如何调用MSSQL存储过程并使用ODBC在PHP中检索输出参数

I am trying to call a MSSQL stored procedure from PHP using ODBC and I guess it is fairly working. I mean, the procedure gets invoked. I get no error. But I am having some trouble retrieving output params.

The procedure does not return anything, but it has one output parameter, which is a varchar and I need to get this data.

Here is the code in SQL:

CREATE PROCEDURE [dbo].[ProcName] 
    @peCodFact varchar(max) = NULL, 
    @peCodsAlmcs varchar(max) = NULL, 
    @peFechaConfDesde date = NULL, 
    @peFechaConfHasta date = NULL, 
    @peCodMed varchar(50) = NULL, 
    @psRegEjec varchar(max) = '<>' OUTPUT 
AS
BEGIN TRY
    /* data processing where @psRegEjec is set */
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;

All the input params can be null, and the param I need to retrieve is @psRegEjec.

Here's the code in PHP:

$idCon = odbc_connect($dns, $user, $passwd);
if (empty($idCon))
    throw new Exception(odbc_errormsg());

$query = 'DECLARE @psRegEjec varchar(max); ';
$query .= 'EXEC BDName.dbo.ProcName @psRegEjec = @psRegEjec OUTPUT; ';
$query .= 'SELECT @psRegEjec AS data; ';

$res = odbc_exec($idCon, $query);
$datosRes = @odbc_fetch_array($res);

I've tried to retrieve the results with odbc_fetch_object, ``odbc_fetch_array`, and all get the same. The results I'm getting is something like ...

\x0ø\x14\x7cen\x0)\x0\x0\x0\x11\x0\x0\x0Hø\x11\x7ado_nm_grupo_gestion\x0\xEÔ\x0\x18\x12\x7)\x0\x0\x0)\x0\x0\x0˜ø\x11\x7ccion_co_descripcion\x0\xF\x12\x7è\x11\x12\x7)\x0\x0\x0)\x0\x0\x0 ø\x11\x7ado_nm_funcionalidad\x0\xEÔ\x0P\x16\x12\x7)\x0\x0\x0)\x0\x0\x0 ù\x11\x7orporacion_descripcion\x0\x7 \x1B\x12\x7)\x0\x0\x0)\x0\x0\x0pø\x11\x7raseña anterior\x0\x0ˆ\x1F\x12\x7\x19\x0\x0\x0)\x0\x0\x0hú\x11\x7ripcion\x0\x0\x0\x0\x0y\x0\x0\x0\x19\x0\x0\x0\xF\x1.

and it goes on, pretty large... So I really don't know what to do. I saw in some article, that is required to create a second procedure, to return the output param from the first one, as ODBC driver does not suppor procedures output params ...

Is that really so? Is there any other way?

  • 写回答

1条回答 默认 最新

  • dongni8124 2019-01-28 09:53
    关注

    You may try to convert the value of your output parameter as nvarchar(max). If your stored procedure executes SELECT statement, then you'll have multiple result sets. Next is working example:

    Stored procedure:

    CREATE PROCEDURE [dbo].[ProcNameODBC] 
    @peCodFact varchar(max) = NULL, 
    @peCodsAlmcs varchar(max) = NULL, 
    @peFechaConfDesde date = NULL, 
    @peFechaConfHasta date = NULL, 
    @peCodMed varchar(50) = NULL, 
    @psRegEjec varchar(max) = '<>' OUTPUT
AS
BEGIN TRY
    -- Your actual result set
    SELECT [Column] AS [Result] FROM [Table]
    -- Output parameter
    SET @psRegEjec = 'Value for the output parameter'
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber, 
        ERROR_SEVERITY() AS ErrorSeverity, 
        ERROR_STATE() AS ErrorState, 
        ERROR_PROCEDURE() AS ErrorProcedure, 
        ERROR_LINE() AS ErrorLine, 
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;

    PHP:

    <html>
    <head>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta charset="utf-8">
    <?php
    $sql_server   = "server\instance,port";
    $sql_user     = "uid";
    $sql_password = "pwd";
    $sql_database = "database";
    $sql_conn_string = 'Driver={SQL Server Native Client 11.0};Server='.$sql_server.';Database='.$sql_database.';';
    if ($sql_conn = odbc_connect($sql_conn_string, $sql_user, $sql_password)) {
        $sql = "EXEC ProcNameODBC";

        $sql = 'DECLARE @psRegEjec varchar(max); ';
        $sql .= 'EXEC ProcNameODBC @psRegEjec = @psRegEjec OUTPUT; ';
        $sql .= 'SELECT CONVERT(nvarchar(max), @psRegEjec) AS [Result]; ';
        #$sql .= 'SELECT @psRegEjec AS [Result]; ';

        # Result set from stored procedure
        $result = odbc_exec($sql_conn, $sql);
        while (odbc_fetch_row($result)) {
            $value = odbc_result($result, "Result");
            echo $value.'</br>';
        }

        # SELECT output parameter
        odbc_next_result($result);
        while (odbc_fetch_row($result)) {
            $value = odbc_result($result, "Result");
            echo $value.'</br>';
        }
    }
    ?>
    </head>
    <body></body>
</html>

    Notes:

    Consider using PHP Driver for SQL Server. This driver fully supports input and output parameters for stored procedures.

    评论

报告相同问题？

悬赏问题

  • ¥100 嵌入式系统基于PIC16F882和热敏电阻的数字温度计
  • ¥20 BAPI_PR_CHANGE how to add account assignment information for service line
  • ¥500 火焰左右视图、视差（基于双目相机）
  • ¥100 set_link_state
  • ¥15 虚幻5 UE美术毛发渲染
  • ¥15 CVRP 图论 物流运输优化
  • ¥15 Tableau online 嵌入ppt失败
  • ¥100 支付宝网页转账系统不识别账号
  • ¥15 基于单片机的靶位控制系统
  • ¥15 真我手机蓝牙传输进度消息被关闭了，怎么打开？(关键词-消息通知)