[DEPRECATED] When WSO2 Data Services Server 3.0.1 and Cassandra 1.1 CQL 2 JDBC driver — doesn’t support BLOB

Attempted to use the following CQL 2 schema with two BLOBs:

CREATE KEYSPACE chf WITH strategy_class = 'SimpleStrategy' AND strategy_options\
:replication_factor = 1;

USE chf;

CREATE TABLE measurements (
  measurement_uuid text PRIMARY KEY,
  patient_uuid text,
  age_years double,
  answers_csv text,
  blood_glucose_milligrams_per_deciliter double,
  blood_pressure_diastolic_millimeters_mercury double,
  blood_pressure_systolic_millimeters_mercury double,
  electrocardiogram_1khz_16bit blob,
  height_inches double,
  oxygen_saturation_milligrams_per_litre double,
  pain_scale_0_to_10 int,
  photoplethysmogram_1khz_16bit blob,
  pulse_beats_per_minute double,
  pulse_oximetry_percent double,
  questions_csv text,
  respiratory_rate_breaths_per_minute double,
  weight_pounds double
);

CREATE INDEX ON measurements(patient_uuid);

CREATE TABLE patients (
  patient_uuid text PRIMARY KEY,
  external_id text,
  password_hash text
);

CREATE INDEX ON patients(external_id);

INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES ('d73771e0\
-97eb-11e2-9e96-0800200c9a66','1','test');

INSERT INTO measurements (measurement_uuid,patient_uuid,age_years) VALUES ('f0c\
f1c70-97eb-11e2-9e96-0800200c9a66','d73771e0-97eb-11e2-9e96-0800200c9a66',99);

CREATE KEYSPACE chf WITH strategy_class = 'SimpleStrategy' AND strategy_options\

:replication_factor = 1;

USE chf;

CREATE TABLE measurements (

measurement_uuid text PRIMARY KEY,

patient_uuid text,

age_years double,

answers_csv text,

blood_glucose_milligrams_per_deciliter double,

blood_pressure_diastolic_millimeters_mercury double,

blood_pressure_systolic_millimeters_mercury double,

electrocardiogram_1khz_16bit blob,

height_inches double,

oxygen_saturation_milligrams_per_litre double,

pain_scale_0_to_10 int,

photoplethysmogram_1khz_16bit blob,

pulse_beats_per_minute double,

pulse_oximetry_percent double,

questions_csv text,

respiratory_rate_breaths_per_minute double,

weight_pounds double

);

CREATE INDEX ON measurements(patient_uuid);

CREATE TABLE patients (

patient_uuid text PRIMARY KEY,

external_id text,

password_hash text

);

CREATE INDEX ON patients(external_id);

INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES ('d73771e0\

-97eb-11e2-9e96-0800200c9a66','1','test');

INSERT INTO measurements (measurement_uuid,patient_uuid,age_years) VALUES ('f0c\

f1c70-97eb-11e2-9e96-0800200c9a66','d73771e0-97eb-11e2-9e96-0800200c9a66',99);

With the following DSS configuration:

<data name="chf_insert">
   <config id="cassandra_chf">
      <property name="url">jdbc:cassandra://localhost:9160/chf</property>
      <property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>
   </config>
   <query id="insert_new_patient" useConfig="cassandra_chf">
      <sql>INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES (?,?,?)</sql>
      <param name="patient_uuid" sqlType="STRING"/>
      <param name="external_id" sqlType="STRING"/>
      <param name="password_hash" sqlType="STRING"/>
   </query>
   <query id="insert_new_measurement" useConfig="cassandra_chf">
      <sql>INSERT INTO measurements (measurement_uuid,age_years,answers_csv,blood_glucose_milligrams_per_deciliter,blood_pressure_diastolic_millimeters_mercury,blood_pressure_systolic_millimeters_mercury,electrocardiogram_1khz_16bit,height_inches,oxygen_saturation_milligrams_per_litre,pain_scale_0_to_10,patient_uuid,photoplethysmogram_1khz_16bit,pulse_beats_per_minute,pulse_oximetry_percent,questions_csv,respiratory_rate_breaths_per_minute,weight_pounds) VALUES (:measurement_uuid,:age_years,:answers_csv,:blood_glucose_milligrams_per_deciliter,:blood_pressure_diastolic_millimeters_mercury,:blood_pressure_systolic_millimeters_mercury,:electrocardiogram_1khz_16bit,:height_inches,:oxygen_saturation_milligrams_per_litre,:pain_scale_0_to_10,:patient_uuid,:photoplethysmogram_1khz_16bit,:pulse_beats_per_minute,:pulse_oximetry_percent,:questions_csv,:respiratory_rate_breaths_per_minute,:weight_pounds)</sql>
      <param defaultValue="NaN" name="age_years" sqlType="DOUBLE"/>
      <param defaultValue="null" name="answers_csv" sqlType="STRING"/>
      <param defaultValue="NaN" name="blood_glucose_milligrams_per_deciliter" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="blood_pressure_diastolic_millimeters_mercury" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="blood_pressure_systolic_millimeters_mercury" sqlType="DOUBLE"/>
      <param defaultValue="0" name="electrocardiogram_1khz_16bit" sqlType="BLOB"/>
      <param defaultValue="NaN" name="height_inches" sqlType="DOUBLE"/>
      <param name="measurement_uuid" sqlType="STRING"/>
      <param defaultValue="NaN" name="oxygen_saturation_milligrams_per_litre" sqlType="DOUBLE"/>
      <param defaultValue="-1" name="pain_scale_0_to_10" sqlType="INTEGER"/>
      <param name="patient_uuid" sqlType="STRING"/>
      <param defaultValue="null" name="photoplethysmogram_1khz_16bit" sqlType="BLOB"/>
      <param defaultValue="NaN" name="pulse_beats_per_minute" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="pulse_oximetry_percent" sqlType="DOUBLE"/>
      <param defaultValue="0" name="questions_csv" sqlType="STRING"/>
      <param defaultValue="NaN" name="respiratory_rate_breaths_per_minute" sqlType="DOUBLE"/>
      <param defaultValue="NaN" name="weight_pounds" sqlType="DOUBLE"/>
   </query>
   <operation name="insert_new_patient" returnRequestStatus="true">
      <call-query href="insert_new_patient">
         <with-param name="patient_uuid" query-param="patient_uuid"/>
         <with-param name="external_id" query-param="external_id"/>
         <with-param name="password_hash" query-param="password_hash"/>
      </call-query>
   </operation>
   <operation name="insert_new_measurement" returnRequestStatus="true">
      <call-query href="insert_new_measurement">
         <with-param name="age_years" query-param="age_years"/>
         <with-param name="answers_csv" query-param="answers_csv"/>
         <with-param name="blood_glucose_milligrams_per_deciliter" query-param="blood_glucose_milligrams_per_deciliter"/>
         <with-param name="blood_pressure_diastolic_millimeters_mercury" query-param="blood_pressure_diastolic_millimeters_mercury"/>
         <with-param name="blood_pressure_systolic_millimeters_mercury" query-param="blood_pressure_systolic_millimeters_mercury"/>
         <with-param name="electrocardiogram_1khz_16bit" query-param="electrocardiogram_1khz_16bit"/>
         <with-param name="height_inches" query-param="height_inches"/>
         <with-param name="measurement_uuid" query-param="measurement_uuid"/>
         <with-param name="oxygen_saturation_milligrams_per_litre" query-param="oxygen_saturation_milligrams_per_litre"/>
         <with-param name="pain_scale_0_to_10" query-param="pain_scale_0_to_10"/>
         <with-param name="patient_uuid" query-param="patient_uuid"/>
         <with-param name="photoplethysmogram_1khz_16bit" query-param="photoplethysmogram_1khz_16bit"/>
         <with-param name="pulse_beats_per_minute" query-param="pulse_beats_per_minute"/>
         <with-param name="pulse_oximetry_percent" query-param="pulse_oximetry_percent"/>
         <with-param name="questions_csv" query-param="questions_csv"/>
         <with-param name="respiratory_rate_breaths_per_minute" query-param="respiratory_rate_breaths_per_minute"/>
         <with-param name="weight_pounds" query-param="weight_pounds"/>
      </call-query>
   </operation>
</data>

<property name="url">jdbc:cassandra://localhost:9160/chf</property>

<property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>

</config>

<sql>INSERT INTO patients (patient_uuid,external_id,password_hash) VALUES (?,?,?)</sql>

</query>

<sql>INSERT INTO measurements (measurement_uuid,age_years,answers_csv,blood_glucose_milligrams_per_deciliter,blood_pressure_diastolic_millimeters_mercury,blood_pressure_systolic_millimeters_mercury,electrocardiogram_1khz_16bit,height_inches,oxygen_saturation_milligrams_per_litre,pain_scale_0_to_10,patient_uuid,photoplethysmogram_1khz_16bit,pulse_beats_per_minute,pulse_oximetry_percent,questions_csv,respiratory_rate_breaths_per_minute,weight_pounds) VALUES (:measurement_uuid,:age_years,:answers_csv,:blood_glucose_milligrams_per_deciliter,:blood_pressure_diastolic_millimeters_mercury,:blood_pressure_systolic_millimeters_mercury,:electrocardiogram_1khz_16bit,:height_inches,:oxygen_saturation_milligrams_per_litre,:pain_scale_0_to_10,:patient_uuid,:photoplethysmogram_1khz_16bit,:pulse_beats_per_minute,:pulse_oximetry_percent,:questions_csv,:respiratory_rate_breaths_per_minute,:weight_pounds)</sql>

</query>

<call-query href="insert_new_patient">

<with-param name="patient_uuid" query-param="patient_uuid"/>

<with-param name="external_id" query-param="external_id"/>

<with-param name="password_hash" query-param="password_hash"/>

</call-query>

</operation>

<call-query href="insert_new_measurement">

<with-param name="age_years" query-param="age_years"/>

<with-param name="answers_csv" query-param="answers_csv"/>

<with-param name="blood_glucose_milligrams_per_deciliter" query-param="blood_glucose_milligrams_per_deciliter"/>

<with-param name="blood_pressure_diastolic_millimeters_mercury" query-param="blood_pressure_diastolic_millimeters_mercury"/>

<with-param name="blood_pressure_systolic_millimeters_mercury" query-param="blood_pressure_systolic_millimeters_mercury"/>

<with-param name="electrocardiogram_1khz_16bit" query-param="electrocardiogram_1khz_16bit"/>

<with-param name="height_inches" query-param="height_inches"/>

<with-param name="measurement_uuid" query-param="measurement_uuid"/>

<with-param name="oxygen_saturation_milligrams_per_litre" query-param="oxygen_saturation_milligrams_per_litre"/>

<with-param name="pain_scale_0_to_10" query-param="pain_scale_0_to_10"/>

<with-param name="patient_uuid" query-param="patient_uuid"/>

<with-param name="photoplethysmogram_1khz_16bit" query-param="photoplethysmogram_1khz_16bit"/>

<with-param name="pulse_beats_per_minute" query-param="pulse_beats_per_minute"/>

<with-param name="pulse_oximetry_percent" query-param="pulse_oximetry_percent"/>

<with-param name="questions_csv" query-param="questions_csv"/>

<with-param name="respiratory_rate_breaths_per_minute" query-param="respiratory_rate_breaths_per_minute"/>

<with-param name="weight_pounds" query-param="weight_pounds"/>

</call-query>

</operation>

</data>

But received the following error:

Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQL\
Query.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: chf_insert
Location: /chf_insert.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: insert_new_measurement
Current Params: {age_years=13, patient_uuid=d73771e0-97eb-11e2-9e96-0800200c9a6\
6, measurement_uuid=48a69dc0-99b7-11e2-9e96-0800200c9a66}
Nested Exception:-
DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
java.sql.SQLFeatureNotSupportedException: the Cassandra implementation does not\
 support this method

Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQL\

Query.processNormalQuery'

DS Code: DATABASE_ERROR

Source Data Service:-

Name: chf_insert

Location: /chf_insert.dbs

Description: N/A

Default Namespace: http://ws.wso2.org/dataservice

Current Request Name: insert_new_measurement

Current Params: {age_years=13, patient_uuid=d73771e0-97eb-11e2-9e96-0800200c9a6\

6, measurement_uuid=48a69dc0-99b7-11e2-9e96-0800200c9a66}

Nested Exception:-

DS Fault Message: Error in 'createProcessedPreparedStatement'

DS Code: UNKNOWN_ERROR

Nested Exception:-

java.sql.SQLFeatureNotSupportedException: the Cassandra implementation does not\

support this method

Decidable Dreams

[DEPRECATED] When WSO2 Data Services Server 3.0.1 and Cassandra 1.1 CQL 2 JDBC driver — doesn’t support BLOB

Leave a Reply Cancel reply