I have a query dealing with latitude/longitude that's not returning the same results as when querying the database via navicat. only thing i can think of is that cfqueryparam is truncating the lat/long decimal points... does anyone know how many decimal places it supports? I've tried using, float, real, and decimal sql types. also when doing a cfdump it's showing the same query that i'm testing, do we know for a fact that this is the exact query being executed? Version: Lucee 5.4.3.2 sample query:

what is the database platform and what is the datatype of the lat/long columns

First run your SQL query with just pure SQL, no arguments, Next if that works Try to run the same query but with the comparison as strings before setting up the query.

If it's SQL Server, the datatype should be CF_SQL_DOUBLE or CF_SQL_DECIMAL. You'll have to pass the scale if you use decimal. CF_SQL_REAL only supports a precision of 7.

sorry for not responding.. got stuck in a meeting. I'm on mariaDB, not sure the version. I tried using scale on decimal but the results were the same. i'll double check that though.

According to mariaDB docs, real should work. I'd try decimal though and set the scale in cfqueryparam to something high (16+). The driver might be truncating the precision going from real to decimal.

let me give that a whirl.

This looks like it might be worth a read (though it doesn’t solve your immediate problem, it might be helpful in terms of performance tuning for your queries and db): https://mariadb.com/kb/en/latitudelongitude-indexing/

this is using decimal with cfqueryparam at scale 16 VS removed cfqueryparam all together... it's rounding up on the precision.. which the results are still the same but this was interesting none the less'

Yeah, that's what I suspected was happening.

both queries returned the same recordcount, but 6 fewer than running directly on the server

Yeah, the driver appears to be truncating the value.

that's what i was afraid of.. i've only got 8 decimal places in my database for precision. i wonder if i truncated what was being sent to match the precision of my data would work

just curious, but what happens if you don't use cfqueryparam at all... do you get the correct results? (you could do some conditional logic before the query to sanitize any user input and You could declare the lat and long as variables at the beginning of the query to have the db set the datatype of the parameters

@Scott Bennett I get the same results, even though though it rounds up the precision. here's the comparision of with and without queryparam. both returned 95 records in cf and 101 on the server directly

ok... this is probably byond my personal expertise, but the article I linked above talked about some ways to store the long/lat data in a "Scaled" fashion. I wonder if that would be relevant here

Good question. Might be over my head too. Lol. But I’ll read it.

if I'm understanding it correctly, you could basically multiply the values by a factor of 10 or 100 or something, to effectively move the decimal place over a bit and avoid problems with precision of decimals

reading this sounds like it's more of a performance enhancement and not accuracy related. I'm afraid the driver is doing something it shouldn't. I'm going to dig into it and see if i can see what the server is seeing from cf side and go from there.

Yes, I was just thinking that their scaling idea could also be a way of bypassing this driver issue

It should work. I've done that in the past. It works well with decimal data types but not floating point types.

i would think that truncating the values down to match the database would have done the trick, but apparently not. I'll see if i can get the powers that be authorize me to try doing this method.. it would require changes to our imports that affect hundreds of clients.. but if its a solution, its a solution. thanks guys!

Maybe if you implement the indexing strategy, you can also get performance improvements at the same time