[DB-SIG] file locking error bonking access (long!)

kromag@nsacom.net kromag@nsacom.net
Fri, 1 Jun 2001 15:29:44 -0700 (PDT)


I am having some weirdness occour while experimenting with access 97 SR2.

I am using python 2.0 under windows 95B.

When I run the scripts copied below, I occaisionally get the following:

--------begin barfage--------------------

816
817
817 records written in 167.529999971 seconds.
Traceback (most recent call last):
  File "c:windowsdesktoptryme.py", line 19, in ?
    dbwrite.writetwo(pack)
  File "c:python20dbwrite.py", line 26, in writetwo
    db=engine.OpenDatabase("\windows\desktop\db1.mdb")
  File "<COMObject DAO.DBEngine.35>", line 2, in OpenDatabase
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 
'DAO.Workspace',
"Couldn't lock file.", None, -1, -2146825238), None)

--------end barfage---------------

Hrm.
The script writes 500 lines with 6 values each into two access tables. 

---------begin script--------------

import time
import random
import dbwrite ## my attempt at a module...
tock=1
then=time.time()
try:
	while tock < 1000:
		tick=time.time()
		surprise=random.randint(1,10000)
		a=random.choice(['a','e','i','o','u'])
		b=random.choice(['a','e','i','o','u'])
		c=random.choice(['a','e','i','o','u'])
		d=random.choice(['a','e','i','o','u'])
		pack=(tick,surprise, a,b,c,d)
		dbwrite.write(pack)
		print tock
		tock=tock+1
		dbwrite.writetwo(pack)
		print tock
		tock=tock+1
finally:
	now=time.time() - then
	print ( '%s' +' records written in ' + '%s' + ' seconds.')% (tock -1, 
now)
--------------end script------------

I had previously written this module as an excersise in.... writing a module! 
:-). I know I really should write a way to identify the tables without the 
redundantcy. Next week.

--------begin weak attempt at module, probably better left as functions!----
import win32com.client
# import string

## Function write() writes a list to the database
def write(inputtage):
    time=inputtage[0]
    number=inputtage[1]
    str1=inputtage[2]
    str2=inputtage[3]
    str3=inputtage[4]
    str4=inputtage[5]
    engine=win32com.client.Dispatch("DAO.DBEngine.35")
    db=engine.OpenDatabase("\windows\desktop\db1.mdb")
    db.Execute("insert into food values(%f, '%s', '%s','%s','%s','%s')" 
%(time, number, str1, str2, str3, str4))
    return 'ok'

## Function writetwo() writes a list to the database
def writetwo(inputtage):
    time=inputtage[0]
    number=inputtage[1]
    str1=inputtage[2]
    str2=inputtage[3]
    str3=inputtage[4]
    str4=inputtage[5]
    engine=win32com.client.Dispatch("DAO.DBEngine.35")
    db=engine.OpenDatabase("\windows\desktop\db1.mdb")
    db.Execute("insert into baboon values(%f, '%s', '%s','%s','%s','%s')" 
%(time, number, str1, str2, str3, str4))
    return 'ok'


def wipe():
    engine=win32com.client.Dispatch("DAO.DBEngine.35")
    db=engine.OpenDatabase("\windows\desktop\db1.mdb")
    db.execute("delete * from food")
    return 'wiped'

def help():
	print 'write() - Writes a list of 6 values to the database file,'
	print 'first value is a float and the following five are string.'
	print 'wipe() - Wipes database file.'
	print 'help() - Prints this message.'
	return 0
---------------end--------------------

Any clues as to why this fails? It only happens once out of every 10 or so.

It also seems to be a bit slower than I would have guessed. A successful run:

998
999
1000
1000 records written in 124.460000038 seconds.

Is 10 per second too many to ask from a P3 7xx with 256 megs of ram?

This is about what I get on a P166 running linux postgresql and psycopg using 
essentially the same script with a slightly different module.. (I'd copy them 
here, but I am at work... and this is already too long!)

I am _EMPHATICALLY_NOT_ trying to start an MS bashing thread here. I just 
wonder if I am missing a speed/safety technique that is not immediately 
obvious. Do I need to add threading? Will that prevent lock issues?

Thanks!