PHP - MSSQL 从存储过程中获取结果 [英] PHP - MSSQL getting results from Stored Procedures

问题描述

我是一名老派的开发人员，刚刚进入 WWW 编程世界.我正在为我合作的公司开发一个使用 HTML、CSS、PHP 和 MSSQL Server 2008 R2 的应用程序.

I'm an old school developer and just getting in the WWW programming world. I'm developing an application with HTML, CSS, PHP and MSSQL Server 2008 R2 for the company i'm working with.

在我的应用程序中，我使用存储过程从/向数据库插入、修改、删除或查询信息.根本不使用TSQL指令，只是从PHP代码中执行存储过程.

In my application I'm using stored procedures to insert, modify, delete or query information from/to the database. Not using TSQL instructions at all, just executing stored procedures from the PHP code.

我使用 PHP 5 和 SQLSRV 驱动程序进行数据库交互.

I'm using PHP 5 and SQLSRV driver for database interaction.

到目前为止一切正常，但现在我被困在插入部分...如果一切正常，SP 会插入记录，如果不是，则不会...但我没有看到直到我再次查询表以查看记录是否存在.

Everything working fine so far, but now I'm stuck on the Insert piece... If everything is ok, the SP inserts the record, if not, it doesn't... but i'm not seeing the result until i query the table again just to see if the record is there or not.

我在PHP中使用以下代码运行将记录插入表中的SP:

Im using the following code in PHP to run the SP that inserts the record in the table:

function spinserta($tabla, $columnas, $valores, $cnct) {

   $stmt = 'Exec spinsert @tabla=?,@columnas=?,@valores=?';         
   $params = array($tabla,$columnas,$valores);

   $result = sqlsrv_query($cnct,$stmt,$params) ;

   return $result;
}

如果交易不成功，我不会在 $result 变量中得到任何信息，并希望从 SP 获得结果消息，以便向用户显示错误消息.

if the transaction is not succesful, im not getting anything in the $result variable and would like to have the resulting message from the SP in order to display an error message to the user.

如何从 SP 获取结果消息(无论是否出错)?

How to get the resulting message from the SP (no matters if it is an error or not)?

提前致谢！

推荐答案

经过数小时的研究……终于有了概念！事情是这样的:原来的 PHP 代码是:

after hours of researching.... finally got the concept! here is the thing: the original PHP code was:

function spinserta($tabla, $columnas, $valores, $cnct) {
   $stmt = 'Exec spinsert @tabla=?,@columnas=?,@valores=?';         
   $params = array($tabla,$columnas,$valores);

   $result = sqlsrv_query($cnct,$stmt,$params) ;

   return $result;
}

原来的SP是:

ALTER PROCEDURE [dbo].[spinsert] 
    @tabla varchar(50),
    @columnas varchar(8000),
    @valores varchar(8000)
AS
BEGIN
    SET NOCOUNT ON;
    declare @orden varchar(8000)
    declare @return_value int

    set @orden='Insert into ' + @tabla + ' (' + @columnas + ') values (' + @valores + ')';
    execute (@orden);

    return 
END

非常简单...当php代码执行成功并且SP成功时，变量$result被加载为Resource id#14"，如果SP失败，$result值为null.

very straight forward... When the php code was executed and the SP succeded, the variable $result was loaded with "Resource id#14", if the SP failed, the $result value was null.

一切顺利！！！但不是我想要的方式.然后我找到了这篇文章:http://msdn.microsoft.com/en-us/library/ms178592.aspx

Things were working well!!! But not the way i wanted. Then i found this article: http://msdn.microsoft.com/en-us/library/ms178592.aspx

基于此，我修改了 SP:

Based on that I modified the SP:

ALTER PROCEDURE [dbo].[spinsert] 
    @tabla varchar(50),
    @columnas varchar(8000),
    @valores varchar(8000)
AS
BEGIN
    SET NOCOUNT ON;
    declare @orden varchar(8000)
    declare @return_value int

    begin try
        set @orden='Insert into ' + @tabla + ' (' + @columnas + ') values (' + @valores + ')';
        set @return_value=0;
        execute (@orden);
    end try

    begin catch
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @return_value = ERROR_NUMBER()
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    end catch

    return @return_value

END

和 PHP 代码:

function spinserta($tabla,$columnas,$valores,$cnct) {
    $tsql = 'Exec spinsert @tabla=?,@columnas=?,@valores=?';            
    $params = array($tabla,$columnas,$valores);

    $stmt = sqlsrv_query($cnct,$tsql,$params) ; 
    $errors=sqlsrv_errors();

    if ($stmt === false or $stmt===0) {
        foreach( $errors as $error ) {
            $stmt=str_replace("'","","Error: ". $error['code']. " - " . $error['message']);
            }
    } else {
        $stmt="1";
    }       

    return $stmt;
}

我原来的方法有两个问题，一个是在数据库引擎端，SP 并没有真正产生系统错误，即使语句失败.使用 Try-Catch 技术，加上 RAISEERROR 概念，SP 最终在语句失败时生成系统错误.在此之后，只需对 PHP 代码进行细微调整即可.

There were two problems with my original approach, 1 at database engine side, the SP was not really generating a system error, even though the statement failed. With the Try-Catch, technique, pluse the RAISEERROR concept, the SP was finally generating the system error when the statemente failed. After this, it was just matter of minor adjustments to the PHP code.

使用这种方法，发送到数据库的信息的验证在数据库引擎端完成，无需编写大量代码，只需在提交时验证表单中的字段.需要的是确保数据库表、关系、约束、完整性等得到很好的应用，并且数据库将保护自己免受错误数据的影响.如果提交表单中提供的信息有错误，数据库将拒绝这些错误，代码将向用户显示正确的错误信息.

With this approach, the validation of information sent to the database, is done at Database Engine side, eliminating the need of writing a lot of code, just to validate the fields in the forms at the submission time. what is needed is to ensure database tables, relationships, constraints, integrity and others are well applied, and the database will protect itself against incorrect data. If errors with information provided in the form are submited, the database will reject them and the code will show to the user the proper errors behind.

我想看看是否可以用 MySQL 做类似的事情...，我想是的！

I would like to see if something similar is doable with MySQL..., i think so!

非常感谢Maximus2012！！！干杯！！！

Many thanks to Maximus2012!!! Cheers!!!

这篇关于PHP - MSSQL 从存储过程中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！