http://qs321.pair.com?node_id=731442

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

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!

Replies are listed 'Best First'.
Re: A puzzled garbage character issues by use Perl-DBI
by lostjimmy (Chaplain) on Dec 19, 2008 at 15:38 UTC
    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.
Re: A puzzled garbage character issues by use Perl-DBI
by afoken (Chancellor) on Jun 17, 2009 at 12:18 UTC

    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.

    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

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      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

Re: A puzzled garbage character issues by use Perl-DBI
by Anonymous Monk on Dec 19, 2008 at 05:14 UTC
    your first example is wrong, you need to quote, which placeholder will do.
Re: A puzzled garbage character issues by use Perl-DBI
by pysome (Scribe) on Jun 17, 2009 at 08:06 UTC
    update