HQL SELECT IN (:list)

[Matt Quackenbush]

Based upon this link here, http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSf0ed2a6d7fb07688310730d81223d0356fc-7ffe.html, I would expect the following to work beautifully.

myIDs = [1,2,3,4,5,6,7,8,9,10];
hql = "FROM foo WHERE bar IN (:idList)";
foos = ormExecuteQuery( hql, { idList = myIDs });

However, I receive the following error message:

"Can't cast Complex Object Type Array to String"

That message seems to indicate that for some reason Railo's implementation requires a string rather than an array for a list parameter.  So I changed it to a simple string (list), which eliminates the exception, but returns 0 results.

Is this a bug? A planned difference between ACF and Railo? Something I am simply doing wrong?

Thanks in advance.

[Matt Quackenbush]

I forgot to mention that if I provide only a single ID as a string (rather than a list of IDs), an object is returned to me. That would be fantastic, of course, except the fact that I want an array of objects.  :-)

[Judah McAuley]

I believe that your array needs to be a comma delimited list (string).

The Params section of ormExecuteQuery takes a struct of parameters (if
you are using named parameters). You are passing in one parameter, a
list, not an array of individual parameters.

Consider, for example, if you also wanted to specify another
parameter, like whether the foo is new or not. You'd do:

myIDs = "1,2,3,4,5,6,7,8,9,10";
isNew = true;
hql = "FROM foo WHERE bar IN (:idList) AND isNew = (:isNew)";
foos = ormExecuteQuery(hql, {idList = myIDs, isNew = isNew});

If you look at it that way, you see you are passing in a string of ids
that would result in the SQL:
FROM foo WHERE bar IN ('1,2,3,4,5,6,7,8,9,10') AND isNew = false

Hope that helps (and is correct as I didn't trying it)

Cheers,
Judah

[Gary Stanton]

I'm having real problems with this too:
    result = ORMExecuteQuery("
            FROM     model
            WHERE    id in (:list)"
        ,    {list = '1,2'}
    );

Code simplified for readability.
When I run this, I get the following error:
can't cast [1,2] string to a number value

Anyone know why this is happening?

[John Whish]

Use an array, instead of list:

result = ORMExecuteQuery("
  FROM     model
  WHERE    id in (:list)"
  , {list = [1,2]} 
);

[Gary Stanton]

Thanks for the reply John,

Unfortunately, that was my starting point... I get the original error message:
Can't cast Complex Object Type Array to a number value

On Monday, 30 April 2012 09:17:24 UTC+1, John Whish wrote:

Use an array, instead of list:

result = ORMExecuteQuery("
  FROM     model
  WHERE    id in (:list)"
  , {list = [1,2]} 
);

[John Whish]

Hmm, that doesn't work in Railo.

Hibernate has a SetParameterList method which does support this, but you need to use a Java Collection to populate it from what I can see :(

[John Whish]

I've filed a ticket for this:

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

[Michael Offner]

this is solved for release following in a couple of hours.

/micha

2012/4/30 John Whish <john....@googlemail.com>

[John Whish]

Awesome - thanks!

- sent by a little green robot powered device