Query of Query error message

Hi Folks,

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

<cfset application.authDIDLog =
queryNew(‘did,uid,accessDate’,‘varchar,varchar,date’)>

<cfset queryAddRow(application.authDIDLog)/>
<cfset querySetCell(application.authDIDLog, “did”, “tempdid”) />
<cfset querySetCell(application.authDIDLog, “uid”, “tempuid”) />
<cfset querySetCell(application.authDIDLog, “accessDate”, now()) />

select did from application.authDIDLog where did =

<!— deleting a row from the query throws an error —>

delete from application.authDIDLog
where accessDate <

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

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:

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;

Option2

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;

HTH, JonOn July 24, 2015 at 3:57:50 PM, Jordan Michaels (@Jordan_Michaels) wrote:

Hi Folks,

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


<cfset application.authDIDLog =
queryNew(‘did,uid,accessDate’,‘varchar,varchar,date’)>

<cfset queryAddRow(application.authDIDLog)/>
<cfset querySetCell(application.authDIDLog, “did”, “tempdid”) />
<cfset querySetCell(application.authDIDLog, “uid”, “tempuid”) />
<cfset querySetCell(application.authDIDLog, “accessDate”, now()) />

select did from application.authDIDLog where did =

<!— deleting a row from the query throws an error —>

delete from application.authDIDLog
where accessDate <

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


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+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@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.