Over a million developers have joined DZone.

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


<cfdump var="#qryOptions#">

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

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

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


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 }}