You can translate the question and the replies:

BLOB Field - [BINARY DATA] - Help Displaying field

Hello, I have a view I created that is pulling its data from tables in a postgreSQL. One of the fields has the output type of "BLOB" and in the field...instead of the values, "[BINARY DATA]" is shown. When I would come across this issue in the past in greenplum i would type in the follow syntax: pg_catalog.encode(pg_catalog.decode(encode(FIELD NAME, 'escape'), 'hex'), 'escape') . Using this syntax would update the BLOB field to show me the actual text in the field. Can you please advise how I can update the output of my BLOB fields to actually show the text in the field instead of "[BINARY DATA]" Thanks
BLOB BINARY
user
13-11-2018 16:10:49 -0500
code

5 Answers

Hi, In your case you can do a cast from blob type to text type with Denodo. The syntax is: ``` CAST( <vdp data type:text>, <value:expression>) ``` For more information you can have a look at the section [CAST](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast) of Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
14-11-2018 05:36:52 -0500
code
Hello, Thanks for the information. I gave the cast a try and it worked. I noticed however when I would run my report for a different dates that the cast wouldn't work, but in those cases instead of saying BINARY the return would just show a bunch of random numbers and letters. Have you ever come across this before when the cast would work for certain dates but not others? If so can you pease advise what to do. Thanks
user
14-11-2018 14:56:54 -0500
Hello, Since the cast did not work I tried the follow by creating my base view with a query using the logic below to translate the field into readable text... pg_catalog.encode(pg_catalog.decode(encode(cscf_text, 'escape'), 'hex'), 'escape') AS OUTTXT... this works at times but then I will receive the error below... 'ERROR: invalid hexadecimal digit: "p" (seg3 slice1 172.28.8.1:1028 pid=654942)' can you advise how to proceed as this issue is preventing us from moving foraward with a vital report. thanks
user
16-11-2018 10:42:01 -0500
Hello, Since the cast did not work I tried the follow by creating my base view with a query using the logic below to translate the field into readable text... pg_catalog.encode(pg_catalog.decode(encode(cscf_text, 'escape'), 'hex'), 'escape') AS OUTTXT... this works at times but then I will receive the error below... 'ERROR: invalid hexadecimal digit: "p" (seg3 slice1 172.28.8.1:1028 pid=654942)' can you advise how to proceed as this issue is preventing us from moving foraward with a vital report. thanks
user
16-11-2018 10:42:02 -0500
Hi, I saw that the error comes from PostgreSQL so I would suggest you to execute the same query in PostgreSQL using a JDBC client to analyze the origin of the issue. Hope this helps!
Denodo Team
23-11-2018 04:00:38 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here