Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Preserving large numbers in Query or Query

DZone's Guide to

Preserving large numbers in Query or Query

· Java Zone
Free Resource

Get the Edge with a Professional Java IDE. 30-day free trial.

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:

QofQDump

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" )>

Get the Java IDE that understands code & makes developing enjoyable. Level up your code with IntelliJ IDEA. Download the free trial.

Topics:

Published at DZone with permission of John Whish, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}