A puzzled garbage character issues by use Perl-DBI

[pysome]

Hi Monkers,
I have use the DBD::Sybase one year or so. That's nice to work to connect SqlServer.
But recently, i came across a strange garbage issues. pls help me to check.

I wanna insert a Asian characters' html content into SqlServer ,
if i do it like this, it work nicely.

$some_content = 'so long Asian character (maybe it contain single quot
+ation)';
$sql = "insert into test_table(field) values ($dbh->quote($some_conten
+t))";
$dbh->do($sql); # you know, this works.

But if i do it by another way, i get some garbage character in test_table:

$some_content = $dbh->quote('so long Asia character (maybe it contain 
+single quotation)');
$sth = $dbh->prepare("insert into test_table(field) values (?)");
$sth->execute($some_content);
$sth->finish();

What's wrong with the second method? Or it is a $dbh->quote bug in Asian character?
Thanks in advance!

[lostjimmy]

I believe you have things backwards. In your first example, you want to use $dbh->quote(), because if you have single quotes in your content, you could run into some security vulnerabilities. In your second example, you do not need to quote because placeholders will automatically (and appropriately) quote any content. Try removing the call to quote and see if it makes a difference.

[Anonymous Monk]

your first example is wrong, you need to quote, which placeholder will do.

[afoken]

If you mean Unicode with "Asian character", make sure every component knows that you are using Unicode. Perl should set the utf8-Flag on all strings containing Unicode, the DBI driver should know that your data may contain Unicode both while storing and while fetching, and your database should know that as well.

• DBD::Pg has the pg_enable_utf8 connect attribute
• DBD::ODBC must be compiled to support Unicode (default on on Win32, default off on any other platform)
• DBD::Oracle needs Environment variables (NLS_NCHAR=AL32UTF8) and at least Oracle 9.2
• DBD::mysql has mysql_enable_utf8

The Unicode setup of the database is very different from RDBMS to RDBMS. Oracle and PostgreSQL store Unicode in any CHAR or VARCHAR/VARCHAR2, MS SQL Server needs NVARCHAR or NCHAR instead of VARCHAR or CHAR, and MySQL -- I don't know. I prefer RDBMSes with less surprises.

See also the tests t/40UnicodeRoundTrip.t and t/41Unicode.t in DBD::ODBC

Alexander

[pysome]

Thank you very much.

Now the core of problem is that the garbage display by using
method two. I means only some little character is garbage and
other remained characters still display normally .
Database is MSSQL and the column is varchar(8000) type.
(in order to store Asain HTML's content) Regards

[pysome]

update