VARCHAR or NVARCHAR?

[Ray (a.k.a. Iceberg)]

Hi there,

Long story short, I am now working in a project with web2py trying to connect to a MSSQL database. My team tends to use the connect string as "mssql4://..." to take the advantage of the (latest and greatest?) MSSQL4Adapter, however we also notice from the dal source code mssql.py that, MSSQL4Adapter defines "string" as "VARCHAR", while an older MSSQL2Adapter defines "string" as "NVARCHAR".

It seems there is some other opinion in favor of "NVARCHAR" for internationalization reason. But then why the web2py MSSQL4dapter chooses "VARCHAR" instead of "NVARCHAR"? Any insight?

Thanks in advance!

Regards,

Ray

[Paolo Valleri]

mssql adapters are quite messy.

MSSQL2Adapter is the adapter that uses 'nvarchar' as type, however it inherits from MSSQLAdapter, given that it doesn't have the features of MSSQL4Adapter.

For backward compatibility I don't think MSSQL2Adapter will be never updated to inherits from MSSQL4Adapter.

My suggestion is to create your own adapter, something like that should work

from pydal.adapters import ADAPTERS, MSSQL4Adapter, MSSQL2Adapter

class MyMSSQLAdapter(MSSQL2Adapter, MSSQL4Adapter):

    pass

ADAPTERS.update( {

    'mymssql': MyMSSQLAdapter

})

db = DAL('mymssql://user:pass@host/database')

Your new adapter will have the types of MSSQL2Adapter, the methods of both.

Paolo

[Massimo Di Pierro]

yes but be aware this will affect all the applications. not just this one.

[Niphlod]

I'm on top of this "issue" in these days, but in the end we will just have to create yet another adapter (maybe a mssqlu , mssql3u and mssql4u).

The problem is subtle, and goes pretty unnoticed.

If - as I - you create an app with mssql: (or mssql3, or mssql4) which have "varchar" type the odbc underlying mappings store happily the utf8 representation when needed and they fetch it back. Sure, it's garbled but from web2py's standpoint what you insert is what you retrieve, so no problems there. Bonus points of messiness because on linux there's often another middleware involved (freetds), which does the conversion implicitely without telling a soul about it.

The problem arises with existing databases when trying to store and fetch from nvarchar values, that are NOT inherently converted to/from unicode utf8. They need to be passed as u'something' and you'll get back a unicode. No str whatsoever.

Still, web2py by default SHOULD use nvarchar fields for "modern" applications, to enable integration with existing databases or just letting other applications read correctly the data stored in 'string' fields.