Help Understanding mx.ODBC Error

[Greg Corradini]

Steve Holden wrote:
> 
> 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
> 
> -- 
> http://mail.python.org/mailman/listinfo/python-list
> 
> 

Steve,
Thanks for your reply. I used the proper parameterization with "?" in my SQL
statements and still the problem persisted (although now it's gone). I do
agree, an untraced error is definately annoying. Next time, I'll be more
patient despite the workflow pileing up in front of me. 

Marc-Andre Lemburg did offer a couple pieces of advice that I'll follow up
on and read about. He mentioneded the connection pooling and Access driver
version I'm working with. 

Thanks again
Greg Corradini
-- 
View this message in context: http://www.nabble.com/Help-Understanding-mx.ODBC-Error-tf3602497.html#a10077031
Sent from the Python - python-list mailing list archive at Nabble.com.