Coldfusion conversion failed

MySQL and SQL Server handle things a bit differently when it comes to varchar columns. 

I Created a search for a site that needed to search between two different $ amounts. The data was already listed in varchar columns. When doing a search the MySQL and Lucee it produced the correct results. However when trying to do the exact same search with CF 2016 and SQL Server I got the following error. 

coldfusion Conversion failed when converting the varchar value '38.50' to data type int.


Well it works in Lucee and MySQL WTF! It turns out the cause is MySQL permits a nonstandard syntax so my initial code will work  
WHERE  cost >= :low AND cost <= :high 
In SQL Server I needed to convert my cost column into a float using the following function.
convert(float,columnNAme);

So now I have to account for two different ways dealing with this issue. Because I develop locally with Docker Lucee and MySQL I needed a bit of code that would detect my DB type so the app would function in both environments without having to worry about, this is my prod code dev code nightmare. ugh who needs that. What I came up with is the following script. This private function detects the DB type.

private any function DatabaseType()  {
        
        // detect database type
        cfdbinfo(type="Version", name="dbinfo", datasource=application.configBean.getDatasource());
        
        DBType = dbinfo.database_Productname;
        
        return DBType;
    }

Using an ajax post I post high and low costs. Below is a very simple function that takes in the costs and checks to see what DB type we have based on the private function. In a prod scenario you would want  to add way more error checking.

 

remote any function CostwithIn(low,high) returnformat="JSON" {

var clow = replace(ARGUMENTS.low, ',','');
var chigh = replace(ARGUMENTS.high, ',','');

// return database type via private function
var DBType = DatabaseType(this);
if(DBType eq 'MySQL'){
var getNeeds = new Query(datasource=application.configBean.getDatasource());
getNeeds.setSQL(" SELECT * FROM fno_needs_tbl WHERE cost >= :low AND cost <= :high ");
}else{
var getNeeds = new Query(datasource=application.configBean.getDatasource());
getNeeds.setSQL(" SELECT * FROM fno_needs_tbl WHERE convert(float,cost) >= :low AND convert(float,cost) <= :high ");
}
getNeeds.addParam(name="low",value="#clow#",CFSQLTYPE="CF_SQL_INTEGER");
getNeeds.addParam(name="high",value="#chigh#",CFSQLTYPE="CF_SQL_INTEGER");

// get the records
var records = getNeeds.execute().getResult();
//create an array
var arrNeeds = arrayNew(1);
if(records.recordcount){
// loop records
for(key in records) { /
/ create struct in each array element for JSON return
needInfo = structNew();
needInfo["descrip"] = key.description;
needInfo["title"] = trim( replace(key.descriptShort, '&hellip;', '') );
needInfo["lID"] = key.lID_n;
needInfo["cost"] = decimalFormat(key.cost);
needInfo["nid_search"] = key.nid;

arrayAppend(arrNeeds,needInfo);
} // end loop
} // end if
// return array via JSON
return arrNeeds;
} // end function

I hope this example helps someone out there.