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? 3 1836
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/
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 ! ::::
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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,
|
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)
|
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:
|
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
| |
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
|
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
|
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?
|
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...
|
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
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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,...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
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
|
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...
| |