Blobs with ADO

[Gabe Gittings]

Found the answer from a colleague at work though it is really simple
zero direct documentation occurs. Hope this helps someone.

def insertRecordBLOB(self,table,fieldValueDict,blobField, blob):
        """inserts a record where the blobField is an OLE Object such
as an excel file
           blob is either a string with the file name of the object or
the binary object itself
           i.e. if you pull the blob out of the database to insert
into a new record.
        """
        if type(blob) == str:
            file = open(blob,'rb').read()
        else:
            file = blob
        binaryBuffer = buffer(file)
        self.openRcd(table)
        mstream = win32com.client.Dispatch(r'ADODB.Stream')
        mstream.Type = win32com.client.constants.adTypeBinary
        mstream.Open()
        mstream.Write(binaryBuffer)
        mstream.Position = 0
        self.acsRst.AddNew()
        
        fields = fieldValueDict.keys()
        for y in range(len(fields)):
          self.setFieldValue(fields[y],fieldValueDict[fields[y]]) 
        
        self.setFieldValue(blobField,mstream.Read())
        self.acsRst.Update()
        self.closeRcd()

    def writeBLOBToDisk(self,table,field,value,blobField,fileName):
        """Saves blob to disk for first record found to fileName
location
        """
        self.selectRcdSet(table,field,value)
        mstream = win32com.client.Dispatch(r'ADODB.Stream')
        mstream.Type = win32com.client.constants.adTypeBinary
        mstream.Open()
        mstream.Write(buffer(self.getFieldValue(blobField)))
        mstream.Position = 0
        mstream.SaveToFile(fileName)
        self.closeRcd()


The trick is the built in function buffer() it converts the file
object to a COM friendly variant.



ggittings at arraybiopharma.com (Gabe Gittings) wrote in message news:<5523ec5.0212041118.668bd16b at posting.google.com>...
> I am attempting to insert excel files into an access db through a
> small python app. I have run into difficulty because the docs. are so
> slim however. The following is what I have so far.
> 
>    def insertBLOB(self,table,blobField, blobFileLocation):
>         file = open(blobFileLocation,'rb')
>         
>         mstr = win32com.client.Dispatch(r'ADODB.Stream')
>         mstr.Type = win32com.client.constants.adTypeBinary
>         mstr.Open()
>         mstr.Write(file)
>         mstr.Position = 0
>         
>         self.openRcd(table)  #method that opens record set via ADO
>         self.acsRst.AddNew()
>         
>         self.acsRst.Fields.Item(blobField).Value = mstream.Read()
>         self.acsRst.Update()
> 
> 
> Now I know that the Write() method of the Stream object only accepts
> buffers as COM variants but I don't have the slightest clue as to how
> to get the file into an array that can be passed to the Stream object.
> The documentation is virtually non-existant. Also I am not sure if the
> Stream Object is the best way to go in the first place. Any help would
> be much appreciated.
> 
> Thanks