5 Replies Latest reply: Nov 27, 2012 9:28 AM by calvinIsMe RSS

    Variables in CQL Statements


      Is there any way we can use a variable in any CQL ?
      Whenever we build a pattern, sometimes though the pattern remains same over a period of time but the threshold value ( constant ) may change or vary from business of business. If we could have something like a PreparedStatements. Then we may externally change this value without touching the main Query.

      I have tried to draft an example below.

      Total amount of transaction in a day must not exceed 10,000 USD. Now this 10,000 will be a variable as this value might change from bank to bank or may be trader to trader etc and may be replaced in the CQL by a variable


      Edited by: calvinIsMe on Nov 24, 2012 12:16 AM
        • 1. Re: Variables in CQL Statements
          Hi calvin,
          put those values in a cache and access them in the query.
          example: the cache event would be with 2 properties: 1) bankName(key) 2) maxTransLimit (value)
          If you need any help in setting up the cache loader please let me know.


          Edited by: Patha on Nov 26, 2012 11:14 AM
          • 2. Re: Variables in CQL Statements
            Hi Patha

            Interesting thoughts. But unfortunately I could not get the complete idea.Can you please elaborate and show what the CQL may look like?
            Are you suggesting to add a cache node as an input channel that will have these constant values like bankname, maxTransLimit? If I join a Cache with a stream, would not I be restricted to use [now] window. I guess same happens with table input, which might have been a choice as well.

            Too many questions from my end. Sorry if it confuses you.


            Edited by: calvinIsMe on Nov 26, 2012 11:49 PM
            • 3. Re: Variables in CQL Statements

              Patha's suggestion is a good one, and you are right, you can use any relational source (e.g. cache, table) as a placeholder for constants.

              You indeed would have to join to it using some window construct, however keep in mind that you can always output the result back as a stream in a base view and do whatever you intended originally in a derived query on top of it.

              In addition, we do support parametrized queries and view. Please take a look at: http://docs.oracle.com/cd/E17904_01/apirefs.1111/e14303/com/bea/wlevs/ede/api/PreparedStatement.html

              • 4. Re: Variables in CQL Statements
                Thanks to Alex.

                Hi calvin,
                So easiest way to implement this is in a <view> join the cache and input stream to get modified event along with the details you need (using inp[now]) and use the Rstream(output) of this view as input channel for your main query. No you can do what ever you want in main query since your event have the required attribute and you can use it directly.

                <view id="inpjoin">
                                              C.bankName as bankName,
                                              C.maxTransLimit as maxTransLimit,
                                              from inputStream[now] as C, myCache as D
                                              where D.bankName=C.bankname)
                <!-- add all the other attributes which you have in the input stream in above list -->
                <query id="finaloutput"> <![CDATA[select
                                                           B.bankName as bankName,
                                                           --- (whatever logic you need) ---          
                                                              from inpjoin []

                Note : myCache should be connected to Processor directly and inorder to feed some data to the cache you will need a cacheLoader, sample code :

                package cache;
                import java.util.*;
                import com.bea.wlevs.ede.api.InitializingBean;
                public class MyCacheLoader implements InitializingBean {
                     private Map myCache;
                     public void afterPropertiesSet() throws Exception{
                          CacheEvent event;
                          event = new CacheEvent();
                          event.setBankName("HDFC"); event.setMaxTranLimit(100000);
                event = new CacheEvent();
                          event.setBankName("ABC"); event.setMaxTranLimit(200000);
                     public Map getMyCache() {
                          return myCache;
                     public void setMyCache(Map myCache) {
                          this.myCache = myCache;

                Edited by: Patha on Nov 27, 2012 12:15 PM
                • 5. Re: Variables in CQL Statements
                  Hi Alex

                  I am pleasantly amused to see for parametrized query support. Can you give some more details how can we use this? How will I mention this in a processor node?


                  Edited by: calvinIsMe on Nov 27, 2012 8:57 PM