Problem with cfquery

[jdhilljr]

I am having an issue and I think it is the server but want to make sure. I have changed the coding so much to minimalize what could be causing the error hence the simplistic queries. The output is perfect at the end and I can paste it into the sql online and it works. Is there a way on a coldfusion server to block the update from the server?

This is my code

<cfinclude template="security.cfm">
<cfif !isdefined("session.admin")>
<cflocation url="denied.cfm">
</cfif>
<cfquery name="get_cities" datasource="#request.dsn#">
SELECT * FROM state_cities WHERE yr2012 IS NULL LIMIT 2
</cfquery>
<cfloop query="get_cities">
<cfquery name="get_county" datasource="#request.dsn#">
SELECT county FROM zip_code WHERE city='#get_cities.name#' AND state_prefix='#get_cities.state#'
</cfquery>
<cfquery name="get_2012" datasource="#request.dsn#">
SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2012' 
</cfquery>
<cfquery name="get_2011" datasource="#request.dsn#">
SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2011' 
</cfquery>
<cfquery name="get_2010" datasource="#request.dsn#">
SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2010' 
</cfquery>
<cfquery name="get_2009" datasource="#request.dsn#">
SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2009' 
</cfquery>
<cfquery name="get_2008" datasource="#request.dsn#">
SELECT *,((unemployed/labor_force)*100) AS unemppct FROM employment WHERE county LIKE '%#get_county.county# County, #get_cities.state#%' AND info_year='2008' 
</cfquery>
<cfoutput>
UPDATE state_cities SET yr2012='#get_2012.unemppct#',yr2011='#get_2011.unemppct#',yr2010='#get_2010.unemppct#',yr2009='#get_2009.unemppct#',yr2008='#get_2008.unemppct#' WHERE city_id=#get_cities.city_id#
</cfoutput>
<cfquery name="update_info" datasource="#request.dsn#">
UPDATE state_cities SET yr2012='#get_2012.unemppct#',yr2011='#get_2011.unemppct#',yr2010='#get_2010.unemppct#',yr2009='#get_2009.unemppct#',yr2008='#get_2008.unemppct#' WHERE city_id=#get_cities.city_id#
</cfquery>
</cfloop>

This is the output when I block the update query otherwise I get an error with no info.

UPDATE state_cities SET yr2012='4.80769230769',yr2011='5.76923076923',yr2010='5.94059405941',yr2009='6.06060606061',yr2008='3' WHERE city_id=5

Which works fine.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[jdhilljr]

I found out what the problem was. I moved it offline to a testing setup. Apparently online it only give the server a mysql truncation error when the data exceeds the length not when you enter the query manually. Since I dont control the server on this shared hosting on dailyrazor was an interesting quirk.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[cfSearching]

Nothing to do with your error, but is the your real UPDATE? Because there could be problems if the SELECT queries ie get_2008, get_2009, .. return multiple records.

 

[jdhilljr]

I did run those actual queries with no problems as there is only one entry per county.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[jdhilljr]

As I had stated before I had simplified the queries to grab each separate. I initially did a loop through the years for each county but changed it to pinpoint the error.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[cfSearching]

Yeah, I saw your mention that it was a simplified version, but ... the fact that you are using LIKE suggested the select queries might return multiple rows. If they only ever return a single row, then it is not an issue. However, if they return more than one row, your UPDATE's would be wrong, since they would only be using the value in the first row of the query. It would not cause syntax error. So it is not something you would not notice unless you looked at the data.

Having said all that, is there a reason you need to store the aggregates in a table, rather than doing it dynamically, like with a VIEW?

 

[jdhilljr]

The table is being used to place colored markers on a google map whose color is decided by the change in the county's unemployment percentage for each lead in a database of 1.3 million records so to be able to do a large area of the US I wanted to trim the processing time as much as possible.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[cfSearching]

Performance is a good case for denormalization. Though, typically you are better off storing the years in rows rather than columns ie yrXXXX. It is easier to optimize, and you do not have to alter the table structure every time the year changes. Just insert a new row, and you can have as many or as few years as you need.

> FROM employment WHERE county LIKE

Also, for data integrity, it is better to store the distinct counties in a separate table. Then have the "employment" table store the ID, rather than the name.