Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with cfquery

Status
Not open for further replies.
jdhilljr

jdhilljr

Programmer
Joined
Sep 1, 2003
Messages
296
Location
US
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
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.
Code: 
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FALCONSEYE
  • Locked
  • Question Question
ColdFusion microservices with REST
Replies
0
Views
718
FALCONSEYE
FALCONSEYE
mdvp
  • Locked
  • Question Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
705
mdvp
mdvp
oshinwa
  • Locked
  • Question Question
Which Call collector should I use and which one is compatible with my Ericsson BP250?
Replies
0
Views
820
oshinwa
oshinwa
ibeenjammin
  • Locked
  • Question Question
FrameMaker 2015 Conditional Text Problems
Replies
0
Views
722
ibeenjammin
ibeenjammin
Kjonnnn
  • Locked
  • Question Question
What is is called when you end a domain with a folder name.
Replies
2
Views
430
Kjonnnn
Kjonnnn

Part and Inventory Search

Sponsor

Back
Top