mx.ODBC minor problem

[Steve Holden]

Greg Corradini wrote:
> Hello all,
> In a script i just wrote, my code calls a function createTables(), which
> checks for an existing table and then creates it, and then immediately calls
> selectSQL(), which selects from a different table and inserts on the table I
> just created (see below). 
> 
> However, I continue to get an Interface Error (see below) when i run it. And
> yet, if I allow the code to call createTables() and then manually call
> selectSQL() after a couple seconds, the thing works fine. In short, there's
> no mismatch in the number of parameters. Why would this be? I've never
> experienced this before. How can i restructure my code to resolve this
> problem? 
> ---
> CODE
> ---
> def createTables():
>     # Drop AddScript Table
>     try:
>         curse.execute('Drop table ' +countyname+'ADD_SCRIPT_TABLE')
>         conn.commit

It may not make any difference, but you should *call* commit() in the 
statement above rather than just referencing it.

>     except:
>         pass
>     # Create AddScript Table
>     curse.execute('Create table ' +countyname+'ADD_SCRIPT_TABLE'+ ' (TISCODE
> TEXT(12), STATUS TEXT(4))')
>     conn.commit()
> 
> def selectSQL():
>     sql = "Select TISCODE,STATUS from " +countyname+"0"+ " where STATUS =
> 'AS'"
>     curse.execute(sql)
>     a = curse.fetchall()
>     curse.executemany('Insert into ' +countyname+'ADD_SCRIPT_TABLE'+ '
> (TISCODE,STATUS) values (?,?)',x)

Shouldn't the second argument to executemany() be a, not x?

>     conn.commit()
> ---
> ERROR
> ---
> InterfaceError: mismatch in number of parameters; expected 2, found none

I find this whole thing a little disturbing. I keep asking myself why 
you are apparently maintaining entirely separate data structures for 
each county instead of having the county as a data item that can be used 
to select appropriate rows in the normal SQL way.

I suspect the correct way to proceed otherwise is to rectify the 
database design before going too much further. If there is any intention 
to be able to compare data across different counties, for example, you 
will be dead in the water doing it this way.

If this is someone else's crappy database design that you are obliged to 
deal with then please accept my apologies. If it's your crappy design 
then fix it ;-)

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