php 7.1 PDO + SQL Server 2014: bindParam issue

Question

I have a problem executing the SQL Server Stored Procedures from php PDO:

If parameters in $SQL are not in the same order sequence of the parameters in the Stored Procedure, values are not passed to the correspondent parameter but just to the next in the list:

here an example:

<?php
        $hostname   = '';
        $database   = '';
        $user       = '';
        $password = '';
        $DB = new PDO("sqlsrv:Server=".$hostname.";Database=".$database.";TransactionIsolation=".PDO::SQLSRV_TXN_READ_UNCOMMITTED, $user, $password);

        $p1='p1';
        $p2='p2';
        $p3='p3';

      $SQL="EXEC dbo.sp__TEST :p1,:p2,:p3";
      $rsx= $DB->prepare($SQL,[PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
      $rsx->bindParam(':p1',$p1, PDO::PARAM_STR);
      $rsx->bindParam(':p3',$p3, PDO::PARAM_STR);
      $rsx->bindParam(':p2',$p2, PDO::PARAM_STR);
      $rsx->execute();
      $res=$rsx->fetch(PDO::FETCH_ASSOC);
      echo '<br>'.$res['result'];
?>

and here the SQL Server sproc:

CREATE PROCEDURE dbo.sp__TEST
(
    @p1     nvarchar(65)='',
    @p2     nvarchar(65)='',
    @p3     nvarchar(65)=''
)
AS
SET NOCOUNT ON;
BEGIN
    declare @p4 nvarchar(65)
    set @p4='p1=>'+@p1+'<br>p2=>'+@p2+'<br>p3=>'+@p3
    select @p4 as result
END
SET NOCOUNT OFF;

Here we will have as output:

p1=>p1
p2=>p2
p3=>p3

but if we change the $SQL declaration as follows:

$SQL="EXEC dbo.sp__TEST :p1,:p3";

we will have the following output:

p1=>p1
p2=>p3
p3=>

while I would expect to get

p1=>p1
p2=>
p3=>p3

This means that if I need to change/add/remove some parameters I have always to check if are in right sequence and is a waste of time..

Am I forgetting something or...

Thanks

TO THE UNWISE WHO DOWNVOTE: IF YOU CHANGE THE DECLARATION TO

$SQL="EXEC dbo.sp__TEST :p1,:p3,:p2";

YOU WILL GET AS RESULT:

p1=>p1
p2=>p3
p3=>p2

THAT IS NOT CORRECT:

MOREOVER, IF YOU CALL THE STORED PROCEDURE IN OTHER LANGUAGES (ASP, ASP.NET) IT RETURNS THE RIGHT RESULT EVEN IF YOU SHUFFLE THE PARAMETERS OR IF YOU OMIT SOME OF THEM.

ASP EXAMPLE

<%
    dim cn,cst,cmd,p1,p2,p3,rsx 
    set cn=Server.CreateObject("ADODB.Connection") 
    cst="Provider=sqloledb;server=XXXXX;Database=yyyyy;User ID=sa;Password=zzzzz" 
    cn.CursorLocation = 3 
    cn.open cst 
    set cmd=Server.CreateObject("ADODB.Command") 
    set cmd.ActiveConnection= cn 

    p1="p1" 
    p2="p2" 
    p3="p3" 

    cmd.CommandText="dbo.sp__TEST" 
    cmd.CommandType=4 'adCmdStoredProc 
    cmd.Parameters("@p1")= p1 
    cmd.Parameters("@p2")= p2 
    cmd.Parameters("@p3")= p3 

    set rsx=cmd.execute() 
    if not rsx.eof then 
        response.write rsx("result") 
    end if 
%>

That is the same as PHP functions work, `function test ($a, $b)` then `test($b)` would have `$b` as `$a` in the function and `$b` would be undefined. — user3783243, Aug 30 '18 at 16:48

score 2 · Answer 1 · answered Aug 30 '18 at 16:58

2

This has nothing to do with PHP.

Your procedure declaration states that it accepts 3 paramteters:

CREATE PROCEDURE dbo.sp__TEST
(
    @p1     nvarchar(65)='',
    @p2     nvarchar(65)='',
    @p3     nvarchar(65)=''
)

but your execution is only supplying 2:

$SQL="EXEC dbo.sp__TEST :p1,:p3"; // This is only supplying the first two parameters to your procedure

You would get the same result if you manually did:

$SQL="EXEC dbo.sp__TEST 'hi!','hey!'"; // This is only supplying the first two parameters to your procedure

You need to bind :p2 as an empty string to produce your expected result.

answered Aug 30 '18 at 16:58

MonkeyZeus

20,375
4
36
77

I do not agree: you can get a wrong solution even I change the declaration as follows: `$SQL="EXEC dbo.sp__TEST :p1,:p3,:p2";` with parameters not in the same sequence as in the Stored Procedure: What I mean is that PHP/PDO is not using the parameter for its names but for its position:What is the meaning of naming parameters if then you check only the position. Moreover if I call this Stored Procedure in vbscript, for example and supply only parameter p1 and p3 it put the value to p1 and p3 and not to the first 2 parameters it founds – Joe Aug 31 '18 at 06:05
@Joe The PDO bound `:p1` declaration has absolutely no significance to the `@p1` parameter of your stored procedure. You could call it `@jumbo` and use PHP to bind `:airplane` and as long as you sequence your parameters properly then `@jumbo` will receive the value of `:airplane`. – MonkeyZeus Aug 31 '18 at 12:38
Perhaps my question was not so clear, but this is it: is it correct that PHP PDO (for SQL Server) does not link parameters by their names but according to their position? I think that this is a strange behavior itself, even more, considering that in other languages ASP parameter `@p1` is linked to StoredProcedure `@p1`, regardless their position Thanks for your answer – Joe Sep 01 '18 at 13:17
@Joe Your recent comment is correct. That is how parameter binding works in PHP. Since I am not familiar with ASP I cannot agree with nor deny your claim about ASP but I do find your claim to be strange. If you think about it in simple terms then it sounds like you need to know the parameter names in order to use them? That sounds like a hellish experience especially if you have a large codebase and call that stored procedure at many locations. So if you decide to give your parameters meaningful names then you will break your entire codebase? – MonkeyZeus Sep 01 '18 at 18:34
@Joe Can you provide me a link to the documentation about this? Or even a Stack Overflow post which presents your claim? Imagine if PHP forced you to know the parameter names which a function uses in order to use the function. – MonkeyZeus Sep 01 '18 at 18:34
Well, Actually for what I know, ASP allows to pass parameters both by name and by their position, but I always used by name because I think it was much more reasonable: if I have to enter the `weight` it is easier for me to write `cmd.parameters("@weight")=weight` than checking the position of the weight parameter and then write something cmd.parameters(27)=weight. while you think that is much more reasonable to enter by sequence.. Probably this is due to how the 2 languages works: PHP requires you to declare the parameters in advance: ASP will not. Anyway Now I understood how PHP works – Joe Sep 02 '18 at 09:05
here the same procedure in classic ASP:`<% dim cn,cst,cmd,p1,p2,p3,rsx set cn=Server.CreateObject("ADODB.Connection") cst="Provider=sqloledb;server=XXXXX;Database=yyyyy;User ID=sa;Password=zzzzz" cn.CursorLocation = 3 cn.open cst set cmd=Server.CreateObject("ADODB.Command") set cmd.ActiveConnection= cn p1="p1" p2="p2" p3="p3" cmd.CommandText="dbo.sp__TEST" cmd.CommandType=4 'adCmdStoredProc cmd.Parameters("@p1")= p1 cmd.Parameters("@p2")= p2 cmd.Parameters("@p3")= p3 set rsx=cmd.execute() if not rsx.eof then response.write rsx("result") end if %>` – Joe Sep 02 '18 at 09:05
@Joe I don't understand the code you've posted and I am having a hard time finding any docs on the subject but I will take your word for it as far as how ASP does things. Anyways, PHP doesn't do it that way so you'll just have to accept what I have answered. – MonkeyZeus Sep 04 '18 at 13:31
Clearly you do not need to believe in my words, although I have some hundred stored procedure written in ASP and working in this way. Anyway I understood the way PHP works and will follow the rules. thanks – Joe Sep 14 '18 at 09:08
@Joe Clearly I do need to believe in your words especially since you have not linked me to any docs, I am not familiar with ASP, and I cannot read your code since it is one continuous line. – MonkeyZeus Sep 14 '18 at 11:36
this [link](https://stackoverflow.com/questions/21561657/classic-asp-ado-execute-stored-procedure-passing-in-parameters) can be a sample answer: you can see that @Parameters Name matches with variables Names – Joe Sep 14 '18 at 12:33
@Joe Thanks, I believed you the whole time about ASP but now I was able to verify it "[*NOTE: It can be difficult to determine how to properly call a stored procedure if you are unaware of the stored procedure's parameter information. Without the correct information, you cannot properly create the ADO parameters...*](https://support.microsoft.com/en-us/help/300488/how-to-run-sql-server-stored-procedures-from-an-asp-page)" So this is a specific feature of the ADO object. – MonkeyZeus Sep 14 '18 at 13:49

php 7.1 PDO + SQL Server 2014: bindParam issue

1 Answers1