R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.
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?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
Or PDO_SQLSRV http://php.net/manual/en/ref.pdo-sqlsrv.php .
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.
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
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?
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.
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.
-
Microsoft ApplicationsCertification: MCSA MCSE Microsoft Certified Solutions Associate & … 398 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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