Over a million developers have joined DZone.

Preserving large numbers in Query or Query

· Java Zone

Discover how AppDynamics steps in to upgrade your performance game and prevent your enterprise from these top 10 Java performance problems, brought to you in partnership with AppDynamics.

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

The Java Zone is brought to you in partnership with AppDynamics. AppDynamics helps you gain the fundamentals behind application performance, and implement best practices so you can proactively analyze and act on performance problems as they arise, and more specifically with your Java applications. Start a 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.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

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

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

{{ parent.tldr }}

{{ parent.urlSource.name }}