Query of Query error message

[Jordan Michaels]

Hi Folks,

I'm hitting a runtime error when I run the following block of code:


------------------------------------------------------------------
<!--- create a test query object --->
<cfset application.authDIDLog =
queryNew('did,uid,accessDate','varchar,varchar,date')>

<!--- give the query some test data --->
<cfset queryAddRow(application.authDIDLog)/>
<cfset querySetCell(application.authDIDLog, "did", "tempdid") />
<cfset querySetCell(application.authDIDLog, "uid", "tempuid") />
<cfset querySetCell(application.authDIDLog, "accessDate", now()) />

<!--- performing a select on the query works fine --->
<cfquery dbtype="query">
select did
from application.authDIDLog
where did = <cfqueryparam cfsqltype="cf_sql_varchar"
value="tempdid" />
</cfquery>

<!--- deleting a row from the query throws an error --->
<cfquery name="temp" dbtype="query">
delete from application.authDIDLog
where accessDate < <cfqueryparam cfsqltype="cf_sql_date"
value="#now()#" />
</cfquery>
------------------------------------------------------------------

The error message I get is attached. I've tried lots of variations of
the above as well, with the same result.

Am I doing something wrong?

Appreciate any insight anyone might be able to offer.

--
Warm Regards,
Jordan Michaels

[Jon Clausen]

Jordan,

No, you’re not doing anything wrong. It’s a known issue with QoQ. There are basically two solutions:

1 ) Use a SELECT * QoQ with the WHERE statement excluding the rows you want deleted and re-assign the old variable.

2 ) Loop the query and use queryDeleteRow() where needed.

Option 1:

<cfscript>
var q = queryNew('index,date');
for(i=1;i LTE 10;i=i+1){
    var row = queryAddRow(q);
    querySetCell(q,"index",i,row);
    querySetCell(q,"date",dateAdd("d",i,now()),row);
}
writeDump(var=q);

var q2 = new query();
q2.setDBType("query");
q2.setAttributes(sourceQuery=q);
q2.addParam(name='max_date',value=dateAdd("d",9,now()));
q2Result=q2.execute(sql="SELECT * from sourceQuery where date < :max_date");
q=q2Result.getResult();

writeDump(var=q);
abort;
</cfscript>

Option2

<cfscript>
var q = queryNew('index,date');
for(i=1;i LTE 10;i=i+1){
    var row = queryAddRow(q);
    querySetCell(q,"index",i,row);
    querySetCell(q,"date",dateAdd("d",i,now()),row);
}
writeDump(var=q);

var max_date = dateAdd("d",8,now());
var i = 1;
for(row in q){
    if(row.date GTE max_date){
        queryDeleteRow(q,i);
    }
    i=i+1;
}
writeDump(var=q);
abort;
</cfscript>

HTH, Jon

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/55B298BA.2020709%40viviotech.net.
For more options, visit https://groups.google.com/d/optout.

---

[Jordan Michaels]

Thanks Jon. Really appreciate the help!

-Jordan

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/etPan.55b2a38e.78eaa758.e431%40jonclausen-mbp.local.