Question

bytea::text sqlCreator differs from Pgadmin

T

We store a large number of files in a Bytea type as base64 ecoded strings in our database. If i use the pgAdmin to retrieve the data as Text it works fine.

If you excecute the statement below in PgAdmin you get a different result then when you run it in sqlCreator to a Postgres database.

How do i setup FME to get the same results as PgAdmin?

What am i missing here?

select ('\\x55457344424251414141414941474e7a4b6b3535706a744957514141414873414141414b414141414d584a6c593256774c6e4a6a6343584679776d414d4252453062316744314f427650796553545642564e796f415546527133636b44476675666d416674686c33486b7335384e514d31354a6677565332764a355954304f5748486b4b704e5254704e5132677567546a427276416d7a534b414c704e495a6b4453774571727736352f393855457342416a3841464141414141674159334d71546e6d6d4f30685a414141416577414141416f414a41414141414141414141674141414141414141414446795a574e6c634335795933414b4143414141414141414145414741444956446774364b6a55415276594e43336f714e5142473967304c65696f3141465153775547414141414141454141514263414141416751414141414141'::bytea)::text as test

 

 

 

 

2 replies

david_r
Rank
Userlevel 4

FME does not know what to do with a bytea sequence, you'll have to cast it to something else before FME gets to it. 

Have you tried something like:

select encode(('\x5545734...141'::bytea), 'base64') as test

If it works as intended, it should be possible to use a BinaryDecoder set to "Base64" to decode the binary.

T

Thanks for your response, but i had already tried this. The string i put into the bytea is already encoded to base64 and looks like (when i inspect when inserting it into our database)

'UEsDBBQAAAAIAGNzKk55pjtIWQAAAHsAAAAKAAAAMXJlY2VwLnJjcCXFywmAMBRE0b1gD1OBvPyeSTVBVNyoAUFRq3ckDGfufmAfthl3Hks58NQM15JfwVS2vJ5YT0OWHHkKpNRTpNQ2gugTjBrvAmzSKALpNIZkDSwEqrw65/98UEsBAj8AFAAAAAgAY3MqTnmmO0hZAAAAewAAAAoAJAAAAAAAAAAgAAAAAAAAADFyZWNlcC5yY3AKACAAAAAAAAEAGADIVDgt6KjUARvYNC3oqNQBG9g0Leio1AFQSwUGAAAAAAEAAQBcAAAAgQAAAAAA'

and when i use your solution i get a different string. (probably double encoded)

The problem is that i expect to get the same result from Postgres (pgadmin) aswel with SqlExecutor.

And I found the problem. Appearantly Postgres has a bytea ouput setting that isn't used by FME sqlExecutor. So i added :

set bytea_output = 'escape';

select calculation_point_content::text, calculation_point_content_text from am_calculation_point_sets

where calculation_point_set_code = '1recep'

And now i get the desired results.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.