mxODBC SQL Type error with MS SQL Server 
Author Message
 mxODBC SQL Type error with MS SQL Server

I am a few hours into trying to use mxODBC.  Environment is Windows NT
using ODBC to talk to a local SQL Server test database.

With the following extension of Marc's test program (basically just
forming the connection to SQL Server and plugging in some SQL) I get:
Traceback  (innermost last):
   File "testODBC.py", line 10, in ?
      mydata = mycursor.fetchall()
mxODBC.InterfaceError: SQL type (code -9) not implemented.

Anyone know what this is?  Or how to fix it?  The SQL runs fine by
itself under MS Query Analyzer.

Bill Hunter

import ODBC.Windows
from pprint import pprint
myconn = ODBC.Windows.Connect('SC', user='sa')
mycursor = myconn.cursor()
mycursor.execute(''' \
SELECT sc.StoreNr, sc.Prototype, sc.Quantity, spp.Setup, spp.StartDate
AS sppStart, spp.EndDate AS sppEnd, sl.StartDate AS slStart,
sl.EndDate AS slEnd, ips.SKUNr, ips.SKUQty, ips.SKUQty*sc.Quantity AS
TotalSKUQty \
FROM ((StoreComposition as sc INNER JOIN SetupsPerPrototype as spp ON
sc.Prototype = spp.Prototype) INNER JOIN SetupsList as sl ON spp.Setup
= sl.Name) INNER JOIN ItemsPerSetup as ips ON sl.Id = ips.SetupId \
WHERE (((sc.StoreNr)='0100') AND (('5/15/2000') Between spp.StartDate
And spp.EndDate And ('5/15/2000') Between sl.StartDate And
sl.EndDate)); \
''')
mydata = mycursor.fetchall()
mycursor.close()
myconn.close()
pprint(mydata)



Sat, 14 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server


Quote:
>I am a few hours into trying to use mxODBC.  Environment is Windows NT
>using ODBC to talk to a local SQL Server test database.

>With the following extension of Marc's test program (basically just
>forming the connection to SQL Server and plugging in some SQL) I get:
>Traceback  (innermost last):
>   File "testODBC.py", line 10, in ?
>      mydata = mycursor.fetchall()
>mxODBC.InterfaceError: SQL type (code -9) not implemented.

mxODBC supports python DB API Version 1.0. which doesn't support
fetchall()

// Anders



Sat, 14 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server
Any thoughts on how best to understand  which parts of Python DB API
Version 1.0 to use?

I was going by the fetchall() section in

 http://www.python.org/topics/database/DatabaseAPI-1.0.html#cursor

which said:

fetchall()
Fetch all rows of a query result, returning as a list of tuples. Note
that the cursor's arraysize attribute can affect the performance of
this operation.

Bill Hunter

On Tue, 28 Mar 2000 17:50:14 +0200, Anders M Eriksson

Quote:



>>I am a few hours into trying to use mxODBC.  Environment is Windows NT
>>using ODBC to talk to a local SQL Server test database.

>>With the following extension of Marc's test program (basically just
>>forming the connection to SQL Server and plugging in some SQL) I get:
>>Traceback  (innermost last):
>>   File "testODBC.py", line 10, in ?
>>      mydata = mycursor.fetchall()
>>mxODBC.InterfaceError: SQL type (code -9) not implemented.

>mxODBC supports Python DB API Version 1.0. which doesn't support
>fetchall()

>// Anders



Sat, 14 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server


Quote:
>Any thoughts on how best to understand  which parts of Python DB API
>Version 1.0 to use?

>I was going by the fetchall() section in

> http://www.python.org/topics/database/DatabaseAPI-1.0.html#cursor

First: I apologize for being stupid! Of cause DB API 1.0 supports
fetchall(), and so do mxODBC.

Somehow my brain glitched yesterday and produced the former answer.
Sorry about that!

So to be honest I don't have an answer to your question so the rest of
the letter is me resoning with myself. Maybe the answer will come to
me....

The error isn't about the database, since it says InterfaceError which
according to the documentation is:

InterfaceError
Exception raised for errors that are related to the interface rather
than the database itself.

The interface of mxODBC is DB API 1.0 with some extensions, e.g.

mxODBC uses the DB API 2.0 conventions for .fetchXXX() return values.

I would simplefy the SQL statement so I begin with just getting one
column from a table and then expand it bit by bit.

"SELECT * FROM StoreComposition"
"SELECT * FROM StoreComposition WHERE ...."
...

Then I also would try to use .fetchone() to see if the .execute()
worked!

BTW what database are you connecting to?

I'm sorry that I don't have any answer. Hope that you will solve this.
(Marc will probably answer soon, with a perfectly simple answer)

// Anders
--
I will connect my brain before talking in public



Sun, 15 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server
Hi there,
I had experienced the same problem ( SQL type (code -9) not implemented
) and I reproduce below a fragment from Marc Lehmburg's


SQL type -9 is not defined in ODBC 3.5 AFAIK. No idea what
type this is. The next version of mxODBC will support
unkown types by fetching them as strings, but the current
one cannot deal with them, sorry.
------------------------------------------------------------

Personal Addendum: If you open SQLUCODE.H in the INCLUDE directory of
your Visual Studio installation, you will find a line that reads

#define SQL_WVARCHAR         (-9)

Hope this helps.

regards,
<george/>
--------------------------------------------------------------------------
George Thomas
Resident,Planet Earth,Third Rock from the Sun

Flat No. FI-5, Fifth Floor,
Himagirinath Co-op. Hsg. Society,
486A Elphinstone Road,
Kirkee, Pune - 411003,
Maharashtra State,
India.
--------------------------------------------------------------------------
Faced with the choice between changing one's mind and proving
there is no need to do so, almost everybody gets busy on the proof.
               - John Kenneth Galbraith
--------------------------------------------------------------------------



Sun, 15 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server
The mysterious type that mxODBC cannot handle on SQL Server 7 is
"nvarchar" - unfortunately this is the default that SQL Server
attempts to use for character strings!

mxODBC works fine against fields that are not character strings, but
heck, over half my fields are nvarchar ... true for many other
people, I conjecture.   Guess I will look at some of the other ODBC
wrappers that are floating around.

How big a deal would it be to add a type to mxODBC?  And would it be
in Python or in C?


Quote:

>Hi there,
>I had experienced the same problem ( SQL type (code -9) not implemented
>) and I reproduce below a fragment from Marc Lehmburg's


>SQL type -9 is not defined in ODBC 3.5 AFAIK. No idea what
>type this is. The next version of mxODBC will support
>unkown types by fetching them as strings, but the current
>one cannot deal with them, sorry.
>------------------------------------------------------------

>Personal Addendum: If you open SQLUCODE.H in the INCLUDE directory of
>your Visual Studio installation, you will find a line that reads

>#define SQL_WVARCHAR         (-9)



Mon, 16 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server

Quote:

> The mysterious type that mxODBC cannot handle on SQL Server 7 is
> "nvarchar" - unfortunately this is the default that SQL Server
> attempts to use for character strings!

> mxODBC works fine against fields that are not character strings, but
> heck, over half my fields are nvarchar ... true for many other
> people, I conjecture.   Guess I will look at some of the other ODBC
> wrappers that are floating around.

> How big a deal would it be to add a type to mxODBC?  And would it be
> in Python or in C?



> >Hi there,
> >I had experienced the same problem ( SQL type (code -9) not implemented
> >) and I reproduce below a fragment from Marc Lehmburg's


> >SQL type -9 is not defined in ODBC 3.5 AFAIK. No idea what
> >type this is. The next version of mxODBC will support
> >unkown types by fetching them as strings, but the current
> >one cannot deal with them, sorry.
> >------------------------------------------------------------

> >Personal Addendum: If you open SQLUCODE.H in the INCLUDE directory of
> >your Visual Studio installation, you will find a line that reads

> >#define SQL_WVARCHAR         (-9)

Ehm, you probably mean "wvarchar" ... I suspect this to be
a wide character VARCHAR field. Adding Unicode to mxODBC is
on the list, but not yet done (have to get the Python
Unicode support rock solid first).

You could try having the ODBC driver convert the type to
a normal string by adding the type to the following switch statement
in mxODBCursor_AllocateOutputVars():

        case SQL_CHAR:
        case SQL_VARCHAR:
        case SQL_WVARCHAR:
            var->data_len = var->sqllen;
            /* 0-terminated, so we need to reserve one more byte */
            var->data_buflen = var->data_len + 1;
            data = (SQLPOINTER)new(char,var->data_buflen);
            if (!data) {
                PyErr_NoMemory();
                goto onError;
            }
            var->data = data;
            var->free_data = 1;
            var->ctype = SQL_C_CHAR;
            var->convert = &mxODBC_StringFromString;
            break;

Same for the other possible W+CHAR-type combinations... I
don't know whether the drivers will handle this conversion
correctly and what their output will be though, so you're
on your own here.

BTW, does anyone have a pointer to the specifications
behind the Unicode support in SQL Server or the latest
ODBC variant ?

--
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/



Mon, 16 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server
Well, I was just reading up on SQL Server 7.0 and the SQL Language that it
supports just today.  I was reading up on Transact-SQL just to learn about
SQL Server 7.0 data types.

The first thing that I learned was that Transact-SQL data types nvarchar,
nchar, and ntext, are Unicode (n for national) formatted strings.  This is
how they are named in SQL.  (They are indeed delivered in wide character
arrays in memory.)  The default internal storage of the server is Unicode,
but there is also support for 8-bit character codes, (transported with
string types called, naturally, varchar, char and text).  I don't know what
you get to do to switch for access to these via mxODBC, but it would seem
the direct thing to do.  Is your data anything that couldn't be represented
in the ANSI character code (code page 1252 in SQL Server parlance)?

Other than that, I have nothing to add by way of hope.

Oh, and I am looking at the SQL Server Books Online, part of the SQL Server
Developer Edition that pops up when I load the MSDE for Visual Studio 6.0
CD-ROM.  This is part of the Microsoft Visual Studio 6.0 Plus Pack that
ships with current versions of Visual Studio 6.0 Professional.

On the MSDN Library, January 2000 edition, there seems to be what you want
in the path

Platform SDK / Data Access Services / Microsoft SQL Server Programmer's
Toolkit / and places below such as Building SQL Server Applications.

I assume that the same material is available on MSDN On-Line.

I have no mastery of this.  I just stumbled upon it.

-- Dennis

Quote:
-----Original Message-----


Sent: Thursday, March 30, 2000 00:57

Subject: Re: mxODBC SQL Type error with MS SQL Server


> The mysterious type that mxODBC cannot handle on SQL Server 7 is
> "nvarchar" - unfortunately this is the default that SQL Server
> attempts to use for character strings!

[ ... ]

Ehm, you probably mean "wvarchar" ... I suspect this to be
a wide character VARCHAR field. Adding Unicode to mxODBC is
on the list, but not yet done (have to get the Python
Unicode support rock solid first).

[ ... example of how to do the down-shifting in a conversion layer ...]

BTW, does anyone have a pointer to the specifications
behind the Unicode support in SQL Server or the latest
ODBC variant ?

--
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/

--
http://www.python.org/mailman/listinfo/python-list



Mon, 16 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server

Quote:

> BTW, does anyone have a pointer to the specifications
> behind the Unicode support in SQL Server or the latest
> ODBC variant ?

SQL Server 7 specific details:

Unicode constants are desginated by a N'A Unicode string' in the query
string.
The datatypes in SQL Server are: nchar, nvarchar, and ntext.
All of these are UTF-16 data fields. (machine endian)
As far as handling collation issues:

SQL Server 7 requires that character set, sort order, and Unicode collation
be specified on a per server basis.

The details on Unicode collation support can be found at:
http://msdn.microsoft.com/library/psdk/sql/2_006_39.htm

The ODBC C Unicode datatypes is:
SQL_C_WCHAR

The ODBC SQL datatypes are:
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR

The ODBC API also uses UTF-16.

For more info on the ODBC stuff:
http://msdn.microsoft.com/library/psdk/dasdk/odch2h9h.htm

Bill



Tue, 17 Sep 2002 03:00:00 GMT  
 mxODBC SQL Type error with MS SQL Server

Quote:


> > BTW, does anyone have a pointer to the specifications
> > behind the Unicode support in SQL Server or the latest
> > ODBC variant ?

> SQL Server 7 specific details:

> Unicode constants are desginated by a N'A Unicode string' in the query
> string.
> The datatypes in SQL Server are: nchar, nvarchar, and ntext.
> All of these are UTF-16 data fields. (machine endian)
> As far as handling collation issues:

> SQL Server 7 requires that character set, sort order, and Unicode collation
> be specified on a per server basis.

> The details on Unicode collation support can be found at:
> http://msdn.microsoft.com/library/psdk/sql/2_006_39.htm

> The ODBC C Unicode datatypes is:
> SQL_C_WCHAR

> The ODBC SQL datatypes are:
> SQL_WCHAR
> SQL_WVARCHAR
> SQL_WLONGVARCHAR

> The ODBC API also uses UTF-16.

> For more info on the ODBC stuff:
> http://msdn.microsoft.com/library/psdk/dasdk/odch2h9h.htm

Thank you :-)

That'll make a great start for the Unicode integration into mxODBC.

--
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/



Tue, 17 Sep 2002 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. mxODBC and MS SQL Server

2. Python and MS SQL (Microsoft SQL server)

3. Error 4149 occurred at Fetch Next Record, SQL Server 7.0, and SQL Toolkit 5.0

4. Error 4149 occurred at Fetch Next Record, SQL Server 7.0, and SQL Toolkit 5.0

5. MS SQL Server file types?

6. MS SQL 7.0 vs. MS SQL 2000

7. Error 37000 when trying to connect to MS SQL Server

8. mx.DateTime, mx.ODBC.Windows, and MS SQL Server - Fractional truncation error

9. RexxGMSQ just released---IBM Object Rexx---MS SQL Server APIs for MS Windows NT

10. ADO in MS-IIS with MS-SQL Server

11. mxODBC and SQL-Server

12. VO Succesfuly created a Client/Server app using VO and MS-SQL Server

 

 
Powered by phpBB® Forum Software