#WSO2 - DAS - storing event in SQL server 2008

Question

In my project I need to collect data, process/Analyze data and published it. I am using WSO2 DAS 3.0.0 for the same.

In order to achieve this I have followed below steps:-

1. Created event stream(define attribute).
2. Created Event receiver. (HTTP,SOAP)
3. Created event publisher (store event in My sql but getting error in SQL server see below).

My question is as below:-

1. Is there any other way to store persisted event in Database. Can I store event in database using Main->stream->persist event. Can I use this to store event in Database or I need to use Main->publishers. Select "output event adapter type"- RDBMS to store the event?

2. Can I use SQL server 2008 to store the event in SQL server 2008? I am getting below error while storing data into SQL server 2008.

Cannot Execute Create Table Query. There is already an object named 'tbl_City' in the database. Hence Event is dropped.

TID[-1234] [DAS] [2015-11-09 12:28:06,265] ERROR {org.wso2.carbon.event.output.adapter.rdbms.RDBMSEventAdapter} - Cannot Execute Create Table Query. There is already an object named 'tbl_City' in the database. Hence Event is dropped. org.wso2.carbon.event.output.adapter.rdbms.RDBMSEventAdapter.createTableIfNotExist(RDBMSEventAdapter.java:416) org.wso2.carbon.event.output.adapter.rdbms.RDBMSEventAdapter.executeProcessActions(RDBMSEventAdapter.java:290) org.wso2.carbon.event.output.adapter.rdbms.RDBMSEventAdapter.publish(RDBMSEventAdapter.java:134) org.wso2.carbon.event.output.adapter.core.internal.OutputAdapterRuntime.publish(OutputAdapterRuntime.java:62) org.wso2.carbon.event.output.adapter.core.internal.CarbonOutputEventAdapterService.publish(CarbonOutputEventAdapterService.java:143) org.wso2.carbon.event.publisher.core.internal.EventPublisher.process(EventPublisher.java:361) org.wso2.carbon.event.publisher.core.internal.EventPublisher.sendEvent(EventPublisher.java:209) org.wso2.carbon.event.publisher.core.internal.EventPublisher.consumeEvent(EventPublisher.java:275) org.wso2.carbon.event.stream.core.internal.EventJunction.sendEvent(EventJunction.java:142) org.wso2.carbon.event.receiver.core.internal.management.InputEventDispatcher.onEvent(InputEventDispatcher.java:27) org.wso2.carbon.event.receiver.core.internal.EventReceiver.sendEvent(EventReceiver.java:259) org.wso2.carbon.event.receiver.core.internal.EventReceiver.processTypedEvent(EventReceiver.java:237) org.wso2.carbon.event.receiver.core.internal.EventReceiver$TypedEventSubscription.onEvent(EventReceiver.java:317) org.wso2.carbon.event.input.adapter.core.internal.InputAdapterRuntime.onEvent(InputAdapterRuntime.java:110) org.wso2.carbon.event.input.adapter.http.HTTPMessageServlet$HTTPRequestProcessor.run(HTTPMessageServlet.java:210) java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) java.util.concurrent.FutureTask.run(FutureTask.java:262) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) java.lang.Thread.run(Thread.java:745)

Please confirm.

Answer 1

Seems like you have confused event publishers with Data publishers.

Events publishers are used to publish results to external systems for taking further actions. Event publishers provide the capability to send event notifications and alerts from WSO2 DAS to external systems.

For your use case, what you have to do is publishing data to DAS. There are couple of ways of doing that. Please have a look on [1] for more information.

You can also follow the DAS quick start guide [2] to get a better understanding about the concepts.

About the question on using SQL server 2008, yes DAS support Microsoft SQL server.

[1] https://docs.wso2.com/display/DAS300/Publishing+Data+to+DAS

[2] https://docs.wso2.com/display/DAS300/Quick+Start+Guide

Answer 2

Thanks for your reply.

Yes you are correct. I got confused between event/data publisher. I dont want to publish my event I want it to be stored in the SQL server.

Please go through my system flow as below:-

An external system will be pushing data into DAS. That data I need to store in SQL server and need to processed for further analysis.

I did the below steps for it.

1. Created event stream. Define 5 attributes. :- Done
2. Created Event receiver. Exposed HTTP endpoint.:- Done
3. I need to store this data into the SQL server 2008. - Pending

I can publishes below sample data to DAS.:-

"<events>
<event>
    <payloadData>
        <queueName>data3</queueName>
        <agentsOnThreshold>100</agentsOnThreshold>
        <agentsAvailThreshold>45</agentsAvailThreshold>
        <callsWtngThreshold>4</callsWtngThreshold>
        <avgWaitInSecThreshold>100</avgWaitInSecThreshold>
        <oldestCallInSecThreshold>45</oldestCallInSecThreshold>
    </payloadData>
</event>`enter code here`

"

So my question is how to store above data into SQL server which I received in DAS (event receiver).

Answer 3

In order to persist the data that is being received by DAS for a particular stream, you need to enable data persistence for it. Please follow [1] and persist the stream if you haven't done that yet.

You can verify the DAS data recieving functionality by sending a sample event to DAS by using DAS inbuilt event simulation feature [2].

To make sure whether data is persisted in Event Store, go to the Data Exporer [3] and explore your published data.

[1] https://docs.wso2.com/display/DAS300/Persisting+Data+for+Batch+Analytics

[2] https://docs.wso2.com/display/DAS300/Publishing+Data+Using+Event+Simulation

[3] https://docs.wso2.com/display/DAS300/Data+Explorer

Answer 4

Thanks for the links and information it really help me a lot.

I am able to save the event into the SQL server 2008.

I just wanted to add what steps I have followed:-

1. Open \repository\conf\datasources\master-datasources.xml change the datasource tag. Change URL, username,password, driveclassname.

Follow below link for the details steps:-

https://docs.wso2.com/display/DAS300/Setting+up+Microsoft+SQL

2. Open \repository\conf\datasources\analytics-datasources.xml Change the configuration settings. Change URL, username,password, driveclassname. e.g.

      "<datasource>
         <name>WSO2_ANALYTICS_FS_DB</name>
         <description>The datasource used for analytics file system</description>
         <definition type="RDBMS">
             <configuration>
   

   jdbc:sqlserver://localhost:1433;databaseName=testDAS sa ****** driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver 50 60000 5 true SELECT 1 30000 "

3. Make similar changes to the "WSO2_ANALYTICS_EVENT_STORE_DB", "WSO2_ANALYTICS_PROCESSED_DATA_STORE_DB".

4. Create an event and check in SQL server table whether it get capture. In my case SQL table is created with name "[ANX___7Lk3JrzI_]".

Thanks.