Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
Why does this function work one way in MSSQL Studio and another when accessed via PDO?
Posted on 2016-07-27
I'm trying to troubleshoot a function that's part of a SELECT statement. I built a little sandbox so I could better manipulate / view the code in a PHP environment. But when I went to fire the code, I got some different results and I don't understand why.
Here's the SQL:
When you fire that in MSSQL Studio, you get this:
Notice the value you're getting for "StatementRollUp."
Now when I try to run the same SELECT using a PDO dynamic, I get this:
...notice the value of "StatementRollUp."
In the Studio, StatementRollUp is "1." When I run it via PDO, it's "0" and I don't understand why.
Here's my PDO code:
What am I missing?
Here's the SQL:
SELECT
ac.AccountID,
clt.ClientName,
clt.ClientTetrisID,
clt.ClientID,
cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp
from Statement st with (nolock)
Join Account ac with (nolock) on st.Accountid = ac.AccountID
join Patient pat with (nolock) on pat.AccountID = ac.AccountID
join Practice prt with (nolock) on ac.PracticeID = prt.PracticeID
join Client clt with (nolock) on prt.ClientID = clt.ClientID
left join
(select pmtplanamount, pmtplanday, primarypatientid, startingplanbalance from PaymentPlan
where planendeddate is null
and iscurrentplan = 1) pp
on pp.primarypatientid = pat.patientid
where ac.accountid=10093227
When you fire that in MSSQL Studio, you get this:
Notice the value you're getting for "StatementRollUp."
Now when I try to run the same SELECT using a PDO dynamic, I get this:
...notice the value of "StatementRollUp."
In the Studio, StatementRollUp is "1." When I run it via PDO, it's "0" and I don't understand why.
Here's my PDO code:
<?php
date_default_timezone_set('America/Chicago');
$mssql_pdo=new PDO("");
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
echo "no connection";
}
function test(){
global $mssql_pdo;
//$sql="select TOP 5 patientcode from txn where accountid=10001124";
$sql="
SELECT
ac.AccountID,
clt.ClientName,
clt.ClientTetrisID,
clt.ClientID,
cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp
from Statement st with (nolock)
Join Account ac with (nolock) on st.Accountid = ac.AccountID
join Patient pat with (nolock) on pat.AccountID = ac.AccountID
join Practice prt with (nolock) on ac.PracticeID = prt.PracticeID
join Client clt with (nolock) on prt.ClientID = clt.ClientID
left join
(select pmtplanamount, pmtplanday, primarypatientid, startingplanbalance from PaymentPlan
where planendeddate is null
and iscurrentplan = 1) pp
on pp.primarypatientid = pat.patientid
where ac.accountid=10093227";
//echo $sql;
$query=$mssql_pdo->query($sql);
if(!$query){
echo "nope";
}
$result_array=array();
while($row=$query->fetch(PDO::FETCH_ASSOC) )
{
$result_array[]=$row;
}
return $result_array;
}
//$test_sql=getOldTransactions();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Patient Focus | SQL Machine</title>
<script language="JavaScript"><!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
window.open(selObj.options[selObj.selectedIndex].value, "_blank");
if (restore) selObj.selectedIndex=0;
}
// -->
</script>
<script type="text/javascript">
<!--
function toggle_visibility(id) {
var e = document.getElementById(id);
if(e.style.display == 'none')
e.style.display = 'block';
else
e.style.display = 'none';
}
//-->
</script>
<style>
body {
font-family:Arial;
size:12pt;
color:#000000;
}
.content {
margin:auto;
width:900px;
height:auto;
border:1px solid #cccccc;
border-radius:10px;
}
.wysiwyg {
white-space: pre-line;
padding-left:10px;
padding-right:10px;
}
.code_container {
border:1px solid #000000;
width:807px;
height:250px;
overflow:auto;
background-color: #013c76;
font-family:Arial;
color:#ffffff;
font-size:10pt;
margin:auto;
white-space:pre;
}
ol {
white-space: normal;
word-wrap: break-word;
width:750px;
margin:auto;
background-color:#036; background-repeat:repeat-y;background-position:center;
font-family:Helevtica, Arial;
font-size:10pt;
color:#ffffff;
}
ol li {
padding:6px;
background-color:#ffc;
color:#ffffff;
}
ol li span{
color:#000000; }
.blue {
color:blue;
}
</style>
</head>
<body>
<br><br>
<div class="content">
<div class="wysiwyg">
<table border="1" cellspacing="5" cellpadding="5" width="100%">
<tr>
<td>Account ID</td>
<td>Client Name</td>
<td>Client Tetris ID</td>
<td>Client ID</td>
<td>Statement Roll Up</td>
</tr>
<?php
$test_stuff=test();
//echo $test_stuff;
if($test_stuff){
//echo "yes";
}
foreach($test_stuff as $row)
{
echo "<tr><td>";
echo $row['AccountID'];
echo "</td><td>";
echo $row['ClientName'];
echo "</td><td>";
echo $row['ClientTetrisID'];
echo "</td><td>";
echo $row['ClientID'];
echo "</td><td>";
echo $row['StatementRollUp'];
echo "</td></tr>";
}
?>
</table>
</div>
</div>
</body>
</html>
What am I missing?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
3 Comments
Active 1 day ago
Are you sure your CAST is correct? https://msdn.microsoft.com/en-us/library/ms187928.aspx
What are you trying to do with cast(dbo.ufn_ClientPrefere
Is your ClientID already an integer? if it is, no need to cast it.
What are you trying to do with cast(dbo.ufn_ClientPrefere
Is your ClientID already an integer? if it is, no need to cast it.
Active 1 day ago
Scott!
I appreciate your time, friend! I'm up against a deadline so I'm glad you're at your desk! BTW: I'm in Nashville, so good to be talking to fellow Tennessean!
I should've provided the function. Here it is:
If you're like most, you're looking at this and wondering "Who wrote this?" I know. But be that as it may, I've got to understand what it's doing and how so I can determine conclusively whether or not it's posing a problem.
It works when I fire it in Studio, but it gives me a 0 when I run in PDO. Why the disparity?
I appreciate your time, friend! I'm up against a deadline so I'm glad you're at your desk! BTW: I'm in Nashville, so good to be talking to fellow Tennessean!
I should've provided the function. Here it is:
USE [PCAR_Data]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_ClientPreference] Script Date: 07/27/2016 10:07:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
ufn_ClientPreference - returns a client's preference for a given item
DROP FUNCTION [dbo].[ufn_ClientPreference]
*/
ALTER FUNCTION [dbo].[ufn_ClientPreference]
(
@ParmClientID BIGINT,
@ParmItem VARCHAR(200)
)
RETURNS sql_variant
BEGIN
DECLARE @Preference sql_variant
SET @Preference = (select coalesce(
(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
from ClientPreference with (NOLOCK), client with (NOLOCK)
where ClientPreference.ended is null
and client.clientid = @ParmClientID
and ClientPreference.clientid = client.clientid
and client.clientstatuscode = ClientPreference.clientstatuscode
and ClientPreference.item = @ParmItem),
coalesce(
(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
from ClientPreference with (NOLOCK), client with (NOLOCK)
where ClientPreference.ended is null
and client.clientid = @ParmClientID
and ClientPreference.clientid = client.clientid
and ClientPreference.clientstatuscode is null
and ClientPreference.item = @ParmItem),
coalesce(
(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
from ClientPreference with (NOLOCK), client with (NOLOCK)
where ClientPreference.ended is null
and client.clientid = @ParmClientID
and ClientPreference.clientid is null
and client.clientstatuscode = ClientPreference.clientstatuscode
and ClientPreference.item = @ParmItem),
(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
from ClientPreference with (NOLOCK)
where ClientPreference.ended is null
and ClientPreference.clientid is null
and ClientPreference.clientstatuscode is null
and ClientPreference.item = @ParmItem)
)
)
))
RETURN @Preference
END
If you're like most, you're looking at this and wondering "Who wrote this?" I know. But be that as it may, I've got to understand what it's doing and how so I can determine conclusively whether or not it's posing a problem.
It works when I fire it in Studio, but it gives me a 0 when I run in PDO. Why the disparity?
Active 1 day ago
Scott, I've run into this on a couple of situations since posting this question. I was able to solve the problem I was working on, but for the sake of establishing a bottom line, I'm going to close this question, but if I'm what I'm getting ready to suggest here inspires you to add any commentary, please do.
This particular app uses pconnect to connect to a T-SQL database. Not sure if pconnect has any advantages over PDO, but I've encountered situations where PDO sometimes coughs and sputters a little bit when it interacts with functions and stored procedures on the server. I remember one project where we boiled it down to a temp table being constructed within a stored procedure that was causing a problem. When we reconfigured that proc, it worked fine.
Whether or not that was an example of something that applies across the boards, I'm not sure. What I did this time around to circumvent even the possibility of an inconsistency is I built a little "sandbox" that used the same database connection as the actual app.
There you have it! Thanks for weighing in!
This particular app uses pconnect to connect to a T-SQL database. Not sure if pconnect has any advantages over PDO, but I've encountered situations where PDO sometimes coughs and sputters a little bit when it interacts with functions and stored procedures on the server. I remember one project where we boiled it down to a temp table being constructed within a stored procedure that was causing a problem. When we reconfigured that proc, it worked fine.
Whether or not that was an example of something that applies across the boards, I'm not sure. What I did this time around to circumvent even the possibility of an inconsistency is I built a little "sandbox" that used the same database connection as the actual app.
There you have it! Thanks for weighing in!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Foreword (July, 2015)
Since I first wrote this article, years ago, a great many more people have begun using the internet. They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing.
Set up your basic HTML file. Open your form tag and set the method and action attributes.:
(CODE)
Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month12 days, 17 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.