How to insert string value in blob data type cassandra?

[Check Peck]

I have a table like this -

    CREATE TABLE data_test (
      key text PRIMARY KEY,
      size int,
      record_value blob,
      data_id text
    )
   
I already have couple of records inserted in the above table from different application which is writing into this table. Now I am trying to insert one record in which I have string value for `record_value` column and I don't know how can I insert string data in blob data type. If I try below code then it complains and give an error.

I am using Datastax Java driver to insert into cassandra.

    public void insertData(MelMetadata rec) {
        String sql = "insert into data_test (key, size, record_value, data_id, last_modified_date)"
                + " values ("
                + "'"
                + rec.getKey()
                + "',"
                + rec.getSize()
                + ","
                + ByteBuffer.wrap(rec.getRecord_value().getBytes(Charset.forName("UTF-8")))
                + ","               
                + rec.getData_Id() + "');";

        try {
            Query query = new SimpleStatement(sql);
            query.setConsistencyLevel(ConsistencyLevel.QUORUM);
            session.execute(query);
        } catch (Exception e) {
            // log an exception
        }
    }
   
And below is the exception I am getting -

    line 1:155 no viable alternative at input '.'
   
My question is - How can I insert String value in blob data type? Here record_value is blob data type and I am having string value in it.

[Sylvain Lebresne]

Have you looked at the CQL query you're generating above? You're trying to concatenate a ByteBuffer to your string query, but
ByteBuffer does not have a specific toString() (so it will print something like "java.nio.HeapByteBuffer[pos=0 lim=0 cap=0]" which is
obsiously not valid CQL).

 

   
My question is - How can I insert String value in blob data type? Here record_value is blob data type and I am having string value in it.

If you want to manually build a query string, you'll have to convert your blob value to hexadecimal since that's how blob literals are inputed

in CQL query. Long story short, with the driver you can simply replace your
  ByteBuffer.wrap(rec.getRecord_value().getBytes(Charset.forName("UTF-8")))

above by

  Bytes.toHexString(rec.getRecord_value().getBytes(Charset.forName("UTF-8")))

That said, there is a fair change that using prepared statements would be faster here, and it would save you from having to serialize your blobs
in hexadecimal (which is obviously not terribly efficient).

--

Sylvain

[allan.c...@gmail.com]

Hi Sylvain. Thanks for your response. However, Bytes.tohexString returns type String, and then you get this exception on trying to insert:

Exception in thread "main" com.datastax.driver.core.exceptions.CodecNotFoundException: Codec not found for requested operation: [blob <-> java.lang.String]

Here's my code (comments removed for brevity):

BatchStatement batchStatement = new BatchStatement();

        Insert insert = QueryBuilder

                .insertInto("detail_metadata_by_comm_uuid_stage_code")

                .value("comm_uuid", bindMarker())

                .value("processing_stage_code", bindMarker())

                .value("feeder_system_message_dropoff_timestamp", bindMarker())

                .value("key_id", bindMarker())

                .value("details_payload", bindMarker()); // this is the DataStax Cassandra blob column

        PreparedStatement detail_metadata_by_comm_uuid_stage_code_insert_ps = session.prepare(insert.toString());

        

        BoundStatement boundStmt = detail_metadata_by_comm_uuid_stage_code_insert_ps.bind

            (

           comm_uuid,

            processing_stage_code,

            feeder_system_message_dropoff_timestamp,

            key_id,

            Bytes.toHexString (

            details_payload.getBytes(Charset.forName("UTF-8"))

                                                                // details_payload is of type String

            )

            );

            

            batchStatement.add(boundStmt);

            

            session.execute(batchStatement);

 

Thoughts?

[Andrew Tolbert]

Hi Allan,

Bytes.toHexString(details_payload.getBytes(Charset.forName("UTF-8"))

Sylvain's advice was about providing blobs in raw CQL strings. In that context, he is correct in that you would want to use Bytes.toHexString to convert a String to a hexidecimal String representation of the String's bytes.

However, in your context, you are using Prepared & BoundStatements.  Here the driver knows what the underlying CQL types are, so therefore you need to provide the appropriate java type (ByteBuffer).

If you used:

ByteBuffer.wrap(details_payload.getBytes(Charset.forName("UTF-8"))

This should fix the issue as now you are providing a ByteBuffer for details_payload column, which is a cql blob.

Thanks,

Andy