problem with dbi oracle blob

--_009_04AAC626111C2B41BCBB54D144C76AA7010B8629MIRAMAIL10mir ab_
Content-Type: multipart/alternative;
boundary="_000_04AAC626111C2B41BCBB54D144C76AA7010B8629MIRAM AIL10mirab_"

--_000_04AAC626111C2B41BCBB54D144C76AA7010B8629MIRAMAIL10mir ab_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi List,

I'm struggeling with the following:

There is a blob field in the oracle db which contains xml...
I want to read this blob and make a single xml file out of it...
Now when using the code below I get the data out of the blob with parts of =
xml but its all messed up...

¬í^@^Esr^@^Porg.jdom.Element°^]<84>=C3^Q=C3^D:^C^@^CL^@
attributest^@^XLorg/jdom/AttributeList;L^@^Gcontentt^@^VLorg /jdom/ContentLi=
st;L^@^Dnamet^@^RLjava/lang/String;xr^@^Porg.jdom.ContentB=C 3=A1<91>=C3bS<9=
9>G^B^@^AL^@^Fparentt^@^QLorg/jdom/Parent;xppsr^@^Vorg.jdom. AttributeList =
^YZ¨té=C3^@^CI^@^Dsize[^@^KelementDatat^@^U[Lorg/j dom/Attri=
bute;L^@^Fparentt^@^RLorg/jdom/Element;xp^@^@^@^@pq^@~^@^Fsr ^@^Torg.jdom.Co=
ntentList^@^@^@^@^@^@^@^²4^B^@^@xp^@^@^@^Hsq^@~^@^@q^@~ ^@^Fsq^@~^@^G^@=
^@^@^@pq^@~^@^Psq^@~^@^K^@^@^@^Guq^@~^@^^@^S[Lorg.jdom.Conte nt;]=C3+m­=
=C3
N^@^@^@^Hsq^@~^@^@q^@~^@^Psq^@~^@^G^@^@^@^@pq^@~^@^Tsq^@~^@^ K^@^@^@^Auq^@~^=
@^N^@^@^@^Esr^@^Morg.jdom.Text^Oö=C3hw"m<89^B^@^AL^@^Evalueq^@~^@^Cxq^=
@~^@^Dq^@~^@^Tt^@^QmkjjKL565udFGJERdppppq^@~^@^Tt^@^Ksecurit yKeyt^@^@t^@3ht=
tp://www.mondial-assistance.com/ecommerce/schema/w^A^@xsq^@~ ^@^@q^@~^@^Psq^=
@~^@^G^@^@^@^@pq^@~^@^^sq^@~^@^K^@^@^@^Auq^@~^@^N^@^@^@^Esq^ @~^@^Xq^@~^@^^t=
^@^CTSVppppq^@~^@^^t^@^KpartnerNameq^@~^@^\q^@~^@

Etc etc.... Code below...

#!/usr/bin/perl
use warnings;
use strict;
use DBI;

my $db =3D DBI->connect("dbi:Oracle:host=3D????;sid=3D???", "???", "???", {=
RaiseError =3D> 1}) or die "$DBI::errstr";

open XML, ">./xmlfile"
or die "Can't create xml file ($!)";

# Set Max BLOB size
$db->{LongReadLen} =3D 150000;

# Select statement
my $SEL =3D "select xml_message from table where bla =3D 'bla'";

# Prepare select
my $sth =3D $db->prepare($SEL);

# Execute select
$sth->execute();
my @row =3D $sth->fetchrow_array();

print XML "$row[0]\n";

# Disconnect from DB when finished
$db->disconnect if defined($db);



Any help is appreciated!

[cid:blank29.gif]
Marco van Kammen
Applicatiebeheerder
[cid:blank4823.gif]


[cid:blank6784.gif]
Mirabeau | Managed Services Dr. C.J.K. van Aalstweg 8F 301, 1625 NV Hoor=
n
+31(0)20-5950550 - www.mirabeau.nl
[Mirabeau]

[cid:leaf3d6c.gif] Please consider the environment before printing thi=
s email




--_000_04AAC626111C2B41BCBB54D144C76AA7010B8629MIRAMAIL10mir ab_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">

1">

0" width=3D"100%">

or=3D"#ffffff" align=3D"left"> =3D"16"> LOR: #666; FONT-SIZE: 12px"> Marco van Kammen LOR: #666; FONT-SIZE: 12px"> Applicatiebeheerder =3D"8"> lor=3D"#cccccc"> " width=3D"1" height=3D"1"> =3D"8"> LOR: #666; FONT-SIZE: 12px"> Mirabeau | Managed Services &= nbsp;  Dr. C.J.K. van Aalstweg 8F 301, 1625 NV Hoorn +31(0)20-5950550  -   DECORATION: none" href=3D"http://www.mirabeau.nl">www.mirabeau.nl cid:miralogo4ae1.png"> =3D"16" height=3D"16"> SIZE: 12px"> Please consider the environment before printing thi= s email

Re: problem with dbi oracle blob

On Wed, May 25, 2011 at 11:45:13 +0000 , Marco van Kammen wrote:
> Hi List,
>
> I'm struggeling with the following:
>
> There is a blob field in the oracle db which contains xml...
> I want to read this blob and make a single xml file out of it...
> Now when using the code below I get the data out of the blob with parts of xml but its all messed up...
> [nasty encoded data snipped]

Are you certain the column isn't encoded somehow, say with a compression
algorithm or something of that sort? What do Oracle's docs say on the
matter?

> #!/usr/bin/perl
> use warnings;
> use strict;
> use DBI;
>
> my $db = DBI->connect("dbi:Oracle:host=????;sid=???", "???", "???", {RaiseError => 1}) or die "$DBI::errstr";
>
> open XML, ">./xmlfile"
> or die "Can't create xml file ($!)";
>
> # Set Max BLOB size
> $db->{LongReadLen} = 150000;
>
> # Select statement
> my $SEL = "select xml_message from table where bla = 'bla'";
>
> # Prepare select
> my $sth = $db->prepare($SEL);
>
> # Execute select
> $sth->execute();
> my @row = $sth->fetchrow_array();
>
> print XML "$row[0]\n";
>
> # Disconnect from DB when finished
> $db->disconnect if defined($db);

Your DBI code looks reasonable. I would suggest using the three-argument
form of open with a lexical filehandle, though, like so:

open my $xml, '>', './xmlfile' or die ...

This way is safer and more robust, plus $xml is easier to deal with than
the global XML (try passing XML to a function: it's not immediately
obvious how). I can't see this as related to your issue, but it's good
form nonetheless.

--
Chris Nehren | Coder, Sysadmin, Masochist
Shadowcat Systems Ltd. | http://shadowcat.co.uk/

--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/