I'm having some problems trying to read a Microsoft Access database (JET 4 .mdb) using Python 2.7.2 (unixODBC 2.2.14, pyodbc 2.1.11 and the mdbtools driver) on Ubuntu 11.10 32-bit. Yes, I know it is horrible idea, but oddly enough it is the easiest solution I could find. I want to avoid converting the .mdb database to another format (like sqlite) for various reasons, but it seems this might be the only solution if I cannot figure this out. I apologize for the length of this question, and I'm also rather new to linux and odbc/database handling. I've spent 3 days trying to figure this out, but have gotten nowhere the last two.
Problem: I can read the database, but the values are not encoded/formated correctly.
Python code:
import pyodbc
connection=pyodbc.connect('DSN=dbcon_test') # Driver = /usr/lib/libmdbodbc.so.0
cursor=connection.cursor()
cursor.execute('SELECT * FROM FIELDNOTES')
rows=cursor.fetchone()
Outputs:
rows:
('03/14/03', 49, 49, None, 'visit\x00\xfd', None, 'upstream of ', 942815025)
should be:
('03/14/03 15:40:00, 1, 1, None, 'visit', None, 'upstream of road, just below small drop, 1728)
What I think, and have been working on trying to figure out, is that there is an error either when reading the column info (SQLDescribeCol.c) and/or when fetching the data (SQLFetch.c and SQLGetData.c).
Take column 1 and 2 for example. Column 1 is a time stamp, and correctly identified as such (SQL_TYPE_TIMESTAMP). It also reads the correct value into buffer (Buffer = [03/14/03 15:40:00]), but seems to be shorted by Column Size/StrLen Or Ind which is 8, as resulting output is 8 characters long '03/14/03', although I thought the size 8 referred to bytes(?).
Column 2 is integer value 1, but in Buffer it is read as 49. I have not figured out why, but it reads all integer values as ascii codes/numerical characters (1 becomes 49,2 becomes 50 etc), which is rather inconvenient and I don't know how to deal with this when the number becomes large (e.g. 1728 becomes 942815025). Double numbers are also read badly in the buffer.
SQLDescribeCol from log, column 1 and 2 (same as in complete log file linked below):
[ODBC][16118][1320928843.731400][SQLDescribeCol.c][243]
Entry:
Statement = 0xa0e1ab0
Column Number = 1
Column Name = 0xbffd2820
Buffer Length = 300
Name Length = (nil)
Data Type = 0xbffd281a
Column Size = 0xbffd2814
Decimal Digits = 0xbffd281c
Nullable = 0xbffd281e
[ODBC][16118][1320928843.731411][SQLDescribeCol.c][493]
Exit:[SQL_SUCCESS]
Column Name = [Date/Time]
Data Type = 0xbffd281a -> -1
Column Size = 0xbffd2814 -> 8
Decimal Digits = 0xbffd281c -> 0
Nullable = 0xbffd281e -> 0
[ODBC][16118][1320928843.731423][SQLDescribeCol.c][243]
Entry:
Statement = 0xa0e1ab0
Column Number = 2
Column Name = 0xbffd2820
Buffer Length = 300
Name Length = (nil)
Data Type = 0xbffd281a
Column Size = 0xbffd2814
Decimal Digits = 0xbffd281c
Nullable = 0xbffd281e
[ODBC][16118][1320928843.731434][SQLDescribeCol.c][493]
Exit:[SQL_SUCCESS]
Column Name = [Site]
Data Type = 0xbffd281a -> 4
Column Size = 0xbffd2814 -> 4
Decimal Digits = 0xbffd281c -> 0
Nullable = 0xbffd281e -> 0
SQLGetData from log column 1 and 2:
[ODBC][16118][1320928843.732565][SQLGetData.c][233]
Entry:
Statement = 0xa0e1ab0
Column Number = 1
Target Type = 1 SQL_CHAR
Buffer Length = 1024
Target Value = 0xbffd24dc
StrLen Or Ind = 0xbffd24d8
[ODBC][16118][1320928843.732584][SQLGetData.c][497]
Exit:[SQL_SUCCESS]
Buffer = [03/14/03 15:40:00]
Strlen Or Ind = 0xbffd24d8 -> 8
[ODBC][16118][1320928843.732595][SQLGetData.c][233]
Entry:
Statement = 0xa0e1ab0
Column Number = 2
Target Type = 4 SQL_INTEGER
Buffer Length = 4
Target Value = 0xbffd2950
StrLen Or Ind = 0xbffd295c
[ODBC][16118][1320928843.732606][SQLGetData.c][497]
Exit:[SQL_SUCCESS]
Buffer = [49]
Strlen Or Ind = 0xbffd295c -> 4
These are the columns of the table abd give by mdbtools:
mdb-schema database.mdb -T FIELDNOTES
( [Size from MDB Viewer]
Date/Time DateTime (Short), 8
Site Long Integer, 4
Note_ID Long Integer, 4
Sampler Text (100), 100
Action Text (100), 100
Instrument ID Long Integer, 4
Memo Memo/Hyperlink (255), 0
Note_AutoID Long Integer 4
);
complete ODBC log file : http://pastebin.com/Q01ahwCW
If anyone has any tips on how to solve this (including easy database conversions, as I would have to convert very often if I do), it would be greatly appreciated! If more info is needed, I can provide that!
Thanks!
