Driver omitting leading zero in decimal

Question

Hello everyone,

I have an issue that might have something to do with the sqlsrv driver:
this query:

SELECT


	convert(numeric(20,2), 0) as [zero_column]


;

The query is silly, but it shows what happens when you have a zero in a column and you convert it to numeric. SQL Server Management studio handles it as expected, showing 0.00 in the column.

PHP version is 5.3.0, sqlsrv driver version 1.1. SQL Server used is SQL Server 2005 Standard, 9.0.4053.

PHP code (uses a modified part of a wrapper we use):

$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

$query = "
SELECT
	convert(numeric(20,2), 0) as [zero_column]
;
";

$result = sqlsrv_query($conn, $query);
$conn_msg = print_r(sqlsrv_errors(), true);
if( $result === false )
{
	echo 'Chyba: nepodarilo sa vykonat databazovu operaciu:<br /><pre>', $conn_msg, '</pre>';
	System::Alert($query);
	return false;
}
elseif( $result === NULL )
{
	return array();
}
else
{
	$row = '';
	while( ($row !== NULL) && ($row !== false) )
	{
		// echo '|';
		$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
		if( ($row !== NULL) && ($row !== false) )
		{
			echo '<pre>';
			var_dump($row);
			echo '</pre>';
		}
		elseif($row === false)
		{
			$conn_msg = print_r(sqlsrv_errors(), true);
			break;
		}
		else
		{
			$result_tmp = sqlsrv_next_result($result);
			if($result_tmp === false)
			{
				$conn_msg = print_r(sqlsrv_errors(), true);
				break;
			}
			if($result_tmp === NULL)
			{
				break;
			}
		}
	}
	sqlsrv_free_stmt($result);
}

sqlsrv_close( $conn);

var_dump output:

array(1) {
  ["zero_column"]=>
  string(3) ".00"
}

As you can see, the output is a string and the leading zero is missing. This could lead to unexpected results in case you needed to use the number later on in PHP code.

Can anyone confirm this?
Could this be specific to my setup and/or configuration?

Answer 1

I can confirm this. And, I don't seem to be able to fix the issue by specifying the PHP type when using sqlsrv_fetch/sqlsrv_get_field. I'll ping the development team and see if I can get more information.

[Edit: More information...It appears that this is an issue with the underlying SNAC driver. I've filed a bug with the team.]

-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

We think this is an issue with the underlying SQL Server Native Client driver, but we need to verify that.

---

Jay Kint MSFT

Answer 3

Thank you for looking into it to both of you :)

Answer 4

I have filed it as a bug here:
https://connect.microsoft.com/SQLServer/feedback/details/541935/sql-server-native-client-omitting-leading-zero-in-decimal-when-used-by-sql-server-driver-for-php-1-1

It's my first such report on Connect, I might have put it into the wrong category or made a different mistake.

Answer 5

This will solve it for now, by changing the type to varchar in the query:

SELECT cast( convert(numeric(20,2), 0) as nvarchar(50)) as [zero_column];

So add the text in bold around the column or expression from your own query.

---

Rob

Answer 6

Guys, do you think this will ever get fixed? In my framework I can't just cast that everywhere in above 3000 scripts... there are also views and procedures where you can't find that automatically. It's really horrible. That is not just a minor problem. :\

 

Edit: Oh it's getting even funnier. When you try to create a workaround in your engine for that behaviour - like that (very simplified):

 

$myarray = sqslrv_fetch_array($resultset);

function getField($fieldname)

{

 global $myarray;

 if($myarray[$fieldname]=='.00')

  $myarray[$fieldname] = '0.00';

 

 return $myarray[$fieldname];

}

 

it won't work, because all the int fields, which give you a plain "0" will become a "0.00". It seems that php casts the .00 into a float, so that 0 is then the same as .00.

If you try then to fix that behaviour with a cast:

 if((string)$myarray[$fieldname]==(string)'.00')

it still won't work, because php doesn't give a... well because php hates me.

Fortunately the ".00" values come as a string and the 0 values come as int (check it with var_dump, you'll see) so that for now the following workaround works:

        if(is_string($textArray[$text]) && $textArray[$text] == ".00")

 

I have some colleagues in other companys which have a similar engine in their company... so I thought there might be someone out there which will have the same problems like I have with this bug - I hope this post will help a little bit.

Still, this workaround isn't good. This if check is fired everytime I'm retrieving a sql field in my engine... but for now I don't believe there is a better solution for this.

 

Answer 7

Hi Thomas

This is one of those times where you have to work around a problem, and you know a much nicer solution is possible, if only...!

In PHP, there is another comparison operator, === / !==, e.g. try this:

if( $myarray[$fieldname] === '.00')
....

It means do a comparison of the value and the type.  Here are some more examples:

0 == false (true)

0 == "" (true)

0 === false (false)

0 === "" (false)

Here's a little php program that could give you an idea how to solve your problem:

header('Content-type: text/plain; charset=utf-8');
$conn = sqlsrv_connect('.\SQLEXPRESS',array('Database'=>'master'));

function Adjust($item)
{<br/>
	if ( $item === '.00' )
 	{
		return '0.00';
	}
	return $item;
}

if ( $conn )
{
	if ( ($stmt = sqlsrv_query($conn, "SELECT 'My Result' as TextColumn, cast(0 as decimal(18,2)) as TestNumber")) )
	{
		$row = sqlsrv_fetch_array($stmt);
		echo 'Before: ';
		print_r($row);
		
		$row = array_map('Adjust', $row);
		echo 'After: ';
		print_r($row);
	}
	else 
	{
		print_r(sqlsrv_errors());
	}
}
else 
{
	echo "Connection error\r\n";
	print_r(sqlsrv_errors());
}

---

Rob

Answer 8

Ignore the visible "<br />" in previous post, I can't edit it, it was added by the forum's code inserter.

---

Rob