This discussion is archived
5 Replies Latest reply: Nov 27, 2012 7:28 AM by calvinIsMe RSS

Variables in CQL Statements

calvinIsMe Newbie
Currently Being Moderated
Hi

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


Regards

Edited by: calvinIsMe on Nov 24, 2012 12:16 AM
  • 1. Re: Variables in CQL Statements
    877753 Newbie
    Currently Being Moderated
    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.

    Thanks
    Patha

    Edited by: Patha on Nov 26, 2012 11:14 AM
  • 2. Re: Variables in CQL Statements
    calvinIsMe Newbie
    Currently Being Moderated
    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.

    Regards

    Edited by: calvinIsMe on Nov 26, 2012 11:49 PM
  • 3. Re: Variables in CQL Statements
    alexalves Explorer
    Currently Being Moderated
    Hi,

    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

    Rgds
    Alex
  • 4. Re: Variables in CQL Statements
    877753 Newbie
    Currently Being Moderated
    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.

    <processor>      
    <name>LIMITCHECKprocessor</name>
    <rules>
    </view>
    <view id="inpjoin">
         <![CDATA[Rstream(select
                                  C.bankName as bankName,
                                  C.maxTransLimit as maxTransLimit,
                                  from inputStream[now] as C, myCache as D
                                  where D.bankName=C.bankname)
         ]]>
    </view>
    <!-- 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 []
                                       ]]>           
    </query>
    </rules>
    </processor>

    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);
              myCache.put(event.getBankName(),event);
    event = new CacheEvent();
              event.setBankName("ABC"); event.setMaxTranLimit(200000);
              myCache.put(event.getBankName(),event);
         }
         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
    calvinIsMe Newbie
    Currently Being Moderated
    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?

    Regards

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points