[egenix-users] mxODBC Cursor Unicode issue with method
executedirect().
Cliff Xuan
Cliff.Xuan at artsalliancemedia.com
Wed Aug 15 16:47:42 CEST 2007
Hi Marc-Andre,
Thanks very much for your answer.
Dose that mean I should avoid using executedirect() if I do have parameters?
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.
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',)])
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 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
Cliff Xuan
Cliff Xuan
Arts Alliance Media Ltd
Company Registration No. 04801432
Registered office: 9-11 North End Road, London W14 8ST
Registered in England & Wales
-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com]
Sent: 15 August 2007 15:21
To: Cliff Xuan
Cc: egenix-users at egenix.com
Subject: Re: [egenix-users] mxODBC Cursor Unicode issue with method executedirect().
Cliff,
your problem seems to be related to the problem reported by Harri
Pesonen. We will be releasing mxODBC 3.0.1 in the next few days
which includes a work-around for the SQL Server problem with
.executedirect(). This should also fix the problem you are seeing.
Note that .executedirect() is normally only useful for one-shot
queries that don't require parameters. Depending on the ODBC
driver implementation it bypasses the normal processing done
in the ODBC driver and sends off the query directly to the
server.
This can result in better performance, however, it also means
that the ODBC driver doesn't have any type information available
which usually results in less data type conversions and thus
reduces memory foot-print and avoids extensive copying.
For queries which take parameters it is therefore often better
to use the standard .execute() method.
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
On 2007-08-07 15:26, Cliff Xuan wrote:
> I had a problem when using unicode object in mxODBC Cursor's executedirect() method.
>
> These are the lines from my test code:
>
> u'\xe5' is the Unicode object for Norwegian vow å, spFindUser is a MS SQLServer stored procedure which returns the columns which contains the character in the parameter.
>
> Method execute() returns the right result, but executedirect doesn't.
>
>
>
> con = mx.ODBC.Windows.DriverConnect(dsn)
>
> con.encoding='utf-8'
>
> selectsql = "{call spFindUser(?)}"
>
> cur.execute(selectsql, (u'\xe5'))
>
> cur.executedirect(selectsql, (u'\xe5'))
>
>
>
> Any idea why it's happening?
>
> This is the explanation of method executedirect() in the official documentation:
>
>
>
> .executedirect(operation[,parameters])
>
> Just like .execute(), except that no prepare step is issued and the operation is not cached. This can result in better performance with some ODBC driver setups, but also implies that Python type binding mode is used to bind the parameters.
>
> operation may be a Unicode object in case the ODBC driver and/or database support this.
>
> Return values are not defined.
>
>
>
> One questions on this:
>
> What does 'prepare step' mean?
>
>
>
> Many thanks
>
> Cliff
>
>
>
>
>
>
> ------------------------------------------------------------------------
>
>
> _______________________________________________________________________
> eGenix.com User Mailing List http://www.egenix.com/
> https://www.egenix.com/mailman/listinfo/egenix-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: executedirect.py
Type: application/octet-stream
Size: 3749 bytes
Desc: executedirect.py
Url : /mailman-archives/egenix-users/attachments/20070815/ebe51b53/executedirect.obj
More information about the egenix-users
mailing list