DBI using DBD::ODBC on MS SQL 2008 R2 corrupting Unicode characters

[1DMF]

Hi,

I have found that using DBI with the DBD::ODBC driver for Windows is corrupting Unicode characters in nvarchar defined columns.

I did a test using Win32::ODBC and I don't get the Unicode corruption.

Is there a switch or flag or parameter I need to use when utilising DBI on Windows to stop this Unicode corruption?

I use the following to access the DB (not including the connection!)

    # Set Long Read (required for DBI & ODBC!)
    $self->dbh->{LongReadLen} = 99999; 
    
    # Run SQL Command
    my $sth = $self->dbh->prepare($sql) || die('Error in getSQL (' . $self->server . "): $sql");

    $sth->execute();
   
    # Declare array
    my @rs;
    
    # Loop SQL & build recordset
    while (my $ref = $sth->fetchrow_hashref()) {
        # Build Array of Hashes with SQL Data
        $rs[@rs] = \%$ref;	
    }  
        
    # Close DB Connection
    $sth->finish();

    # Disconnect from the database.
    $self->dbh->disconnect();
            
    # Return record set
    return @rs;

Any Unicode character retrieved seems to get corrupted into an unrecognised 'wide character'.

If I use the same logic but with Win32::ODBC module...

# Run SQL Command
if(!$db->Sql("$sel")) {

	# Loop SQL Record Set
		while($db->FetchRow()){ 
				# Build Array of Hashes with SQL Data
				my %dt = $db->DataHash();
			    $rs[@rs] = \%dt;	
		}
	# Close DB Connection
	$db->Close();

	# Return Record Set Array of Hashes
	@rs;

} 
else{die "Error in getSQL ($sel)" . Win32::ODBC::Error();}

...the Unicode chars are fine?

I know I could simply go back to Win32::ODBC, but this is being run with Catalyst, and the community prefer DBI, so was hoping to find a resolve with DBI, especially as I intend at some point to move to the DBIC ORM, which won't be an option if DBI on Windows can't handle Unicode characters properly.

Your help is appreciated.

Regards,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads