[egenix-users] Re: mxODBC 3.0.1 RC1 to address problems with .executedirect() and SQL Server

M.-A. Lemburg mal at egenix.com
Fri Aug 17 17:32:43 CEST 2007


On 2007-08-17 15:59, Harri Pesonen wrote:
> Yes, and the problem can be solved in the procedure by adding
> 
> SET NOCOUNT ON
> 
> as a first statement:
> 
> o.execute("""CREATE PROC #proc
> @p as nvarchar(200)
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #test2 (A nvarchar(64), B varchar(64))
> INSERT INTO #test2 (A,B) VALUES ('A','B')
> SELECT * FROM #test2
> END""") 
> 
> I don't know, perhaps mxODBC should check if the result set is a real
> result set, or just information about the number of inserted rows.
> Perhaps it is not easy to detect it.

The information mxODBC is looking for is the number of result set
columns.

If that number is zero, it regards the result set as not existing,
since in most cases, that's the correct interpretation and
avoids strange errors you'd otherwise get from the ODBC fetch
calls.

BTW, you can access the number of rows that were inserted
by querying c.rowcount just before the .nextset() call.

> -- Harri
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com] 
> Sent: 17. elokuuta 2007 16:43
> To: Harri Pesonen
> Cc: egenix-users at egenix.com
> Subject: Re: mxODBC 3.0.1 RC1 to address problems with .executedirect()
> and SQL Server
> 
> On 2007-08-17 15:33, Harri Pesonen wrote:
>> Hi, we found one problem in version mxODBC 3. The following works in
>> mxODBC 2. 
> 
> That's because mxODBC 3.0 supports multiple result sets.
> 
>> import mx.ODBC.Windows
>> dsn = "Driver={SQL SERVER};Server=dskpesonha;Database=master;APP=test"
>> connection = mx.ODBC.Windows.DriverConnect(dsn, 0)
>> connection.stringformat = mx.ODBC.Windows.MIXED_STRINGFORMAT
>> o = connection.cursor()
>> o.execute("""CREATE PROC #proc
>> @p as nvarchar(200)
>> AS
>> BEGIN
>> CREATE TABLE #test2 (A nvarchar(64), B varchar(64))
>> INSERT INTO #test2 (A,B) VALUES ('A','B')
>> SELECT * FROM #test2
>> END""")
>> o.execute("EXEC #proc 'huhuu'")
>> rs = o.fetchall()
>> for r in rs:
>>   print r
>> o  = None
>>
>> In mxODBC 3 it fails with error:
>>
>> ProgrammingError: missing result set
>>
>> If I remove the INSERT statement, then it works! (No error but empty
>> result set). Odd. It must be a bug somewhere.
> 
> The INSERT creates an empty result set with no columns (this results
> in the "missing result set" message - a bit misleading perhaps).
> 
> To get to the next result set, you have to use .nextset():
> 
> o.execute("EXEC #proc 'huhuu'")
> o.nextset()
> rs = o.fetchall()
> for r in rs:
>   print r
> o  = None
> 
>> -- Harri
>>
>> -----Original Message-----
>> From: M.-A. Lemburg [mailto:mal at egenix.com] 
>> Sent: 17. elokuuta 2007 0:18
>> To: egenix-users at egenix.com
>> Cc: Cliff Xuan; Harri Pesonen
>> Subject: mxODBC 3.0.1 RC1 to address problems with .executedirect()
> and
>> SQL Server
>>
>> Hello,
>>
>> in order to address the issues discussed in the last few days
>> with using .executedirect() on connections to SQL Server, we
>> are making a release candidate available.
>>
>> The final release of mxODBC 3.0.1 is planned for Monday, provided
>> that the workarounds we've added do fix all the problems you've
>> encountered.
>>
>> Please give the rc1 a try and let us know whether there are
>> any outstanding issues.
>>
>>
> http://downloads.egenix.com/python/egenix-mxodbc-3.0.1_rc1.win32-py2.4.e
>> xe
>>
> http://downloads.egenix.com/python/egenix-mxodbc-3.0.1_rc1.win32-py2.5.m
>> si
>>
>> Thanks,
> 

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 17 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