How to set encoding for database connection with MS Access

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi ! Could you help me with following situation? I want to read data from MS Access database (*.mdb) via dbi and odbc. Everything is working fine. But some characters (like fractions - 1/2) are being read as '?' signs. It's strange, because fractions should be in ISO/IEC 8859-1 codepage for sure. I'm using Windows XP with English (UK) as default language, ActiveState ActivePerl 5.8.6, dbd-odbc package, MS Acess 2003 and 2007. Code:

my $dbh = DBI->connect("dbi:ODBC:testDSN", undef, undef);
            
$dbh->{LongReadLen} = 65000;
$dbh->{LongTruncOk} = 0;

my $sql = "select * from $table_name";
my $table_records = $dbh->selectall_arrayref($sql) or die "cannot do "
+ . $sql;

#...

$dbh->disconnect;
[download]

How can I "tell" Access to use ISO 8859-1 ? Thanks in advance, Oleg

Comment on How to set encoding for database connection with MS Access Download Code

Replies are listed 'Best First'.
Re: How to set encoding for database connection with MS Access by moritz (Cardinal) on May 07, 2008 at 13:10 UTC
I don't know how to configure Access, but perhaps I can help a bit nonetheless. Most databases are stored in one character encoding, which is defined in the database configuration (normally per database). If you use DBI from perl, it usually just returns the bytes that the databases emit. Some drivers (like DBD::Pg and DBD::mysql) have options that take care of automatic decoding, which means that DBI calls will return text strings rather than byte strings. If your DBD:: module doesn't support that, you have to use Encode to handle the character encodings yourself.	[reply]
Re: How to set encoding for database connection with MS Access by Anonymous Monk on May 07, 2008 at 16:28 UTC
Thanks a lot, Moritz, But it seems that I get broken characters from Access. i.e. field, that contains "1 1/2", on the perl side looks as "1 ?". So probably ODBC driver gets this string in wrong encoding...	[reply]