Hello. I made some progress. I have client charset in my freetds config file. I am also certain that the config and the charset is used by pyodbc / freetds combo. Without it I get 'Ne?...'. With it I get back a str encoded in utf-8. I also enabled freetds logging where I clearly see two conversions being prepared:
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion If I comment the client charset line in the config, I see: iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion I beleive the second one has to do with description_encoding which is 'latin-1' by default (in MSDialect_pyodbc.__init__). However I tried to set it to utf-8, utf-16le and whatnot without any effect (in debugger). I also found out that my problem is not in information_schema per se but with all nvarchar values / columns. For example this u'\U0073006f\U0062006f' is actually a garbled string 'osob': u'\U0073006f\U0062006f' u'\U006f0073\U006f0062' u'\u006f\u0073\u006f\u0062' = u'osob' though I have no idea how and where it comes into existence. Furthermore the correct value is 'osoba' (a table name). I guess the conversion stripped out the odd character. Current state of nvarchar processing in my dev env: tsql OK isql OK pyodbc KO Ladislav Lenart On 9.10.2012 16:19, Michael Bayer wrote: > > On Oct 9, 2012, at 6:03 AM, Ladislav Lenart wrote: > >> Hello. >> >> No, I was not able to connect via pymssql. Furthemore I would like to use >> pyodbc >> if it is the preferred way. I just did not know that when I started with >> pymssql. >> >> It really is a misconfigured character encoding issue in pyodbc / freetds on >> my >> part. I am just clueless as to what should I set to what to make it all work. >> >> The code: >> >> cnxn = >> pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=ZFP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0') >> cursor = cnxn.cursor() >> cursor.execute("select prijmeni from osoba where id_osoba = 462493") >> row = cursor.fetchone() >> print row >> >> prints: >> >> ('Ne?asov', ) > > you definitely, definitely need "client charset" to be part of your FreeTDS > config, either in that URL string up there (not sure if that's supported), or > preferably in your freetds.conf. pyodbc is pretty sensitive to this. > >> However, when I connect via tsql, it works correcly: > > yeah, the whole FreeTDS story is awful, I don't understand any of it either. > I'm still at "wave a dead chicken" stage with FreeTDS > (http://dictionary.reference.com/browse/wave+a+dead+chicken). > > >> >> tsql -S zfp -U efractal >> Password: >> locale is "cs_CZ.UTF-8" >> locale charset is "UTF-8" >> using default charset "utf8" >> 1> select prijmeni from osoba where id_osoba = 462493 >> 2> go >> prijmeni >> Nečasová >> (1 row affected) >> 1> >> >> >> I guess I am on my own now. Anyway, thank you for your kind assistance. >> >> Ladislav Lenart >> >> >> On 8.10.2012 19:06, Michael Bayer wrote: >>> if you didnt have this problem with pymssql then please apply the patch I >>> sent previously. >>> >>> However, I'm going to bet the problem remains as it seems something is not >>> right with how your database and/or client is configured. I'm not familiar >>> with the encoding pattern seen in your information_schema queries. you may >>> have to reproduce the issue using plain SELECT statements against >>> information_schema and then seek outside help. >>> >>> >>> >>> diff -r 17cab4ad55d5 lib/sqlalchemy/dialects/mssql/pymssql.py >>> --- a/lib/sqlalchemy/dialects/mssql/pymssql.py Thu Oct 04 18:26:55 >>> 2012 -0400 >>> +++ b/lib/sqlalchemy/dialects/mssql/pymssql.py Fri Oct 05 18:46:01 >>> 2012 -0400 >>> @@ -80,7 +80,7 @@ >>> def _get_server_version_info(self, connection): >>> vers = connection.scalar("select @@version") >>> m = re.match( >>> - r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers) >>> + r"\s*Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers) >>> if m: >>> return tuple(int(x) for x in m.group(1, 2, 3, 4)) >>> else: >>> >>> On Oct 8, 2012, at 12:10 PM, Ladislav Lenart wrote: >>> >>>> Hello. >>>> >>>> I adjusted the ODBC/FreeTDS condifugration according to your suggestions >>>> but >>>> still get the "NoSuchTableError: mlm_spol". >>>> >>>> freetds.conf: >>>> >>>> [zfp] >>>> host = 10.230.128.140 >>>> port = 1433 >>>> tds version = 8.0 >>>> asa database = ZFP_CRM >>>> client charset = utf8 >>>> text size = 50000000 >>>> >>>> >>>> odbc.ini: >>>> >>>> [ODBC Data Sources] >>>> zfp = test >>>> >>>> [zfp] >>>> Driver = /usr/lib/libtdsodbc.so >>>> Description = test >>>> Trace = No >>>> Servername = zfp >>>> >>>> >>>> odbcinst.ini (for the sake of completeness): >>>> >>>> [FreeTDS] >>>> Driver = /usr/lib/libtdsodbc.so >>>> UsageCount = 2 >>>> >>>> >>>> The code: >>>> >>>> from sqlalchemy.engine import create_engine >>>> from sqlalchemy.ext.sqlsoup import SqlSoup >>>> >>>> if __name__ == '__main__': >>>> engine = create_engine("mssql://efractal:efR@cZFP13@zfp", echo='debug') >>>> db = SqlSoup(engine) >>>> x = db.mlm_spol.fetchone() >>>> print x >>>> >>>> >>>> still fails with NoSuchTableError: mlm_spol >>>> >>>> Its debug output is the same as in my previous email which used different >>>> connect style. >>>> >>>> And the code that uses db.execute('select * from mlm_spol').fetchone() >>>> still >>>> works... >>>> >>>> Any other ideas? >>>> >>>> Ladislav Lenart >>>> >>>> >>>> On 8.10.2012 17:45, Michael Bayer wrote: >>>>> your freetds datasource should be configured with CLIENT_CHARSET=utf8 as >>>>> I illustrated earlier: >>>>> >>>>> [ms_2005] >>>>> host = 172.16.248.128 >>>>> port = 1213 >>>>> tds version = 8.0 >>>>> client charset = UTF8 >>>>> text size = 50000000 >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.