- PHP
- Microsoft SQL Server
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:
The above triggers no errors, but I don't get any data. Here is my question:
date_default_timezone_set(
ini_set('display_errors', 1);
include('mssql_db_cred.php
$mssql_pdo = new PDO(“dblib:host=”.$mssql_c
$mssql_pdo->setAttribute(P
$sth = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_Dev
$sth->bindParam(1, $str_ProjectName);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASS
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
$sth->bindParam(':colour',
$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?
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);
}
The above triggers no errors, but I don't get any data. Here is my question:
date_default_timezone_set(
ini_set('display_errors', 1);
include('mssql_db_cred.php
$mssql_pdo = new PDO(“dblib:host=”.$mssql_c
$mssql_pdo->setAttribute(P
$sth = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_Dev
$sth->bindParam(1, $str_ProjectName);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASS
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
$sth->bindParam(':colour',
$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?
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’]);
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_c
$mssql_pdo->setAttribute(P
$mssql_stmt = $mssql_pdo->query("Select count(*) from net_BudgetTools..tbl_DEVEL
//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_S
$sth->bindParam(1, $int_DEVELOPERS_Project_Id
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASS
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/c0g
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:
include('mssql_db_cred.php
$mssql_pdo = new PDO("dblib:host=".$mssql_c
$mssql_pdo->setAttribute(P
$mssql_stmt = $mssql_pdo->query("Select count(*) from net_BudgetTools..tbl_DEVEL
//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_S
$sth->bindParam(1, $int_DEVELOPERS_Project_Id
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASS
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/c0g
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);
}
?>
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.
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:
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_S
...doesn't appear to accommodate something like...
$sth = $mssql_pdo->prepare("store
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?
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
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_S
...doesn't appear to accommodate something like...
$sth = $mssql_pdo->prepare("store
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?
-
![]()
received a Solution
PHP and using a stored procedure to insert money value -
![]()
wrote an Article
![]()
How to create a simple form plugin with server validation in Wordpress
-
![]()
created a Video
![]()
How to Add a Watermark to an Image Using PHP (Part 2)
-
Explore Cloud Class® ![]()
RESTful Services: Data, JAX-RS and Jersey
Premium Content
You need an Expert Office subscription to comment.Start Free Trial