PHP + MSSQL using PDO with Named Parameters not '?'

Question

I'm about to commence building a site using mssql and php.

I plan to use PDO's, however, as I currenlty believe its not possible to use named parameters.

Currently in MySQL I would use named placeholders in my query as such;

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT *
        FROM table
        LIMIT :numRows";

$st = $this->conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );

However when using MSSQL;

$conn = new PDO("mysql:host=" . DB_HOST .";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);

$sSQLInsert = "SELECT TOP ? *
    FROM table";

$aParams = array($iLimit);

$st = sqlsrv_query($dbhandle, $sSQLInsert, $aParams)){}

My worry appears when I have many parameters that need to be bound. Managing the order of them and dancing back and forth between query-parameters doesnt seem ideal.

So my question; is it posible to use named placeholders with MSSQL?

it's mysqli that can't have named parameters. PDO supports ? and :foo, which stands to reason, since it's a generic "all databases" interface, while mysqli is (obviously) specific to mysql. — Marc B
– Marc B, Commented Jul 6, 2015 at 14:25

Derokorian · Accepted Answer · 2015-07-06 14:00:38Z

1

This would be a simple script to write an check, however I found documentation and an example! The answer is YES! Name parameters works with PDO_MSSQL.

https://msdn.microsoft.com/en-us/library/ff628166(v=sql.105).aspx

$stmt = null;
$contact = "Sales Agent";
$stmt = $conn->prepare("select * from Person.ContactType where name = :contact");
$stmt->bindParam(':contact', $contact);
$contact = "Owner";
$stmt->execute();

answered Jul 6, 2015 at 14:00

Derokorian

1,5001 gold badge11 silver badges16 bronze badges

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

Comments

meda · Accepted Answer · 2015-07-06 14:19:13Z

1

No You cannot use named Placeholder with sqlsrv_ or any other extension.

This is a feature of PDO only.

I plan to use PDO's, however, as I currenlty believe its not possible to use named parameters.

You can do it with SQL server:

$sql = "SELECT TOP :numRows *
        FROM table";
$st = $this->conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->execute();
var_dump($st->fetch());

It is not about the server, it is more about the driver, this PDO advantage since it is compatible with most database. You don't have to change your code, just the connection.

edited Jul 6, 2015 at 14:19

answered Jul 6, 2015 at 14:05

meda

45.5k14 gold badges97 silver badges123 bronze badges

2 Comments

atoms Over a year ago

thanks meda need to brush up on my mssql... Have updated the question. Can you shed any light on the actual question?

meda Over a year ago

@atoms I answered your question

Community · Accepted Answer · 2017-05-23 11:43:58Z

1

You might have luck looking at the following PDF (auto download of pdf):

http://tinyurl.com/orc2xkc

It has examples binding with variables and arrays.

$sql = ‘select Title, 
 FirstName, 
 MiddleName, 
 LastName 
 from SalesLT.Customer 
 where Title = :title and CustomerId<10’;
$query = $conn->prepare($sql);
$title = ‘Mr.’;
$query->bindParam(‘:title’, $title);
$query->execute();

In the meantime I will look up info on mssql driver to use because that apparently plays into it.

Edit ... As for the driver look at the comments under this question: From PDO to SQLSRV

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jul 6, 2015 at 14:09

Drew

25k10 gold badges47 silver badges81 bronze badges

Comments

atoms · Accepted Answer · 2015-07-07 10:23:42Z

Many thanks for all the replies to this. After some time I've managed to get it working, so in answer to my question; Yes it is possible to use Named Placeholders with MSSQL.

After installing the SQLSRV PDO extension, placeholders can be used as such;

$database = "";
$server = "";
$conn = new PDO( "sqlsrv:server=$server ; Database = $database", DB_USER, DB_PASSWORD);

$sql = "SELECT * FROM Table WHERE MemberID =:MemberID";

$iMemberID = 5;

$st = $conn->prepare($sql);

//named placeholders within the execute() as an array.
   $st->execute(array(':MemberID'=>$iMemberID));
//OR using bind param..
   $st->bindParam(':MemberID', $iMemberID);

while ($row = $st->fetch()){
    echo "<pre>";
    var_dump($row);
    echo "</pre>";
}

Thanks to Drew Pierce for the link to the drivers and pdf and everyone else for the help.

Collectives™ on Stack Overflow

PHP + MSSQL using PDO with Named Parameters not '?'

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related