Passing a list of parameters/arguments into operatingBinding

[khanhlam]

I need to create a fetch/select statement in a datasource's operationBinding where a list of parameters/arguments from the client side need to be passed into the whereClause. Is it possible to pass in a variant list of values? e.g.

<operationBinding operationType="fetch" operationId="fetchOperation">
<tableClause>tableA, tableB</tableClause>
<whereClause>tableA.fieldA = tableB.fieldB and tableA.fieldA IN (variant list of values) </whereClause>
</operationBinding>

Also is it better to build the fetch statement as above or using customSQL statement? e.g.

<operationBinding operationType="fetch" operationId = "fetchOperation">
<customSQL>select ...
where ...
</customSQL>
</operationBinding>


Thanks,

[Isomorphic]

You don't actually need SQL templating for this - you can use dataSourceField.includeFrom to cause the join to happen, and then criteria can target an included field. In this case, you could use an inSet Criterion, driving the whole thing on the fly from the client side if you like.

In addition, the FilterBuilder provides an end user UI for putting together such criteria. And, doing it this way means that client-side filtering also works with that criteria, greatly boosting performance.

However, if you actually needed to put an array of values into a SQL template, you can either:

1) put the String together in Java code (or use Server Scripting to use a different language) and use dsRequest.addToTemplateContext() to make the pre-formed String available to Velocity

2) just use a directive in a Velocity expression (such as a join(",") call) to turn an Array or List into a comma-delimited String. Here again, the Array will need to be available somewhere that Velocity can access it, such as in the template context or in the DSRequest somewhere

[Blama]

Hi khanhlam,

I'd definitely not go with the customSQL-option - having most or all of the code generated is almost always better, as the framework then knows more about your data and your setup.

<whereClause> is a Velocity Expression - so ways to make it dynamic there.
Other options include:

• DMI (most likely the easiest route)
• DynamicDSGenerator (most likely not the way to go in this case)
• Server Scripting (example)

Your <tableClause> could perhaps be replaced by a includeFrom-field - again better than writing the join yourself.

Best regards
Blama

[khanhlam]

The actual sql statement that I need to do is a little more complex than what I showed above. I just want to simplify the sql statment to learn how to pass in a list of parameters. But your suggestion of using dataSourceField.includeFrom, FilterBuilder, Velocity expression...is a way to go. I don't have experience in using these. Do you have examples, or a link to where I can read more about them? Thanks.

For the DMI suggestion, I see that I need to build more than one fetch to get the final result (fetch tableA with the list of parameters, then fetch tableB with the result from the first fetch) which I kind of shy away from doing so with the actual sql statement that I have. Maybe I'm missing a better way of doing it in DMI? Thanks.

[Blama]

Hi khanhlam,

I'd start with the Quick Start Guide-PDF and then at least all of DataSourceField, OperationBinding and DataSource (in that order) here in the docs.
It will show you what the framework might be able to for you out of the box with just the right .ds.xml.

For DMI: Multiple fetches to prepare the criteria for the final statement are no problem - these also happen in the same transaction almost automatically. But you can also put the criteria (if you know them) directly on the dataSourceField.includeFrom field as Isomophic is saying.

Best regards
Blama