Inserting unicode characters

Hi,

TS 6.7.1 SP1
Perl 5.8

I trying to insert unicode characters to a db through a SP call from a perl script.

I tried the SP call bellow and it inserted: ÐšÐ¾Ð½Ñ�ÑƒÐ»ÑŒÑ‚Ð°Ñ†Ð¸Ð¾Ð½Ð½Ñ‹Ðµ ÑƒÑ�Ð»ÑƒÐ³Ð



dbo.Mon_InsertDeployedContent 4 ,3 , '11122007153257871', 'Открытое письмо генеральному директору'

So I added N, see bellow and still inserted the same



dbo.Mon_InsertDeployedContent 4 ,3 , '11122007153257871', N'Открытое письмо генеральному директору'

However, when I run the first piece fo code in sql management studio, it inserts ?????????????????????, but when I run the other, with the N in front, it inserts the unicode correctly. So it might possibly be that I'm missing something when I call the SP from the perl scritp?


#Perl version of the SP call

if(!($conn->Sql("exec dbo.Mon_InsertDeployedContent $geoSiteId, $workareaId, '$contentId', '$title'))) {    
     debug("$dbgInfo", " SQL procedure executed successfully .\n");   

    } else {
     callBackAndDie($task, 1, "SQL procedure failed.");
    }  


The other version is:

if(!($conn->Sql("exec dbo.Mon_InsertDeployedContent $geoSiteId, $workareaId, '$contentId', N'$title'))) {    
     debug("$dbgInfo", " SQL procedure executed successfully .\n");   

    } else {
     callBackAndDie($task, 1, "SQL procedure failed.");
    }

Any suggestions?

Thanks in advance.

Find more posts tagged with

Comments

ISCBorisB

I [am] trying to insert unicode characters to a db through a SP call from a perl script...

I do not think it's Perl issue. What is your DB? What is the target Column(s) Data Type?

Note that some DBs have char, varchar and so on as one byte characters, for Unicode types you use nchar, nvarchar
and the like. Further, single leading 'N' (stands for 'National') can be a required Unicode Syntactical marker for the Insert Argument.
Note that it's just an indicator, not a code conversion Directive.

Note also that in this context "Unicode" is a loaded Term. DB may require UTF-8 or UTF-16LE or USC-2, etc

cdoloriert

I do not think it's Perl issue. What is your DB? What is the target Column(s) Data Type?

SQL server 2005, column data type nvarchar

Note that some DBs have char, varchar and so on as one byte characters, for Unicode types you use nchar, nvarchar
and the like. Further, single leading 'N' (stands for 'National') can be a required Unicode marker for the Insert Argument.
Note that it's just an indicator, not a code conversion Directive.

What can I use for conversion directive then?

Thank you in advance for your help.

ISCBorisB

SQL server 2005, column data type nvarchar

What can I use for conversion directive then?

Thank you in advance for your help.

Unicode data can be encoded in several formats. I *believe* that SQL Server uses UCS-2 (2-byte Universal Character Set),
you may want to double-check that with your SQL Server Product Documentation.

It is your (the Developer) responsibility to translate the data to and from the encoding required by your Data Base.
Run 'perldoc Encode' to see an API of one of the Modules that may help you to do that in Perl. (Since IWOV keeps DCRs
encoded in UTF-8 in the TeamSite case transcoding often means "To/From UTF-8")

oduiubo

Definitely nothing wrong with Perl at all.

I had similar task with MSSQL and character set Latin-1.

I did first of all checking string encoding for UTF-8. If examinated string is not UTF-8 convert to UTF-8. After that you have all your required data in one format. Then you just convert to desirable format before insertion or updates SQL statements.
I also at conversion level I handled Microsoft special characters and convert it as entities.

cdoloriert

Then you just convert to desirable format before insertion or updates SQL statements.

I also at conversion level I handled Microsoft special characters and convert it as entities.

I've done what you mentioned, but what do you mean by the last 2 sentences?

How can I convert to the desirable format when I call the SP within the perl script?

Thanks in advance.

nipper

perldoc HTML::Entities

cdoloriert

perldoc HTML::Entities

I tried it and I got the following

[PHP]
Тонкий баланс между риском и вознаграждением

[/PHP]

I want to get this:

Тонкий баланс между риском и вознаграждением

in the db as well

cdoloriert

Hi,

I attached part of the PERL code, when the perl reads from the xml file as utf-8 until the stored procedure is executed.

Please could you take a look at it?
Any suggestions?

Thanks in advance.

ISCBorisB

Hi,

I attached part of the PERL code, when the perl reads from the xml file as utf-8 until the stored procedure is executed.

Please could you take a look at it?
Any suggestions?

Thanks in advance.

It *seems* that 'Title' is the only Unicode-dependent element in your Code Fragment.

The way it's written, you are inserting (trying to?) UTF-8 Octets as the corresponding column Value.
Is it what you want? Does your DB support UTF-8?

cdoloriert

Hi,

That's what I'm trying to do. the DB is sql server 2005 and it supports unicode.

Any other suggestions?

Thanks

cdoloriert

Hi,

I can see the PERL handles unicode characters properly, but when ODBC executes the stored procedure is when the unicode characters change to gibberish.

Any suggestions on ODBC unicode support?

Thanks

ISCBorisB

Hi,

I can see the PERL handles unicode characters properly, but when ODBC executes the stored procedure is when the unicode characters change to gibberish.

Any suggestions on ODBC unicode support?

Thanks

Please note that your phrase "the DB is sql server 2005 and it supports unicode" does not mean much.

There are several Unicode Formats, popular ones are UTF-8, UTF-16LE and UTF-16BE, UTF-32, and so on.
Last time I had to deal with it ( I *believe* it was SQL sever 2005, but I may be wrong ) we had to
encode/decode our data because that SQL Server Version supported UCS-2 Unicode Format (older brother of UTF-16)
and did not support UTF-8 (hence my previous question, "Does your DB support UTF-8?")

If that's the case, you yourself would have to convert UTF-8 to UCS-2 before storing and then again to whatever
you need after retrieving Data from DB

cdoloriert

Hi,

There are several Unicode Formats, popular ones are UTF-8, UTF-16LE and UTF-16BE, UTF-32, and so on.

You are right there are several Unicode formats.

Last time I had to deal with it ( I *believe* it was SQL sever 2005, but I may be wrong ) we had to
encode/decode our data because that SQL Server Version supported UCS-2 Unicode Format (older brother of UTF-16)
and did not support UTF-8 (hence my previous question, "Does your DB support UTF-8?")

The database supports UTF-8, but I think it's something else cause as soon as the ODBC connection calls the SP, it passes unicode characters in the bad format.

However, I fixed this issue by switching to ADO instead of ODBC. I created the connections and SP calls and now it passes the title in the correct Unicode format.

Thanks for all your responses