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

[M.-A. Lemburg]

On 2007-08-20 08:34, Harri Pesonen wrote:
> But if .colcount is zero, and you get the error "ProgrammingError:
> missing result set" when you try to use it, wouldn't it be better if
> mxODBC would skip that result set automatically? It would be more
> compatible with mxODBC 2.
>
> Now I think that every .fetchall() should be prepared with
> 
> while o.colcount == 0:
>   if not o.nextset():
>     break
> 
> You would lose o.rowcount in this case, though. Perhaps it should be an
> option (on by default), so that all non-existing result sets should be
> skipped automatically.

Thanks for the suggestion. We'll put this on our TODO list.

Please note that management of the result sets is up to the
ODBC driver (or the backend). mxODBC 3.0 will make all results
sets available to the user, to let the user decide what is
best for the application.

However, I agree that in some cases, skipping virtual result
sets (like the one SQL Server creates in order to report the
rowcount) and always skipping to the last result set is better
handled in mxODBC than in the application.

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

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


> -- Harri
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com] 
> Sent: 17. elokuuta 2007 17:33
> 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: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,
>