Help Understanding mx.ODBC Error

[Steve Holden]

Greg Corradini wrote:
[actually, her wrote it here but I moved it to the bottom]
> Steve Holden wrote:
>> Greg Corradini wrote:
>>> Hello All,
>>> A few weeks ago, I wrote two scripts using mx.ODBC on an Access DB. Among
>>> other things, both scripts create new tables, perform a query and then
>>> populate the tables with data in a dictionary that I've uploaded from
>>> elsewhere. These scripts have run hundreds of times in the last few weeks
>>> with no problems. 
>>>  
>>> But recently they continue to bail on the mycursor.execute('An SQL
>>> Statement') after the table has been created. I get the following error
>>> message: 
>>> Traceback (most recent call last):
>>> File "C:\Documents and Settings\marv1shi\Desktop\Workspace\Existence
>>> Script\DBF Checker\Access_SQL.py", line 35, in ?
>>> curse.execute(sql)
>>> ProgrammingError: ('07001', -3010, '[Microsoft][ODBC Microsoft Access
>>> Driver] Too few parameters. Expected 4.', 4612) 
>>>  
>>> The real stinker, however, is that after it bails I can manually call
>>> mycursor.execute('An SQL Statement'), then call my insert statement in
>>> the
>>> Python Shell and it works fine. 
>>>  
>>> I just can't figure out how to reconcile this problem. Has anybody run
>>> into
>>> this before? 
>>>  
>>> Thanks
>>> Greg Corradini
>> I suspect what's happening here is that you are presenting statements 
>> you have made up programmatically, and the values you are trying to 
>> insert include apostrophes that break the syntax of your SQL. However 
>> there isn't really enough evidence to decide unless you are prepared to 
>> show us the error traceback, possibly with a dump of the SQL statement 
>> you are actually trying to execute.
>>
>> I apologize in advance if you are using parameterized queries (as you 
>> should to avoid SQL injection vulnerabilities among other major 
>> problems) but this message is typical of Access when it sees words it 
>> can't parse.
>>
 > Steve,
 > As always, thanks for your consistent help on matters big and small.
 > I've managed to solve the problem, although I'm scared b/c the bug is 
still
 > elusive.
 > I dumped and deleted my separate Access DBs, created new ones and tried
 > running the scripts on old data (that these scripts were able to digest
 > successfully before) and new data (that they errored on to begin with).
 > Everything works without me changing any code around. Hmm?
 >
 > I don't know much about Access or the JetEngine. Is it possible that 
.mdbs
 > can go corrupt if overused? This seems unlikely, but I'm dumbfounded.

Greg:

No, there are no known cases of a database getting "tired" :-)

It sounds like a data dependency of some sort, but if the error has 
"gone away" then I guess we no longer have anything to work with. This 
is somewhat annoying, as I hate to see an error go untraced.

Take it from me, it does look like a SQL error - *are* you building your 
statements in the program, or are you using proper parameterization with 
"?" in your statements where a parameter would go?

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb     http://del.icio.us/steve.holden
Recent Ramblings       http://holdenweb.blogspot.com