Ado stored procs with parameters

[Sam]

I feel stupid replying to myself but this is something somebody will
probably run across and now the answer will be in the archives as
well.  

Apparently ADO is stupid when running statements.  The default
statement type is adCmdText.  It looks like in this mode it doesn't
look for any parameters that might have added..  Changing the
CommandType to adCmdStoredProc makes it decide to look if any
parameters were added.  So in this case the line:

cmd.CommandType = constants.adCmdStoredProc 

should be added somewhere before the rs.Open command is executed.  So
is the DB Api any better?  I saw somebody announced a DBAPI for Ado.
Right now I don't have the time to check it out.  Besides if I want to
get Python more accepted where I work I can't expect them to learn
another DB object model on top of a new language.  At least for now.

-Sam

On Thu, 06 Jul 2000 08:47:43 -0400, Sam <> wrote:

>I'm having a bit of trouble using a stored proc on MSSQL that takes
>parameters.  I create a command object and then append the parameters
>to it.  Then I create a recordset object and use the open method
>passing the command object and a few other parameters.  Unfortunately
>I get an error saying that the stored proc requires The same parameter
>that I just added.  Below is the code I'm using.  Any ideas?
>
>Sam Corder
>
>import win32com.client
>constants = win32com.client.constants
>
>class dbcon:
>    def __init__ (self, connect = None):
>        self.connectstr = connect
>    def runSQLReturnRS(self, sql, params = None):
>        if self.connectstr == None:
>            return None
>        else:
>            rs = win32com.client.Dispatch('ADODB.Recordset')
>            cmd = win32com.client.Dispatch('ADODB.Command')
>            cmd.ActiveConnection = self.connectstr
>            cmd.CommandText = sql
>            if params != None:
>                for parm in params:
>                    cmd.Parameters.Append(cmd.CreateParameter(parm[0],
>parm[1], constants.adParamInput, parm[2], parm[3]))
>
>            rs.Open(cmd, CursorType = constants.adOpenForwardOnly,
>LockType = constants.adLockReadOnly)
>            return rs
>                
>    def __AddParams(self, cmd, params):
>        for parm in params:
>            cmd.Parameters.Append(cmd.CreateParameter(parm[0],
>parm[1], constants.adParamInput, parm[2], parm[3]))
>
>if __name__  == '__main__':
>    db = dbcon("Provider=SQLOLEDB;Data Source=s-incigna2k;User
>Id=msl_login;Password=***;Connect Timeout=5;network
>library=dbmssocn;Initial Catalog=msl;")
>    params = ("ExpJobID", constants.adInteger, 4, 11),
>    rs = db.runSQLReturnRS("ExpConfig_Get", params)
>    print rs.Fields.Count
>