2

I want to update event table (RDBMS) using additional condition that one column in that is not null. Table name is MSISDNProfileDB and it's in oracle db.

from incomingStream#window.length(1)
select  correlation_MSISDN as MSISDN, 
        INTERACTION_DT as INTERACTION_DT
update MSISDNProfileDB
on MSISDNProfileDB.MSISDN == MSISDN
and not(MSISDNProfileDB.column1 is null);

it validates the code, but does not update INTERACTION_DT. For testing purposes, I changed it to check if the column is null, and manually remove data from column1.

from incomingStream#window.length(1)
select  correlation_MSISDN as MSISDN, 
        INTERACTION_DT as INTERACTION_DT
update MSISDNProfileDB
on MSISDNProfileDB.MSISDN == MSISDN
and MSISDNProfileDB.column1 is null;

...and it still doesnt work. But when I change column value to 1 and do this:

from incomingStream#window.length(1)
select  correlation_MSISDN as MSISDN, 
        INTERACTION_DT as INTERACTION_DT
update MSISDNProfileDB
on MSISDNProfileDB.MSISDN == MSISDN
and MSISDNProfileDB.column1 == '1';

it works! So, conclusion is that cep has problem with null values from oracle db. Does anyone knows how are null values handled?

Kind Regards, Stefan

Community
  • 1
  • 1
datahack
  • 477
  • 1
  • 11
  • 32
  • Hi, are you getting any exception similar to `org.wso2.siddhi.core.exception.ExecutionPlanRuntimeException: Error while updating events in database,You have an error in your SQL syntax;...`, when you send an event? – Grainier Sep 19 '16 at 05:17
  • Hi Granier, when I try to update using this line ...and MSISDNProfileDB.column1 is null... in carbon log there is error: "Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression" – datahack Sep 19 '16 at 07:36

1 Answers1

1

I came across a similar problem with MySQL. The issue seems to be in the way CEP parse Siddhi query into SQL. I did a quick fix for that, and it worked for my scenario. It should work in your case too, but haven't tested with Oracle though. To use the fix (assuming you are using CEP 4.2.0);

  1. Delete siddhi-extension-event-table_3.1.2.jar from <cep>/repository/components/plugins/ directory.

  2. Add compiled jar to <cep>/repository/components/lib/ diectory.

  3. Use following query;

    from incomingStream
select  
    correlation_MSISDN as MSISDN, 
    INTERACTION_DT as INTERACTION_DT
update MSISDNProfileDB
on MSISDNProfileDB.MSISDN == MSISDN and not (MSISDNProfileDB.column1 is null);
Grainier
  • 1,634
  • 2
  • 17
  • 30
  • Thank you Grainier for your support! I'm working with CEP 4.1.0, can I also compile this jar on 4.1.0 version? – datahack Sep 20 '16 at 07:17
  • CEP 4.2.0 uses Siddhi 3.1.2, CEP 4.1.0 uses Siddhi 3.0.5. So you might need to apply the changes to [v3.0.5 tag](https://github.com/wso2/siddhi/tree/v3.0.5). Here's a [patched jar for v3.0.5](https://mega.nz/#!Qx8zRbTD!-_JySYc0KHT-pmRWGy31GX0rbF_hHIjv-eAs_SLwloE). Try it and see. – Grainier Sep 20 '16 at 08:44
  • Now everything works. Thank you very much! Kind regards, Stefan – datahack Sep 21 '16 at 07:16
  • Hi, me again :) Can you please also check how publisher is behaving for same problem. When I try to insert empty string to rdbms from execution plan it works fine(column value in oracle is null), but when i try this using publisher it's not. I guess you resolved this for execution plan and not for publisher also? Kind Regards,Stefan – datahack Sep 21 '16 at 12:08
  • Hi @StStojanovic, could you please create a [JIRA](https://wso2.org/jira/browse/CEP) for the above, and inform it on WSO2 public mailing list (dev@wso2.org). Also, please consider accepting, or voting up the answer if it was helpful :) – Grainier Sep 21 '16 at 14:23