Inconsistent query results 1

[ljevans]

I am running a query inside a <cfloop>. The first time I run the code, I get the expected results. The next time I run the code, the query returns a Null value and I get the following error:

Error Diagnostic Information

An error occurred while evaluating the expression:


total_m = #get_months_eng.total_month# * 53.07



Error near line 12, column 8.
--------------------------------------------------------------------------------

Cannot convert to number.

Please, check the ColdFusion manual for the allowed conversions between data types


The error occurred while processing an element with a general identifier of (CFSET), occupying document position (12:2) to (12:55) in the template file C:\InetPub\eac_report\eacgetmanload.cfm.


Date/Time: 04/04/01 07:20:55
Browser: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
Remote Address: 192.168.77.173
Query String: project_number=8800


This is my code:

<cfset all_months = 0>
<cfset month_num = 1>

<cfloop index=&quot;loop_count&quot; from=&quot;1&quot; to=&quot;18&quot;>
	<cfquery datasource=&quot;eac_report&quot; name=&quot;get_months_eng&quot;>
		SELECT Sum(month#month_num#) AS total_month
		FROM eac_manload
		WHERE project_number = '#project_number#'
			  and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)
	</cfquery>
	
	<cfoutput query=&quot;get_months_eng&quot;>
		#total_month#
	</cfoutput>
	
	<cfset total_m = #get_months_eng.total_month# * 53.07>
	<cfset all_months = #all_months# + #total_m#>
	<cfset month_num = #month_num# + 1>
</cfloop>

Why is this happening? Any help would be appreciated!

 

[JNC73]

The second time the query is run the query returns a NULL value which cannot be used in a mathematic expression. When you execute the query make sure that the result you get is a numeric value. If not do not execute the next code lines.

 

[ljevans]

To clarify my question: The Null result being returned subsequent times the query is run is incorrect. The result should always be the same. What I can't understand, is why I get the correct results the first time and a Null result subsequent times the query is run?

 

[GunJack]

Are you saying that you run the script, it works, then you click &quot;reload/refresh&quot; and it gives the error?

GJ

 

[ljevans]

Yes, I run the script and get the results I expect. I refresh the page and get the error.

 

[GunJack]

Ok, that's odd. Try this and let's see what happens. Create a new directory and put an application.cfm file in it with just the code &quot;<cfset test=0>&quot;. Then create a new script in that directory called test.cfm. Inside test.cfm, put this code.


<cfquery datasource=&quot;eac_report&quot;
name=&quot;get_months_eng&quot;>
SELECT Sum(month1) AS total_month
FROM eac_manload
WHERE project_number = '#project_number#'
and dept IN
(50,51,52,55,57,60,61,62,63,64,65,68,69)
</cfquery>

<cfset total_m = #get_months_eng.total_month# *
53.07>

Hard code a value in place of #project_numeber# and then see if you can load and re-load test.cfm without the error. It'll take a few steps but we'll be able to isolate the bug.

GJ

 

[ljevans]

I have done as you suggested and I get the results I expect each time I run the code. Now what?

 

[GunJack]

Hey ljevans,

This will likely be a several step process to get down to the problem and I'll need you to be fairly specific about what happens each time so we don't waste time or mis-communicate. When you say you get the results you expected, does that mean it was the same as before in that the first time you run it, it works but the second time it gives an error?

If so, let me know what number you used in place of #project_number# and then replace the <cfset> with this:

<cfoutput>
~#get_months_eng.total_month#~<p>
#len(get_months_eng.total_month)#
</cfoutput>

Let me know what happens and please post the output of the page after this change. Just to verify though, this test file is in a separate directory with an application.cfm in the same directory that basicly does nothing? Also, what database are you using?

GJ

 

[ljevans]

The project_number = 8800. The first time Test.cfm was run, I got the following results:

7429.8


--------------------------------------------------------------------------------
Queries

get_months_eng (Records=1, Time=680ms)
SQL =
SELECT Sum(month1) AS total_month
FROM eac_manload
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)

When I make the changes you indicated, the first time I run the code I get these results:

~140~
3



--------------------------------------------------------------------------------
Queries

get_months_eng (Records=1, Time=0ms)
SQL =
SELECT Sum(month1) AS total_month
FROM eac_manload
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)

The second time I run the code (refresh), the result is as follows:

~~
0



--------------------------------------------------------------------------------
Queries

get_months_eng (Records=1, Time=0ms)
SQL =
SELECT Sum(month1) AS total_month
FROM eac_manload
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)

 

[GunJack]

Ok, that's giving me some ideas about what's happenning but the next question I have is in regards to the error clearing. When you made the last change, it ran fine the first time, then you reloaded and got the null value. How or when does it give you the correct result again? Do you have to wait several minutes, do you have to make a change to the script, or does it give you the correct result every other reload? What I'm trying to understand is how do you get it to give the correct result again.

One thing that came to mind is the possibility of you running against a clustered database where your first request goes to one server but the next request goes to another server. If this were the case, you would see the correct result appearing at a regular interval of page reloads (assuming no browser caching).

I can see two possibilities at this point, some type of trigger on the database is interfering with the results or you're running against a clustered database and they're out of sync.

Please let me know what database you're using and also run this for me in place of the last cfoutput block. I forgot to check the recordcount and want to verify it's returning 0 records when it errors.

<cfoutput>#get_months_eng.recordcount#</cfoutput>

GJ

 

[ljevans]

My database is on a single server (not clustered). The only way I can get back to my first-run query result, is to reboot the server.

The results from the last change are as follows:

1


--------------------------------------------------------------------------------
Queries

get_months_eng (Records=1, Time=0ms)
SQL =
SELECT Sum(month1) AS total_month
FROM eac_manload
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)

 

[GunJack]

Ok, few more questions.

1. What database are you using?
2. Is the database running on the same server as CF?
3. If not, are you rebooting the db server or the CF server?
4. Was the output &quot;1&quot; under the normal output and error condition?

Anticipating answers to those questions, the next step I would do is to copy the table &quot;eac_manload&quot; to &quot;test&quot;. Also, create a new datasource to point to the same server and &quot;test&quot; table. Then change the query to reference this new datasource and new table, reboot the server, and try again.

GJ

 

[ljevans]

1. I am using SQL 7 on an NT Server with ColdFusion 4.5.1.
2. ColdFusion is on the same server as my database.
4. Each time I run the code (refresh numerous times) I will get a record count of 1.

 

[ljevans]

I followed your suggestion and created a new database and copied eac_manload table in as test. I created a new datasource. I ran the code:

<cfquery datasource=&quot;test_eac&quot; name=&quot;get_months_eng&quot;>
SELECT Sum(month1) AS total_month
FROM test
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)
</cfquery>



<cfoutput>
~#get_months_eng.total_month#~<p>
#len(get_months_eng.total_month)#
</cfoutput>

The first time I get the following results:

~140~

3

The second time I get:

~~

0

 

[GunJack]

Ok, we're getting closer. Lets find out if the problem is with CF or Sql server. Restart the CF service (don't reboot) and see if it gives the correct answer. If not, restart the Sql server service and see if the correct answer is returned. One or the other should cause it to start over and this will give a better idea of which one is the culprit.

Also, download this database

http://atlbiz.com/db1.mdb

and setup a test dsn and run the code against it. This is the database that works on my server. Take a look and let me know if any of the fields are different than your database such as text/datetime/integer, etc.. as I set it up as I thought yours would be.

GJ

 

[KevinFSI]

GJ,
I work with Linda, but she's gone for the day. I stopped/started CF (all components) but still get the same results she's been getting. I then rebooted the server, refreshed the page, and got the correct results. When I refreshed the page again, I got:
~~
0 1



--------------------------------------------------------------------------------
Queries

get_months_eng (Records=1, Time=10ms)
SQL =
SELECT Sum(month1) AS total_month
FROM test
WHERE project_number = 8800
and dept IN (50,51,52,55,57,60,61,62,63,64,65,68,69)

Is this the strangest thing you've ever seen or what? I'm completely stumped! Kevin
slanek@ssd.fsi.com

 

[KevinFSI]

I just stopped/started SQL Server and SQL ServerAgent and now I can't get it to crap out.

What could possibly be the difference between rebooting the server which is only good for one run of the query, and manually stopping and starting services which seems to be working? Kevin
slanek@ssd.fsi.com

 

[GunJack]

Hey Kevin,

This is definitely a bizarre one but those are the kind I like The one thing I don't think you did that I wanted to test was whether restarting the Sql server fixed it instead of rebooting the entire server. Can you try that for me real quick? I'll probably just start again with Linda tomorrow as I'm getting pretty busy today.

How've things been for you lately?
GJ

 

[GunJack]

Looks like you posted at the same time I did. Not sure about that one as I would think they're about the same. I think we may be getting into the realms of a Sql server issue where you need someone more familiar the db server to help. I might be able to get a friend of mine to take a look.

GJ

 

[KevinFSI]

Things are going really well these days. No major catastrophies for you to bail me out of lately.

Here's something weird. The first time I stopped and restarted MSSQL Server and SQL ServerAgent I refreshed the page (never closed it) and the result was 140. I refreshed several more times and at some point the result changed from 140 to 86. I refreshed about 100 times after that and never got it to change again. So, I restarted the 2 SQL services again, refreshed the page and it went back to 140. I've been refreshing the page for about 10 min straight, but it hasn't changed. Kevin
slanek@ssd.fsi.com