cfqueryparam decimal datatype issue

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?
ParadiddleAsked:
Who is Participating?
 
RCorfmanCommented:
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
 
pmascariCommented:
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
 
RCorfmanCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
RCorfmanCommented:
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
 
ParadiddleAuthor Commented:
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
 
ParadiddleAuthor Commented:
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
 
RCorfmanCommented:
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
 
pmascariCommented:
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
 
RCorfmanCommented:
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
 
RCorfmanCommented:
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
 
ParadiddleAuthor Commented:
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
 
pmascariCommented:
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
 
ParadiddleAuthor Commented:
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
 
RCorfmanCommented:
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
 
RCorfmanCommented:
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
 
ParadiddleAuthor Commented:
I just posted the question in the MySQL forum. Thanks again!
0
 
ParadiddleAuthor Commented:
The MySQL experts came up with a good work around.

http://www.experts-exchange.com/Databases/Mysql/Q_21787678.html
0
 
RCorfmanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.