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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
