Switchyard reference - How to change SQL query at runtime?

[stelmik]

Hi,

I look for example with switchard's SQL reference where I can change SQL query dynamically (from code (Java Bean or Camel)) depends on request data.

In my project I need to find persons with given firstname and/or lastname and/or age.

The full SQL query is:

SELECT * FROM persons WHERE firstname = # AND lastname = # AND age >= # AND age <= #

The input params aren't required. Therefore for example if firstname is empty than SQL query should be:

SELECT * FROM persons WHERE lastname = # AND age >= # AND age <= #

etc.

I don't want create many references with separate SQL query. The best solution is one reference where I can change query at runtime.

How to solve above problem? If possible.

[trohovsky]

Hi Krzysztof,

I haven't tried that by myself, but you can set a query in the CamelSqlQuery header. It takes precedence over the query specified in the binding according to the documentation: http://camel.apache.org/sql-component.

You can do that in Java bean:

@Inject

@Reference("SQLService")

ReferenceInvoker invoker;

ReferenceInvocation invocation = invoker.newInvocation("execute");

invocation.setProperty("CamelSqlQuery", "SELECT * FROM persons WHERE lastname = ? AND age >= ? AND age <= ?");

try {

    invocation.invoke(person);

} catch (Exception e) {

    e.printStackTrace();

}

or in Camel: .setHeader("CamelSqlQuery", "SELECT * FROM persons WHERE lastname = ? AND age >= ? AND age <= ?").to("switchyard://SQLService")

Finally, allow mapping of the header to the output message by setting includes="CamelSqlQuery" on the outbound ContextMapper.

Tomas

[stelmik]

Thank you.

It work.

[satyajit_ijt]

Hi,

I was trying to use the same. What jar do you need for ReferenceInvoker and ReferenceInvocation classes

Best Regards,

Satyajit

[trohovsky]

Hi Satyajit,

what version of SY are you using? This capability is available since version 1.1. It was introduced by resolving of this JIRA: [SWITCHYARD-1465] Make Context and Message available to bean services on all paths - JBoss Issue Tracker

Tomas

[satyajit_ijt]

Hi Tomas,

I have included SwitchYard in my JBOSSDeveloper using jbdevstudio-integration-stack-updatesite-7.0.0.GA. In POM file I have mentioned switchyard version as 1.1.0.M3-redhat-2.

My JBOSSDeveloper does not find ReferenceInvoker class.

Do I need to use some other version?

Best Regards,

satyajit

[trohovsky]

The class org.switchyard.component.bean.ReferenceInvoker is in the SwitchYard runtime. I am not sure what a problem do you exactly have. Doesn't your application compile? Doesn't it deploy? Or you just facing a problem with an error marker on a component reference in the tooling? If yes, then it is known issue.

[satyajit_ijt]

Hi Thomas,

Thanks for your reply. It was not compiling as I was using a different jar. It is compiling now. My bad. Thanks a lot.

Best Regards,

Satyajit

[satyajit_ijt]

Hi Thomas,

I was successful in calling it :

ReferenceInvocation invocation = invoker.newInvocation("execute");

invocation.setProperty("CamelSqlQuery", "SELECT sample query");

try {

    invocation.invoke(ArrayList);

} catch (Exception e) {

    e.printStackTrace();

}

It executed successfully.

My question is how do I get the result of the query?

Thanks and Regards,

Satyajit

[trohovsky]

Hi Satyajit,

you can return the result by:

ReturnType result = (ReturnType) invocation.getMessage().getContent();

Tomas

[satyajit_ijt]

Hi Thomas,

The query I am running is "SELECT SYSDATE FROM DUAL" on an Oracle Database.

The ArrayList I am getting is [{DUMMY=X}]

Any idea what could be wrong?

Thanks and Regards,

Satyajit

[satyajit_ijt]

Hi Thomas,

I found that the class org.apache.camel.component.sql.SqlProducer where the query is being executed is not picking up query injected through ReferenceInvocation object in this way :
  ReferenceInvocation invocation = invoker.newInvocation("execute");
  invocation.setProperty("CamelSqlQuery", "SELECT USER_ID, FIRST_NAME, LAST_NAME, COMPANY_NAME FROM USERS");
I found that in code there is a constructor and method process as follows :

public SqlProducer(SqlEndpoint endpoint, String query, JdbcTemplate jdbcTemplate, boolean batch) {
         super(endpoint);
         this.jdbcTemplate = jdbcTemplate;
         this.query = query;
         this.batch = batch;
     }

public void process(final Exchange exchange) throws Exception {
         String queryHeader = exchange.getIn().getHeader(SqlConstants.SQL_QUERY, String.class);
         String sql = queryHeader != null ? queryHeader : query;
**************************************************************************
While we are is a SQL binding associated with a query it is set in query variable picked up as sql and being executed.

While we are trying to bing query dynamically by setting property through reference invocation, it is not being set as header.
  exchange.getIn().getHeader(SqlConstants.SQL_QUERY, String.class) is being evaluated as null.
So queryheader is comming as null. Variable query is getting a default value Select * From Dual.
And result returned is [{DUMMY=X}].

Thanks and Regards,

Satyajit

We are using:

ojdbc6.jar.

  Server we are deploying application is jboss-eap-6.1. SwitchYaed version is 1.1.0.M3-redhat-2.

[satyajit_ijt]

Here is the fix for the issue:

1) Change xmlns:sql="urn:switchyard-component-camel-sql:config:1.0” to xmlns:sql="urn:switchyard-component-camel-sql:config:1.1”

2) Add  <sql:contextMapper includes="CamelSqlQuery"/> as below to sql binding.

<sql:binding.sql name="CamelSqlQuery">

<sql:contextMapper includes="CamelSqlQuery"/>

<sql:query>Select * From Dual</sql:query>

<sql:dataSourceRef>java:jboss/MLMDatasource</sql:dataSourceRef>

</sql:binding.sql>