cfquery returning 0 in scientific notation in CF 8 but not CF 7
Greetings. In one of our systems we're upgrading from ColdFusion 7 to CF 8. While testing a piece of functionality we were getting 0 returned as 0E-8 for a query using a stored procedure. If the value is greater than 0, there's no issue. Updating the procedure to cast the returned value as a numeric resolved the issue, but since this functionality is working fine against CF 7 ... We're using MS SQL, and the data source settings in ColdFusion Administrator are consistent between 7 and 8. Google and searching these forums isn't helping much, other than suggesting it's coming out of MSSQL, but ... does anyone have any additional background on why we're seeing this? Do we need to evaluate each of our procedures/calls to verify that values are coming back correctly and cast if not, or ...? Thanks! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
What is the data type of the field in SQL Server? If you run the query in Management Studio, what do you see for that value? How are you outputting the value in CF to see the 0E-8 number? What tag are you using to call the stored procedure in ColdFusion? Which version of MS SQL Server are you using? -Mike Chabot On Wed, Feb 16, 2011 at 11:20 AM, James Skemp wrote: > > Greetings. > > In one of our systems we're upgrading from ColdFusion 7 to CF 8. > > While testing a piece of functionality we were getting 0 returned as 0E-8 > for a query using a stored procedure. If the value is greater than 0, > there's no issue. > > Updating the procedure to cast the returned value as a numeric resolved the > issue, but since this functionality is working fine against CF 7 ... > > We're using MS SQL, and the data source settings in ColdFusion > Administrator are consistent between 7 and 8. > > Google and searching these forums isn't helping much, other than suggesting > it's coming out of MSSQL, but ... does anyone have any additional background > on why we're seeing this? Do we need to evaluate each of our > procedures/calls to verify that values are coming back correctly and cast if > not, or ...? > > Thanks! > > ~James > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Thanks for the clarification questions, Mike. >What is the data type of the field in SQL Server? numeric(10,2) > If you run the query in Management Studio, what do you see for that value? 0.00 > How are you outputting the value in CF to see the 0E-8 number? In a tossed error it was displayed in the stack trace and a cfdump of the returned information from the initial cfquery call against the sp showed it there as well. When it works, it's stored to a variable before being passed to SQL via another query. > What tag are you using to call the stored procedure in ColdFusion? cfquery > Which version of MS SQL Server are you using? 64 bit, 2005 Standard edition SP3 ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342337 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
It occurred to me that I should first ask if this issue is causing any problems, or if it is only an unexpected way to format a number? For example 0.00 = 0E-8. They are both the same number regardless of how they are displayed. For clarification, which number has the unexpected formatting, the value pulled directly out of a database table and placed in the cfquery record set, the variable that is being assigned the value, or both? -Mike Chabot On Wed, Feb 16, 2011 at 12:29 PM, James Skemp wrote: > > Thanks for the clarification questions, Mike. > > >What is the data type of the field in SQL Server? > > numeric(10,2) > > > If you run the query in Management Studio, what do you see for that > value? > > 0.00 > > > How are you outputting the value in CF to see the 0E-8 number? > > In a tossed error it was displayed in the stack trace and a cfdump of the > returned information from the initial cfquery call against the sp showed it > there as well. When it works, it's stored to a variable before being passed > to SQL via another query. > > > What tag are you using to call the stored procedure in ColdFusion? > > cfquery > > > Which version of MS SQL Server are you using? > > 64 bit, 2005 Standard edition SP3 > > > ~James > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342339 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
>It occurred to me that I should first ask if this issue is causing any >problems, or if it is only an unexpected way to format a number? For example >0.00 = 0E-8. They are both the same number regardless of how they are >displayed. Unfortunately, it is causing an issue in this particular instance (and is in fact why we had a stack trace to see that 0E-8 was being returned) :) It's legacy code, so with some work we could certainly resolve the issue, but, as a legacy *third-party* solution, my concern is it's going to cause issues in other parts of the system. Assuming this is expected behavior in CF8/we haven't flipped the appropriate 'switch.' >For clarification, which number has the unexpected formatting, the value >pulled directly out of a database table and placed in the cfquery record >set, the variable that is being assigned the value, or both? Probably the former - ColdFusion's interpretation of the value from SQL / the value in the cfquery record set, since the resolution in this particular case is to cast the returned value to a particular type in the procedure. But, another way to resolve the issue is to check the formatting when setting the variable, so I suppose one could argue both. The immediate problem is because of the latter, but the root cause is the former. In short, "0E-8" is populated in the proc's cfquery result set, a variable is being set with "0E-8", and another query is failing because it doesn't care for "0E-8" being passed. Hopefully that last bit didn't muddy the waters ... :) ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342341 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Why would a SQL cast fix the issue? Casting a numeric(10,2) to a numeric(10,2) would not solve anything, which means that the value you are casting to numeric must be of a different data type, likely float or varchar. Floating point numbers often display in scientific notation. The numeric value is possibly being automatically converted into a floating point number by its use in a math function. It might help to see the SQL with the cast function that is fixing the issue. SELECT numeric_col FROM table should not require any further casting and you would not see the problem you are seeing. However, if you do something like this SELECT '000' + numeric_col + 1.2E-20 / 1.234 + 'cat' - 'dog' as newval FROM table then you are no longer dealing with a numeric, even though the source number might have started out as one. I'm still not clear on what the problem is. As a reminder, try to create a simplified example that isolates the problem being seen, away from any surrounding code. During the exercise of creating the simplified example, testing each increasingly complex iteration of the code, the source of the error will likely be revealed. If you were hoping for a quick answer to your question, I don't have one. Perhaps someone else has seen this issue when upgrading CF. -Mike Chabot On Wed, Feb 16, 2011 at 1:43 PM, James Skemp wrote: > > >It occurred to me that I should first ask if this issue is causing any > >problems, or if it is only an unexpected way to format a number? For > example > >0.00 = 0E-8. They are both the same number regardless of how they are > >displayed. > > Unfortunately, it is causing an issue in this particular instance (and is > in fact why we had a stack trace to see that 0E-8 was being returned) :) > > It's legacy code, so with some work we could certainly resolve the issue, > but, as a legacy *third-party* solution, my concern is it's going to cause > issues in other parts of the system. Assuming this is expected behavior in > CF8/we haven't flipped the appropriate 'switch.' > > > >For clarification, which number has the unexpected formatting, the value > >pulled directly out of a database table and placed in the cfquery record > >set, the variable that is being assigned the value, or both? > > Probably the former - ColdFusion's interpretation of the value from SQL / > the value in the cfquery record set, since the resolution in this particular > case is to cast the returned value to a particular type in the procedure. > > But, another way to resolve the issue is to check the formatting when > setting the variable, so I suppose one could argue both. The immediate > problem is because of the latter, but the root cause is the former. > > In short, "0E-8" is populated in the proc's cfquery result set, a variable > is being set with "0E-8", and another query is failing because it doesn't > care for "0E-8" being passed. > > Hopefully that last bit didn't muddy the waters ... :) > > ~James > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342348 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Thanks Mike.
Opened up the procedure and gave it a look. It looks like there's some
multiplication and division going on, which is resulting in a change. Relevant
part for one of the returned values:
MemberPrice =
(case when qp.productid is null then p.MemberPrice
else
case when qp.MemberPrice is null then
p.MemberPrice*((100-qp.memberdiscountpercent)/100)
else qp.MemberPrice
end
end)
Running this query on ColdFusion 7.('current') against MS SQL returns (again,
cfdump of the cfquery, and the same thing as SQL Studio returns):
0.
On CF 8, we instead get:
0E-8
So, updated CF 8 SQL Driver perhaps? Either way, unless there's an
Administrator change, this does mean we'll have to evaluate code since, right
or wrong, CF 7 doesn't make this change.
Thanks!
~James
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342349
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Looks like you might need to revise the procedure to CAST the calculated
value back to Numeric(10,2) before returning it?
Carl
On 2/16/2011 2:30 PM, James Skemp wrote:
> Thanks Mike.
>
> Opened up the procedure and gave it a look. It looks like there's some
> multiplication and division going on, which is resulting in a change.
> Relevant part for one of the returned values:
>
> MemberPrice =
> (case when qp.productid is null then p.MemberPrice
> else
> case when qp.MemberPrice is null then
> p.MemberPrice*((100-qp.memberdiscountpercent)/100)
> else qp.MemberPrice
> end
> end)
>
> Running this query on ColdFusion 7.('current') against MS SQL returns (again,
> cfdump of the cfquery, and the same thing as SQL Studio returns):
>
> 0.
>
> On CF 8, we instead get:
>
> 0E-8
>
> So, updated CF 8 SQL Driver perhaps? Either way, unless there's an
> Administrator change, this does mean we'll have to evaluate code since, right
> or wrong, CF 7 doesn't make this change.
>
> Thanks!
>
> ~James
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342350
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
>Looks like you might need to revise the procedure to CAST the calculated >value back to Numeric(10,2) before returning it? >Carl Yeah, which is what we're doing for this one to resolve the issue. But since this is a legacy, third-party, application ... it worries me the number of places this may occur. And of course, as we all know, there's usually one place that gets missed in these types of situations ... :D I was hoping it was a configuration option that we missed, but ... I don't suppose someone with a similar environment could test against CF 9, could they? Thanks Carl! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342354 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
The behavior change might be due to updated drivers, which is the most common source of modified database behavior. You can probably fix this either in CF, using something like NumberFormat, or cfqueryparam, or using casting in SQL Server. If this is a lot of work for you, it might be worth it to contact Adobe tech support to confirm the behavior change. One alternative is to pick a different JDBC driver than the one that comes with CF. Microsoft has their own free JDBC driver that you can use fairly easily. The Microsoft-provider JDBC driver behaves differently. I have had to use the Microsoft one in certain situations where the one that comes bundled with CF did not work properly. Check if any of the three columns present in your SQL are float, real, or money data types. If you find one of these data types in your database you can change it to a numeric as another way to fix the problem. -Mike Chabot On Wed, Feb 16, 2011 at 6:44 PM, James Skemp wrote: > > >Looks like you might need to revise the procedure to CAST the calculated > >value back to Numeric(10,2) before returning it? > >Carl > > Yeah, which is what we're doing for this one to resolve the issue. > > But since this is a legacy, third-party, application ... it worries me the > number of places this may occur. And of course, as we all know, there's > usually one place that gets missed in these types of situations ... :D > > I was hoping it was a configuration option that we missed, but ... > > I don't suppose someone with a similar environment could test against CF 9, > could they? > > Thanks Carl! > > ~James > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342376 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
