We help IT Professionals succeed at work.
Get Started

How do I incorporate a MSSQL stored procedure into a PDO query?

Bruce Gust
Bruce Gust asked
on
1,453 Views
Last Modified: 2015-03-23
I've got my connection happening, so that much is done.

The next step is to take the store procedure and incorporate that into my PDO select statement. Mind you, this new territory for the kid, so pardon any ridiculously rudimentary questions.

Here's what I've got thus far:

date_default_timezone_set('America/Chicago');
ini_set('display_errors', 1);
include('mssql_db_cred.php');
$mssql_pdo = new PDO(“dblib:host=”.$mssql_cred_data[‘server’].”;dbname=”.$mssql_cred_data[‘dbname’],$mssql_cred_data[‘user’],$mssql_cred_data[‘pw’]); 
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);

$sth = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_DeveloperCurrentProjectList.sql");
$sth->bindParam(1, $str_ProjectName);
$sth->execute();

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

Open in new window


The above triggers no errors, but I don't get any data. Here is my question:

date_default_timezone_set('America/Chicago');
ini_set('display_errors', 1);
include('mssql_db_cred.php');
$mssql_pdo = new PDO(“dblib:host=”.$mssql_cred_data[‘server’].”;dbname=”.$mssql_cred_data[‘dbname’],$mssql_cred_data[‘user’],$mssql_cred_data[‘pw’]);
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); // got my connection, this much is working

$sth = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_DeveloperCurrentProjectList.sql"); /*ultimately, I need to store my stored procedures in a different directory. How do I document that? Would it be exec stored_procedures/stp_Select_REPORT_SSRS_DeveloperCurrentProjectList.sql?*/
$sth->bindParam(1, $str_ProjectName);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
var_dump($result);
}

What I'm trying to do in my noble, albeit ignorant and clumsy way is to print the variable $str_ProjectName. I know just enough about binding parameters to sound impressive at parties, but I really don't know for certain what I'm doing.

In the PHP Manual, I'm looking at this:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

From what I can gather when I'm "binding" parameters I'm matching column headings with the names I've given to either incoming or outgoing data. So, I'm thinking that "$sth->bindParam(1, $str_ProjectName);" means that I'm expecting the first column retrieved to be $str_ProjectName.

Is that right? Obviously something's wrong since I'm not getting any errors,not am I getting any data. What am I doing wrong?
Comment
Watch Question
Fixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE