Re: Unicode insert into mssql from perl DBD

The problem may involve your method of querying data back from the table, and/or displaying the query results, as well as (or instead of) your method of inserting data into the table. It may also involve how the table is defined and how the database server is configured.

What are you using to view the table contents after the insertion? If you use a perl script, there's a chance that you can inspect the byte sequence as it comes out of the table, and that you can use the Encode module to make sure it gets interpreted correctly for use in a particular display.

I don't know about MSSQL, but I know that for MySQL, there are parameters at various levels for managing the character encoding -- for the server itself (set at start-up), for a given database, for a given table, for given fields in the table. If MSSQL provides docs on character encoding and collation, you'll need to consult that.

The particular example you give is puzzling (and I'm not sure whether the posted data accurately reflects your actual data). You might want to look at the input and output via a hex-dump tool. (You might also consider this tool, posted at the Monastery as tlu -- TransLiterate Unicode, which is helpful for referring to characters by their unicode "names" and hex codepoint values. That's the best way to talk about the data in cases like this.)

Comment on Re: Unicode insert into mssql from perl DBD

Replies are listed 'Best First'.
Re^2: Unicode insert into mssql from perl DBD by Anonymous Monk on Jan 02, 2008 at 15:02 UTC
graff, thanks for the comment. If only I could use mysql on this project.... You're right about the "example", which was more obfucsating then helpful, I admit. The way you "tell" MicroSoft SQLserver that data is Unicode is by way of the datatype, strangely enhough. Whereas in mysql any text or (var)char datatype can have e.g. a utf8 COLLATION, in MSSQL you'd use nvarchar (instead of varchar) for storing Unicode data. I am using the so called MSSQL Management Studio Express to actually see the data in the tables. I can paste 'real' Unciode data into it through the interface, as I can run an INSERT-statement with the N'unicodedata' syntax, and it works fine. The problem is somewhere in ODBC/DBD driver, there the fact that the data is to be interpreted as Unicode is lost... Grtz=JPvdV	[reply]
Re^2: Unicode insert into mssql from perl DBD by nikosv (Deacon) on Jan 05, 2008 at 21:26 UTC
I had a similar problem with mysql.Although the format of the table was in iso-8859-7 when passing the string from the script which had the IO layers switched to 8859-7 the data was not passed in correctly. I used the Encode module and worked.here is a the actual code `foreach $key (keys %fileindex) { my $val = encode("iso-8859-7", $fileindex{$key}); $sth = $dbh->prepare ('INSERT INTO products4 (p_url,p_desc) VALUES(?,?)'); $sth->execute ($key,$val); $sth->finish ();` [download] check the utf-8 flag of the Encode module maybe that helps	[reply] [d/l]