PERL XML to SQL : Problem with Special characters

[defdefdef]

Hi,

I'm extracting data from XML to a MySQL database with XML::Xpath (PERL). It works fine now (thanks again for your help) but I'm loosing special characters in the process. French character 'é' becomes a 'é' in the associated database field.
Ex: XML -> 'Mon trésor' ===> MySQL -> 'Mon trésor'

Have any idea to avoid this problem during the process. ??

Best Regards
Defer

 

[stevexff]

Defer

Don't know much about MySQL, but in SQL Server 2000 you have to set the columns up as NCHAR or NVARCHAR to allow Unicode support, otherwise you get the effect you are seeing.

 

[defdefdef]

stevexff,

Thanks for your help, but the columns up is already set as NVARCHAR.
In Fact, the problem is probably not with the database cause if I enter the string manually (Paste) in the field, there is no trouble with the characters.
It seems that I loose my special characters during the transfer XML -> MySql ???

Best Regards
Defer

 

[stevexff]

Defer

What version of perl are you using? 5.8 is a lot better than 5.6 for Unicode support.

Also, can you post the code that does the insert/update of the DB?

 

[PaulTEG]

what version of DBI?
--Paul

cigless ...

 

[defdefdef]

Hi,

Finally the problem is upstream.
Before the process XML -> MySQL.
I use LWP::Simple to retrieve the XML file and store the XML String in a variable $document and then extract the data from $document with XML::Xpath.
The Problem is : the Special characters are missing in $document !!!
Any idea how to avoid this ??

my code : (Perl 5.8)

..............
use strict;
use warnings;

use LWP::Simple;


my $link = "

http://www.popo.com/file.xml";

my $document = get($link)
or die "Couldn't download";

use DBI;
use XML::XPath;


my $dbh = DBI->connect('dbi:mysql:mydb', 'root',
'') || die $DBI::errstr;
my $xp = XML::XPath->new(xml => $document);
........

Best Regards
Defer