CODEWITHSUNDEEP
db2db2-luw
user1060500
user1060500

Reputation: 1535

Convert a BLOB to VARCHAR instead of VARCHAR FOR BIT

I have a BLOB field in a table that I am selecting. This field data consists only of JSON data.

If I do the following:

Select CAST(JSONBLOB as VARCHAR(2000)) from MyTable

--> this returns the value in VARCHAR FOR BIT DATA format.

I just want it as a standard string or varcher - not in bit format.

That is because I need to use JSON2BSON function to convert the JSON to BSON. JSON2BSON accepts a string but it will not accept a VarChar for BIT DATA type...

This conversation should be easy.

I am able to do the select as a VARCHAR FOR BIT DATA.. Manually COPY it using the UI. Paste it into a select literal and convert that to BSON. I need to migrate a bunch of data in this BLOB from JSON to BSON, and doing it manually won't be fast. I just want to explain how simple of a use case this should be.

What is the select command to essentially get this to work:

  Select JSON2BSON(CAST(JSONBLOB as VARCHAR(2000))) from MyTable

--> Currently this fails because the CAST converts this (even though its only text characters) to VARCHAR for BIT DATA type and not standard VARCHAR.

What is the suggestion to fix this?

DB2 11 on Windows.

Upvotes: 0

Views: 3715

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

You can use this function to remove the "FOR BIT DATA" flag on a column

CREATE OR REPLACE FUNCTION DB_BINARY_TO_CHARACTER(A VARCHAR(32672 OCTETS) FOR BIT DATA)
    RETURNS VARCHAR(32672 OCTETS)
    NO EXTERNAL ACTION
    DETERMINISTIC
BEGIN ATOMIC
    RETURN A;
END

or if you are on Db2 11.5 the function SYSIBMADM.UTL_RAW.CAST_TO_VARCHAR2 will also work

Upvotes: 0

Charles
Charles

Reputation: 23783

If the data is JSON, then the table column should be CLOB in the first place...

Having the table column a BLOB might make sense if the data is actually already BSON.

Upvotes: 1

Steve
Steve

Reputation: 131

You could change the blob into a clob using the converttoclob procedure then you should be ok.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055119.html

Upvotes: 0

Related Questions