0

I need some help in relation to PHP PDO MSSQL Stored Procedure.

I have a Stored Procedure which is called with two parameters userId and pwd, the Stored Procedure then returns two values status and token (using SELECT @status as status, null as token in the Stored Procedure to return the value)

When I try to call the Stored Procedure from PHP (ver. 7.0) using PDO I don't receive any return values

This is the PHP code:

$conn = new PDO("sqlsrv:server=".$host.";Database=".$db_name, 
$username,$password);

$userId = "2465";   
$pwd = "460";

$query = "exec sp_getToken @userId=:userId, @pwd=:pwd";
$stmt = $conn->prepare($query);

$stmt->bindValue(':userId', $userId);
$stmt->bindValue(':pwd', $pwd);

$stmt->execute();

while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    var_dump($result);
}

Can anyone tell what to do?

Improve this question
7
  • 3
    simple; you never executed the query. Commented Aug 3, 2018 at 13:10
  • Ups it must have slipped in the copy I have $stmt->execute(); between the last bindValue and the while loop Commented Aug 3, 2018 at 13:15
  • update your post then and check for errors and the logs. You're not doing that. Commented Aug 3, 2018 at 13:15
  • 1
    TBH, I don't know what's causing your code to not be working. Can you check for errors with php.net/manual/en/pdo.error-handling.php and php.net/manual/en/function.error-reporting.php (set to catch and display) and a possibility to try and change the bindValue() to bindParam(). Commented Aug 3, 2018 at 13:35
  • 1
    Can you do another update to your question (please) as to what the schema is, the column types and example values? It will probably help in many ways. If you feel that is irrelevant, then see the answer below. Commented Aug 3, 2018 at 13:43

3 Answers 3

Reset to default
1

It is almost the same, but you may try with this:

<?php
$host = 'server\instance,port';
$db_name = 'database';
$username = 'user';
$password = 'password';

# Connection
try {
    $conn = new PDO("sqlsrv:server=".$host.";Database=".$db_name, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Error connecting to SQL Server: ".$e->getMessage());
}

# Stored procedure
try {
    $query = "{call sp_getToken(@userId=?, @pwd=?)}";
    $userId = "2465";   
    $pwd = "460";
    $stmt = $conn->prepare($query);
    $stmt->bindParam(1, $userId, PDO::PARAM_STR);
    $stmt->bindParam(2, $pwd, PDO::PARAM_STR);
    $stmt->execute();
    while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        var_dump($result);
        echo"</br>";
    }
} catch(PDOException $e) {
    die("Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;

#
$conn = null;
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks for your reply - it didn't solved the problem directly, but by running the code, I got another error message (Error executing stored procedure: SQLSTATE[IMSSP]: The active result for the query contains no fields.) - which did help me to find a solution to problem. The problem was that a stored procedure returns a result containing the number of rows affected as the first result and then the actual result. By adding "SET NOCOUNT ON" in my stored procedure the first row is skipped.
@hedemann I'm glad, that you have found a solution to your problem!
1

Problem solves :)

By adding "SET NOCOUNT ON" to my stored procedure. Obviously the problem was related to the facts, that a stored procedure returns two results, the first result containing the number of rows affected and the second result containing the actual data.

Thanks to everybody for trying helping me :)

Improve this answer

Comments

0

Stored procedures are stored in the database schema I believe.

If you add the schema to your query SQL server should know where to "look" for your stored procedure.

$query = "EXEC [dbo].[sp_getToken] @userId=:userId, @pwd=:pwd";

Also when binding the parameters it might help defining the type. I've had issues with SQL server where defining the parameter typed resolved the issue.

$stmt->bindValue(':userId', $userId, PDO::PARAM_STR);
$stmt->bindValue(':pwd', $pwd, PDO::PARAM_STR);

Also, make sure that the user that PHP logs into the database has the Execute permission in SQL Server.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure?view=sql-server-2017

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.

From Stored Procedure Properties, select the Permissions page.

To grant permissions to a user, database role, or application role, click Search.

In Select Users or Roles, click Object Types to add or clear the users and roles you want.

Click Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.

In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions (Database Engine).

Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.

Improve this answer

9 Comments

I agree that by adding the types as an argument helps, however we don't know what their schema looks like, or the column types and respective values are. The user id one is most likely an int type, but the password column's type is questionable. I feel the question lacks a lot of detail. An int type for a probable text type will fail on PARAM_INT, just saying.
True, but we can depend on OP to define the proper types. A password field would be an nvarchar type so a string I would think, but one never knows. But if it's an int or something else OP will have to modify it himself.
I've posted a comment asking for it. I'll come back later to see how this is unraveling ;-) Cheers
the userId and pwd is of type varchar(50) (old system) - I have now added "PDO::PARAM_STR" to both bindValue - result still the same
yes - but should the $db_name in the "$conn = new PDO("sqlsrv:server=".$host.";Database=".$db_name, $username,$password);" do the same job
|

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.