ColdFusion Executing Queries Parallel (Data not updating)

I do an update and then I try to get back the updated value of the field of which was updated. The issue however, is I am not getting the updated value, rather the prior value. I know th开发者_Python百科e update works correctly because if I query it shows the returned data. I have tried cftransaction isolation="serializable" but it does not work.

My code is below, is there a way around this or will I have to make 2 AJAX request for simpe issue?

<cfquery datasource="#application.datasource#">
UPDATE gt_timesheet
SET
    phaseid=<cfqueryparam value="#form.phase#" cfsqltype="CF_SQL_INTEGER">,
    projectid=<cfqueryparam value="#form.project#" cfsqltype="CF_SQL_INTEGER">
WHERE
    timesheetid=<cfqueryparam value="#form.timesheetid#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

<cfquery name="phase" datasource="#application.datasource#">
SELECT status,
(
    SELECT ROUND(sum(time_to_sec(duration))/3600,2)
    FROM gt_timesheet
    WHERE gt_timesheet.phaseid=gt_phases.phaseid
) as billedbillablehours,
(
    SELECT ROUND(sum(time_to_sec(hours))/3600,2)
    FROM gt_services
    WHERE gt_phases.phaseid=gt_services.phaseid
) as billablehours
FROM gt_phases
WHERE phaseid=<cfqueryparam value="#form.phase#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

This is making me go nuts because this language is constantly making tasks that should be simple tedious.

You don't have a CF problem, you have an SQL problem, and I think its the subqueries. First why a subquery and not a join?

Can you confirm that they are 1:1 relationships in the subqueries, because if they are one to many you are just going to get the first match.

I don't know your database schema, but doing it the way you currently are i think the SELECT needs to have the table match based on the form field you have just updated

<cfquery name="phase" datasource="#application.datasource#">
SELECT status,
(
    SELECT ROUND(sum(time_to_sec(duration))/3600,2)
    FROM gt_timesheet
    WHERE gt_timesheet.phaseid=<cfqueryparam value="#form.phase#" cfsqltype="CF_SQL_INTEGER">
) as billedbillablehours,
(
    SELECT ROUND(sum(time_to_sec(hours))/3600,2)
    FROM gt_services
    WHERE gt_services.phaseid=<cfqueryparam value="#form.phase#" cfsqltype="CF_SQL_INTEGER">
) as billablehours
FROM gt_phases
WHERE phaseid=<cfqueryparam value="#form.phase#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

Hope that gets you somewhere, if not I think you need to explain the database schema more.

ColdFusion does not execute anything in the same request in Parallel unless you use <cfthread>

How is the duration updated after you run the first Update statement? Does it call a trigger? Possibly that is causing the update of the "duration" field to run after the second query. You could try adding a 1 second sleep after the first query to make sure the second query gets the correct value.

I guess you are missing something in the update statement. Why don't you re-evaluate the query's once again.