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

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?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
In this line...
$mssql_pdo = new PDO(“dblib:host=”.$mssql_cred_data[‘server’].”;dbname=”.$mssql_cred_data[‘dbname’],$mssql_cred_data[‘user’],$mssql_cred_data[‘pw’]); 

Open in new window

You are using 'smart quotes' which are not an acceptable replacement for standard single or double quotes (did you type this in Word?).  You are referring to 'dblib' which is an old out of date Windows library.  The current driver on Windows is 'sqlsrv'.  http://php.net/manual/en/book.sqlsrv.php
0
Dave BaldwinFixer of ProblemsCommented:
0
Bruce GustPHP DeveloperAuthor Commented:
Dave, you're right! Since I posted this, I saw that and was able to fix it and run the following with throngs of fans cheering in the background:

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_EXCEPTION);

$mssql_stmt = $mssql_pdo->query("Select count(*) from net_BudgetTools..tbl_DEVELOPERS_Projects");
//notice two dots between database name and table name - a necessary piece of syntax in the MSSQL world
$row_count = $mssql_stmt->fetchColumn(0);
echo $row_count;

So, that works! I've got a connection and I can access the appropriate database which brings us to the core question:

How can I incorporate a stored procedure? The .NET folks have done a great job of crafting some truly elegant queries and I want to leverage their efforts as much as possible.

Given the now verified connection, here's what I'm trying to do:

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

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

The error I get back is:

421 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2812 General SQL Server error: Check messages from the SQL Server [2812] (severity 16) [(null)]' in /var/www/html/testsite/c0gusb1/mssql_class.php:25 Stack trace: #0 /var/www/html/testsite/c0gusb1/mssql_class.php(25): PDOStatement->execute() #1 {main} thrown in /var/www/html/testsite/c0gusb1/mssql_class.php on line 25

I've eliminated some of the notes I've made in the code, so for the sake of ensuring you can reference / view the actual code that I'm using in full, I've got it below:

<?php
date_default_timezone_set('America/Chicago');
ini_set('error_reporting', E_ALL);

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_EXCEPTION);

/*if($pdo)
{
	echo "yes";
}*/
  
/*$mssql_stmt = $mssql_pdo->query("SELECT count(*) FROM [user]");
$row_count = $mssql_stmt->fetchColumn(0);
echo $row_count;*/

$mssql_stmt = $mssql_pdo->query("Select count(*) from net_BudgetTools..tbl_DEVELOPERS_Projects");
//notice two dots between database name and table name - a necessary piece of syntax in the MSSQL world
$row_count = $mssql_stmt->fetchColumn(0);
echo $row_count;

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

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

Open in new window

0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Dave BaldwinFixer of ProblemsCommented:
Here is the page http://php.net/manual/en/ref.pdo-dblib.php along with the note that...
This extension is not available anymore on Windows with PHP 5.3 or later.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
No points for this, please, but MySQL, PostGreSQL, SQL Server and SQLite all work well with PDO.
0
Bruce GustPHP DeveloperAuthor Commented:
Dave and Ray: At the risk of sounding like a needy teacup, I'm wondering if you could point me in the direction of something more verbose / basic than the example on the PHP manual.

Here's the stored procedure that I'm trying to reference:

USE [net_BudgetTools]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_Select_REPORT_SSRS_DeveloperCurrentProjectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_Select_REPORT_SSRS_DeveloperCurrentProjectList]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/************************************************************************************
** 1) Application(s) that use Procedure		:	BudgetTools
** 2) Creator								:	Heather Carrouth
** 3) Date Created							:	
** 4) SQL Developer Updater					:	
** 5) Date(s) Updated						:	
** 6) Procedure Description					:	Select Developer Project List
************************************************************************************/
CREATE PROCEDURE [dbo].[stp_Select_REPORT_SSRS_DeveloperCurrentProjectList]
AS
BEGIN

SET NOCOUNT ON

	SELECT		DP.int_DEVELOPERS_Project_Id				,
				DP.str_ProjectId							,
				DP.int_DEVELOPERS_Tool_Id					,
				ISNULL(DP.int_ProjectRank, 999)	AS 	int_ProjectRank,
				DP.int_DEVELOPERS_ProjectType_Id			,
				DP.str_ProjectName							,
				DP.int_Developer_Id							,
				DP.str_Description							,
				DP.int_DEVELOPERS_ProjectPriority_Id		,
				DP.bit_Is_Upcoming							,
				DP.int_ProjectSubmitter_Id					,
				DP.str_DateSubmitted						,
				DP.dte_DateSubmitted						,
				DP.int_DEVELOPERS_DocumentationStatus_Id	,
				DP.int_DEVELOPERS_ProjectStatus_Id			,
				DP.int_DEVELOPERS_LevelOfEffort_Id			,
				DP.dec_PercentageCompleted					,
				DP.int_BenefitEquation						,
				DP.str_CurrentStatus						,
				DP.str_RequestorDueDate						,
				DP.str_DateStarted							,
				DP.str_DocumentationCompletionDate			,
				DP.str_TargetCompletionDate					,
				DP.str_DateCompleted						,
				DP.str_DateOnProduction						,
				DP.dte_RequestorDueDate						,
				DP.dte_DateStarted							,
				DP.dte_TargetCompletionDate					,
				DP.dte_DateCompleted						,
				DP.dte_DateOnProduction						,
				DP.str_Notes								,
				DP.bit_Is_Active							,
				DP.int_Insert_User_Id						,
				DP.dte_Inserted_Date						,
				DP.int_Update_User_Id						,
				DP.dte_Updated_Date							,

				DT.str_Tool									,
				DPP.str_ProjectPriority						,
				DPt.str_ProjectType							,
				DDS.str_DocumentationStatus					,
				DPS.str_ProjectStatus						,
				DLE.str_LevelOfEffort						,
		
				(CId.str_First_Name + ' ' + CId.str_Last_Name) AS str_Full_Name_D	,
				(CIs.str_First_Name + ' ' + CIs.str_Last_Name) AS str_Full_Name_S	,
				(CIi.str_First_Name + ' ' + CIi.str_Last_Name) AS str_Full_Name_I	,
				(CIu.str_First_Name + ' ' + CIu.str_Last_Name) AS str_Full_Name_U	,
				(CIo.str_First_Name + ' ' + CIo.str_Last_Name) AS str_Full_Name_O	

	FROM		tbl_DEVELOPERS_Projects					DP
	LEFT JOIN	tbl_DEVELOPERS_Tools					DT	ON DP.int_DEVELOPERS_Tool_Id = DT.int_DEVELOPERS_Tool_Id AND DT.bit_Is_Active = 1
	LEFT JOIN	tbl_DEVELOPERS_ProjectPriorities		DPP	ON DP.int_DEVELOPERS_ProjectPriority_Id = DPP.int_DEVELOPERS_ProjectPriority_Id AND DPP.bit_Is_Active = 1
	LEFT JOIN	tbl_DEVELOPERS_DocumentationStatuses	DDS	ON DP.int_DEVELOPERS_DocumentationStatus_Id = DDS.int_DEVELOPERS_DocumentationStatus_Id AND DDS.bit_Is_Active = 1
	LEFT JOIN	tbl_DEVELOPERS_ProjectStatuses			DPS	ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
	LEFT JOIN	tbl_DEVELOPERS_LevelOfEfforts			DLE	ON DP.int_DEVELOPERS_LevelOfEffort_Id = DLE.int_DEVELOPERS_LevelOfEffort_Id AND DLE.bit_Is_Active = 1
	LEFT JOIN	tbl_DEVELOPERS_ProjectTypes				DPT	ON DP.int_DEVELOPERS_ProjectType_Id = DPT.int_DEVELOPERS_ProjectType_Id AND DPT.bit_Is_Active = 1

	LEFT JOIN	tbl_CORE_Individuals					CId	ON DP.int_Developer_Id = CId.int_CORE_Individual_Id AND CId.bit_Is_Active = 1
	LEFT JOIN	tbl_CORE_Individuals					CIs	ON DP.int_ProjectSubmitter_Id = CIs.int_CORE_Individual_Id AND CIs.bit_Is_Active = 1
	LEFT JOIN	tbl_CORE_Individuals					CIi	ON DP.int_Insert_User_Id = CIi.int_CORE_Individual_Id AND CIi.bit_Is_Active = 1
	LEFT JOIN	tbl_CORE_Individuals					CIu	ON DP.int_Update_User_Id = CIu.int_CORE_Individual_Id AND CIu.bit_Is_Active = 1
	LEFT JOIN	tbl_CORE_Individuals					CIo	ON DT.int_Owner_Id = CIo.int_CORE_Individual_Id AND CIo.bit_Is_Active = 1
	
	WHERE		(DPS.bit_Is_OpenStatus = 1 OR DPS.bit_Is_OpenStatus IS NULL)
	AND			DP.bit_Is_Active = 1
	AND			DPS.int_DEVELOPERS_ProjectStatus_Id IN (1, 2, 3, 4)

	ORDER BY	ISNULL(DP.int_ProjectRank, 999) ASC, str_ProjectId


END
GO

SET QUOTED_IDENTIFIER OFF 
GO

SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[stp_Select_REPORT_SSRS_DeveloperCurrentProjectList]  TO [net_budgettools_user]
GO

Open in new window


I want to be able to keep all of my stored procedures in a separate directory and I've yet to find an example / tutorial that demos how to do that.

This...

$sth = $mssql_pdo->prepare("stp_Select_REPORT_SSRS_DeveloperCurrentProjectList.sql");

...doesn't appear to accommodate something like...

$sth = $mssql_pdo->prepare("stored_procedures/stp_Select_REPORT_SSRS_DeveloperCurrentProjectList.sql");

In addition, I've got to find something that shows me how to bind the parameters that I'm grabbing from the stored procedure and then display in the context of a PHP page. This all could be right in front of my nose and I don't see it, but that's what I'm looking for.

Any suggestions?
0
Dave BaldwinFixer of ProblemsCommented:
As I have already said... there is no point in helping you with code that is obsolete.  You must be running PHP 5.2 or lower because the library you are trying to use will simply not work with PHP 5.3 or newer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.