473,850 Members | 2,117 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

mx.ODBC 2.0.7 bug?

Joe
Python 2.4
Windows XP SP2
MS Access 2000
mx.ODBC 2.0.7

Problem data truncation occuring (here's the actual error message):

mxODBC.Warning: ('01004', 5, '[Microsoft][ODBC Microsoft Access Driver]
String data, right truncated on column number 3 (Expr1002)', 3326)

I believe that have found a bug in mx.ODBC not properly assigning the
correct data type to a column.

Here is a sample script that demonstrates the problem and why I think it is
being handled incorrectly:

# NOTE memo1 and memo2 are memo fields in the test_table

import mx.ODBC.Windows

dbs = mx.ODBC.Windows .connect('datab ase', '', '')

sql = "select memo1, memo2, memo1 & ' ' & memo2 from test_table where
record_id = 1"

c = dbs.cursor()

c.execute(sql)

print
print 'mxODBC SQL DataTypes:'
print

for i in mx.ODBC.Windows .sqltype:
print i, mx.ODBC.Windows .sqltype[i]

print
print 'Column DataTypes:'
print

for i in range(len(c.des cription)):
print c.description[i][1]

c.close()
dbs.close()

When you run this script it produces the following output:

mxODBC SQL DataTypes:

1 CHAR
2 NUMERIC
3 DECIMAL
4 INTEGER
5 SMALLINT
6 FLOAT
7 REAL
8 DOUBLE
9 DATE
10 TIME
11 TIMESTAMP
12 VARCHAR
91 TYPE_DATE
92 TYPE_TIME
93 TYPE_TIMESTAMP
-1 LONGVARCHAR
-10 WCHAR_LONGVARCH AR
-9 WCHAR_VARCHAR
-8 WCHAR
-7 BIT
-6 TINYINT
-5 BIGINT
-4 LONGVARBINARY
-3 VARBINARY
-2 BINARY

Column DataTypes:

-1
-1
12

From the output you can see that memo1 and memo2 are both determined to be
of type longvarchar (-1) but when the columns are concatenated together the
resulting column is given a type of varchar (12). Obviously this is why the
data truncation is occurring.

Is this a known problem?

I can work around the problem using a converter function:

def converter(posit ion, sqltype, sqllen):
print 'in :', position, sqltype, sqllen
if position == 2:
sqltype = -1
sqllen = 1073741823
print 'out:', position, sqltype, sqllen
return sqltype, sqllen

and then using:

c.setconverter( converter)

but shouldn't mx.ODBC have properly assigned the correct sqltype and sqllen
for the concatenated memo columns in the first place?





Jul 18 '05 #1
3 1855
Joe wrote:
Python 2.4
Windows XP SP2
MS Access 2000
mx.ODBC 2.0.7

Problem data truncation occuring (here's the actual error message):

mxODBC.Warning: ('01004', 5, '[Microsoft][ODBC Microsoft Access Driver]
String data, right truncated on column number 3 (Expr1002)', 3326)

I believe that have found a bug in mx.ODBC not properly assigning the
correct data type to a column.

Here is a sample script that demonstrates the problem and why I think it is
being handled incorrectly:

# NOTE memo1 and memo2 are memo fields in the test_table

import mx.ODBC.Windows

dbs = mx.ODBC.Windows .connect('datab ase', '', '')

sql = "select memo1, memo2, memo1 & ' ' & memo2 from test_table where
record_id = 1"

c = dbs.cursor()

c.execute(sql)

print
print 'mxODBC SQL DataTypes:'
print

for i in mx.ODBC.Windows .sqltype:
print i, mx.ODBC.Windows .sqltype[i]

print
print 'Column DataTypes:'
print

for i in range(len(c.des cription)):
print c.description[i][1]

c.close()
dbs.close()

When you run this script it produces the following output:

mxODBC SQL DataTypes:

1 CHAR
2 NUMERIC
3 DECIMAL
4 INTEGER
5 SMALLINT
6 FLOAT
7 REAL
8 DOUBLE
9 DATE
10 TIME
11 TIMESTAMP
12 VARCHAR
91 TYPE_DATE
92 TYPE_TIME
93 TYPE_TIMESTAMP
-1 LONGVARCHAR
-10 WCHAR_LONGVARCH AR
-9 WCHAR_VARCHAR
-8 WCHAR
-7 BIT
-6 TINYINT
-5 BIGINT
-4 LONGVARBINARY
-3 VARBINARY
-2 BINARY

Column DataTypes:

-1
-1
12

From the output you can see that memo1 and memo2 are both determined to be
of type longvarchar (-1) but when the columns are concatenated together the
resulting column is given a type of varchar (12). Obviously this is why the
data truncation is occurring.

Is this a known problem?

I can work around the problem using a converter function:

def converter(posit ion, sqltype, sqllen):
print 'in :', position, sqltype, sqllen
if position == 2:
sqltype = -1
sqllen = 1073741823
print 'out:', position, sqltype, sqllen
return sqltype, sqllen

and then using:

c.setconverter( converter)

but shouldn't mx.ODBC have properly assigned the correct sqltype and sqllen
for the concatenated memo columns in the first place?


This is a very nice piece of deduction, and I am copying this message to
you and to the egenix-users list, since that's generally a reliable way
to get Marc-Andre's attention.

I'm not convinced that it demonstrates an mxODBC bug, since I don't
believe that the ampersand is actioned by the drivers, but I'm not the
best one to be authoritative about this.

others-who-read-this-reply-will-ly y'rs - steve
--
Meet the Python developers and your c.l.py favorites March 23-25
Come to PyCon DC 2005 http://www.pycon.org/
Steve Holden http://www.holdenweb.com/
Jul 18 '05 #2
Thanks for forwarding the message, Steve.

Steve Holden wrote:
Joe wrote:
Python 2.4
Windows XP SP2
MS Access 2000
mx.ODBC 2.0.7

Problem data truncation occuring (here's the actual error message):

mxODBC.Warning: ('01004', 5, '[Microsoft][ODBC Microsoft Access
Driver] String data, right truncated on column number 3 (Expr1002)',
3326)

I believe that have found a bug in mx.ODBC not properly assigning the
correct data type to a column.

Here is a sample script that demonstrates the problem and why I think
it is being handled incorrectly:

# NOTE memo1 and memo2 are memo fields in the test_table

import mx.ODBC.Windows

dbs = mx.ODBC.Windows .connect('datab ase', '', '')

sql = "select memo1, memo2, memo1 & ' ' & memo2 from test_table where
record_id = 1"

c = dbs.cursor()

c.execute(sql)

print
print 'mxODBC SQL DataTypes:'
print

for i in mx.ODBC.Windows .sqltype:
print i, mx.ODBC.Windows .sqltype[i]

print
print 'Column DataTypes:'
print

for i in range(len(c.des cription)):
print c.description[i][1]

c.close()
dbs.close()

When you run this script it produces the following output:

mxODBC SQL DataTypes:

1 CHAR
2 NUMERIC
3 DECIMAL
4 INTEGER
5 SMALLINT
6 FLOAT
7 REAL
8 DOUBLE
9 DATE
10 TIME
11 TIMESTAMP
12 VARCHAR
91 TYPE_DATE
92 TYPE_TIME
93 TYPE_TIMESTAMP
-1 LONGVARCHAR
-10 WCHAR_LONGVARCH AR
-9 WCHAR_VARCHAR
-8 WCHAR
-7 BIT
-6 TINYINT
-5 BIGINT
-4 LONGVARBINARY
-3 VARBINARY
-2 BINARY

Column DataTypes:

-1
-1
12

From the output you can see that memo1 and memo2 are both determined
to be of type longvarchar (-1) but when the columns are concatenated
together the resulting column is given a type of varchar (12).
Obviously this is why the data truncation is occurring.

Is this a known problem?
No, but then the MS Access ODBC drivers are always full of
surprises :-) (things have gotten a lot better recently, though).
I can work around the problem using a converter function:

def converter(posit ion, sqltype, sqllen):
print 'in :', position, sqltype, sqllen
if position == 2:
sqltype = -1
sqllen = 1073741823
print 'out:', position, sqltype, sqllen
return sqltype, sqllen

and then using:

c.setconverter( converter)

but shouldn't mx.ODBC have properly assigned the correct sqltype and
sqllen for the concatenated memo columns in the first place?

mxODBC gets the select column information from the ODBC driver
and then fetches the data rows based on that information.

In the above case, the Access ODBC driver tells mxODBC that
the third column is of type VARCHAR and passes it some
length information that obviously is wrong.

The only way to fix this is using a converter like you did.

BTW, do you have more info on the length of the memo field
contents and the value that Access passes back as sqllen
for the third column ?

It is possible that this is some off-by-one bug in the driver.
We could work around that by creating a larger buffer to
hold the data.
This is a very nice piece of deduction, and I am copying this message to
you and to the egenix-users list, since that's generally a reliable way
to get Marc-Andre's attention.
Indeed :-) (don't read c.l.p that often these days)
I'm not convinced that it demonstrates an mxODBC bug, since I don't
believe that the ampersand is actioned by the drivers, but I'm not the
best one to be authoritative about this.

others-who-read-this-reply-will-ly y'rs - steve


--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Mar 03 2005)
Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Dat abase.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________

::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,FreeBSD for free ! ::::
Jul 18 '05 #3
Joe
Hi Steve,

Thanks, I find you get much better help when you provide the details needed
for your case instead of expecting others to invest alot of their time
before you did your homework.

Thanks for sending it over to Marc. Last time I found a bug in the mx stuff
(YEARS AGO) I sent it directly to Marc but was trying not to bother him
until I tried the list first.

One point I forgot to mention in the previous reply is that AFTER the
converter function is setup and used if you examine the
cursor.descript ion[column][1] values it still has the old sqltype and
sqllen. Not sure if that would be considered a bug or not? I would have
expected that the converter hook would have also modified the description
info to match what the converter hook did.

Thanks again for your assistance.

Regards,

Joe

This is a very nice piece of deduction, and I am copying this message to
you and to the egenix-users list, since that's generally a reliable way to
get Marc-Andre's attention.

I'm not convinced that it demonstrates an mxODBC bug, since I don't
believe that the ampersand is actioned by the drivers, but I'm not the
best one to be authoritative about this.

others-who-read-this-reply-will-ly y'rs - steve
--
Meet the Python developers and your c.l.py favorites March 23-25
Come to PyCon DC 2005 http://www.pycon.org/
Steve Holden http://www.holdenweb.com/

Jul 18 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2258
by: Marco Aschwanden | last post by:
Hi - Win2000 - Python 2.3.3 - py2exe 0.5.0 - eGenix Comercial & Base Package I am developing an app using mxODBC. On the home page they say http://www.egenix.com/files/python/mxODBC.html#Hints
11
3779
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue, I am posting this on all of the three newsgroups. That's the setup: Windows 2003 Server with IIS and ASP.NET actiavted Access 2002 mdb file (and yes, proper rights are set on TMP paths and path,
0
1386
by: Kong Li | last post by:
Follow up to this thread, the latest Oracle 9i release 2 patchset (9.2.0.5) fixed the handle count leak problem. The problem is in Oracle client component. Thanks. Kong ----- From: Kong Li (likong@email.com)
6
6792
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
4
7259
by: Roger Redford | last post by:
Dear Experts, I'm attempting to marry a system to an Oracle 817 datbase. Oracle is my specialty, the back end mainly, so I don't know much about java or javascript. The system uses javascript to make ODBC calls to the db. The particular system I'm working with, will not work with an Oracle stored procedure I'm told. However, it
4
5655
by: Andreas Lauffer | last post by:
Can anyone tell me advantages / disadvantages of DataDirect Server Wire ODBC-driver? Any experiences? What about redistribution? Andreas Lauffer, easySoft. GmbH, Germany
3
6193
by: Lauren Quantrell | last post by:
Maybe a dumb question - I'm new to ODBC. How do I install an Access ..mde file on a user's workstation and create the ODBC connection to the backend SQL Server database without having to go through the Administrative Tools>Data SOurces (ODBC) menu? IS there a way to do this with VBA code from my installer disk, or by simply copying a file onto the user's PC? Any help is appreciated. LQ
5
40421
by: Alec | last post by:
Hi All, I am currently trying to link in Access 97 to a table in a MSSQL 7 server. Initially the link is fine, however, when I close the access database and re-open it from the same network lacation on a different machine, it tells me that the ODBC connection to 'SQL Server Name' failed. How can I create the linked table permenant no matter what machine the networked database is opened on?
2
9957
by: Crazy Cat | last post by:
Hi all, I am having trouble getting linked Oracle 9 server in MS SQL Server 2005 Express to work properly. My machine is running Windows XP. The Microsoft and Oracle OLE DB Providers have problems dealing with Oracle's Numeric Data Type, so I decided to use Microsoft's OLE DB for ODBC Provider and an Oracle ODBC source. When using the Microsoft ODBC for Oracle Driver in my ODBC source I have inconsistent behavior. Sometimes my queries...
0
12081
by: bazzer | last post by:
hey, im trying to access a microsoft access database from an ASP.NET web application in visual basic 2003.NET. i get the following error when i try running it: Server Error in '/CinemaBookingSystem' Application. -------------------------------------------------------------------------------- ERROR General error Unable to open registry key 'Temporary (volatile) Jet DSN for process
0
11011
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10724
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10351
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9503
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5735
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5929
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4546
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4140
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3178
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.