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!!!
