×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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!)

CODE

# 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...

CODE

# 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close