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

The single app analytics solutions to take your web and mobile apps to the next level.  Try today!  Brought to you in partnership with CA Technologies

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

CA App Experience Analytics, a whole new level of visibility. Learn more. Brought to you in partnership with CA Technologies.


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

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


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

{{ parent.tldr }}

{{ parent.urlSource.name }}