Coldfusion integer problem calliing cffunction query

I have a strange problem that happens erratically.  In short, I open the page in a browser and retrieve the data for an individual.  I open another tab and retrieve the data for a different individual.  A list of comm file records for the first individual shows up on the page for the second individual.  I can't recreate the problem but I have screen shots that corroborate the problem.  I put in an email to myself when the individual number used to retrieve the individual does not match the individual number in the comm records returned.  The data returned always appears correct but the individual number used to retrieve that data has a .0 at the end of it.  The number in the database is an integer.

<cfset recentComms = Application.objIndivBasic.qrecentComms(indivNum="#val(indivNum)#") />  <!--- ticket 2642  vjl  2015/03/30  indivnum shows 29901.0 but is an integer.  Somehow causing wrong comm records to show up. --->
<!--- testing for wrong user records  vjl  20015/03/25 --->
<cfset commdebug = 0>
<cfloop query="recentComms">
	<cfif recentComms.users_id neq #indivnum#>
		<cfset commdebug = 1>
	</cfif>
</cfloop>
<cfif commdebug>
	<cfmail to="vaughn@t.com" from="info@t.com" subject="indiv debug recent comms" type="html"> Indiv num = #indivNum#<br><cfdump var="#recentComms#"></cfmail>
</cfif>

<!--- <div id="box_1" style="width:475px"> --->
	<div class="PanelTab_Recent" tabindex="0">Recent Phone Contact</div>
	<div class="PanelContent_Recent">

          	<table width="100%">
            <cfif recentComms.recordcount NEQ 0>
            	<cfoutput query="recentComms" maxrows="3">
      				<tr>
						<td><strong>Date:</strong></td><td>
								#dateformat(recentComms.date_entered, "mm/dd/yy")#</td>
			<!--- <td><strong>Date:</strong></td><td>
							<cfif recentComms.date_updaated eq "">
								#dateformat(recentComms.date_entered, "mm/dd/yy")#
							<cfelse>
								#dateformat(recentComms.date_updated, "mm/dd/yy")#
							</cfif>
						</td> --->

          			<td><strong>Type:</strong></td><td>#Description#
                	<!--- <cfif recentComms.communication_type EQ "T">Telephone
                  <cfelse>Voice Mail
                  </cfif> --->
                 </td>
                <td><strong>Search Num</strong>:</strong></td><td>#recentComms.search_number#</td>
                <td><strong>By: </strong>#AS400_Initials#</td><td><strong></td>
        			</tr>
       				<tr>

               	<td colspan="8">#recentComms.comment#</td>
        			</tr>
              <cfif recentComms.currentrow NEQ recentComms.recordcount>
              <tr>
              	<td colspan="8"><hr /></td>
              </tr>
              </cfif>
              </cfoutput>
            <cfelse>
            	<tr>
              	<td>No Calls, Voice Mails or Marketing TTs</td>
              </tr>
            </cfif>
            </table>

    </div>



	<cffunction name="qrecentComms" output="false" returntype="query">
		<cfargument name="indivnum" required="Yes" type="string" default="" />
		<cfargument name="subspecialtyid" required="Yes" type="string" default="" />
		<cfset var subSpecCheck="" />
		<cfquery name="recentComms" datasource="#request.dsn#">
			SELECT UC.ID,UC.users_id, UC.comment, UC.date_entered, UC.communication_type, UC.search_number, AU.AS400_Initials, CT.Description
			FROM Users_Communications AS UC INNER JOIN
    		admin_users AS AU ON UC.Consultant_ID = AU.adminID INNER JOIN
				Communication_Types AS CT ON UC.communication_type = CT.letter AND CT.Category = 'S'
  			WHERE UC.users_id = <cfqueryparam value="#val(arguments.indivNum)#" cfsqltype="CF_SQL_NUMERIC" maxlength="10"/>
  				AND (communication_type = 'T' OR communication_type = 'A' OR communication_type = 'K' OR communication_type = 'G')
 			ORDER BY date_entered desc, id desc
		</cfquery>
		<cfreturn recentComms />
	</cffunction>

Open in new window

lantervAsked:
Who is Participating?
 
_agx_Commented:
EDIT:  What you described sounds more like classic race conditions, than a data type issue.  (Though FWIW, CF_SQL_INTEGER is really the correct type for an "integer" column). Especially since you say it is difficult to reproduce.  That's typical of race conditions which usually manifest only under load.

Your function is stored in a shared scope (ie application).  Since the query name inside the function isn't properly scoped, it ends up in the VARIABLES scope of the component. Since that component is being stored in the application scope, it's variables are visible to/can be modified by multiple threads at the same time - leading to race conditions.  

Change the code to VAR/LOCAL scope the "recentComms" query and the problem should go away.

     <cffunction name="qrecentComms" output="false" returntype="query">
            <cfargument name="indivnum" required="Yes" type="string" default="" />
            <cfargument name="subspecialtyid" required="Yes" type="string" default="" />
            <cfset var subSpecCheck="" /> <!--- not the name of your query --->
            <cfquery name="recentComms" datasource="#request.dsn#">
                  .....
            </cfquery>
            <cfreturn recentComms />
      </cffunction>
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
try using the Int function or the Fix function with a combination of val function or without val function,

not sure as to why.0 is being added, could be due to the setup of your application etc..

but try that instead and see if u can see any difference

Cheers
0
 
lantervAuthor Commented:
I can't believe I missed the cfqueryparam being numeric.  I'm not sure how it affects the problem.  And I guess I have to just wait and see.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
_agx_Commented:
BTW, its not the cause of the problem, but the query param should be using cf_sql_integer instead of numeric:

....
WHERE UC.users_id = <cfqueryparam value="#val(arguments.indivNum)#" cfsqltype="CF_SQL_INTEGER"/>
<!--- shorter alternative --->
AND   UC.communication_type IN ('T', 'A','K' ,'G')
ORDER BY date_entered desc, id desc

Open in new window

0
 
lantervAuthor Commented:
I caught the edit.  I'll give it a few days.  Then I'll close this question if no one sees the problem again.
0
 
_agx_Commented:
Ok,  I'm never sure.  I participated in one thread where the other person didn't see and edit and it was several posts before we both realized were weren't talking about the same thing ;-)

Sounds good.
0
 
lantervAuthor Commented:
Well,  the problem got worse.  Here's the email I received;

Indiv num = 89908
query
RESULTSET      query
       INITIALS      COMMENT      TYPE  DATE_ENTERED      DESCRIPTION         ID   SEARCH_NUMBER      USERS_ID
1      AG               2106/FD/DIR       T         2006/04/20               Telephone       321114       0                        99382

CACHED      false
EXECUTIONTIME      4473
SQL      SELECT UC.ID,UC.users_id, UC.comment, UC.date_entered, UC.communication_type, UC.search_number, AU.AS400_Initials, CT.Description FROM Users_Communications AS UC INNER JOIN admin_users AS AU ON UC.Consultant_ID = AU.adminID INNER JOIN Communication_Types AS CT ON UC.communication_type = CT.letter AND CT.Category = 'S' WHERE UC.users_id = ? AND (communication_type = 'T' OR communication_type = 'A' OR communication_type = 'K' OR communication_type = 'G') ORDER BY date_entered desc, id desc
SQLPARAMETERS      array
1      99382

The individual number at the top of the email does not match the SQLPARAMETER at the bottom (or user id in the query results).
0
 
lantervAuthor Commented:
Here's some code from the very beginning of the page;

<cfif form.INDIVGRID.THEUSERID NEQ "">
	<cfset indivNum = "#form.INDIVGRID.THEUSERID#" />
<cfelseif url.indivnum NEQ "">
	<cfset indivNum = "#url.indivnum#" />
<cfelseif IsDefined("url.CFGRIDKEY")>
	<cfset indivNum = "#url.CFGRIDKEY#" />
<cfelse>
	<cflocation url="index.cfm" addtoken="false">
</cfif>
<cfset client.indivID = "#val(indivNum)#">

Open in new window

0
 
_agx_Commented:
1. Did you VAR scope the correct query in the function?
2. Did you restart the app so the new settings take affect?
0
 
_agx_Commented:
The individual number at the top of the email does not match the SQLPARAMETER at the bottom (or user id in the query results).

You are dumping both the number and query inside the cffunction, correct?

Race conditions could definitely cause this behavior. Because the query is stored in a shared scope VARIABLES, you aren't guaranteed to get the query (and parameters) you just ran.  If the multiple requests execute at the same time, thread A could end up with the query results from thread B.
0
 
lantervAuthor Commented:
I put the query on the page instead of calling a function.  We'll see how it goes.

If all the functions in the file basic.cfc are in the application scope,  I can see where this might be a bigger problem.  I have maybe 150 functions in the file.  There are probably 20 browsers with one or more individuals displayed.  Why haven't I seen this problem before?
0
 
_agx_Commented:
>  I can see where this might be a bigger problem.

 If you're storing the functions in the app scope you have to properly scope the function local variables.  Otherwise, its akin to storing those variables the application scope too.  It looks like you were trying to VAR scope the variable, but the query name doesn't match the VAR'd variable so it doesn't work.  

150 functions in the file.
As long as you VAR/LOCAL scope the variables, there's no issue with race conditions.  Not sure about your other functions, but there are tools like the varscoper which can help you check the code for proper scoping.

Why haven't I seen this problem before?

IF its race conditions (which the erratic behavior suggests), they're notoriously tricky.  Race conditions  are hard to reproduce because they don't happen every time. They only manifest under certain conditions. It requires multiple threads reading/writing shared variables - at the same time. If that never happens in your app, you might never see the issue. But if that code has been around for a while it's entirely possible it's happened before but you just weren't aware of it.


EDIT:

Important, as I mentioned above if you do change the functions - be sure to restart the application. Otherwise, the new code won't take affect until the application times out (default is several days)
0
 
lantervAuthor Commented:
Great catch!  Great advice.  Thank you.
0
 
_agx_Commented:
You're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.