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

[Harri Pesonen]

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.

-- 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