Save Image to Field


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Save Image to Field

Hybrid View

  1. #1
    Chicho Guest

    Save Image to Field


    Hello, Iam trying to save an image to a Field in a Recordset, but dont really
    Know how.
    can you help me?

  2. #2
    Gary Johnson Guest

    Re: Save Image to Field


    "Chicho" <chicho_chicho@hotmail.com> wrote:
    >
    >Hello, Iam trying to save an image to a Field in a Recordset, but dont really
    >Know how.
    >can you help me?


    Once you have a recordset with the image field try something along the lines
    of this...

    BlockSize = 1024
    Set oField = Rs("ImageField")
    FileLen = oField.ActualSize
    NumBlocks = FileLen / BlockSize
    LeftOver = FileLen Mod BlockSize
    'For ASP Change the Img to Response.BinaryWrite
    Img = oField.GetChunk(LeftOver)
    For Ctr = 1 to NumBlocks
    Img = Img & field.GetChunk(BlockSize)
    Next
    Rs.Close

    Hope this helps!

    Gary Johnson
    Sr Database Engineer


  3. #3
    D. Patrick Hoerter Guest

    Re: Save Image to Field

    Gary,

    He's trying to SAVE the image, not retrieve it.

    Chicho,

    You'll need to open the file for Binary access, and read it a chunk at a
    time (say, 2K or so), and use the ADO AppendChunk method to append to the
    field in your recordset.

    Regards,
    D. Patrick Hoerter

    Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >
    >"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>
    >>Hello, Iam trying to save an image to a Field in a Recordset, but dont

    really
    >>Know how.
    >>can you help me?

    >
    >Once you have a recordset with the image field try something along the

    lines
    >of this...
    >
    >BlockSize = 1024
    >Set oField = Rs("ImageField")
    >FileLen = oField.ActualSize
    >NumBlocks = FileLen / BlockSize
    >LeftOver = FileLen Mod BlockSize
    >'For ASP Change the Img to Response.BinaryWrite
    >Img = oField.GetChunk(LeftOver)
    >For Ctr = 1 to NumBlocks
    > Img = Img & field.GetChunk(BlockSize)
    >Next
    >Rs.Close
    >
    >Hope this helps!
    >
    >Gary Johnson
    >Sr Database Engineer
    >



  4. #4
    Justin Guest

    Re: Save Image to Field


    This code is almost what I'm chasing but I am stuck on what Img is supposed
    to be. It is an image control, an ole object, a file ..... hmmmm

    "D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >Gary,
    >
    > He's trying to SAVE the image, not retrieve it.
    >
    >Chicho,
    >
    > You'll need to open the file for Binary access, and read it a chunk

    at a
    >time (say, 2K or so), and use the ADO AppendChunk method to append to the
    >field in your recordset.
    >
    >Regards,
    >D. Patrick Hoerter
    >
    >Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >>
    >>"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>>
    >>>Hello, Iam trying to save an image to a Field in a Recordset, but dont

    >really
    >>>Know how.
    >>>can you help me?

    >>
    >>Once you have a recordset with the image field try something along the

    >lines
    >>of this...
    >>
    >>BlockSize = 1024
    >>Set oField = Rs("ImageField")
    >>FileLen = oField.ActualSize
    >>NumBlocks = FileLen / BlockSize
    >>LeftOver = FileLen Mod BlockSize
    >>'For ASP Change the Img to Response.BinaryWrite
    >>Img = oField.GetChunk(LeftOver)
    >>For Ctr = 1 to NumBlocks
    >> Img = Img & field.GetChunk(BlockSize)
    >>Next
    >>Rs.Close
    >>
    >>Hope this helps!
    >>
    >>Gary Johnson
    >>Sr Database Engineer
    >>

    >



  5. #5
    D. Patrick Hoerter Guest

    Re: Save Image to Field

    Justin,

    In that code snippet provided, I assume that Img is a byte array.

    Regards,
    D. Patrick Hoerter


    Justin wrote in message <395cec36$1@news.devx.com>...
    >
    >This code is almost what I'm chasing but I am stuck on what Img is supposed
    >to be. It is an image control, an ole object, a file ..... hmmmm
    >
    >"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>Gary,
    >>
    >> He's trying to SAVE the image, not retrieve it.
    >>
    >>Chicho,
    >>
    >> You'll need to open the file for Binary access, and read it a chunk

    >at a
    >>time (say, 2K or so), and use the ADO AppendChunk method to append to the
    >>field in your recordset.
    >>
    >>Regards,
    >>D. Patrick Hoerter
    >>
    >>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >>>
    >>>"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>>>
    >>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont

    >>really
    >>>>Know how.
    >>>>can you help me?
    >>>
    >>>Once you have a recordset with the image field try something along the

    >>lines
    >>>of this...
    >>>
    >>>BlockSize = 1024
    >>>Set oField = Rs("ImageField")
    >>>FileLen = oField.ActualSize
    >>>NumBlocks = FileLen / BlockSize
    >>>LeftOver = FileLen Mod BlockSize
    >>>'For ASP Change the Img to Response.BinaryWrite
    >>>Img = oField.GetChunk(LeftOver)
    >>>For Ctr = 1 to NumBlocks
    >>> Img = Img & field.GetChunk(BlockSize)
    >>>Next
    >>>Rs.Close
    >>>
    >>>Hope this helps!
    >>>
    >>>Gary Johnson
    >>>Sr Database Engineer
    >>>

    >>

    >



  6. #6
    Justin Guest

    Re: Save Image to Field


    I'm still having problems.

    The code noted below is suppposed to save the image of Andrew Fuller found
    in the employees table of the northwind database.

    I have created a data environment (DataEnvironment1) that points to the northwind
    database (Connection1), one form, and one button (Command1).

    The problem I have got is that image file saved cannot be opened.

    I suspect that I am either not transfering bytes correctly or I am saving
    some sort of ole object and not the actual data file.

    If anyone could shed some light would be appreciated.

    Dim cnn As ADODB.Connection

    Private Sub Command1_Click()
    Dim oFiled As ADODB.Field

    Dim FileLen As Long

    Dim Image() As Byte

    Set cnn = DataEnvironment1.Connection1
    Set rs = New ADODB.Recordset

    F = FreeFile
    Open "C:\temp\test.bmp" For Binary As #F

    cnn.CursorLocation = adUseServer
    cnn.Open
    SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    rs.Open SQL, cnn, adOpenStatic, adLockReadOnly

    Set oField = rs!Photo

    FileLen = oField.ActualSize
    NumBlocks = FileLen / 1024
    LeftOver = FileLen Mod 1024
    Image = oField.GetChunk(LeftOver)
    Put #F, , Image
    For Ctr = 1 To NumBlocks
    Image = oField.GetChunk(1024)
    Put #F, , Image
    Next
    Close #F
    rs.Close
    cnn.Close
    End Sub

    "D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >Justin,
    >
    > In that code snippet provided, I assume that Img is a byte array.
    >
    >Regards,
    >D. Patrick Hoerter
    >
    >
    >Justin wrote in message <395cec36$1@news.devx.com>...
    >>
    >>This code is almost what I'm chasing but I am stuck on what Img is supposed
    >>to be. It is an image control, an ole object, a file ..... hmmmm
    >>
    >>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>>Gary,
    >>>
    >>> He's trying to SAVE the image, not retrieve it.
    >>>
    >>>Chicho,
    >>>
    >>> You'll need to open the file for Binary access, and read it a chunk

    >>at a
    >>>time (say, 2K or so), and use the ADO AppendChunk method to append to

    the
    >>>field in your recordset.
    >>>
    >>>Regards,
    >>>D. Patrick Hoerter
    >>>
    >>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >>>>
    >>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>>>>
    >>>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
    >>>really
    >>>>>Know how.
    >>>>>can you help me?
    >>>>
    >>>>Once you have a recordset with the image field try something along the
    >>>lines
    >>>>of this...
    >>>>
    >>>>BlockSize = 1024
    >>>>Set oField = Rs("ImageField")
    >>>>FileLen = oField.ActualSize
    >>>>NumBlocks = FileLen / BlockSize
    >>>>LeftOver = FileLen Mod BlockSize
    >>>>'For ASP Change the Img to Response.BinaryWrite
    >>>>Img = oField.GetChunk(LeftOver)
    >>>>For Ctr = 1 to NumBlocks
    >>>> Img = Img & field.GetChunk(BlockSize)
    >>>>Next
    >>>>Rs.Close
    >>>>
    >>>>Hope this helps!
    >>>>
    >>>>Gary Johnson
    >>>>Sr Database Engineer
    >>>>
    >>>

    >>

    >



  7. #7
    D. Patrick Hoerter Guest

    Re: Save Image to Field

    Justin,

    You aren't pulling the "Leftover" bytes from the field. You process the
    whole number of chunks, but you still need to pull the last little bit,
    after the loop.

    Regards,
    D. Patrick Hoerter

    Justin wrote in message <395dec16$1@news.devx.com>...
    >
    >I'm still having problems.
    >
    >The code noted below is suppposed to save the image of Andrew Fuller found
    >in the employees table of the northwind database.
    >
    >I have created a data environment (DataEnvironment1) that points to the

    northwind
    >database (Connection1), one form, and one button (Command1).
    >
    >The problem I have got is that image file saved cannot be opened.
    >
    >I suspect that I am either not transfering bytes correctly or I am saving
    >some sort of ole object and not the actual data file.
    >
    >If anyone could shed some light would be appreciated.
    >
    >Dim cnn As ADODB.Connection
    >
    >Private Sub Command1_Click()
    > Dim oFiled As ADODB.Field
    >
    > Dim FileLen As Long
    >
    > Dim Image() As Byte
    >
    > Set cnn = DataEnvironment1.Connection1
    > Set rs = New ADODB.Recordset
    >
    > F = FreeFile
    > Open "C:\temp\test.bmp" For Binary As #F
    >
    > cnn.CursorLocation = adUseServer
    > cnn.Open
    > SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    > rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
    >
    > Set oField = rs!Photo
    >
    > FileLen = oField.ActualSize
    > NumBlocks = FileLen / 1024
    > LeftOver = FileLen Mod 1024
    > Image = oField.GetChunk(LeftOver)
    > Put #F, , Image
    > For Ctr = 1 To NumBlocks
    > Image = oField.GetChunk(1024)
    > Put #F, , Image
    > Next
    > Close #F
    > rs.Close
    > cnn.Close
    >End Sub
    >
    >"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>Justin,
    >>
    >> In that code snippet provided, I assume that Img is a byte array.
    >>
    >>Regards,
    >>D. Patrick Hoerter
    >>
    >>
    >>Justin wrote in message <395cec36$1@news.devx.com>...
    >>>
    >>>This code is almost what I'm chasing but I am stuck on what Img is

    supposed
    >>>to be. It is an image control, an ole object, a file ..... hmmmm
    >>>
    >>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>>>Gary,
    >>>>
    >>>> He's trying to SAVE the image, not retrieve it.
    >>>>
    >>>>Chicho,
    >>>>
    >>>> You'll need to open the file for Binary access, and read it a chunk
    >>>at a
    >>>>time (say, 2K or so), and use the ADO AppendChunk method to append to

    >the
    >>>>field in your recordset.
    >>>>
    >>>>Regards,
    >>>>D. Patrick Hoerter
    >>>>
    >>>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >>>>>
    >>>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>>>>>
    >>>>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
    >>>>really
    >>>>>>Know how.
    >>>>>>can you help me?
    >>>>>
    >>>>>Once you have a recordset with the image field try something along the
    >>>>lines
    >>>>>of this...
    >>>>>
    >>>>>BlockSize = 1024
    >>>>>Set oField = Rs("ImageField")
    >>>>>FileLen = oField.ActualSize
    >>>>>NumBlocks = FileLen / BlockSize
    >>>>>LeftOver = FileLen Mod BlockSize
    >>>>>'For ASP Change the Img to Response.BinaryWrite
    >>>>>Img = oField.GetChunk(LeftOver)
    >>>>>For Ctr = 1 to NumBlocks
    >>>>> Img = Img & field.GetChunk(BlockSize)
    >>>>>Next
    >>>>>Rs.Close
    >>>>>
    >>>>>Hope this helps!
    >>>>>
    >>>>>Gary Johnson
    >>>>>Sr Database Engineer
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    Justin Guest

    Re: Save Image to Field


    Good try, but I don't think so. I am actually grabbing the left over bytes
    first, before I run the loop, I apologise for the sloppy coding. Just to
    be sure I checked to see if it would make a difference be grabbing full blocks
    first and then the remaining bits, but sadly no... The upshot of it all
    is that when I manually save the image using access, I get different file
    sizes. Well sort of. I am not sure what the properties window of Win 98
    is telling me but here it is.

    My coding saves the picture as:
    Size: 21.1KB (21,626 bytes), 24,576 bytes used

    Manually saving the picture gives:
    Size: 21.0KB (21,536 bytes), 24,576 bytes used

    It appears that I am writing too many bytes!!

    But here is something that is even weirder,
    I delete the image found in the northwind database assigned to Fuller, and
    add in a jpg image photo.jpg using access 97. I note the file sizes of the
    database Nwind.mdb. They are as follows:

    Nwind database with bitmap picture deleted:
    Size: 1.53MB (1,607,680 bytes), 1,613,824 bytes used

    Nwind database with jpg picture added:
    Size: 2.00MB (2,097,152 bytes), 2,097,152 bytes used

    I run the program again (I changed the code to save test.jpg instead of test.bmp)
    and I get the following results:

    My coding saves the picture as:
    Size: 541KB (554,822 bytes), 557,056 bytes used

    The original picture size is:
    Size: 8.03KB (8,229 bytes), 16,384 bytes used

    As you can see this is a big difference in file sizes, but here is something
    to consider, the database increases in size by approximately half a meg and
    the file extracted from the database is also approximately half a meg, yet
    the file added is only 8k! My hypothesis is that the data that is stored
    in the ole field is not just a binary file but instead a serialised object.
    In my particular case it would be a Microsoft photo-editor object, and unfortunately
    it is not taking advantage of the compression algorithms used in JPEG files.

    I am now going to write a program that is going to use the append chunk function
    and write a file directly to an ole field and then extract it back using
    the same program below. I suspect this will work as I am not using the insert
    object dialog box in access.

    However, this doesn't yet solve my original problem, how on earth do you
    get an embedded file out of an ole record and display it in an ole control
    using ADO.

    Please don't give up trying to help me solve this problem because I am well
    and truly stumped.

    "D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >Justin,
    >
    > You aren't pulling the "Leftover" bytes from the field. You process

    the
    >whole number of chunks, but you still need to pull the last little bit,
    >after the loop.
    >
    >Regards,
    >D. Patrick Hoerter
    >
    >Justin wrote in message <395dec16$1@news.devx.com>...
    >>
    >>I'm still having problems.
    >>
    >>The code noted below is suppposed to save the image of Andrew Fuller found
    >>in the employees table of the northwind database.
    >>
    >>I have created a data environment (DataEnvironment1) that points to the

    >northwind
    >>database (Connection1), one form, and one button (Command1).
    >>
    >>The problem I have got is that image file saved cannot be opened.
    >>
    >>I suspect that I am either not transfering bytes correctly or I am saving
    >>some sort of ole object and not the actual data file.
    >>
    >>If anyone could shed some light would be appreciated.
    >>
    >>Dim cnn As ADODB.Connection
    >>
    >>Private Sub Command1_Click()
    >> Dim oFiled As ADODB.Field
    >>
    >> Dim FileLen As Long
    >>
    >> Dim Image() As Byte
    >>
    >> Set cnn = DataEnvironment1.Connection1
    >> Set rs = New ADODB.Recordset
    >>
    >> F = FreeFile
    >> Open "C:\temp\test.bmp" For Binary As #F
    >>
    >> cnn.CursorLocation = adUseServer
    >> cnn.Open
    >> SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    >> rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
    >>
    >> Set oField = rs!Photo
    >>
    >> FileLen = oField.ActualSize
    >> NumBlocks = FileLen / 1024
    >> LeftOver = FileLen Mod 1024
    >> Image = oField.GetChunk(LeftOver)
    >> Put #F, , Image
    >> For Ctr = 1 To NumBlocks
    >> Image = oField.GetChunk(1024)
    >> Put #F, , Image
    >> Next
    >> Close #F
    >> rs.Close
    >> cnn.Close
    >>End Sub
    >>
    >>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>>Justin,
    >>>
    >>> In that code snippet provided, I assume that Img is a byte array.
    >>>
    >>>Regards,
    >>>D. Patrick Hoerter
    >>>
    >>>
    >>>Justin wrote in message <395cec36$1@news.devx.com>...
    >>>>
    >>>>This code is almost what I'm chasing but I am stuck on what Img is

    >supposed
    >>>>to be. It is an image control, an ole object, a file ..... hmmmm
    >>>>
    >>>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >>>>>Gary,
    >>>>>
    >>>>> He's trying to SAVE the image, not retrieve it.
    >>>>>
    >>>>>Chicho,
    >>>>>
    >>>>> You'll need to open the file for Binary access, and read it a chunk
    >>>>at a
    >>>>>time (say, 2K or so), and use the ADO AppendChunk method to append to

    >>the
    >>>>>field in your recordset.
    >>>>>
    >>>>>Regards,
    >>>>>D. Patrick Hoerter
    >>>>>
    >>>>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
    >>>>>>
    >>>>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
    >>>>>>>
    >>>>>>>Hello, Iam trying to save an image to a Field in a Recordset, but

    dont
    >>>>>really
    >>>>>>>Know how.
    >>>>>>>can you help me?
    >>>>>>
    >>>>>>Once you have a recordset with the image field try something along

    the
    >>>>>lines
    >>>>>>of this...
    >>>>>>
    >>>>>>BlockSize = 1024
    >>>>>>Set oField = Rs("ImageField")
    >>>>>>FileLen = oField.ActualSize
    >>>>>>NumBlocks = FileLen / BlockSize
    >>>>>>LeftOver = FileLen Mod BlockSize
    >>>>>>'For ASP Change the Img to Response.BinaryWrite
    >>>>>>Img = oField.GetChunk(LeftOver)
    >>>>>>For Ctr = 1 to NumBlocks
    >>>>>> Img = Img & field.GetChunk(BlockSize)
    >>>>>>Next
    >>>>>>Rs.Close
    >>>>>>
    >>>>>>Hope this helps!
    >>>>>>
    >>>>>>Gary Johnson
    >>>>>>Sr Database Engineer
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    Justin Guest

    Re: Save Image to Field


    Wahoo,
    I am finally getting somewhere,
    As I suspected the OLE field that I was attempting to write to file was infact
    a serialised object and not simply a binary file.

    I can store a Long Binary Object in an OLE field using the appendchunk method,
    thus storing a file in the database in its original format. ie: If I write
    an 8k jpg file to the database using appendchunk, then I am simply storing
    a whole heap of one's and zero's to total storage space of 8k! and not 512K
    as an OLE Photo Editor Object.

    When I use the GetChunk Method I am simply transferring those one's and zero's
    back to a binary file. Thus maintaining the original format.

    There are a couple of issues to note:
    1. The file stored in the OLE field can no longer be accessed in Access with
    an OLE control in the usual fashion as it is looking for an OLE Object and
    not a binary file.
    2. If the OLE field in the database has already been stretched by a larger
    object and you simply update the field with a smaller one then you will get
    two end of file symbols in that binary field. When you write the field back
    to disk the file size will be as large as the field. This should not affect
    things however as most programs will read the file until it reaches the first
    end of file symbol. To ensure the database remains compact, I would suggest
    trying to delete the contents of the field first, then updating the field
    with new file.

    Additionally, the previous code issued in the last message had a few logic
    errors in it. Due to the way VB handles rounding in it's divisor and mod
    operators, inconsistencies with calculations in establishing the number of
    blocks and the amount of data left over were found. I have resolved this
    problem by using integer division and doing away with the mod operator and
    using instead simple operators.

    The test application is as follows:
    I created one form with two buttons: Save and Load
    The save button saves a jpg image "Photo.jpg" to Andrew Fullers photo field
    in the Nwind database.
    The load button loads the Long Binary Object from Andrew Fullers photo field
    and saves it as "test.jpg".
    I created one data environment called "dnvMainDataBase"
    and one connection that points to the Nwind database "cnnNorthWind".

    My Code is as follows:
    Code Behind Form1 -

    Dim cnn As ADODB.Connection

    Private Sub cmdLoad_Click()
    Set cnn = dnvMainDatabase.cnnNorthWind
    Set rs = New ADODB.Recordset
    cnn.Open
    cnn.CursorLocation = adUseServer
    SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    FileToBlob "c:\temp\photo.jpg", rs!Photo
    rs.Update
    MsgBox "The jpg file has updated Andrew Fullers photo"
    rs.Close
    cnn.Close
    End Sub

    Private Sub cmdSave_Click()
    Set cnn = dnvMainDatabase.cnnNorthWind
    Set rs = New ADODB.Recordset
    cnn.CursorLocation = adUseServer
    cnn.Open

    SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"

    rs.Open SQL, cnn, adOpenStatic, adLockReadOnly

    BlobToFile rs!Photo, "c:\temp\test.jpg"

    rs.Close
    cnn.Close
    End Sub

    Code Behind Module1-

    Option Explicit

    Sub BlobToFile(fld As ADODB.Field, _
    ByVal FName As String)
    '
    ' Assumes file does not exist and write access
    ' to the path is granted (Something to note with
    ' NT based systems}.
    ' Data cannot exceed approx. 2Gb in size.
    '
    Dim F As Long, bData() As Byte, sData As String
    F = FreeFile
    Open FName For Binary As #F
    If fld.Type = adLongVarBinary Then
    WriteFromBinary F, fld, fld.ActualSize
    Else
    MsgBox "This is not an OLE Field"
    End If
    Close #F
    End Sub

    Sub WriteFromBinary(ByVal F As Long, _
    fld As ADODB.Field, _
    FieldSize As Long)
    Dim Data() As Byte
    Dim NumBlocks As Long
    Dim LeftOver As Long
    Dim Ctr As Long
    NumBlocks = FieldSize \ 16384
    LeftOver = FieldSize - NumBlocks * 16384
    If NumBlocks > 1 Then
    For Ctr = 1 To NumBlocks
    Data = fld.GetChunk(16384)
    Put #F, , Data
    Next
    End If
    Data = fld.GetChunk(LeftOver)
    Put #F, , Data
    End Sub

    Sub FileToBlob(ByVal FName As String, fld As ADODB.Field)
    '
    ' Assumes file exists and can be accessed
    ' Assumes calling routine does the UPDATE
    ' File cannot exceed approx. 2Gb in size
    '
    Dim F As Long, Data() As Byte, FileSize As Long
    F = FreeFile
    Open FName For Binary As #F
    FileSize = LOF(F)
    If fld.Type = adLongVarBinary Then
    ReadToBinary F, fld, FileSize
    Else
    MsgBox "This is not an OLE field"
    End If
    Close #F
    End Sub

    Sub ReadToBinary(ByVal F As Long, _
    fld As ADODB.Field, _
    ByVal FileSize As Long)
    Dim Data() As Byte
    Dim NumBlocks As Long
    Dim LeftOver As Long
    Dim Ctr As Long
    NumBlocks = FileSize \ 16384
    LeftOver = FileSize - NumBlocks * 16384
    If NumBlocks > 1 Then
    For Ctr = 1 To NumBlocks
    Data = InputB(16384, F)
    fld.AppendChunk Data
    Next
    End If
    Data = InputB(LeftOver, F)
    fld.AppendChunk Data
    End Sub



  10. #10
    Justin Simpson Guest

    Re: Save Image to Field

    NNTP-Posting-Host: 209.1.14.192
    Message-ID: <39658f32$1@news.devx.com>
    Date: 7 Jul 2000 01:05:06 -0700
    X-Trace: 7 Jul 2000 01:05:06 -0700, 209.1.14.192
    Lines: 210
    Path: news.devx.com!209.1.14.192
    Xref: news.devx.com vb.database.ado:5682


    Someone asked me how do you create the data environment mentioned below.

    Here it is:

    Adding a Data Environment Designer to a VB Project

    1. Choose File -> New Project
    2. Double-click the Standard EXE icon to start a typical, traditional VB
    Project
    3. Choose Project->More ActiveX Designers->Add Data Environment

    If you don't see Add Data Environment continue on. If you do see Add Data
    Environment Skip to Step 8.

    4 Choose Project->Components
    5. Click the Designers tab.
    6. Click the check box next to Data Environment.
    7. Click OK

    8. Now you can choose Project->Add Data Environment
    9. Change the Data Environment Name to dnvMainDataBase
    10. Change Connection1 to cnnNorthWind
    11. Right click the connection object.
    12. Click Properties on the context menu
    13. On the provider tab in the data link properties dialog box, click
    Microsoft Jet 4 OLE DB Provider (or use whatever version is available.)
    14. Click the Connection Tab
    15. Click the button with the three dots on the Connections Tab.
    16. Browse your hard drive until you locate the datbase that you want to
    use- ie Nwind.mdb
    17. Click OK.

    Data Environment is now created (phew!!)

    Recomended book:
    Visual Basic 6 Database Programming for Dummies
    ISBN: 0-7645-0625-0




    "Justin" <baseline2600@yahoo.com> wrote:
    >
    >Wahoo,
    >I am finally getting somewhere,
    >As I suspected the OLE field that I was attempting to write to file was

    infact
    >a serialised object and not simply a binary file.
    >
    >I can store a Long Binary Object in an OLE field using the appendchunk method,
    >thus storing a file in the database in its original format. ie: If I write
    >an 8k jpg file to the database using appendchunk, then I am simply storing
    >a whole heap of one's and zero's to total storage space of 8k! and not 512K
    >as an OLE Photo Editor Object.
    >
    >When I use the GetChunk Method I am simply transferring those one's and

    zero's
    >back to a binary file. Thus maintaining the original format.
    >
    >There are a couple of issues to note:
    >1. The file stored in the OLE field can no longer be accessed in Access

    with
    >an OLE control in the usual fashion as it is looking for an OLE Object and
    >not a binary file.
    >2. If the OLE field in the database has already been stretched by a larger
    >object and you simply update the field with a smaller one then you will

    get
    >two end of file symbols in that binary field. When you write the field

    back
    >to disk the file size will be as large as the field. This should not affect
    >things however as most programs will read the file until it reaches the

    first
    >end of file symbol. To ensure the database remains compact, I would suggest
    >trying to delete the contents of the field first, then updating the field
    >with new file.
    >
    >Additionally, the previous code issued in the last message had a few logic
    >errors in it. Due to the way VB handles rounding in it's divisor and mod
    >operators, inconsistencies with calculations in establishing the number

    of
    >blocks and the amount of data left over were found. I have resolved this
    >problem by using integer division and doing away with the mod operator and
    >using instead simple operators.
    >
    >The test application is as follows:
    >I created one form with two buttons: Save and Load
    >The save button saves a jpg image "Photo.jpg" to Andrew Fullers photo field
    >in the Nwind database.
    >The load button loads the Long Binary Object from Andrew Fullers photo field
    >and saves it as "test.jpg".
    >I created one data environment called "dnvMainDataBase"
    >and one connection that points to the Nwind database "cnnNorthWind".
    >
    >My Code is as follows:
    >Code Behind Form1 -
    >
    >Dim cnn As ADODB.Connection
    >
    >Private Sub cmdLoad_Click()
    > Set cnn = dnvMainDatabase.cnnNorthWind
    > Set rs = New ADODB.Recordset
    > cnn.Open
    > cnn.CursorLocation = adUseServer
    > SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    > rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
    > FileToBlob "c:\temp\photo.jpg", rs!Photo
    > rs.Update
    > MsgBox "The jpg file has updated Andrew Fullers photo"
    > rs.Close
    > cnn.Close
    >End Sub
    >
    >Private Sub cmdSave_Click()
    > Set cnn = dnvMainDatabase.cnnNorthWind
    > Set rs = New ADODB.Recordset
    > cnn.CursorLocation = adUseServer
    > cnn.Open
    >
    > SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
    >
    > rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
    >
    > BlobToFile rs!Photo, "c:\temp\test.jpg"
    >
    > rs.Close
    > cnn.Close
    >End Sub
    >
    >Code Behind Module1-
    >
    >Option Explicit
    >
    >Sub BlobToFile(fld As ADODB.Field, _
    > ByVal FName As String)
    > '
    > ' Assumes file does not exist and write access
    > ' to the path is granted (Something to note with
    > ' NT based systems}.
    > ' Data cannot exceed approx. 2Gb in size.
    > '
    > Dim F As Long, bData() As Byte, sData As String
    > F = FreeFile
    > Open FName For Binary As #F
    > If fld.Type = adLongVarBinary Then
    > WriteFromBinary F, fld, fld.ActualSize
    > Else
    > MsgBox "This is not an OLE Field"
    > End If
    > Close #F
    >End Sub
    >
    >Sub WriteFromBinary(ByVal F As Long, _
    > fld As ADODB.Field, _
    > FieldSize As Long)
    > Dim Data() As Byte
    > Dim NumBlocks As Long
    > Dim LeftOver As Long
    > Dim Ctr As Long
    > NumBlocks = FieldSize \ 16384
    > LeftOver = FieldSize - NumBlocks * 16384
    > If NumBlocks > 1 Then
    > For Ctr = 1 To NumBlocks
    > Data = fld.GetChunk(16384)
    > Put #F, , Data
    > Next
    > End If
    > Data = fld.GetChunk(LeftOver)
    > Put #F, , Data
    >End Sub
    >
    >Sub FileToBlob(ByVal FName As String, fld As ADODB.Field)
    > '
    > ' Assumes file exists and can be accessed
    > ' Assumes calling routine does the UPDATE
    > ' File cannot exceed approx. 2Gb in size
    > '
    > Dim F As Long, Data() As Byte, FileSize As Long
    > F = FreeFile
    > Open FName For Binary As #F
    > FileSize = LOF(F)
    > If fld.Type = adLongVarBinary Then
    > ReadToBinary F, fld, FileSize
    > Else
    > MsgBox "This is not an OLE field"
    > End If
    > Close #F
    >End Sub
    >
    >Sub ReadToBinary(ByVal F As Long, _
    > fld As ADODB.Field, _
    > ByVal FileSize As Long)
    > Dim Data() As Byte
    > Dim NumBlocks As Long
    > Dim LeftOver As Long
    > Dim Ctr As Long
    > NumBlocks = FileSize \ 16384
    > LeftOver = FileSize - NumBlocks * 16384
    > If NumBlocks > 1 Then
    > For Ctr = 1 To NumBlocks
    > Data = InputB(16384, F)
    > fld.AppendChunk Data
    > Next
    > End If
    > Data = InputB(LeftOver, F)
    > fld.AppendChunk Data
    >End Sub
    >
    >



  11. #11
    Douglas J. Steele Guest

    Re:

    As I told you in the Microsoft forum (microsoft.public.vb.database.ado), it
    can't be done.

    When you run from VB, your only connection to the database is through the
    Jet engine, which doesn't know about user-defined functions.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "George Handlin" <handling@nospam.home.com> wrote in message
    news:3ae1445e@news.devx.com...
    > How do I get the results from an Access (2k) query that uses a custom
    > function
    > (in the db) in VB6? The query runs perfectly in Access, but I get an
    > Undefined Function in Expression error when trying to access it from VB.
    > Tried it as both a straight recordset and as the result from a command
    > object. I've tried having the function in both the mdb and VB, neither
    > worked.
    >
    > Thanx...
    >
    > --
    > -George Handlin, MCP
    > http://www.jbconline.com
    > ref: 587968
    > icq: 50614665
    >
    >




  12. #12
    George Handlin Guest

    Re:

    Thanx...

    "Douglas J. Steele" <djsteele@canada.com> wrote in message
    news:3ae15ca5@news.devx.com...
    > As I told you in the Microsoft forum (microsoft.public.vb.database.ado),

    it
    > can't be done.
    >
    > When you run from VB, your only connection to the database is through the
    > Jet engine, which doesn't know about user-defined functions.
    >
    > --
    >
    > Doug Steele, Microsoft Access MVP
    > Beer, Wine and Database Programming. What could be better?
    > Visit "Doug Steele's Beer and Programming Emporium"
    > http://I.Am/DougSteele/
    >
    >
    > "George Handlin" <handling@nospam.home.com> wrote in message
    > news:3ae1445e@news.devx.com...
    > > How do I get the results from an Access (2k) query that uses a custom
    > > function
    > > (in the db) in VB6? The query runs perfectly in Access, but I get an
    > > Undefined Function in Expression error when trying to access it from VB.
    > > Tried it as both a straight recordset and as the result from a command
    > > object. I've tried having the function in both the mdb and VB, neither
    > > worked.
    > >
    > > Thanx...
    > >
    > > --
    > > -George Handlin, MCP
    > > http://www.jbconline.com
    > > ref: 587968
    > > icq: 50614665
    > >
    > >

    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center