0

I'm working in a environment with a very poorly managed legacy Paradox database system. (I'm not the administrator.) I've been messing around with using pyodbc to interact with our tables, and the basic functionality seems to work. Here's some (working) test code:

import pyodbc

LOCATION = "C:\test"

cnxn = pyodbc.connect(r"Driver={{Microsoft Paradox Driver (*.db )\}};DriverID=538;Fil=Paradox 5.X;DefaultDir={0};Dbq={0};CollatingSequence=ASCII;".format(LOCATION), autocommit=True, readonly=True)
cursor = cnxn.cursor()
cursor.execute("select last, first from test")
row = cursor.fetchone()
print row

The problem is that most of our important tables are going to be open in someone's Paradox GUI at pretty much all times. I get this error whenever I try to do a select from one of those tables:

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Paradox Driver] Could not lock 
table 'test'; currently in use by user '(unknown)' on machine '(unknown)'. (-1304) 
(SQLExecDirectW)")

This is, obviously, because pyodbc tries to lock the table when cursor.execute() is called on it. This behavior makes perfect sense, since cursor.execute() runs arbitary SQL code and could change the table.

However, Paradox itself (through its gui) seems to handle multiple users fine. It only gives you similar errors if you try to restructure the table while people are using it.

Is there any way I can get pyodbc to use some sort of read-only mode, such that it doesn't have to lock the table when I'm just doing select and such? Or is locking a fundamental part of how it works that I'm not going to be able to get around?

Solutions that would use other modules are also totally fine.

PeterBB
  • 343
  • 2
  • 9
  • Is there anyway you could create a new account with lower credentials (read-only permission)? –  Dec 06 '12 at 04:21
  • That's what I was hoping the "readonly=True" parameter would do, but it didn't work for me. – PeterBB Dec 06 '12 at 21:12
  • What version of pyobdc do you have? –  Dec 07 '12 at 20:33
  • Oh man, good question. I assumed I had the latest version, but actually I'm using whatever is bundled with PortablePython. I'll have to try getting the latest version manually and see if that helps. – PeterBB Dec 10 '12 at 15:03

3 Answers3

2

Ok, I finally figured it out.

Apparently, odbc dislikes Paradox tables which have no primary key. You cannot update tables with no primary key under any circumstances, and you cannot read from tables with no primary key unless you are the only user trying to access that table.

Unrelatedly, you get essentially the same error messages from password-protected tables if you don't supply a password.

So I was testing my script on two different tables, one of which has both a password and a primary key, and one of which had neither. I assumed the error messages had the same root cause, but it was actually two different problems, with different solutions.

There still seems to be no way to get access to tables without primary keys if they are open in someone's GUI, but that's a smaller issue.

PeterBB
  • 343
  • 2
  • 9
0

Make sure that you have the latest version of pyobdc (3.0.6)here, according to them, they

Added Cursor.commit() and Cursor.rollback(). It is now possible to use only a cursor in your code instead of keeping track of a connection and a cursor.

Added readonly keyword to connect. If set to True, SQLSetConnectAttr SQL_ATTR_ACCESS_MODE is set to SQL_MODE_READ_ONLY. This may provide better locking semantics or speed for some drivers.

Fixed an error reading SQL Server XML data types longer than 4K.

Also, i have tested this on a paradox server using readonly and it does works.

Hope this helps!

  • This didn't turn out to be the problem (see my other post), but I'm giving you the bounty for getting me thinking about the right things. – PeterBB Dec 10 '12 at 21:22
0

I just published a Python library for reading Paradox database files via the pxlib C-library: https://github.com/mherrmann/pypxlib. This operates on the file-level so should also let you read the database independently of who else is currently accessing it. Since it does not synchronize read/write accesses, you do have to be careful though!

Michael Herrmann
  • 4,832
  • 3
  • 38
  • 53