[egenix-users] mxODBC Cursor Unicode issue with method executedirect().

M.-A. Lemburg mal at egenix.com
Wed Aug 15 17:57:21 CEST 2007


On 2007-08-15 16:47, Cliff Xuan wrote:
> Hi Marc-Andre,
> 
> Thanks very much for your answer.
> Dose that mean I should avoid using executedirect() if I do have parameters?

You should always prefer .execute() over .executedirect().

If you do want to try .executedirect(), then please do timings checks
and data transfer checks (like the ones you are apparently doing),
since using .executedirect() may very well be slower for what
you want to do, use more memory and result in unwanted recoding
of data.

Please note that .executedirect() results in mxODBC using Python type
binding instead of the standard SQL type binding used for .execute(),
so the way the communication between Python and the ODBC driver
works can be somewhat different.

> I'm currently working on an I18N project and found a lot of db processing code doesn't work any more when applying to non English dataset. It's really worrying.
> 
> Sorry to trouble you with more questions.
> 
> I sent this email to the mailing list a few minutes ago but failed. I guess it might be the format problem? It was in html.

It's likely that it ended up in our spam system. HTML emails
are rather uncommon on this list.

> Hi
> 
> You might have seen a previous question I had on executedirect() dealing with Unicode parameter if you are subscribed to the mailing list. Unfortunately my problem is still unsolved and quite worrying about this.
> 
> Would really appreciate if you can give me some clue.
> 
> I have done the maximum simplification of my code and it looks like the attachment.
>  
> The core part is this:
> 
> 1 >>> con.encoding='utf-8'
> 2 >>> con.stringformat = mx.ODBC.Windows.MIXED_STRINGFORMAT
> 3 >>> cur = con.cursor()
> 4 >>> uniqueID = str(pythoncom.CreateGuid())[1:-1]
> 5 >>> insertsql = "INSERT INTO simpletable (id, firstname) VALUES ('%s',?)" %uniqueID
> 6 >>> UNICODE = 1
> 7 >>> if UNICODE:
> 8 >>>     cur.executedirect(insertsql, [(u'\xe6\xf8\xe5',)])
> 9 >>> else:
> 10>>>     cur.executedirect(insertsql, [('\xc3\xa6\xc3\xb8\xc3\xa5',)])
> 11>>> con.commit()
> 12>>> selectsql = "exec spFindName ?"
> 13>>> # search for the record with the Norwegian character æ in the firstname column.
> 14>>> if UNICODE:
> 15>>>     cur.executedirect(selectsql, [(u'\xe6',)])
> 16>>> else:
> 17>>>     cur.executedirect(selectsql, [('\xc3\xa6',)])
> 
> Explanations:
> 
> 1. db table simpletable  columns: 
> 	id(uniqueidentifier)
> 	firstname(nchar(10))
> 
> 2. stored procedure spFindName is basically searching for a string in the firstname field: 
>  set ANSI_NULLS ON
>  set QUOTED_IDENTIFIER ON
>  go
>  create procedure [dbo].[spFindName]
>  ( @find nvarchar(64)  )
>   as
>   set nocount on
>   select * from simpletable
> where firstname like ('%' + @find + '%')
> 
> 3.
> | char | utf-8 byte string | Unicode object  | 
> |  æ   |   '\xc3\xa6'      |        u'\xe6'  |
> |  ø   |   'xc3\xb8'       |        u'\xf8'  |
> |  å   |   '\xc3\xa5'      |        u' \xe5' |
> 
> Questions:
> 
> 1.      When running this line python.exe costs 50% of CPU resource and never finishes. Why?
>>>> cur.executedirect(insertsql, [('\xc3\xa6\xc3\xb8\xc3\xa5',)])

This is a bug in the SQL Server ODBC driver. We have put a work-around
for it in mxODBC 3.0.1.

> 2.      This line doesn't fetch any result
>>>> cur.executedirect(selectsql, [(u'\xe6',)])
>
> But changing executedirect() to execute() will get the right result. Why? The official documentation explains that the difference between the two methods is that executedirect() is using Python binding. Is that the cause of the problem? If I change executedirect() to execute(), what's the cost on performance?
> 
> 3.      However, changing that line to the one below will get results.
>>>> cur.executedirect(selectsql, [(u'\xe6\xf8\xe5',)])

The above two should be fixed in 3.0.1 as well.

> The difference between these two is one is the full of the field the other is just part of it. Why one is working but not the other? Could this be an error on my stored procedure? If so, how can I improve it? Or it's a misuse of executedirect() ? 
> 
> I know my use of the method looks silly, but without examples to follow it's really difficult. 
> 
> Answer to any of my questions is much appreciated. 
> 
> Many thanks

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 15 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611



More information about the egenix-users mailing list