Extracting data from Postgres database to XML via Perl - encoding issue

Question

I have a Postgres database encoded in UTF-8. I am using Perl with the following modules:

use DBI
use XML::Generator::DBI
use XML::SAX::Writer

to extract data in the Postgres database to an XML file using a query, i.e.:

use DBI;
use XML::Generator::DBI;
use XML::SAX::Writer;

my $dbh = DBI->connect("dbi:Pg:dbname=postgres;host=MYHOST;port=2278",
                      username,
                      password,
                      {RaiseError => 1},
                     );

my $handler = XML::SAX::Writer->new( Output => 'foo.xml' );

my $generator = XML::Generator::DBI->new(
 Handler => $handler,
 dbh     => $dbh,
 Indent  => 1,
);

$select = qq(

!!!!SQL QUERY!!!!!

);

$generator->execute(
                     $select, 
                     undef,
                     RootElement => 'root',
                        );

This works well and I get a valid XML document as a result. My problem is that some of the data in the database is binary - i.e. there are non UTF-8 characters in there. When this occurs, XML::Generator::DBI detects this and outputs the data as follows in the XML file:

<foo dbi:encoding='base64'>VGhpcyByZXBvcnQgbGlzdHMgYWxsIGZpbGVzIGhhdmluZyBhY2Nlc3NlcyB0byBkYXRhYmFzZSB0
YWJsZXMuDQpJdCBwcm92aWRlcyB0aGUgZm9sbG93aW5nIGluZm9ybWF0aW9uOiAgRmlsZSBmdWxs
IG5hbWUsIGFjY2Vzc2VkIHRhYmxl
</foo>

The namespace is also bound to the URL http://axkit.org/NS/xml-generator-dbi. This is correct behaviour according to the module documentation, but what I want to know is: is it possible to somehow transform this base64 encoded string into UTF-8 so that i can actually use it?

I'm no Perl expert at all, nor Postgres for that matter, so go easy! many thanks.

FalseVinylShrub · Accepted Answer · 2010-12-09 16:29:45Z

1

I imagine you can decode the Base64 using MIME::Base64... But it depends what the data is. If there are non-UTF-8 characters in, what are they? You need to know what to transform from.

answered Dec 9, 2010 at 16:29

FalseVinylShrub

1,2139 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JamesH Over a year ago

Thanks for your reply. Didn't know about the module - thank you (not sure how to go about intergrating that yet but will look into it). The data that is coming out as base64 is typically code samples, so for example, Java, C++ coding samples that have been stored in a table - but not necessarily so. For the example in my question, this translates to: This report lists all files having accesses to database tables. It provides the following information: File full name, accessed table" - so I guess it must be the : that is causing the base64 output?

JamesH Over a year ago

Just expanding on what the data actually is. The example I provided in my question is actually stored as this in the Postgres database: "This report lists all files having accesses to database tables. It provides the following information: File full name, accessed table" I.e. I believe there is a carriage return in there after tghe first full stop. In this example, this must be why base64 is used as ":" is a UTF-8 allowed character and should not cause the use of base64...

JamesH Over a year ago

Further... I looked up the script that creates this data. here is what is contains: This report lists all files having accesses to database tables.!xD!``!xA!It provides the following information: File full name, accessed table

FalseVinylShrub Over a year ago

OK, well I'm not sure why it's translating it to Base64. Carriage return is a perfectly valid character in UTF-8. Are you sure XML::Generator::DBI is the best module? How about using plain DBI and plain XML::Generator, and doing the bit in the middle yourself? I've not got much time to devote to this, but if you're happy with this pace, keep commenting and I'll update the answer when I get a clearer idea ;-)

Dave Gray · Accepted Answer · 2014-04-04 20:34:48Z

1

If by "binary", you mean that the column type in PostgreSQL is bytea, then you could either:

Set bytea_output=escape on $dbh, like so:

$dbh->do('SET bytea_output=escape');
Cast the column with type bytea to text in your query:

SELECT bytea_column::text FROM ...

I doubt either of those will be exactly what you want to end up with. Hopefully that will get you going in the right direction.

answered Apr 4, 2014 at 20:34

Dave Gray

7235 silver badges11 bronze badges

Collectives™ on Stack Overflow

Extracting data from Postgres database to XML via Perl - encoding issue

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related