lanterv
asked on
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>ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 descASKER
I caught the edit. I'll give it a few days. Then I'll close this question if no one sees the problem again.
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.
Sounds good.
ASKER
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).
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).
ASKER
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)#">
1. Did you VAR scope the correct query in the function?
2. Did you restart the app so the new settings take affect?
2. Did you restart the app so the new settings take affect?
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.
ASKER
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?
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?
> 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.
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)
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)
ASKER
Great catch! Great advice. Thank you.
You're welcome :)
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