PageRenderTime 64ms CodeModel.GetById 10ms app.highlight 43ms RepoModel.GetById 1ms app.codeStats 0ms

/External.LCA_RESTRICTED/Languages/IronPython/27/Lib/site-packages/adodbapi/adodbapi.py

http://github.com/IronLanguages/main
Python | 1308 lines | 1240 code | 10 blank | 58 comment | 61 complexity | 5385a65f69a434559cdb07e316a843cb MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1"""adodbapi v2.2.6 -  A python DB API 2.0 interface to Microsoft ADO
   2    
   3    Copyright (C) 2002  Henrik Ekelund
   4    Email: <http://sourceforge.net/sendmessage.php?touser=618411>
   5
   6    This library is free software; you can redistribute it and/or
   7    modify it under the terms of the GNU Lesser General Public
   8    License as published by the Free Software Foundation; either
   9    version 2.1 of the License, or (at your option) any later version.
  10
  11    This library is distributed in the hope that it will be useful,
  12    but WITHOUT ANY WARRANTY; without even the implied warranty of
  13    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14    Lesser General Public License for more details.
  15
  16    You should have received a copy of the GNU Lesser General Public
  17    License along with this library; if not, write to the Free Software
  18    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  19
  20    version 2.1 (and later) by Vernon Cole -- update for Decimal data type
  21    (requires Python 2.4 or above or or Python 2.3 with "import win32com.decimal_23")
  22    all uses of "verbose" below added by Cole for v2.1
  23"""
  24# N.O.T.E.:...
  25# if you have been using an older version of adodbapi and are getting errors because
  26# numeric and monitary data columns are now returned as Decimal data,
  27# try adding the following line to get that data as strings: ...
  28#adodbapi.variantConversions[adodbapi.adoExactNumericTypes]=adodbapi.cvtString # get currency as strings
  29
  30import string
  31import time
  32import calendar
  33import types
  34import sys
  35import traceback
  36import datetime
  37
  38try:
  39    from exceptions import StandardError as _BaseException
  40except ImportError:
  41    # py3k
  42    _BaseException = Exception
  43
  44try:
  45    import decimal
  46except ImportError:  #perhaps running Cpython 2.3 
  47    import win32com.decimal_23 as decimal
  48
  49try:
  50    import win32com.client
  51    onIronPython = False
  52except ImportError:  # implies running on IronPython
  53    onIronPython = True
  54
  55# --- define objects to smooth out IronPython <-> CPython differences    
  56if onIronPython: 
  57    from System import Activator, Type, DBNull, DateTime, Array, Byte
  58    from System import Decimal as SystemDecimal
  59    from clr import Reference
  60    def Dispatch(dispatch):
  61        type = Type.GetTypeFromProgID(dispatch)
  62        return Activator.CreateInstance(type)
  63    def getIndexedValue(obj,index):
  64        return obj.Item[index]
  65else: #pywin32
  66    import pythoncom
  67    import pywintypes
  68    pythoncom.__future_currency__ = True
  69    def Dispatch(dispatch):
  70        return win32com.client.Dispatch(dispatch)
  71    def getIndexedValue(obj,index):
  72        return obj(index) 
  73    DBNull = type(None)
  74    DateTime = type(NotImplemented) #impossible value 
  75
  76# --- define objects to smooth out Python3000 <-> Python 2.x differences
  77unicodeType = unicode  #this line will be altered by 2to3.py to '= str'
  78longType = long        #thil line will be altered by 2to3.py to '= int'
  79memoryViewType = types.BufferType #will be altered to '= memoryview'
  80if sys.version[0] == '3':
  81    StringTypes = [str]
  82    makeByteBuffer = bytes
  83else:
  84    makeByteBuffer = buffer 
  85    bytes = str
  86    StringTypes = types.StringTypes    # will be messed up by 2to3 but never used
  87 
  88def standardErrorHandler(connection,cursor,errorclass,errorvalue):
  89    err=(errorclass,errorvalue)
  90    connection.messages.append(err)
  91    if cursor!=None:
  92        cursor.messages.append(err)
  93    raise errorclass(errorvalue)
  94    
  95class TimeConverter(object):
  96    def __init__(self):
  97        self._ordinal_1899_12_31=datetime.date(1899,12,31).toordinal()-1
  98        #Use self.types to compare if an input parameter is a datetime 
  99        self.types = (type(self.Date(2000,1,1)),
 100                      type(self.Time(12,1,1)),
 101                      type(self.Timestamp(2000,1,1,12,1,1)))
 102    def COMDate(self,obj):
 103        'Returns a ComDate from a datetime in inputformat'
 104        raise NotImplementedError   #"Abstract class"
 105    
 106    def DateObjectFromCOMDate(self,comDate):
 107        'Returns an object of the wanted type from a ComDate'
 108        raise NotImplementedError   #"Abstract class"
 109    
 110    def Date(self,year,month,day):
 111        "This function constructs an object holding a date value. "
 112        raise NotImplementedError   #"Abstract class"
 113
 114    def Time(self,hour,minute,second):
 115        "This function constructs an object holding a time value. "
 116        raise NotImplementedError   #"Abstract class"
 117
 118    def Timestamp(self,year,month,day,hour,minute,second):
 119        "This function constructs an object holding a time stamp value. "
 120        raise NotImplementedError   #"Abstract class"
 121
 122    def DateObjectToIsoFormatString(self,obj):
 123        "This function should return a string in the format 'YYYY-MM-dd HH:MM:SS:ms' (ms optional) "
 124        raise NotImplementedError   #"Abstract class"        
 125    
 126
 127class mxDateTimeConverter(TimeConverter):
 128    def COMDate(self,obj):
 129        return obj.COMDate()
 130
 131    def DateObjectFromCOMDate(self,comDate):
 132        return mx.DateTime.DateTimeFromCOMDate(comDate)
 133    
 134    def Date(self,year,month,day):
 135        return mx.DateTime.Date(year,month,day)
 136
 137    def Time(self,hour,minute,second):
 138        return mx.DateTime.Time(hour,minute,second)
 139
 140    def Timestamp(self,year,month,day,hour,minute,second):
 141        return mx.DateTime.Timestamp(year,month,day,hour,minute,second)
 142    
 143    def DateObjectToIsoFormatString(self,obj):
 144        return obj.Format('%Y-%m-%d %H:%M:%S')
 145
 146class pythonDateTimeConverter(TimeConverter): # datetime module is available in Python 2.3
 147    def __init__(self):       
 148        TimeConverter.__init__(self)
 149        
 150    def COMDate(self,obj):
 151        tt=obj.timetuple()
 152        try:
 153            ms=obj.microsecond
 154        except:
 155            ms=0
 156        YMDHMSmsTuple=tuple(tt[:6] + (ms,))
 157        return self.COMDateFromTuple(YMDHMSmsTuple)
 158
 159    def DateObjectFromCOMDate(self,comDate):
 160        #ComDate is number of days since 1899-12-31
 161        if isinstance(comDate,datetime.datetime):
 162            return comDate.replace(tzinfo=None) # make non aware
 163        elif isinstance(comDate,DateTime):
 164            fComDate = comDate.ToOADate()
 165        else:
 166            fComDate=float(comDate)
 167        millisecondsperday=86400000 # 24*60*60*1000
 168        integerPart = int(fComDate)
 169        floatpart=fComDate-integerPart
 170        if floatpart == 0.0:
 171            return datetime.date.fromordinal(integerPart + self._ordinal_1899_12_31)
 172        dte=datetime.datetime.fromordinal(integerPart + self._ordinal_1899_12_31) \
 173            + datetime.timedelta(milliseconds=floatpart*millisecondsperday)
 174        return dte
 175
 176    def Date(self,year,month,day):
 177        return datetime.date(year,month,day)
 178
 179    def Time(self,hour,minute,second):
 180        return datetime.time(hour,minute,second)
 181
 182    def Timestamp(self,year,month,day,hour,minute,second):
 183        return datetime.datetime(year,month,day,hour,minute,second)
 184
 185    def COMDateFromTuple(self,YMDHMSmsTuple):
 186        d = datetime.date(YMDHMSmsTuple[0],YMDHMSmsTuple[1],YMDHMSmsTuple[2])
 187        integerPart = d.toordinal() - self._ordinal_1899_12_31
 188        ms = ((YMDHMSmsTuple[3]*60 \
 189              +YMDHMSmsTuple[4])*60 \
 190              +YMDHMSmsTuple[5])*1000 \
 191              +YMDHMSmsTuple[6]
 192        fractPart = ms / 86400000.0
 193        return integerPart + fractPart
 194       
 195    def DateObjectToIsoFormatString(self,obj):
 196        if isinstance(obj,datetime.datetime):
 197            s = obj.strftime('%Y-%m-%d %H:%M:%S')
 198        elif isinstance(obj,datetime.date): #exact midnight
 199            s = obj.strftime('%Y-%m-%d 00:00:00')
 200        else:
 201            try:  #usually datetime.datetime
 202                s = obj.isoformat()
 203            except:  #but may be mxdatetime
 204                s = obj.Format('%Y-%m-%d %H:%M:%S')
 205        return s
 206
 207class pythonTimeConverter(TimeConverter):
 208    def __init__(self):
 209        TimeConverter.__init__(self)
 210    def COMDate(self,timeobj):
 211        return self.COMDateFromTuple(timeobj)
 212    def COMDateFromTuple(self,t):
 213            d = datetime.date(t[0],t[1],t[2])
 214            integerPart = d.toordinal() - self._ordinal_1899_12_31
 215            sec = (t[3]*60 + t[4])*60 + t[5]
 216            fractPart = sec / 86400.0
 217            return integerPart + fractPart
 218           
 219    def DateObjectFromCOMDate(self,comDate):
 220        'Returns ticks since 1970'
 221        if isinstance(comDate,datetime.datetime):
 222            return comDate.timetuple()
 223        elif isinstance(comDate,DateTime):
 224            fcomDate = comDate.ToOADate()
 225        else:
 226            fcomDate = float(comDate)
 227        secondsperday=86400 # 24*60*60
 228        #ComDate is number of days since 1899-12-31, gmtime epoch is 1970-1-1 = 25569 days
 229        t=time.gmtime(secondsperday*(fcomDate-25569.0))
 230        return t  #year,month,day,hour,minute,second,weekday,julianday,daylightsaving=t
 231
 232    def Date(self,year,month,day):
 233        return self.Timestamp(year,month,day,0,0,0)
 234
 235    def Time(self,hour,minute,second):
 236        return time.gmtime((hour*60+minute)*60 + second)
 237
 238    def Timestamp(self,year,month,day,hour,minute,second):
 239        return time.localtime(time.mktime((year,month,day,hour,minute,second,0,0,-1)))
 240    
 241    def DateObjectToIsoFormatString(self,obj):
 242        try:
 243            s = time.strftime('%Y-%m-%d %H:%M:%S',obj)
 244        except:
 245            s = obj.strftime('%Y-%m-%d')
 246        return s
 247
 248class Error(_BaseException):
 249    pass   #Exception that is the base class of all other error
 250           #exceptions. You can use this to catch all errors with one
 251           #single 'except' statement. Warnings are not considered
 252           #errors and thus should not use this class as base. It must
 253           #be a subclass of the Python StandardError (defined in the
 254           #module exceptions).
 255
 256class Warning(_BaseException):
 257    pass
 258
 259class InterfaceError(Error):
 260    pass
 261
 262class DatabaseError(Error):
 263    pass
 264
 265class InternalError(DatabaseError):
 266    pass
 267
 268class OperationalError(DatabaseError):
 269    pass
 270
 271class ProgrammingError(DatabaseError):
 272    pass
 273
 274class IntegrityError(DatabaseError):
 275    pass
 276
 277class DataError(DatabaseError):
 278    pass
 279
 280class NotSupportedError(DatabaseError):
 281    pass
 282
 283verbose = False
 284
 285version = __doc__.split('-',2)[0] #v2.1 Cole
 286
 287def connect(connstr, timeout=30):               #v2.1 Simons
 288    "Connection string as in the ADO documentation, SQL timeout in seconds"
 289    try:
 290        if not onIronPython:
 291            pythoncom.CoInitialize()             #v2.1 Paj
 292        conn=Dispatch('ADODB.Connection') #connect _after_ CoIninialize v2.1.1 adamvan
 293    except:
 294        raise InterfaceError #Probably COM Error
 295    try:
 296        conn.CommandTimeout=timeout             #v2.1 Simons
 297        conn.ConnectionString=connstr
 298    except:
 299        raise Error
 300    if verbose:
 301        print '%s attempting: "%s"' % (version,connstr)
 302    try:
 303        conn.Open()
 304    except (Exception), e:
 305        raise DatabaseError(e)
 306    return Connection(conn)
 307        
 308apilevel='2.0' #String constant stating the supported DB API level.
 309
 310threadsafety=1 
 311# Integer constant stating the level of thread safety the interface supports,
 312# 1 = Threads may share the module, but not connections. 
 313# TODO: Have not tried this, maybe it is better than 1?
 314## 
 315## Possible values are:
 316##0 = Threads may not share the module. 
 317##1 = Threads may share the module, but not connections. 
 318##2 = Threads may share the module and connections. 
 319##3 = Threads may share the module, connections and cursors. 
 320
 321paramstyle='qmark'
 322#String constant stating the type of parameter marker formatting expected by the interface. 
 323# 'qmark' = Question mark style, e.g. '...WHERE name=?'
 324
 325
 326adXactBrowse                  =0x100      # from enum IsolationLevelEnum
 327adXactChaos                   =0x10       # from enum IsolationLevelEnum
 328adXactCursorStability         =0x1000     # from enum IsolationLevelEnum
 329adXactIsolated                =0x100000   # from enum IsolationLevelEnum
 330adXactReadCommitted           =0x1000     # from enum IsolationLevelEnum
 331adXactReadUncommitted         =0x100      # from enum IsolationLevelEnum
 332adXactRepeatableRead          =0x10000    # from enum IsolationLevelEnum
 333adXactSerializable            =0x100000   # from enum IsolationLevelEnum
 334
 335defaultIsolationLevel=adXactReadCommitted
 336#  Set defaultIsolationLevel on module level before creating the connection.
 337#   It may be one of the above
 338#   if you simply "import adodbapi", you'll say:
 339#   "adodbapi.adodbapi.defaultIsolationLevel=adodbapi.adodbapi.adXactBrowse", for example
 340
 341
 342adUseClient = 3     # from enum CursorLocationEnum      #v2.1 Rose
 343adUseServer = 2     # from enum CursorLocationEnum      #v2.1 Rose
 344
 345defaultCursorLocation=adUseServer                       #v2.1 Rose
 346#  Set defaultCursorLocation on module level before creating the connection.
 347#   It may be one of the above
 348
 349class Connection(object):
 350    # include connection attributes required by api definition.
 351    Warning = Warning
 352    Error = Error
 353    InterfaceError = InterfaceError
 354    DataError = DataError
 355    DatabaseError = DatabaseError
 356    OperationalError = OperationalError
 357    IntegrityError = IntegrityError
 358    InternalError = InternalError
 359    NotSupportedError = NotSupportedError
 360    ProgrammingError = ProgrammingError
 361
 362    def __init__(self,adoConn):       
 363        self.adoConn=adoConn
 364        self.supportsTransactions=False
 365        for indx in range(adoConn.Properties.Count):
 366            name = getIndexedValue(adoConn.Properties,indx).Name
 367            if name == 'Transaction DDL':
 368                if getIndexedValue(adoConn.Properties,indx).Value != 0:        #v2.1 Albrecht
 369                    self.supportsTransactions=True
 370                break
 371        self.adoConn.CursorLocation = defaultCursorLocation #v2.1 Rose
 372        if self.supportsTransactions:
 373            self.adoConn.IsolationLevel=defaultIsolationLevel
 374            self.adoConn.BeginTrans() #Disables autocommit
 375        self.errorhandler=None
 376        self.messages=[]
 377        if verbose:
 378            print 'adodbapi New connection at %X' % id(self)
 379
 380    def _raiseConnectionError(self,errorclass,errorvalue):
 381        eh=self.errorhandler
 382        if eh==None:
 383            eh=standardErrorHandler
 384        eh(self,None,errorclass,errorvalue)
 385
 386    def _closeAdoConnection(self):                  #all v2.1 Rose
 387        """close the underlying ADO Connection object,
 388           rolling it back first if it supports transactions."""
 389        if self.supportsTransactions:
 390            self.adoConn.RollbackTrans()
 391        self.adoConn.Close()
 392        if verbose:
 393            print 'adodbapi Closed connection at %X' % id(self)
 394
 395    def close(self):
 396        """Close the connection now (rather than whenever __del__ is called).
 397        
 398        The connection will be unusable from this point forward;
 399        an Error (or subclass) exception will be raised if any operation is attempted with the connection.
 400        The same applies to all cursor objects trying to use the connection. 
 401        """
 402        self.messages=[]
 403        
 404        try:
 405            self._closeAdoConnection()                      #v2.1 Rose
 406        except (Exception), e:
 407            self._raiseConnectionError(InternalError,e)
 408        if not onIronPython:
 409            pythoncom.CoUninitialize()                             #v2.1 Paj
 410
 411    def commit(self):
 412        """Commit any pending transaction to the database.
 413
 414        Note that if the database supports an auto-commit feature,
 415        this must be initially off. An interface method may be provided to turn it back on. 
 416        Database modules that do not support transactions should implement this method with void functionality. 
 417        """
 418        self.messages=[]        
 419        try:
 420            if self.supportsTransactions:
 421                self.adoConn.CommitTrans()
 422                if not(self.adoConn.Attributes & adXactCommitRetaining):
 423                    #If attributes has adXactCommitRetaining it performs retaining commits that is,
 424                    #calling CommitTrans automatically starts a new transaction. Not all providers support this.
 425                    #If not, we will have to start a new transaction by this command:                
 426                    self.adoConn.BeginTrans()
 427        except (Exception), e:
 428            self._raiseConnectionError(Error,e)        
 429
 430    def rollback(self):
 431        """In case a database does provide transactions this method causes the the database to roll back to
 432        the start of any pending transaction. Closing a connection without committing the changes first will
 433        cause an implicit rollback to be performed.
 434
 435        If the database does not support the functionality required by the method, the interface should
 436        throw an exception in case the method is used. 
 437        The preferred approach is to not implement the method and thus have Python generate
 438        an AttributeError in case the method is requested. This allows the programmer to check for database
 439        capabilities using the standard hasattr() function. 
 440
 441        For some dynamically configured interfaces it may not be appropriate to require dynamically making
 442        the method available. These interfaces should then raise a NotSupportedError to indicate the
 443        non-ability to perform the roll back when the method is invoked. 
 444        """
 445        self.messages=[]        
 446        if self.supportsTransactions:
 447            self.adoConn.RollbackTrans()
 448            if not(self.adoConn.Attributes & adXactAbortRetaining):
 449                #If attributes has adXactAbortRetaining it performs retaining aborts that is,
 450                #calling RollbackTrans automatically starts a new transaction. Not all providers support this.
 451                #If not, we will have to start a new transaction by this command:
 452                self.adoConn.BeginTrans()
 453        else:
 454            self._raiseConnectionError(NotSupportedError,None)
 455        
 456        #TODO: Could implement the prefered method by havins two classes,
 457        # one with trans and one without, and make the connect function choose which one.
 458        # the one without transactions should not implement rollback
 459
 460    def cursor(self):
 461        "Return a new Cursor Object using the connection."
 462        self.messages=[]        
 463        return Cursor(self)
 464    
 465    def printADOerrors(self):
 466        j=self.adoConn.Errors.Count
 467        if j:
 468            print 'ADO Errors:(%i)' % j
 469        for i in range(j):
 470            e=self.adoConn.Errors[i]
 471            print 'Description: %s' % e.Description
 472            print 'Number: %s ' % e.Number
 473            try:
 474                print 'Number constant:' % adoErrors[e.Number]
 475            except:
 476                pass
 477            print 'Source: %s' %e.Source
 478            print 'NativeError: %s' %e.NativeError
 479            print 'SQL State: %s' %e.SQLState
 480            if e.Number == -2147217871:
 481                print ' Error means that the Query timed out.\n Try using adodbpi.connect(constr,timeout=Nseconds)'
 482
 483    def __del__(self):
 484        try:
 485            self._closeAdoConnection()                  #v2.1 Rose
 486        except:
 487            pass
 488        self.adoConn=None
 489
 490class Cursor(object):
 491    description=None
 492##    This read-only attribute is a sequence of 7-item sequences.
 493##    Each of these sequences contains information describing one result column:
 494##        (name, type_code, display_size, internal_size, precision, scale, null_ok).
 495##    This attribute will be None for operations that do not return rows or if the
 496##    cursor has not had an operation invoked via the executeXXX() method yet. 
 497##    The type_code can be interpreted by comparing it to the Type Objects specified in the section below. 
 498
 499    rowcount = -1
 500##    This read-only attribute specifies the number of rows that the last executeXXX() produced
 501##    (for DQL statements like select) or affected (for DML statements like update or insert). 
 502##    The attribute is -1 in case no executeXXX() has been performed on the cursor or
 503##    the rowcount of the last operation is not determinable by the interface.[7] 
 504    
 505    arraysize=1
 506##    This read/write attribute specifies the number of rows to fetch at a time with fetchmany().
 507##    It defaults to 1 meaning to fetch a single row at a time. 
 508##    Implementations must observe this value with respect to the fetchmany() method,
 509##    but are free to interact with the database a single row at a time.
 510##    It may also be used in the implementation of executemany(). 
 511
 512    def __init__(self,connection):
 513        self.messages=[]        
 514        self.conn=connection
 515        self.rs=None
 516        self.description=None
 517        self.errorhandler=connection.errorhandler
 518        if verbose:
 519            print 'adodbapi New cursor at %X on conn %X' % (id(self),id(self.conn))
 520
 521    def __iter__(self):                   # [2.1 Zamarev]
 522        return iter(self.fetchone, None)  # [2.1 Zamarev]
 523       
 524    def _raiseCursorError(self,errorclass,errorvalue):
 525        eh=self.errorhandler
 526        if eh==None:
 527            eh=standardErrorHandler
 528        eh(self.conn,self,errorclass,errorvalue)
 529
 530    def callproc(self, procname, parameters=None):
 531        """Call a stored database procedure with the given name.
 532
 533            The sequence of parameters must contain one entry for each argument that the procedure expects.
 534            The result of the call is returned as modified copy of the input sequence.
 535            Input parameters are left untouched, output and input/output parameters replaced
 536            with possibly new values. 
 537
 538            The procedure may also provide a result set as output, which is
 539            then available through the standard fetchXXX() methods.
 540        """
 541        self.messages=[]                
 542        return self._executeHelper(procname,True,parameters)
 543
 544    def _returnADOCommandParameters(self,adoCommand):
 545        retLst=[]
 546        for i in range(adoCommand.Parameters.Count):
 547            p=getIndexedValue(adoCommand.Parameters,i)
 548            if verbose > 2:
 549                print 'return', p.Name, p.Type, p.Direction, repr(p.Value)
 550            type_code=p.Type 
 551            pyObject=convertVariantToPython(p.Value,type_code)
 552            if p.Direction == adParamReturnValue:
 553                self.returnValue=pyObject
 554            else:
 555                retLst.append(pyObject)
 556        return retLst
 557
 558    def _makeDescriptionFromRS(self,rs):
 559        self.rs = rs        #v2.1.1 bkline
 560        if not rs:
 561            self.description = None
 562        elif rs.State == adStateClosed: 
 563            self.description = None
 564        else:
 565            # Since the current implementation has a forward-only cursor, RecordCount will always return -1
 566            # The ADO documentation hints that obtaining the recordcount may be timeconsuming
 567            #   "If the Recordset object does not support approximate positioning, this property
 568            #    may be a significant drain on resources # [ekelund]
 569            # Therefore, COM will not return rowcount in most cases. [Cole]
 570            # Switching to client-side cursors will force a static cursor,
 571            # and rowcount will then be set accurately [Cole]
 572            nOfFields=rs.Fields.Count
 573            self.description=[]
 574            for i in range(nOfFields):
 575                f=getIndexedValue(rs.Fields,i)
 576                name=f.Name
 577                type_code=f.Type 
 578                if not(rs.EOF or rs.BOF):
 579                    display_size=f.ActualSize #TODO: Is this the correct defintion according to the DB API 2 Spec ?
 580                else:
 581                    display_size=None
 582                internal_size=f.DefinedSize
 583                precision=f.Precision
 584                scale=f.NumericScale
 585                null_ok= bool(f.Attributes & adFldMayBeNull)          #v2.1 Cole 
 586                self.description.append((name, type_code, display_size, internal_size, precision, scale, null_ok))
 587
 588    def close(self):        
 589        """Close the cursor now (rather than whenever __del__ is called).
 590            The cursor will be unusable from this point forward; an Error (or subclass)
 591            exception will be raised if any operation is attempted with the cursor.
 592        """
 593        self.messages=[]                
 594        self.conn = None    #this will make all future method calls on me throw an exception
 595        if self.rs and self.rs.State != adStateClosed: # rs exists and is open      #v2.1 Rose
 596            self.rs.Close()                                                         #v2.1 Rose
 597            self.rs = None  #let go of the recordset so ADO will let it be disposed #v2.1 Rose
 598
 599# ------------------------
 600# a note about Strategies:
 601# Ekelund struggled to find ways to keep ADO happy with passed parameters.
 602# He finally decided to try four "strategies" of parameter passing:
 603# 1. Let COM define the parameter list & pass date-times in COMdate format
 604# 2. Build a parameter list with python & pass date-times in COMdate format
 605# 3. Let COM define the parameter list & pass date-times as ISO date strings.
 606# 4. Build a parameter list with python & pass date-times as ISO date strings.
 607# For each "execute" call, he would try each way, then pass all four sets of error
 608#  messages back if all four attempts failed.
 609# My optimization is as follows:
 610#  1. Try to use the COM parameter list.
 611#  2. If the Refresh() call blows up, then build a perameter list
 612#  3. If the python data is a date but the ADO is a string,
 613#      or we built the parameter list,
 614#     then pass date-times as ISO dates,
 615#     otherwise, convert to a COM date.
 616# -- Vernon Cole v2.1
 617# (code in executeHelper freely reformatted by me)
 618#---------------------
 619    def _executeHelper(self,operation,isStoredProcedureCall,parameters=None):        
 620        if self.conn == None:
 621            self._raiseCursorError(Error,None)
 622            return
 623        returnValueIndex=None
 624
 625        if verbose > 1 and parameters:
 626            print 'adodbapi parameters=',repr(parameters)
 627
 628        parmIndx=-1
 629        try:                
 630            self.cmd=Dispatch("ADODB.Command")
 631            self.cmd.ActiveConnection=self.conn.adoConn
 632            self.cmd.CommandTimeout = self.conn.adoConn.CommandTimeout  #v2.1 Simons
 633            self.cmd.CommandText=operation
 634            if isStoredProcedureCall:
 635                self.cmd.CommandType=adCmdStoredProc
 636            else:
 637                self.cmd.CommandType=adCmdText
 638
 639            returnValueIndex=-1            # Initialize to impossible value
 640            if parameters != None:
 641                try: # attempt to use ADO's parameter list
 642                    self.cmd.Parameters.Refresh()
 643                    defaultParameterList = False
 644                except: # if it blows up
 645                    defaultParameterList = True
 646                if defaultParameterList:  #-- build own parameter list
 647                    if verbose:
 648                        print 'error in COM Refresh(). adodbapi building default parameter list'
 649                    for i,elem in enumerate(parameters):
 650                        name='p%i' % i
 651                        adotype = pyTypeToADOType(elem)
 652                        p=self.cmd.CreateParameter(name,adotype) # Name, Type, Direction, Size, Value
 653                        if isStoredProcedureCall:
 654                            p.Direction=adParamUnknown
 655                        self.cmd.Parameters.Append(p)  
 656                if verbose > 2:
 657                       for i in range(self.cmd.Parameters.Count):
 658                            P = self.cmd.Parameters[i]
 659                            print 'adodbapi parameter attributes=', P.Name, P.Type, P.Direction, P.Size
 660                if isStoredProcedureCall:
 661                    cnt=self.cmd.Parameters.Count
 662                    if cnt!=len(parameters):
 663                        for i in range(cnt):
 664                            dir = getIndexedValue(self.cmd.Parameters,i).Direction   
 665                            if dir == adParamReturnValue:
 666                                returnValueIndex=i
 667                                break
 668                for elem in parameters:
 669                    parmIndx+=1
 670                    if parmIndx == returnValueIndex:
 671                        parmIndx+=1
 672                    p=getIndexedValue(self.cmd.Parameters,parmIndx)   
 673                    if verbose > 2:
 674                        print 'Parameter %d ADOtype %d, python %s' % (parmIndx,p.Type,type(elem))
 675                    if p.Direction in [adParamInput,adParamInputOutput,adParamUnknown]:
 676                        tp = type(elem) # python type
 677                        if tp in dateconverter.types:
 678                            if not defaultParameterList and p.Type in adoDateTimeTypes:
 679                                p.Value=dateconverter.COMDate(elem)
 680                            else: #probably a string
 681                    #Known problem with JET Provider. Date can not be specified as a COM date.
 682                    # See for example..http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b284843
 683                    # One workaround is to provide the date as a string in the format 'YYYY-MM-dd'
 684                                s = dateconverter.DateObjectToIsoFormatString(elem)
 685                                p.Value = s
 686                                p.Size = len(s)
 687                        elif tp in StringTypes:            #v2.1 Jevon
 688                            L = len(elem)
 689                            if defaultParameterList:
 690                                p.Value = elem
 691                            else:
 692                                if p.Type in adoStringTypes: #v2.2.1 Cole
 693                                    L = min(L,p.Size) #v2.1 Cole limit data to defined size
 694                                    p.Value = elem[:L]       #v2.1 Jevon & v2.1 Cole
 695                                else:
 696                                    p.Value = elem    # dont limit if db column is numeric
 697                            if L>0:   #v2.1 Cole something does not like p.Size as Zero
 698                                p.Size = L           #v2.1 Jevon
 699                        elif tp == memoryViewType: #v2.1 Cole -- ADO BINARY
 700                            p.AppendChunk(elem)
 701                        elif isinstance(elem,decimal.Decimal): #v2.2 Cole
 702                            s = str(elem)
 703                            p.Value = s
 704                            p.Size = len(s)
 705                        elif isinstance(elem, longType) and onIronPython: # Iron Python Long
 706                            s = SystemDecimal(elem)  # feature workaround for IPy 2.0
 707                            p.Value = s
 708                        else:
 709                            p.Value=elem
 710                        if verbose > 2:
 711                            print 'Parameter %d type %d stored as: %s' % (parmIndx,p.Type,repr(p.Value))
 712            parmIndx = -2 # kludge to prevent exception handler picking out one parameter
 713
 714            # ----- the actual SQL is executed here ---
 715            if onIronPython:
 716                ra = Reference[int]()
 717                rs = self.cmd.Execute(ra)
 718                count = ra.Value 
 719            else: #pywin32
 720                rs, count = self.cmd.Execute()
 721            # ----- ------------------------------- ---
 722        except Exception, e:
 723            tbk = u'\n--ADODBAPI\n'
 724            if parmIndx >= 0:
 725                tbk += u'-- Trying parameter %d = %s\n' \
 726                    %(parmIndx, repr(parameters[parmIndx]))
 727            exc_type,exc_value,exc_traceback = sys.exc_info()
 728            tblist=traceback.format_exception(exc_type,exc_value,exc_traceback,8)
 729            tb=''.join(tblist)
 730            tracebackhistory = tbk + tb + u'-- on command: "%s"\n-- with parameters: %s' \
 731                               %(operation,parameters)
 732            self._raiseCursorError(DatabaseError,tracebackhistory)
 733            return
 734
 735        try:
 736            self.rowcount = rs.RecordCount
 737        except:
 738            self.rowcount = count
 739      
 740        self._makeDescriptionFromRS(rs)
 741
 742        if isStoredProcedureCall and parameters != None:
 743            return self._returnADOCommandParameters(self.cmd)
 744        
 745    def execute(self, operation, parameters=None):
 746        """Prepare and execute a database operation (query or command).
 747        
 748            Parameters may be provided as sequence or mapping and will be bound to variables in the operation.
 749            Variables are specified in a database-specific notation
 750            (see the module's paramstyle attribute for details). [5] 
 751            A reference to the operation will be retained by the cursor.
 752            If the same operation object is passed in again, then the cursor
 753            can optimize its behavior. This is most effective for algorithms
 754            where the same operation is used, but different parameters are bound to it (many times). 
 755
 756            For maximum efficiency when reusing an operation, it is best to use
 757            the setinputsizes() method to specify the parameter types and sizes ahead of time.
 758            It is legal for a parameter to not match the predefined information;
 759            the implementation should compensate, possibly with a loss of efficiency. 
 760
 761            The parameters may also be specified as list of tuples to e.g. insert multiple rows in
 762            a single operation, but this kind of usage is depreciated: executemany() should be used instead. 
 763
 764            Return values are not defined.
 765
 766            [5] The module will use the __getitem__ method of the parameters object to map either positions
 767            (integers) or names (strings) to parameter values. This allows for both sequences and mappings
 768            to be used as input. 
 769            The term "bound" refers to the process of binding an input value to a database execution buffer.
 770            In practical terms, this means that the input value is directly used as a value in the operation.
 771            The client should not be required to "escape" the value so that it can be used -- the value
 772            should be equal to the actual database value. 
 773        """
 774        self.messages=[]                
 775        self._executeHelper(operation,False,parameters)
 776            
 777    def executemany(self, operation, seq_of_parameters):
 778        """Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.
 779        
 780            Return values are not defined. 
 781        """
 782        self.messages=[]                
 783        totrecordcount = 0
 784        canCount=True
 785        for params in seq_of_parameters:
 786            self.execute(operation, params)
 787            if self.rowcount == -1:
 788                canCount=False
 789            elif canCount:
 790                totrecordcount+=self.rowcount
 791                
 792        if canCount:                  
 793            self.rowcount=totrecordcount
 794        else:
 795            self.rowcount=-1
 796
 797    def _fetch(self, rows=None):
 798        """ Fetch rows from the recordset.
 799        rows is None gets all (for fetchall).
 800        """
 801        rs=self.rs
 802        if self.conn == None:
 803            self._raiseCursorError(Error,None)
 804            return
 805        if not rs or rs.State == adStateClosed: #v2.1.1 bkline
 806            self._raiseCursorError(Error,None)
 807            return
 808        else:
 809            if rs.State == adStateClosed or rs.BOF or rs.EOF:
 810                if rows == 1: return None # fetchone can return None
 811                else: return [] # fetchall and fetchmany return empty lists
 812
 813            if rows:
 814                ado_results = self.rs.GetRows(rows)
 815            else:
 816                ado_results = self.rs.GetRows()
 817
 818            d=self.description
 819            returnList=[]
 820            i=0
 821            if onIronPython:
 822                type_codes = [descTuple[1] for descTuple in d]
 823                for j in range(len(ado_results)/len(d)):
 824                    L = []
 825                    for i in range(len(d)):
 826                        L.append(convertVariantToPython(ado_results[i,j],type_codes[i]))
 827                    returnList.append(tuple(L))
 828                return tuple(returnList)    
 829            else: #pywin32
 830                for descTuple in d:
 831                    # Desctuple =(name, type_code, display_size, internal_size, precision, scale, null_ok).
 832                    type_code=descTuple[1]
 833                    returnList.append([convertVariantToPython(r,type_code) for r in ado_results[i]])
 834                    i+=1
 835                return tuple(zip(*returnList))
 836
 837    def fetchone(self):
 838        """ Fetch the next row of a query result set, returning a single sequence,
 839            or None when no more data is available.
 840
 841            An Error (or subclass) exception is raised if the previous call to executeXXX()
 842            did not produce any result set or no call was issued yet. 
 843        """
 844        self.messages=[]                
 845        ret = self._fetch(1)
 846        if ret: # return record (not list of records)
 847            return ret[0]
 848        else:
 849            return ret # (in case of None)
 850
 851                    
 852    def fetchmany(self, size=None):
 853        """Fetch the next set of rows of a query result, returning a list of tuples. An empty sequence is returned when no more rows are available.
 854        
 855        The number of rows to fetch per call is specified by the parameter.
 856        If it is not given, the cursor's arraysize determines the number of rows to be fetched.
 857        The method should try to fetch as many rows as indicated by the size parameter.
 858        If this is not possible due to the specified number of rows not being available,
 859        fewer rows may be returned. 
 860
 861        An Error (or subclass) exception is raised if the previous call to executeXXX()
 862        did not produce any result set or no call was issued yet. 
 863
 864        Note there are performance considerations involved with the size parameter.
 865        For optimal performance, it is usually best to use the arraysize attribute.
 866        If the size parameter is used, then it is best for it to retain the same value from
 867        one fetchmany() call to the next. 
 868        """
 869        self.messages=[]                
 870        if size == None:
 871            size = self.arraysize
 872        return self._fetch(size)
 873
 874    def fetchall(self):
 875        """Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).
 876
 877            Note that the cursor's arraysize attribute
 878            can affect the performance of this operation. 
 879            An Error (or subclass) exception is raised if the previous call to executeXXX()
 880            did not produce any result set or no call was issued yet. 
 881        """
 882        self.messages=[]                
 883        return self._fetch()
 884
 885    def nextset(self):
 886        """Make the cursor skip to the next available set, discarding any remaining rows from the current set. 
 887
 888            If there are no more sets, the method returns None. Otherwise, it returns a true
 889            value and subsequent calls to the fetch methods will return rows from the next result set. 
 890
 891            An Error (or subclass) exception is raised if the previous call to executeXXX()
 892            did not produce any result set or no call was issued yet.
 893        """
 894        self.messages=[]                
 895        if not self.conn:
 896            self._raiseCursorError(Error,None)
 897            return
 898        if not self.rs:
 899            self._raiseCursorError(Error,None)
 900            return
 901        else:
 902            if onIronPython:
 903               try:
 904                    rs = self.rs.NextRecordset()
 905               except TypeError:
 906                    rs = None
 907               except Error, exc:
 908                    self._raiseCursorError(NotSupportedError, exc.args)
 909            else: #pywin32
 910                try:                                               #[begin 2.1 ekelund]
 911                    rsTuple=self.rs.NextRecordset()                # 
 912                except pywintypes.com_error, exc:                  # return appropriate error
 913                    self._raiseCursorError(NotSupportedError, exc.args)#[end 2.1 ekelund]
 914                rs=rsTuple[0]
 915            self._makeDescriptionFromRS(rs)
 916            if rs:
 917                return True
 918            return None
 919
 920    def setinputsizes(self,sizes):
 921        pass
 922
 923    def setoutputsize(self, size, column=None):
 924        pass
 925
 926#Type Objects and Constructors
 927#Many databases need to have the input in a particular format for binding to an operation's input parameters.
 928#For example, if an input is destined for a DATE column, then it must be bound to the database in a particular
 929#string format. Similar problems exist for "Row ID" columns or large binary items (e.g. blobs or RAW columns).
 930#This presents problems for Python since the parameters to the executeXXX() method are untyped.
 931#When the database module sees a Python string object, it doesn't know if it should be bound as a simple CHAR
 932#column, as a raw BINARY item, or as a DATE. 
 933#
 934#To overcome this problem, a module must provide the constructors defined below to create objects that can
 935#hold special values. When passed to the cursor methods, the module can then detect the proper type of
 936#the input parameter and bind it accordingly. 
 937
 938#A Cursor Object's description attribute returns information about each of the result columns of a query.
 939#The type_code must compare equal to one of Type Objects defined below. Type Objects may be equal to more than
 940#one type code (e.g. DATETIME could be equal to the type codes for date, time and timestamp columns;
 941#see the Implementation Hints below for details). 
 942
 943def Date(year,month,day):
 944    "This function constructs an object holding a date value. "
 945    return dateconverter.Date(year,month,day)
 946
 947def Time(hour,minute,second):
 948    "This function constructs an object holding a time value. "
 949    return dateconverter.Time(hour,minute,second)
 950
 951def Timestamp(year,month,day,hour,minute,second):
 952    "This function constructs an object holding a time stamp value. "
 953    return dateconverter.Timestamp(year,month,day,hour,minute,second)
 954
 955def DateFromTicks(ticks):
 956    """This function constructs an object holding a date value from the given ticks value
 957    (number of seconds since the epoch; see the documentation of the standard Python time module for details). """
 958    return Date(*time.gmtime(ticks)[:3])
 959
 960def TimeFromTicks(ticks):
 961    """This function constructs an object holding a time value from the given ticks value
 962    (number of seconds since the epoch; see the documentation of the standard Python time module for details). """
 963    return Time(*time.gmtime(ticks)[3:6])
 964
 965def TimestampFromTicks(ticks):
 966    """This function constructs an object holding a time stamp value from the given
 967    ticks value (number of seconds since the epoch;
 968    see the documentation of the standard Python time module for details). """
 969    return Timestamp(*time.gmtime(ticks)[:6])
 970
 971def Binary(aString):
 972    """This function constructs an object capable of holding a binary (long) string value. """
 973    return makeByteBuffer(aString)
 974
 975#v2.1 Cole comment out: BinaryType = Binary('a')
 976
 977#SQL NULL values are represented by the Python None singleton on input and output. 
 978
 979#Note: Usage of Unix ticks for database interfacing can cause troubles because of the limited date range they cover. 
 980
 981def pyTypeToADOType(d):
 982    tp=type(d)
 983    try:
 984        return typeMap[tp]
 985    except KeyError:
 986        if tp in dateconverter.types:
 987            return adDate
 988        if tp == type(decimal.Decimal):
 989            return adDecimal
 990    raise DataError
 991        
 992adCmdText = 1 
 993adCmdStoredProc = 4
 994
 995adParamInput                  =0x1        # from enum ParameterDirectionEnum
 996adParamInputOutput            =0x3        # from enum ParameterDirectionEnum
 997adParamOutput                 =0x2        # from enum ParameterDirectionEnum
 998adParamReturnValue            =0x4        # from enum ParameterDirectionEnum
 999adParamUnknown                =0x0        # from enum ParameterDirectionEnum
1000
1001
1002adStateClosed =0
1003
1004adFldMayBeNull=0x40 
1005
1006adModeShareExclusive=0xc
1007
1008adXactCommitRetaining= 131072
1009adXactAbortRetaining = 262144 
1010
1011
1012adArray                       =0x2000     # from enum DataTypeEnum
1013adBSTR                        =0x8        # from enum DataTypeEnum
1014adBigInt                      =0x14       # from enum DataTypeEnum
1015adBinary                      =0x80       # from enum DataTypeEnum
1016adBoolean                     =0xb        # from enum DataTypeEnum
1017adChapter                     =0x88       # from enum DataTypeEnum
1018adChar                        =0x81       # from enum DataTypeEnum
1019adCurrency                    =0x6        # from enum DataTypeEnum
1020adDBDate                      =0x85       # from enum DataTypeEnum
1021adDBTime                      =0x86       # from enum DataTypeEnum
1022adDBTimeStamp                 =0x87       # from enum DataTypeEnum
1023adDate                        =0x7        # from enum DataTypeEnum
1024adDecimal                     =0xe        # from enum DataTypeEnum
1025adDouble                      =0x5        # from enum DataTypeEnum
1026adEmpty                       =0x0        # from enum DataTypeEnum
1027adError                       =0xa        # from enum DataTypeEnum
1028adFileTime                    =0x40       # from enum DataTypeEnum
1029adGUID                        =0x48       # from enum DataTypeEnum
1030adIDispatch                   =0x9        # from enum DataTypeEnum
1031adIUnknown                    =0xd        # from enum DataTypeEnum
1032adInteger                     =0x3        # from enum DataTypeEnum
1033adLongVarBinary               =0xcd       # from enum DataTypeEnum
1034adLongVarChar                 =0xc9       # from enum DataTypeEnum
1035adLongVarWChar                =0xcb       # from enum DataTypeEnum
1036adNumeric                     =0x83       # from enum DataTypeEnum
1037adPropVariant                 =0x8a       # from enum DataTypeEnum
1038adSingle                      =0x4        # from enum DataTypeEnum
1039adSmallInt                    =0x2        # from enum DataTypeEnum
1040adTinyInt                     =0x10       # from enum DataTypeEnum
1041adUnsignedBigInt              =0x15       # from enum DataTypeEnum
1042adUnsignedInt                 =0x13       # from enum DataTypeEnum
1043adUnsignedSmallInt            =0x12       # from enum DataTypeEnum
1044adUnsignedTinyInt             =0x11       # from enum DataTypeEnum
1045adUserDefined                 =0x84       # from enum DataTypeEnum
1046adVarBinary                   =0xcc       # from enum DataTypeEnum
1047adVarChar                     =0xc8       # from enum DataTypeEnum
1048adVarNumeric                  =0x8b       # from enum DataTypeEnum
1049adVarWChar                    =0xca       # from enum DataTypeEnum
1050adVariant                     =0xc        # from enum DataTypeEnum
1051adWChar                       =0x82       # from enum DataTypeEnum
1052
1053#ado DataTypeEnum -- copy of above, here in decimal, sorted by value
1054#adEmpty 0 Specifies no value (DBTYPE_EMPTY). 
1055#adSmallInt 2 Indicates a two-byte signed integer (DBTYPE_I2). 
1056#adInteger 3 Indicates a four-byte signed integer (DBTYPE_I4). 
1057#adSingle 4 Indicates a single-precision floating-point value (DBTYPE_R4). 
1058#adDouble 5 Indicates a double-precision floating-point value (DBTYPE_R8). 
1059#adCurrency 6 Indicates a currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an eight-byte signed integer scaled by 10,000. 
1060#adDate 7 Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day. 
1061#adBSTR 8 Indicates a null-terminated character string (Unicode) (DBTYPE_BSTR). 
1062#adIDispatch 9 Indicates a pointer to an IDispatch interface on a COM object (DBTYPE_IDISPATCH). 
1063#adError 10 Indicates a 32-bit error code (DBTYPE_ERROR). 
1064#adBoolean 11 Indicates a boolean value (DBTYPE_BOOL). 
1065#adVariant 12 Indicates an Automation Variant (DBTYPE_VARIANT). 
1066#adIUnknown 13 Indicates a pointer to an IUnknown interface on a COM object (DBTYPE_IUNKNOWN). 
1067#adDecimal 14 Indicates an exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL). 
1068#adTinyInt 16 Indicates a one-byte signed integer (DBTYPE_I1). 
1069#adUnsignedTinyInt 17 Indicates a one-byte unsigned integer (DBTYPE_UI1). 
1070#adUnsignedSmallInt 18 Indicates a two-byte unsigned integer (DBTYPE_UI2). 
1071#adUnsignedInt 19 Indicates a four-byte unsigned integer (DBTYPE_UI4). 
1072#adBigInt 20 Indicates an eight-byte signed integer (DBTYPE_I8). 
1073#adUnsignedBigInt 21 Indicates an eight-byte unsigned integer (DBTYPE_UI8). 
1074#adFileTime 64 Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME). 
1075#adGUID 72 Indicates a globally unique identifier (GUID) (DBTYPE_GUID). 
1076#adBinary 128 Indicates a binary value (DBTYPE_BYTES). 
1077#adChar 129 Indicates a string value (DBTYPE_STR). 
1078#adWChar 130 Indicates a null-terminated Unicode character string (DBTYPE_WSTR). 
1079#adNumeric 131 Indicates an exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC). adUserDefined 132 Indicates a user-defined variable (DBTYPE_UDT). 
1080#adUserDefined 132 Indicates a user-defined variable (DBTYPE_UDT). 
1081#adDBDate 133 Indicates a date value (yyyymmdd) (DBTYPE_DBDATE). 
1082#adDBTime 134 Indicates a time value (hhmmss) (DBTYPE_DBTIME). 
1083#adDBTimeStamp 135 Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP). 
1084#adChapter 136 Indicates a four-byte chapter value that identifies rows in a child rowset (DBTYPE_HCHAPTER). 
1085#adPropVariant 138 Indicates an Automation PROPVARIANT (DBTYPE_PROP_VARIANT). 
1086#adVarNumeric 139 Indicates a numeric value (Parameter object only). 
1087#adVarChar 200 Indicates a string value (Parameter object only). 
1088#adLongVarChar 201 Indicates a long string value (Paramete…

Large files files are truncated, but you can click here to view the full file