×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem with cfquery

thread232-1716404

Problem with cfquery

Problem with cfquery

(OP)
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 --> coldfusion

<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

RE: Problem with cfquery

(OP)
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

RE: Problem with cfquery

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.

RE: Problem with cfquery

(OP)
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

RE: Problem with cfquery

(OP)
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

RE: Problem with cfquery

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?

RE: Problem with cfquery

(OP)
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

RE: Problem with cfquery

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close