CODEWITHSUNDEEP
phpsql-serverstored-procedures
wamador
wamador

Reputation: 41

PHP - MSSQL getting results from Stored Procedures

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.

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.

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

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.

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;
}

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.

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

Thanks in advance!

Upvotes: 3

Views: 9677

Answers (2)

wamador
wamador

Reputation: 41

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;
}

and the original SP was:

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

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.

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

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

and the PHP code:

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;
}

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.

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

Many thanks to Maximus2012!!! Cheers!!!

Upvotes: 1

Maximus2012
Maximus2012

Reputation: 1819

This is some code that I have in one of my applications. See if it helps:

//Query SQL
$tsql = "Exec spinsert @tabla=?,@columnas=?,@valores=?"; 
$params = array($tabla,$columnas,$valores);

//Execute the stored query 
$stmt = sqlsrv_query($conn, $tsql, $params);
if ($stmt === false)
{
    echo "<h3>Error in query preparation or execution.</h3>";
    ListErrors();
    die;
}
else {
    echo "Insert Successful";
}

// this should help for the non-insert/update case
$arr = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
var_dump($arr);

Upvotes: 2

Related Questions