Loop variable in SQL

[gobagoo]

Can you show an example(s) of syntax used with the raw SQL. I am
trying to loop here, however getting:

java.lang.IllegalArgumentException: Encountered '$' at index position:
node_type_ids[

SELECT t.template_id, t.interface_name, t.node_type_id,
tam.template_attribute_map_id, tam.attribute_id, tam.optional,
tam.default_value
FROM templates as t, template_attributes_map as tam
WHERE t.template_id = tam.template_id
AND t.node_type_id IN (
<#list node_type_ids as id>
<#if id_index > 0> , </#if> :node_type_ids[$
{id_index}]
</#list>
)

[Nils Kilden-Pedersen]

Not sure if this is the problem, but read this:

http://freemarker.sourceforge.net/docs/dgui_template_exp.html#dgui_template_exp_comparison

"There is a little problem with >= and >. FreeMarker interprets the > as the closing character of the FTL tag. To prevent this, you have to put the expression into parentheses: <#if (x > y)>"

So you should probably write <#if (id_index > 0)>

[gobagoo]

I actually installed velocity instead and changed the sql to:

SELECT t.template_id, t.interface_name, t.node_type_id,
tam.template_attribute_map_id, tam.attribute_id, tam.optional,
tam.default_value
FROM templates as t, template_attributes_map as tam
WHERE t.template_id = tam.template_id
AND t.node_type_id IN (

#if ($ids)
#foreach ( $node_type_id in $ids )
$node_type_id
#end
#end
)

Still no dice. The velocity segment is not interpreted by the
parser. I will try again with the freemarker suggestion.

[gobagoo]

Tried your suggestion, still getting an error:

Caused by: java.lang.IllegalArgumentException: Encountered '$' at
index position: ids[
at org.orbroker.SQLParser$.inParmIdx(SQLParser.scala:80)
at org.orbroker.SQLParser$.org$orbroker$SQLParser$
$handleLine(SQLParser.scala:41)
at org.orbroker.SQLParser$$anonfun$parse$1.apply(SQLParser.scala:17)
at org.orbroker.SQLParser$$anonfun$parse$1.apply(SQLParser.scala:17)
at scala.collection.mutable.ResizableArray
$class.foreach(ResizableArray.scala:60)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:44)
at org.orbroker.SQLParser$.parse(SQLParser.scala:17)
at org.orbroker.SQLStatement$.parseSQL(SQLStatement.scala:37)
at org.orbroker.StaticStatement.<init>(StaticStatement.scala:15)
at org.orbroker.config.BrokerBuilder$$anon
$1.<init>(BrokerBuilder.scala:111)

Query now looks like:

SELECT t.template_id, t.interface_name, t.node_type_id,
tam.template_attribute_map_id, tam.attribute_id, tam.optional,
tam.default_value
FROM templates as t, template_attributes_map as tam
WHERE t.template_id = tam.template_id
AND t.node_type_id IN (

<#if ids??>
<#list ids as id>
<#if (id_index > 0)> , </#if> :ids[${id_index}]
</#list>
</#if>
)

[Nils]

Depending on which version your using, are you doing any of this?

3.1: new BrokerBuilder(ds) with dynamic.FreeMarkerSupport

3.2: new BrokerConfig(ds) with dynamic.FreeMarkerSupport

[gobagoo]

Thanks for the help, I ended up using VelocitySupport instead. I
didn't notice these traits until you pointed them out.
I would be kind of nice to include a little blurb about this on the
wiki (I don't come from a java background so I did not recognize the
FreeMarker/Velocity syntax).
Great project BTW, fairly easy to get working.

[Nils]

With the release of 3.2 there is now built-in support for the IN predicate:

http://code.google.com/p/orbroker/wiki/INPredicate