PDO with MSSQL driver how to get output parameters?

Question

When you are using PDO with MSSQL driver you actually use FreeTDS as low level driver. There is some different ways to execute stored procedures - language queries and RPC call.

FreeTDS also supports TDS protocol version 4.2 and 7.x. The one of main difference between them is a behaviour of stored procedure call. Microsoft changed the behaviour from protocol 4.2 to 7.0 not returning output parameters from language queries. Language queries mainly send the textual query to the server wrapping into a TDS packet.

Example of language query with PDO (from php.net)

    $stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
    $value = 'Hello!';
    $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 
    $stmt->execute();
    print "The output is $value\n";

Actually you send something like "EXEC sp_takes....". And if you run sample above with MSSQL you would get empty output parameter in TDS 7.х and expected result with 4.2. Why we can`t use 4.2 and be happy? It has a lot of limitations:

• ASCII only, of course.
• RPC and BCP is not supported.
• varchar fields are limited to 255 characters. If your table defines longer fields, they'll be truncated.
• dynamic queries (also called prepared statements) are not supported.

So, the 4.2 is not a variant.

Example of RPC call (from php.net odbtp extension)

    $stmt = mssql_init('sp_takes_string_returns_string');
    $value = 'Hello!';
    mssql_bind($stmt, 1, $value, SQLVARCHAR, true,  false,   4000);
    mssql_execute($stmt);
    print "The output is $value\n";

Using sample above with native mssql extension in php you got right result with TDS 7.2. Actually you send binary RPC packet with that code.

Question

Is there is any way to make RPC call for stored procedure with PDO and MSSQL driver?

Answer 1

Try this

 try {
    $hostname = "myhost";
    $port = 10060;
    $dbname = "tempdb";
    $username = "dbuser";
    $pw = "password";
    $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
  $stmt = $dbh->prepare("select name from master..sysdatabases where name = db_name()");
  $stmt->execute();
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
  unset($dbh); unset($stmt);

Answer 2

Either I missed FreeTDS when I first answered, or this question was updated. Either way...

FreeTDS Does not support output parameters in MSSQL Server 7 SP3 or greater. This is due to changes made in SQL Server.

---Old Response Below---

I know it's not exactly what you're looking for.
I ran through some old files to the only time I'd linked to MSSQL and pulled this.

$con = mssql_connect($db['host'], $db['user'], $db['pass']);
mssql_select_db($db['database'], $con);//Select DB
$result = mssql_query("my_Procedure_Name ".$propertyOne.", ".$propertyTwo."");

I hope this helps