2

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.

1 Answer 1

1

It should work - according to PDO's documentation. But, it's said that PDO::PARAM_INPUT_OUTPUT stands for an INOUT parameter, while you are using an OUT.

You could try this way - as explicitly setting the $length parameter (while leaving the PDO::PARAM_INPUT_OUTPUT) is triggering the OUT mode;

$stmt->bindParam(8, $resource_id, PDO::PARAM_STR, 100);

But i think that it should work as INOUT too (as you wrote your code originally).

Maybe the PDO driver (or the ODBC one) at C level does not support the output parameter binding (as in PDO-MySQL).

But if this is the only OUT parameter in your procedure, you could use a function:

SELECT func(?, ?, ..., ?) AS result

+1: Maybe you are interested in this comment on php.net

Sign up to request clarification or add additional context in comments.

3 Comments

SQL Server doesn't recognize the concept of input-output parameters, only input or output, and of course PHP doesn't have output only, just input-output, or if I'm not mistaken, no parameter for input only. As you say, it really should work, but I'll try your suggestion when I finish a couple of other tasks I'm working on. Thanks!
Tried it but it didn't work. Maybe I will have to write a function. Was hoping to use the existing code.
I took the easy way out and removed the output parameter from the stored procedure and added this: select @resource_id to the end of the procedure. Now I have access to the value I need in the PHP code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.