ODBC bug

[Elbert Lev]

# W2K MSSQL-2000

import dbi, odbc

myconn = odbc.odbc('DSN=db; UID=sa; PWD=')
mycursor = myconn.cursor()

mycursor.execute("SELECT ID, PWD FROM TABLE")
rs = mycursor.fetchall()
for ID, PWD in rs:
upd = "UPDATE TABLE SET PWD = \'%s\' WHERE ID = %u" % \
(encode(PWD), ID)
mycursor.execute(upd)
myconn.commit()

#This script updates (encodes) passwords in the database,
#but does this rather slowly if the the recordset contains 2000
records.

To speedup I decided to concatenate upd strings and execute it:

mycursor.execute("SELECT ID, PWD FROM TABLE")
rs = mycursor.fetchall()
updlst = []
for ID, PASSWORD in rs:
upd = "UPDATE TABLE SET PWD = \'%s\' WHERE ID = %u" % \
(encode(PWD), ID)
updlst.append(upd)
upd = " ".join(updlst)
mycursor.execute(upd)
myconn.commit()

#This one works much faster, but if the joined upd string is longer
then about 40K ONLY THE FIRST 40K are actually executed. The rest is
IGNORED. No exceptions and execute() returnes 1.