queryExecute with an "IN" list clause

[cfDEV]

Hello all.

I'm porting an older app over to using cfscript wherever possible.
I'm stumped on how to use an "IN" statement w/ queryExecute.
I get a "cannot cast [101,103] string to a number value" error.

I've searched and I can't find any documentation/examples on how to get this to work.

Any ideas? Thanks, Che

<cfscript>
if (day(now()) mod 2 is 0){
    psportid_list = "100,102,104";
}else{
    psportid_list = "101,103";
}

q = queryExecute(
    sql:"
    select psportid, keyword
    from psport
    where keyword is not null
    and active = :active
    and psportid in (:psportid)
    order by psportid
    "
    ,params:{
        active:{value:1, cfsqltype:"cf_sql_bit"}
        ,psportid:{value:psportid_list, cfsqltype:"cf_sql_integer", list:"yes"}
    }
);

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

[Rory]

Hi Che, try removing the second cfsqltype. You are simply passing a list to the in clause. There is no native 'list' mysql data type, and a list is not numerical. My guess is that is the issue.

[cfDEV]

Rory, I did try that. Whether I remove it entirely or I change it to cfsqltype:"cf_sql_varchar", I only get results for the first psportid.
All subsequent ids appear to be ignored.

Any ideas? I'm on 4.2.1.007..

[Rory]

Can you show me the resulting SQL query code returned by the query dump of the no cfsqltype and varchar?

[Arpan Paul]

Hello, 

Here are a couple that work in cfscript with IN. I couldn't get the queryExecute to work with "list" but here are 2 alternatives

<cfquery name="qry1" datasource="#Application.DSN#">
 select * from users where id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="1,2" list="yes">)
</cfquery>
<cfdump var="#qry1#">

<cfscript>
 query name="qry2" datasource="#Application.DSN#" {
 writeoutput("select * from users where id IN (");
 queryparam cfsqltype="cf_sql_integer" value="1,2" list="yes";
 writeoutput(")");
 }
 dump(qry2);
</cfscript>

<cfscript>
 qry3 = new Query();
 qry3.setDatasource(Application.DSN);
 qry3.addParam(name="ids",value="1,2",cfsqltype="cf_sql_integer", list="yes");
 qry3.setSql("select * from users where id IN (:ids)");
 dump(qry3.execute().getResult());
</cfscript>

Arpan

[Jeroen Knoef]

What happens if you pass an array instead of a list?
Anyhow, it looks like a bug to me. Similar to a bug with ORMExecuteQuery some time ago.

[Adam Cameron]

On Friday, 5 September 2014 23:36:55 UTC+1, Arpan Paul wrote:

Hello, 

Here are a couple that work in cfscript with IN. I couldn't get the queryExecute to work with "list" 

Nor could I. All four of these work in ColdFusion, but not in Railo:

numbers = queryNew("id,en,mi", "integer,varchar,varchar", [
[1,"one", "tahi"],
[2,"two", "rua"],
[3,"three", "toru"],
[4,"four", "wha"]
]);
evens = queryExecute(
 "SELECT * FROM numbers WHERE id IN (?)",
 [{value="2,4", list=true, cfsqltype="CF_SQL_INTEGER"}],
 {dbtype="query", numbers=numbers}
);


writedump(evens);

evens = queryExecute(
 "SELECT * FROM numbers WHERE id IN (:evens)",
 {evens={value="2,4", list=true, cfsqltype="CF_SQL_INTEGER"}},
 {dbtype="query", numbers=numbers}
);

evens = new Query(
 sql = "SELECT * FROM numbers WHERE id IN (?)",
 parameters = [{value="2,4", list=true, cfsqltype="CF_SQL_INTEGER"}],
 dbtype = "query",
 numbers = numbers
).execute().getResult();

evens = new Query(
 sql = "SELECT * FROM numbers WHERE id IN (:evens)",
 parameters = [{name="evens",value="2,4", list=true, cfsqltype="CF_SQL_INTEGER"}],
 dbtype = "query",
 numbers = numbers
).execute().getResult();

Files @ https://github.com/daccfml/scratch/tree/master/blogExamples/cfml/DB/queryExecute

It seems to me that queryExecute() doesn't realise the parameter markers need to be expanded out to be one for each element in the list.

And the Query.cfc solution is just a buggy implementation.

-- 

Adam

[AJ Mercer]

try putting space in side brackets

 and psportid in ( :psportid )

--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/bafcac12-3aaf-401f-915c-85799c548343%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

AJ Mercer
<webonix:net strength="Industrial" /> | <webonix:org community="Open" />
http://twitter.com/webonix

Railo Community Manager

[Igal @ getRailo.org]

if adding the whitespace fixes this then it's probably related to
https://issues.jboss.org/browse/RAILO-3176

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAPURtC1BUwSSLj57estSHAK5g28kTd-D2m29how2aVoQXB253w%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

-- 
Igal Sapir
Railo Core Developer
http://getRailo.org/

[Adam Cameron]

On Monday, 8 September 2014 02:57:02 UTC+1, AJ Mercer wrote:

try putting space in side brackets

 and psportid in ( :psportid )

It's not a parser issue: it's parsing it just fine. It's just doing the wrong thing with it once it's been parsed.

Anyway, for my part, the job's been done: I've indentified and reproed where Railo goes wrong, providing a control of ColdFusion which gets the same test right.

I'll leave it to the OP to raise the relevant tickets.

-- 

Adam

[cfDEV]

Adam, thank you for taking the time to confirm this issue. I created a ticket. Thanks, Che

https://issues.jboss.org/browse/RAILO-3195