Preserving large numbers in Query or Query

I hit a problem with QueryNew recently which caused a few headaches because 13 digit strings were being converted to standard notation. This is a pretty unusual use-case but I thought it was worth blogging anyway.

I've got an admin for products where you can enter the 13 digit EAN code for a product. As products can have multiple variants, each with their own EAN code, I decided to parse the form field data submitted to build a Query object of the varients. This makes validation and CRUD operations much easier to read in my code.

So I had something like this:

<!--- simulate form parameters --->
<cfset rc = {
  optionid_1 = "123",
  optionean_1 = "1234567890123",
  optioncostmodifier_1 = "0",
  optionid_2 = "456",
  optionean_2 = "4561237890123",
  optioncostmodifier_2 = "5"
}>

<cfset qryOptions = QueryNew( "id,ean,costmodifier" )>
  
<cfdump var="#rc#">

<cfloop collection="#rc#" item="item">

  <cfif ListFirst( item, "_" ) eq "optionid">
    <cfset row = ListLast( item, "_" )>
    <cfset QueryAddRow( qryOptions )>
    <cfset QuerySetCell( qryOptions, "id", rc[ "optionid_#row#" ] )>
    <cfset QuerySetCell( qryOptions, "ean", rc[ "optionean_#row#" ] )>
    <cfset QuerySetCell( qryOptions, "costmodifier", rc[ "optioncostmodifier_#row#" ] )>
  </cfif>

</cfloop>

<cfdump var="#qryOptions#">

<!--- reorder qryOptions --->
<cfquery name="qryOptions" dbtype="query">
  select * 
  from qryOptions
  order by costmodifier
</cfquery>

<cfdump var="#qryOptions#">

If you run this code you'll see this:

Note that the second dump after the query or queries shows values of 1.23456789012E+012 and 4.56123789012E+012.

My initial thought was to use the optional datatype argument of QueryNew to define the datatype like so:

<cfset qryOptions = QueryNew( "id,ean,costmodifier", "Integer,BigInt,Decimal" )>

This produced the same result (as did using Integer). In the end, I had to use the VarChar datatype to get it preserve the 13 digit number.

<cfset qryOptions = QueryNew( "id,ean,costmodifier", "Integer,Varchar,Decimal" )>