Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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.


Comment on Re^2: DBI, unicode, ms sql server
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/README.af

    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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://742206]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (12)
As of 2015-07-29 08:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (261 votes), past polls