?
Solved

cfqueryparam decimal datatype issue

Posted on 2006-03-24
18
Medium Priority
?
1,032 Views
Last Modified: 2013-12-24
I have a simple query in a CFC:

<cfquery name="getOD" datasource="#request.dsn#" username="#request.username#" password="#request.password#">
    SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = <cfqueryparam cfsqltype="cf_sql_decimal" value="#arguments.od#">
    ORDER BY machineName;
</cfquery>

which will sometimes return 0 results when there are clearly matches. If I change the cfsqltype to CF_SQL_VARCHAR, it works fine, but that defeats the purpose of defining a datatype. An example of a value that returns the correct result is 4.134. One that does not is 3.937.

I have tried using tht CF_SQL_FLOAT datatype and that doesn't work either. I'm using MySQL 5 as my DBMS. Any thoughts?
0
Comment
Question by:Paradiddle
  • 9
  • 6
  • 3
18 Comments
 
LVL 8

Expert Comment

by:pmascari
ID: 16281047
I suspect that Decimal may be rounding your number to two decimal places.  So the correct result of 4.134 rounds down and would be returned but 3.937 rounds up, causing it to change to 3.94 and not be returned.  Did you try CF_SQL_FLOAT as the data type?

As a last resort you could forgo using cfqueryparam altogether and just go with:

SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = #arguments.od#
    ORDER BY machineName;
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16281235
I tend to use CF_SQL_NUMERIC  it avoids the issue of the exact numeric data type and allows correct binding to any numeric data.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16281267
You many also want to to actually look at the data in the database. Is it possible that there are additional digits of data, but you are rounding it in the select or display of the data so it looks like 3.937 would match, but in reality the data is 3.9372 or 3.9368?  If this is the case, then matching on 3.973 isn't going to work no matter which bind variable type you pass in...  This seems more likely...
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 

Author Comment

by:Paradiddle
ID: 16281316
Yes, I tried float, decimal and double and all did the same thing.

I don't know if rounding is the issue. Another value that returns the correct result is 4.156. If it was rounding to 2 decimal places, this value would be 4.16 and should return 0 results. #arguments.od# is being passed to the CFC via a URL var so I want to use cfqueryparam to ensure data integrity.
0
 

Author Comment

by:Paradiddle
ID: 16281389
The data in the DB is in fact correct and matches the display. I was very careful not to do any rounding in the display as this site will be used by engineers who need exact specifications. I went so far as to manually retype the values in the DB thinking that when it was exported from Oracle there was some trailing nonsense like line breaks.

0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16281432
Is it a float in the database? If so, unfortunately, there is always rounding on floats... .1 base 10, when converted to binary as a floating point number is irrational... like 1/3 base 10 being .333333.

.1 (base to) converts to .1100110011001100...... in base2.
This should only be a POTENTIAL problem if the number is floating point.

Have you tried the same select statement directly using the database tools...
 SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = 3.937    ORDER BY machineName;
This will ensure that it isn't actually a database problem.

If that works, then try using just CF_SQL_NUMERIC
0
 
LVL 8

Expert Comment

by:pmascari
ID: 16281436
Can you turn on CF Debugging and see the actual values CFQUERY PARAM is passing to the query?  That will give us some insight to the problem here.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16281459
In regards to not using <cfqueryparam> at all, I would avoid that if at all possible, not just for the editing, you can do that with a simple <CFIF isnumber(url.parmvalue)> or with a <CFPARAM> tag to ensure the URL is numeric, but because passing BIND VARIABLES to the database is a good thing.  It makes life easier on the databases.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16281534
I missed your comment on CF_SQL_VARCHAR Working.... Use CF_SQL_NUMERIC. I strongly suspect that will work..
<cfquery name="getOD" datasource="#request.dsn#" username="#request.username#" password="#request.password#">
    SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = <cfqueryparam cfsqltype="cf_sql_NUMERIC" value="#arguments.od#">
    ORDER BY machineName;
</cfquery>
0
 

Author Comment

by:Paradiddle
ID: 16281569
The column is defined as a double. When I ran the query directly on the database using Navicat, it did the same thing: returned nothing. However, I changed the 3.937 to '3.937' and it worked. It seems as if the database isn't interpreting 3.937 as a numeric value.

I suppose a potential answer is to not use cfqueryparam and do manual data verification, but I thought cfqueryparam was supposed to be an easy way around that.
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 800 total points
ID: 16281673
It is, but if the database isn't even returning the numeric check as a numeric, then you have a problem ;)
I'm an oracle person, not a mysql, but debugging techniques are the same. When you can't get a query to work right, take coldfusion (or java, or whatever tool) out of the loop and go direct to the database.

If you can't get the data using the database's when you are specifying a numeric entry, you won't be able to get coldfusion to do it.  It is definitely a rounding error of some sort.  I'm VERY surprised that mysql would react like that, and it is probably a good question for the mysql database technical area to just ask why the query doesn't work using Navicat...

BUT, to fix your coldfusion problem, if you want an 'easy' edit, then edit the URL parameter with
<CFPARAM>  It will throw an exception, and earlier than it would using <CFQUERY>...

<CFPARAM name="arguments.od" data_type="numeric">
If you wanted to allow no passing of that parameter, you could also add the default="0" attribute to the tag.  This would assure numeric data is passed.

Then, use the CF_SQL_VARCHAR on your cfquery tag so that the database works...
0
 
LVL 8

Expert Comment

by:pmascari
ID: 16281746
Did you say you were using Oracle?  What if your switch the column type to Number?  This is looking more and more like a DB issue the CF.
0
 

Author Comment

by:Paradiddle
ID: 16281907
Debugging gave me this:

#      CFSQLType      value
1        cf_sql_decimal   3.937

The chain this data went through is oracle (don't know what version - that's handled by an outside engineer) to MS Access 2002 to MySQL 5. Being an oracle person, can you think of any potential problems running the data through a chain like this? I'm grasping at straws at this point.

cf_sql_numeric did not work.

RFCorfman, your CFPARAM suggestion worked (with one small change = attribute is type, not data_type). This will be my work around for now. It's clear that it's not a CF problem but a MySQL problem. Thank you so much for your input.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16282113
Doesn't matter what the data chain is if it doesn't work when you call it as close to the mysql data as possible, using their tools... that is where the problem occurs.  Glad there is a reasonable work around. I really would ask the question in the Database-->Mysql TA if you are curious and have points to use.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16282124
Sorry about the bad attribute, I misread my documentation... it said
<cfparam name="param_name" type="data_type" devault="value">
When I typed it into EE I had a "brain slip".  ;)
0
 

Author Comment

by:Paradiddle
ID: 16282150
I just posted the question in the MySQL forum. Thanks again!
0
 

Author Comment

by:Paradiddle
ID: 16283526
The MySQL experts came up with a good work around.

http://www.experts-exchange.com/Databases/Mysql/Q_21787678.html
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16284479
Doesn't seem to be a problem in Oracle directly, not that it really matters much...
float in Oracle does round, it is IEEE internally, number is more like they are describing decimal in the mysql post.  In the case of Oracle, it seems to work with either one...

SQL> create table udttemp(x float, n number);  

Table created.

SQL> insert into udttemp
  2  select 3.937,3.937 from dual union all
  3  select 4.134,4.134 from dual;

2 rows created.

SQL> select * from udttemp where x in (3.937,4.134);

         X          N
---------- ----------
     3.937      3.937
     4.134      4.134

SQL> select * from udttemp where n in (3.937,4.134);

         X          N
---------- ----------
     3.937      3.937
     4.134      4.134

SQL>
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
What You Need to Know when Searching for a Webhost Provider
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

840 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