?
Solved

Why does this function work one way in MSSQL Studio and another when accessed via PDO?

Posted on 2016-07-27
3
1 solution
Medium Priority
?
101 Views
Last Modified: 2016-08-02
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:

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

Open in new window


When you fire that in MSSQL Studio, you get this:

results screenshot
Notice the value you're getting for "StatementRollUp."

Now when I try to run the same SELECT using a PDO dynamic, I get this:

PDO screenshot
...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>

Open in new window


What am I missing?
0
Comment
Question by:brucegust
[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
Join The Community
  • 2
3 Comments
 
LVL 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 2000 total points
ID: 41731349
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_ClientPreference(clt.ClientID, 'StatementRollUp') as int)  That does not look right to star with.

Is your ClientID already an integer?  if it is, no need to cast it.
0
 

Author Comment

by:brucegust
ID: 41731396
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:

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

Open in new window


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?
0
 

Author Comment

by:brucegust
ID: 41738875
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!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Learn More
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Selecting the ideal social networking tool - Yammer or SharePoint Social
Article by: Shakshi
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Update and Secure Legacy PHP Scripts wih filter_input_array and PDO Prepared Statements
Article by: John
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Matching Tagged Data with PHP Regular Expressions
Video by: Terry
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
PHP, jQuery, and Form Actions
Video by: Marco
The viewer will learn how to dynamically set the form action using jQuery.
Advertise Here
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here