How to connect to Cassandra using WSO2 DSS

WSO2 Data Service Server supports exporting data as a web service, called a data service. It can connect to various data sources and expose those data to do operations on them. This post will guide you to use a Cassandra data source using WSO2 DSS.

1. Download Cassandra from here and install (You may have to change some configurations. Please refer this).
2. Create a keyspace, table and store some data
Now we will look into how to configure DSS
3.You can download DSS from here
4. Extract the pack and goto $DSS_HOME/bin folder and run sh wso2Server.sh (if you are a Windows user run wso2Server.bat file)
5. Add a data source. This step has been described in details here
6. You can test connection and save it
7. Deploy data service

The important thing that I would like to address in here is the versions compatibility of WSO2DSS and Cassandra jdbc drivers.

• The WSO2 DSS version older than 3.1.0 compatible with Cassandra JDBC 1.1.1. By default the Cassandra 1.1.x supports CQL (Common Query Language) 2. CQL3 is released with much more functionalities than CQL2 such as schema-free feature. 
• But you can connect to CQL3 by starting the Cassandra server with the following command ./cqlsh -3
• From WSO2 DSS (older version starting from 3.0.1) you have to specify the connection url as below. jdbc:cassandra://localhost:9160/keyspace_name?version=3.0.0
• If you do not specify the version in the URL it will connect to CQL2 by default
• But from DSS version 3.1.0 onward it supports Cassandra JDBC 1.2.x and because of that, it will connect to CQL3 by default.

Escape non printable characters in WSO2 DSS

WSO2 Dss can be used to expose data from different data sources (ex: MYSQL, ORACLE) as a web service. Escaping non printable characters is a feature introduced from WSO2 DSS 3.1.0 onward.

Lets say in your database there are characters which cannot be printed. But you cannot replace or delete them as those are the data you have. But if you want to retrieve those data through a web service it will not allow to do so as they cannot be represented. For an example when you are invoking a data service (using soap-ui or try-it) and do a select query for a data contains characters which are not in printable format it will throw the following exception.

ERROR {org.wso2.carbon.dataservices.core.description.query.SQLQuery} -  DS Fault Message: Error in XML generation at StaticOutputElement.execute
DS Code: UNKNOWN_ERROR
Nested Exception:-
com.ctc.wstx.exc.WstxIOException: Invalid white space character (0x5) in text to output
 {org.wso2.carbon.dataservices.core.description.query.SQLQuery}
DS Fault Message: Error in XML generation at StaticOutputElement.execute
DS Code: UNKNOWN_ERROR
Nested Exception:-
com.ctc.wstx.exc.WstxIOException: Invalid white space character (0x5) in text to output

 at org.wso2.carbon.dataservices.core.engine.StaticOutputElement.execute(StaticOutputElement.java:232)
 at org.wso2.carbon.dataservices.core.engine.OutputElementGroup.execute(OutputElementGroup.java:121)
 at org.wso2.carbon.dataservices.core.description.query.Query.writeResultEntry(Query.java:339)
 at org.wso2.carbon.dataservices.core.description.query.SQLQuery.processNormalQuery(SQLQuery.java:822)
 at org.wso2.carbon.dataservices.core.description.query.SQLQuery.runQuery(SQLQuery.java:2034)
 at org.wso2.carbon.dataservices.core.description.query.Query.execute(Query.java:254)
 at org.wso2.carbon.dataservices.core.engine.CallQuery.execute(CallQuery.java:161)
 at org.wso2.carbon.dataservices.core.engine.CallQueryGroup.execute(CallQueryGroup.java:118)
 at org.wso2.carbon.dataservices.core.description.operation.Operation.execute(Operation.java:73)
 at org.wso2.carbon.dataservices.core.engine.DataService.invoke(DataService.java:598)
 at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:135)
 at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.serialize(DSOMDataSource.java:147)
 at org.apache.axiom.om.impl.llom.OMSourcedElementImpl.internalSerialize(OMSourcedElementImpl.java:691)

This happened as those characters cannot be serialized to xml when they are going to be printed. But with this newly added feature you can define to escape the those characters with '?' when you define output mapping in the process of creating the data service. You can select "Escape Non Printable Characters" from the UI.

If it is selected the corresponding characters will be replaced by ? and if it is not, then it will throw the above exception. Find more from here.