Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re^2: DBI, unicode, ms sql server

by zod (Scribe)
on Feb 08, 2009 at 03:38 UTC ( #742206=note: print w/replies, xml ) Need Help??

in reply to Re: DBI, unicode, ms sql server
in thread DBI, unicode, ms sql server

Yeah, I read that already. The gist of it seems to be that unicode is enabled by default on Windows with the latest version, but that utf-8 will be converted to utf-16.

i.e. : "perl scalars which are UTF-8 and are sent through the ODBC API will be converted to UTF-16 and passed to the ODBC wide APIs or signalled as SQL_WCHARs (e.g., in the case of bound columns)."

I didn't see anything in the docs about how to actually get around that, however.

Replies are listed 'Best First'.
Re^3: DBI, unicode, ms sql server
by Anonymous Monk on Feb 08, 2009 at 11:46 UTC

    Don't inline Unicode values (in fact, don't inline ANY values) in your SQL. Always use placeholders, i.e. my $sth=$dbh->prepare("insert into XXX values (?,?)"; $sth->execute("\x{2345}\x{3333}","\x{4711}"); $sth->finish(); or $dbh->do("insert into XXX values (?,?)",undef,"\x{2345}\x{3333}","\x{4711}");. Inline values prevent SQL caching, inline values open security holes (SQL injection), inline values break Unicode.

    See also the second paragraph under "Problems" in DBD-ODBC-1.18/

    Alexander Foken

      Thanks for the info.
Re^3: DBI, unicode, ms sql server
by Anonymous Monk on Feb 08, 2009 at 04:05 UTC
    How do you verify it inserts garbage? MS SQL Server 2008 doesn't support utf8, only utf16, so either DBD::ODBC does the conversion to/from incorrectly, or something else :D
      I verify the garbage by selecting from the table in SQL Server Management Studio. I can insert utf-8 via SSMS without a problem. So it is something related to the ODBC driver through perl. I've also been able to bulk insert utf-8 text from MySQL into this SQL Server db via SSIS -- so it isn't on the sql server end of things.

      So, I'm stumped at the moment...

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://742206]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (6)
As of 2018-04-23 01:11 GMT
Find Nodes?
    Voting Booth?