I've been learning PHP by adding some functionality to one of my company's web pages to validate user input of SSN before inserting a new record into the database. That part is working fine. The database insert involves calling a SQL Server stored procedure that has an output parameter that returns the GUID of the new record for use in a subsequent process. The database insert part works but when I echo the value of the returned parameter it's an empty string.
Here are pertinent details: SQL Server 2008 running on a separate server WAMP Server 2.2 running on a Windows 64 PC PHP 5.3.1 Apache 2.2.22
Here is my PHP code:
$conn = new PDO( "odbc:Driver={SQL Server};Server=xxx.xxx.xx.xx;Database=xxxxx;charset=UTF-8'", "uid", "pwd");
if(!$conn) {
die( "<br />Error connecting to SQL Server");
}
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$resource_id = "";
$stmt = $conn->prepare("{CALL xxxx.dbo.app_sp_insert_rq(?, ?, ?, ?, ?, ?, ?, ?)}");
$stmt->bindParam(1, $survey_text, PDO::PARAM_STR);
$stmt->bindParam(2, $event_date, PDO::PARAM_STR);
$stmt->bindParam(3, $type_id, PDO::PARAM_STR);
$stmt->bindParam(4, $description, PDO::PARAM_STR);
$stmt->bindParam(5, $folder_id, PDO::PARAM_STR);
$stmt->bindParam(6, $has_details, PDO::PARAM_STR);
$stmt->bindParam(7, $uid, PDO::PARAM_STR);
$stmt->bindParam(8, $resource_id, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 100);
$stmt->execute();
echo $resource_id;
Here is the stored procedure definition:
CREATE PROCEDURE [dbo].[app_sp_insert_rq]
@survey_text nvarchar(max),
@event_date nvarchar(20),
@type_id nvarchar(100),
@description nvarchar(100),
@folder_id nvarchar(100),
@has_details nchar(1),
@uid nvarchar(100),
@resource_id nvarchar(100) output
AS
select @resource_id = NEWID()
insert into
xxxxx.dbo.app_events (
resource_id,
event_date,
[type_id],
[description],
folder_id,
has_details,
[uid],
survey_text
)
values (
@resource_id,
@event_date,
@type_id,
@description,
@folder_id,
@has_details,
@uid,
@survey_text
)
Any help will be much appreciated. I only got this far via multiple searches but this is one roadblock I haven't been able to figure a way around.