Forum Stats

  • 3,855,236 Users
  • 2,264,483 Discussions
  • 7,905,940 Comments

Discussions

Variables in CQL Statements

calvinIsMe
calvinIsMe Member Posts: 23
edited Nov 27, 2012 10:28AM in Complex Event Processing
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

Best Answer

  • 877753
    877753 Member Posts: 25
    edited Nov 27, 2012 1:46AM Answer ✓
    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

Answers

  • 877753
    877753 Member Posts: 25
    edited Nov 26, 2012 12:45AM
    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
    877753
  • calvinIsMe
    calvinIsMe Member Posts: 23
    edited Nov 26, 2012 1:22PM
    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
  • alexalves
    alexalves Member Posts: 99
    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
    alexalves
  • 877753
    877753 Member Posts: 25
    edited Nov 27, 2012 1:46AM Answer ✓
    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
  • calvinIsMe
    calvinIsMe Member Posts: 23
    edited Nov 27, 2012 10:28AM
    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
This discussion has been closed.