#278 mssql does not precede Unicode strings with N

[Andrew Ziem]

I am having trouble getting Unicode (Korean, for example) to work with mssql. Even though sqlobject made the table varchar (bug#277), I changed it myself to nvarchar. Then I connected with a URI debug=1 and noticed the INSERT statement does not precede string literals with N as described here:

"You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server"
http://support.microsoft.com/kb/239530

So this ticket is specifically with regards to the strings like N'foo'

I am using Python 2.7.3 32-bit, Windows 7, sqlobject 1.3.2, adodbapi 2.4.2.2

[Oleg Broytman]

SQLObject is at most unicode-aware, not unicode-based. UnicodeCol works this way:

• UnicodeCol fetches strings from backend (str from DB API driver), converts them to unicode and pass the unicode strings to the program.
• In the other direction UnicodeCol accepts unicode strings from the program, converts them to strings in the preferred encoding (usually UTF-8 but can be configured) and pass the strings to the DB API driver.

At the time SQLObject generates INSERT or UPDATE query it doesn't remember from what columns the values has been generated and all values are strings, not unicode. I have to think if it's possible to implement what you want.

[Andrew Ziem]

Thanks. What I am hoping to do is insert the data (tweets from Twitter) from multiple human languages into SQL Server so a variety of non-Python tools, such as R and SAS, can read the data without any trouble.

[Andrew Ziem]

For what it's worth, SQLAlchemy has a Unicode data type that I tested and works as expected.

http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.Unicode

[Oleg Broytman]

I think I can create code similar to what I descibed in the mailing list: https://sourceforge.net/mailarchive/message.php?msg_id=30290656

UnicodeValidator would return a value whose __sqlrepr__ would return a N''-quoted string.

[Oleg Broytman]

Fixed in the revision 4585. Thank you for your help!