Re: Convert BLOB into String using SQL

[Suren K <suren7437@xxxxxxxxx>]

Hi Birgitta,

I am using SQL Session in 5250 Emulator to view the data.

For now I am using the following query to view the JSON Data stored in the
file which is working fine
SELECT CAST(SYSTOOLS.BSON2JSON(DATA) AS VARCHAR(1000)) FROM BLOBFILE

I need to find a way to view the normal string (Not a JSON Data) which is
stored in BLOB Field.

Thanks for your hint, for changing the CCSID. I will try some queries to
translate the CCSID.

Regards,
Suren


On Thu, Apr 15, 2021 at 10:58 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> BLOB is a binary string, that's why you see the hex data.
>
> How do you view the data? With ACS?
> If so you need to set the Translate CCSID 65535 in your connection
> (Translation --> Translate CCSID 65535), then the Binary data is displayed
> with your job's CCSID.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Suren K
> Sent: Donnerstag, 15. April 2021 14:27
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Reg: Convert BLOB into String using SQL
>
> Hi All,
>
> I created a file with BLOB Field, where I am inserting data into a file
> using two options, 1) for a valid JSON data I used JSON2BSON, 2) for a
> normal string data I used BINARY(DATA).
>
> When I try to view the data using SQL, I can see the valid JSON data using
> CAST(SYSTOOLS.BSON2JSON(DATA) AS VARCHAR(1000)), but for a normal string
> data I am not able to view the data in character string.
>
> Any suggestions on how to view the normal string data which is stored in
> BLOB Field using SQL?
>
> Regards,
> Suren
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com