[python-win32] question on odbc package

[Jorgensen, Jens]

Shae Erisson wrote:

> From: "Steve Buroff" <sjb@lucent.com>
> Subject: [python-win32] question on odbc package
> Date: Thu, 5 Apr 2001 08:51:54 -0400
>
> > Can anyone tell me how to get the type objects for use with
> > the
> > odbc package? The online documentation for this package is
> > essentially useless. I'm looking the Hammond & Robinson book
> > "Python Programming on Win32". Appendix C talks about the
> > odbc
> > package.
> >
> > They show some type classes like "Date, Binary, ...".
> > However,
> > I can't find these classes. I can't import them. Anyone know
> > how to get them? Thanks.
>
> I didn't see an answer to this, though I might have missed it.
> I agree that the online docs are useless.
>
> I think the types you're looking for are in the "dbi" package.
> I found dbi.dbiDate and a few others in that module.
>
> I'm currently fighting with odbc myself, I'm trying to figure out how to correctly escape random characters so I can put them into a large text field.
> Is there already a function that escapes characters somewhere? Is there a better way to do it?
> Specifically, I'd like to be able to: "INSERT INTO TABLE_NAME VALUES ('!@#%^&*()')"
>
> I'm also curious how to use the vars part of "cursor.execute(sql, vars=[])"
> That's all the documentation says, does anyone know how to use this?
> Is it just like parameters to stored procedures?
>
> Thanks,

If you're having trouble with escaping characters, have you considered using ADO instead of ODBC? ADO let's you easily add parameters and not worry about quoting or escaping. I've successfully used this to add all sorts of nasty characters like nulls etc.. First use makepy.py to compile the ADO type library, eg.:

python c:/Python20/win32com/client/makepy.py -o ADO.py "Microsoft ActiveX Data Objects 2.5 Library"

You don't strictly need this but ADO has lots of defined constants and this will define them for you. Now you can write something like this:

import ADO

# connect to a sql server database (could be odbc, whatever)
conn = ADO.Connection()
conn.Provider = "sqloledb"
conn.Open("server=myserver;user id=auser;password=apassword", "", "", ADO.constants.adConnectUnspecified)

# create our command object
cmd = ADO.Command()
cmd.ActiveConnection = conn
cmd.CommandType = ADO.constants.adCmdText
cmd.CommandText = "insert into DBLogons values (?, ?)"

user = <some encrypted binary string>
password = <some encrypted binary string>

lst = []
lst.append(cmd.CreateParameter('', ADO.constants.adVarChar, ADO.constants.adParamInput, len(user), user))
lst.append(cmd.CreateParameter('', ADO.constants.adVarChar, ADO.constants.adParamInput, len(password), password))

cmd.Execute(None, lst, ADO.constants.adExecuteNoRecords)

Ok, so maybe it's a little more verbose but ADO is easy and seems to work pretty well. There are ways to shortcut the code like passing the connection string as cmd.ActiveConnection.

--
Jens B. Jorgensen
jens.jorgensen@tallan.com